Mega Code Archive

 

Categories / Oracle PLSQL / Cursor

 

Use Complex cursor to simplify the plsql logic

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, 'Enn', '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 ('1', 'Database', 'Oracle', 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 ('2', 'Database', 'MySQL', 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 ('3', 'Database', 'SQL Server', 404, 39.99, 2001, 6, 7, 8); 1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE emp_book_count   2  AS   3     fnameValue emp.fname%TYPE;   4     lnameValue emp.lname%TYPE;   5     v_row_count PLS_INTEGER := 0;   6   7     CURSOR auth_cur IS   8        SELECT a.fname, a.lname, count(b.title)   9        FROM emp a, books b  10        WHERE a.id = b.emp1  11        GROUP BY a.fname, a.lname  12        HAVING count(b.title) > 0  13        ORDER BY a.lname;  14  15  BEGIN  16  17     DBMS_OUTPUT.ENABLE(1000000);  18  19     OPEN auth_cur;  20     LOOP  21        FETCH auth_cur INTO fnameValue, lnameValue, v_row_count;  22        EXIT WHEN auth_cur%NOTFOUND;  23  24        DBMS_OUTPUT.PUT_LINE(lnameValue||','||fnameValue||' wrote '||v_row_count||' book(s).');  25     END LOOP;  26  27     CLOSE auth_cur;  28  29  EXCEPTION  30     WHEN OTHERS  31        THEN  32        DBMS_OUTPUT.PUT_LINE(SQLERRM);  33  END;  34  / Procedure created. SQL> show errors No errors. SQL> drop table books; Table dropped. SQL> drop table emp; Table dropped.