Mega Code Archive
Use cursor variables
<![CDATA[
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 );
Table created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright)
2 VALUES ('72121203', 'Oracle Basics', 'Oracle DBA 101', 563, 39.99, 1999);
1 row created.
SQL>
SQL> INSERT INTO books (isbn, category, title, num_pages, price, copyright)
2 VALUES ('72122048', 'Oracle Basics', 'Oracle8i: A Beginner''s Guide', 765, 44.99, 1999);
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2
3 TYPE book_typ IS REF CURSOR RETURN BOOKS%ROWTYPE;
4 cv_books book_typ;
5 v_books BOOKS%ROWTYPE;
6
7 BEGIN
8
9 DBMS_OUTPUT.ENABLE(1000000);
10
11 OPEN cv_books FOR
12 SELECT *
13 FROM books
14 WHERE isbn = '72121203';
15
16 FETCH cv_books INTO v_books;
17
18 DBMS_OUTPUT.PUT_LINE(v_books.title||' is '||v_books.price);
19
20 CLOSE cv_books;
21 END;
22 /
Oracle DBA 101 is 39.99
PL/SQL procedure successfully completed.
SQL>
SQL> drop table books;
Table dropped.
SQL>
]]>