Form sentences from database data : Cursor Value « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor Value

Form sentences from database data

    
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>
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>
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> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('4', 'Database', 'SQL', 535, 39.99, 2002, 4, 5, 9);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('5', 'Database', 'Java', 487, 39.99, 2002, 10, 11);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2)
  2             VALUES ('6', 'Database', 'JDBC', 592, 39.99, 2002, 12, 13);

1 row created.

SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
  2             VALUES ('7', 'Database', 'XML', 500, 39.99, 2002, 1, 2, 3);

1 row created.

SQL>
SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     v_fname 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        OR a.id = b.emp2
 12        OR a.id = b.emp3
 13        GROUP BY a.fname, a.lname
 14        HAVING count(b.title) > 0
 15        ORDER BY a.lname;
 16
 17  BEGIN
 18
 19     DBMS_OUTPUT.ENABLE(1000000);
 20
 21     OPEN auth_cur;
 22     LOOP
 23        FETCH auth_cur INTO v_fname, lnameValue, v_row_count;
 24        EXIT WHEN auth_cur%NOTFOUND;
 25
 26        DBMS_OUTPUT.PUT_LINE(lnameValue||','||v_fname||' wrote '||v_row_count||' book(s).');
 27     END LOOP;
 28
 29     CLOSE auth_cur;
 30
 31  EXCEPTION
 32     WHEN OTHERS
 33        THEN
 34        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 35  END;
 36  /
B,A wrote 2 book(s).
D,C wrote 2 book(s).
F,Enn wrote 2 book(s).
H,G wrote 2 book(s).
Z,G wrote 2 book(s).

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL> drop table emp;

Table dropped.

   
    
    
    
  

Related examples in the same category