Uses a PL/SQL Nested Block : Code Block « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Code Block

SQL>
SQL>
SQL> CREATE TABLE myItemTable (
  2     item_code varchar2(6) PRIMARY KEY,
  3     item_descr varchar2(20) NOT NULL);

Table created.

SQL>
SQL>
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 :='Java2s';
  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>
SQL> select * from myItemTable;

ITEM_C ITEM_DESCR
------ --------------------
Java2s a website for Oracle

1 row selected.

SQL>
SQL> drop table myItemTable;

Table dropped.

SQL>
SQL>