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






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>








25.2.Cursor Declaration
25.2.1.Declare cursor
25.2.2.VARRAY of Cursor
25.2.3.Select column value into a cursor variable
25.2.4.Use cursor variables
25.2.5.Use the cursor subquery
25.2.6.Returning more than one piece of information: Listing the variables separately
25.2.7.Defining a Record Type for a Cursor by Using %ROWTYPE
25.2.8.Declaring a Cursor within a Procedure
25.2.9.Defining a Cursor in an Anonymous PL/SQL Block
25.2.10.Defining cursors in the package body
25.2.11.Defining cursors in the package specification
25.2.12.Using SELECT * in a Cursor
25.2.13.Cursor for object table
25.2.14.Cursor with order by
25.2.15.Cursor rowid
25.2.16.Cursor for aggregate function