|
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.
|