Read full table data from a cursor : Cursor Value « Cursor « Oracle PL / SQL






Read full table data from a cursor

    
SQL>
SQL>
SQL> CREATE TABLE gifts (
  2    gift_id             INTEGER          CONSTRAINT gifts_pk PRIMARY KEY,
  3    gift_type_id        INTEGER          ,
  4    name                   VARCHAR2(30)     NOT NULL,
  5    description            VARCHAR2(50),
  6    price                  NUMBER(5, 2)
  7  );

Table created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, 'Flower', 'Birthday', 19.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, 'Computer', 'New Year', 30.00);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, 'iPod', 'Birthday', 25.99);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, 'iPhone', 'New Year', 13.95);

1 row created.

SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, 'Book', 'Birthday', 49.99);

1 row created.

SQL>
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    CURSOR giftCursor IS SELECT gift_id, name, price FROM gifts ORDER BY gift_id;
  3  BEGIN
  4    FOR v_gift IN giftCursor LOOP
  5      DBMS_OUTPUT.PUT_LINE('gift_id = ' || v_gift.gift_id ||', name = ' || v_gift.name ||', price = ' || v_gift.price);
  6    END LOOP;
  7  END;
  8  /
gift_id = 1, name = Flower, price = 19.95
gift_id = 2, name = Computer, price = 30
gift_id = 3, name = iPod, price = 25.99
gift_id = 4, name = iPhone, price = 13.95
gift_id = 5, name = Book, price = 49.99

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table gifts;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Cursor with a single value
2.Update statement with cursor variable
3.Server-side cursor variables.
4.A cursor FOR loop.
5.Cursor Variable Example 2
6.Single column cursor
7.Use Complex cursor to simplify the pl/sql logic
8.Use cursor to do full table scan
9.Use data referenced by cursor to update table
10.Use number functions when creating a cursor value
11.Form sentences from database data
12.Two identical queries to demonstrate the impact of changes