Cursor for object table : Cursor Declaration « Cursor « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||' '||SELF.line2||' '||SELF.city||', '||SELF.state_code||' '||SELF.zip);
  6    END get_address;
  7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  8                  addressLine2 VARCHAR2,
  9                  address_city VARCHAR2,
 10                  address_state VARCHAR2,
 11                  address_zip VARCHAR2)
 12    IS
 13    BEGIN
 14      line1 :=addressLine1;
 15      line2 :=addressLine2;
 16      city :=address_city;
 17      state_code :=address_state;
 18      zip :=address_zip;
 19    END set_address;
 20  END;
 21  /

Type body created.

SQL>
SQL> CREATE TABLE address_master OF address;

Table created.

SQL>
SQL> INSERT INTO address_master VALUES (address('19 J','R Rd','E','NJ','00000'));

1 row created.

SQL>
SQL> select * from address_master;

LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J                 R Rd                 E                    NJ
00000


1 row selected.

SQL>
SQL> declare
  2    cursor myCursor is select * from address_master;
  3    v_add address_master%ROWTYPE;
  4  begin
  5    for i in myCursor loop
  6      dbms_output.put_line(i.line1);
  7    end loop;
  8  end;
  9  /
19 J

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table address_master;

Table dropped.

SQL>
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