A Nested Block Example : Begin End Block « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Begin End Block

A Nested Block Example

  
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> --

   
  

Related examples in the same category