Mega Code Archive
REF CURSOR custom type
<![CDATA[
SQL> CREATE TABLE book (
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 book (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 book (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 book (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> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2
3 TYPE book_typ IS REF CURSOR RETURN book%ROWTYPE;
4 cv_book book_typ;
5 v_book book%ROWTYPE;
6
7 BEGIN
8
9 DBMS_OUTPUT.ENABLE(1000000);
10
11 OPEN cv_book FOR SELECT * FROM book WHERE isbn = '1';
12
13 FETCH cv_book INTO v_book;
14
15 DBMS_OUTPUT.PUT_LINE(v_book.title||' is '||v_book.price);
16
17 CLOSE cv_book;
18 END;
19 /
Oracle is 39.99
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.
]]>