Raise exception in if statement : Exception Handle « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Exception Handle

Raise exception in if statement

    
SQL> CREATE TABLE book (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );

Table created.

SQL>
SQL> CREATE TABLE log_table(
  2    info     VARCHAR2(100));

Table created.

SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('1', 'Database', 'Oracle', 563, 39.99, 1999, 1, 2, 3);

1 row created.

SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('2', 'Database', 'MySQL', 765, 44.99, 1999, 4, 5);

1 row created.

SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('3', 'Database', 'SQL Server', 404, 39.99, 2001, 6, 7, 8);

1 row created.

SQL>
SQL>
SQL> DECLARE
  2    
  3    e_Duplicateemp EXCEPTION;
  4
  5    
  6    v_emp1 book.emp1%TYPE;
  7    v_emp2 book.emp2%TYPE;
  8    v_emp3 book.emp3%TYPE;
  9  BEGIN
 10    SELECT emp1, emp2, emp3 INTO v_emp1, v_emp2, v_emp3 FROM book WHERE title = 'XML';
 11
 12    IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) THEN
 13       RAISE e_Duplicateemp;
 14    END IF;
 15  EXCEPTION
 16    WHEN e_Duplicateemp THEN
 17      INSERT INTO log_table (info)VALUES ('XML has duplicate emp');
 18    WHEN OTHERS THEN
 19      INSERT INTO log_table (info) VALUES ('Another error occurred');
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table log_table;

Table dropped.

SQL>
SQL> drop table book;

Table dropped.

   
    
    
    
  

Related examples in the same category