Mega Code Archive
A Nested Block Example
<![CDATA[
SQL>
SQL> CREATE TABLE items_tab (item_code varchar2(6) PRIMARY KEY,
2 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 v_item_code := 'ITM101';
7 v_item_descr := 'Spare parts';
8 BEGIN
9 SELECT 1
10 INTO v_num
11 FROM items_tab
12 WHERE item_code = v_item_code;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 v_num := 0;
16 WHEN OTHERS THEN
17 dbms_output.put_line('Error in SELECT: '||SQLERRM);
18 RETURN;
19 END;
20 IF (v_num = 0) THEN
21 INSERT INTO items_tab VALUES (v_item_code, v_item_descr);
22 END IF;
23 dbms_output.put_line('Successful Completion' ) ;
24 EXCEPTION WHEN OTHERS THEN
25 dbms_output.put_line(SQLERRM);
26 END;
27 /
Successful Completion
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from items_tab;
ITEM_C ITEM_DESCR
------ --------------------
ITM101 Spare parts
1 row selected.
SQL>
SQL> drop table items_tab;
Table dropped.
SQL>
SQL> --
]]>