Parameterized cursor : Cursor Parameter « Cursor « Oracle PL/SQL Tutorial

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

SQL>
SQL> CREATE TABLE books(
  2    book_id  NUMBER  NOT NULL  PRIMARY KEY,
  3    title  VARCHAR2(200),
  4    author  VARCHAR2(200) );

Table created.

SQL>
SQL> INSERT INTO books VALUES(1, 'Oracle SQL*Plus', 'GENNICK,JONATHAN');

1 row created.

SQL>
SQL> INSERT INTO books VALUES(2, 'Oracle PL/SQL Programming', 'FEUERSTEIN,STEVEN');

1 row created.

SQL>
SQL> INSERT INTO books VALUES(3, 'Oracle Built-in Packages', 'FEUERSTEIN,STEVEN');

1 row created.

SQL>
SQL> set serveroutput on size 500000
SQL>
SQL> DECLARE
  2
  3     CURSOR books_cur(author_in IN books.author%TYPE) IS
  4     SELECT *
  5       FROM books
  6      WHERE author = author_in;
  7
  8     book_count PLS_INTEGER;
  9
 10  BEGIN
 11
 12     FOR book_rec IN books_cur (author_in => 'FEUERSTEIN, STEVEN')
 13     LOOP
 14        -- ... process data ...
 15        book_count := books_cur%ROWCOUNT;
 16     END LOOP;
 17
 18     IF book_count > 10
 19     THEN
 20       dbms_output.put_line('Lotsa books, time for vacation.');
 21     ELSE
 22       dbms_output.put_line('Keep writing slacker.');
 23     END IF;
 24
 25  END;
 26  /
Keep writing slacker.

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> DROP TABLE books;

Table dropped.