SQL>
SQL> CREATE TABLE myItemTable (
2 item_code varchar2(6) PRIMARY KEY,
3 item_descr varchar2(20) NOT NULL);
Table created.
SQL>
SQL> DECLARE
2 v_item_code VARCHAR2(6);
3 v_item_descr VARCHAR2(20);
4 v_num NUMBER(1);
5 BEGIN
6
7 v_item_code :='rntsoft';
8
9 v_item_descr :='a website for Oracle';
10
11 BEGIN
12
13 SELECT 1 INTO v_num FROM myItemTable WHERE item_code =v_item_code;
14
15 EXCEPTION
16
17 WHEN NO_DATA_FOUND THEN
18 v_num :=0;
19
20 WHEN OTHERS THEN
21 dbms_output.put_line('Error in SELECT:'||SQLERRM);
22 RETURN;
23 END;
24
25 IF (v_num =0)THEN
26
27 INSERT INTO myItemTable VALUES (v_item_code,v_item_descr);
28
29 END IF;
30 dbms_output.put_line('Successful Completion');
31 EXCEPTION WHEN OTHERS THEN
32 dbms_output.put_line(SQLERRM);
33 END;
34 /
Successful Completion
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myItemTable;
ITEM_C ITEM_DESCR
------ --------------------
rntsoft a website for Oracle
1 row selected.
SQL>
SQL> drop table myItemTable;
Table dropped.
SQL>