Loop till exit when cursorName%notfound : LOOP « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. LOOP

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'E', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> CREATE TABLE books (
  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> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('72121203', 'Oracle Basics', 'Oracle DBA 101', 563, 39.99, 2009, 1, 2, 3);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('72122048', 'Oracle Basics', 'Oracle8i: A Beginner''s Guide', 765, 44.99, 2009, 4, 5);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('72131454', 'Oracle Basics', 'Oracle Performance Tuning 101', 404, 39.99, 2001, 6, 7, 8);

1 row created.



SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     cv_emp SYS_REFCURSOR;
  4     v_title BOOKS.TITLE%TYPE;
  5     v_emp emp%ROWTYPE;
  6     v_counter PLS_INTEGER := 0;
  7
  8     CURSOR book_cur
  9     IS
 10        SELECT b.title, CURSOR (SELECT * FROM emp a WHERE a.id = b.emp1 OR a.id = b.emp2 OR a.id = b.emp3)
 11        FROM books b;
 12
 13  BEGIN
 14
 15     DBMS_OUTPUT.ENABLE(1000000);
 16
 17     OPEN book_cur;
 18
 19     LOOP
 20        FETCH book_cur INTO v_title, cv_emp;
 21        EXIT WHEN book_cur%NOTFOUND;
 22
 23        v_counter := 0;
 24
 25        DBMS_OUTPUT.PUT_LINE('Title from the main cursor: '||v_title);
 26
 27        LOOP
 28           FETCH cv_emp INTO v_emp;
 29           EXIT WHEN cv_emp%NOTFOUND;
 30
 31           v_counter := v_counter + 1;
 32
 33           DBMS_OUTPUT.PUT_LINE('emp'||v_counter||': '||v_emp.fname||' '||v_emp.lname);
 34        END LOOP;
 35     END LOOP;
 36
 37     CLOSE book_cur;
 38
 39  END;
 40  /
Title from the main cursor: Oracle DBA 101
emp1: E F
emp2: C D
emp3: A B
Title from the main cursor: Oracle8i: A Beginner's Guide
emp1: G Z
emp2: G H
Title from the main cursor: Oracle Performance Tuning 101

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL> drop table emp;

Table dropped.