Use cursor variables : Cursor Declaration « Cursor « Oracle PL/SQL Tutorial

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

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>
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>
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>
SQL> drop table books;

Table dropped.

SQL>