Oracle PL/SQL/Object Oriented Database/Cursor

Cursor for user-defined object type

 
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
  2    street_1      VARCHAR2(40),
  3    street_2      VARCHAR2(40),
  4    city          VARCHAR2(40),
  5    state_abbr    VARCHAR2(2),
  6    zip_code      VARCHAR2(5),
  7    phone_number  VARCHAR2(10),
  8    MEMBER PROCEDURE ChangeAddress (
  9      st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
 10      state IN VARCHAR2, zip IN VARCHAR2),
 11    MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
 12    MEMBER FUNCTION getCity RETURN VARCHAR2,
 13    MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
 14    MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
 15    MEMBER FUNCTION getPhone RETURN VARCHAR2,
 16    MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
 17  );
 18  /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER PROCEDURE ChangeAddress (
  3      st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
  4      state IN VARCHAR2, zip IN VARCHAR2) IS
  5    BEGIN
  6      IF ( st_1 IS NULL ) THEN
 18       RAISE_application_error(-20001,"The new Address is invalid.");
 19      ELSE
 20        street_1 := st_1;
 21        street_2 := st_2;
 22        city := cty;
 23        state_abbr := upper(state);
 24        zip_code := zip;
 25      END IF;
 26    END;
 27
 28    MEMBER FUNCTION getStreet (line_no IN number)
 29      RETURN VARCHAR2 IS
 30    BEGIN
 31      IF line_no = 1 THEN
 32        RETURN street_1;
 33      ELSIF line_no = 2 THEN
 34        RETURN street_2;
 35      ELSE
 36        RETURN " ";  
 37      END IF;
 38    END;
 39
 40    MEMBER FUNCTION getCity RETURN VARCHAR2 IS
 41    BEGIN
 42      RETURN city;
 43    END;
 44
 45    MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
 46    BEGIN
 47      RETURN state_abbr;
 48    END;
 49
 50    MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
 51    BEGIN
 52      RETURN zip_code;
 53    END;
 54
 55    MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
 56    BEGIN
 57      RETURN phone_number;
 58    END;
 59
 60    MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
 61    BEGIN
 62      phone_number := newPhone;
 63    END;
 64  END;
 65  /
Type body created.
SQL> set serveroutput on
SQL>
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
  2    BldgName          VARCHAR2(40),
  3    BldgAddress       address,
  4    BldgMgr           INTEGER,
  5    MEMBER PROCEDURE  ChangeMgr (NewMgr IN INTEGER),
  6    ORDER MEMBER FUNCTION Compare (Otherappartment IN appartment)
  7        RETURN INTEGER
  8    );
  9  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
  2    MEMBER PROCEDURE  ChangeMgr(NewMgr IN INTEGER) IS
  3      BEGIN
  4        BldgMgr := NewMgr;
  5      END;
  6
  7    ORDER MEMBER FUNCTION Compare (Otherappartment IN appartment)
  8    RETURN INTEGER IS
  9        BldgName1     VARCHAR2(40);
 10        BldgName2     VARCHAR2(40);
 11      BEGIN
 12        BldgName1 := upper(ltrim(rtrim(BldgName)));
 13        BldgName2 := upper(ltrim(rtrim(Otherappartment.BldgName)));
 14
 15        IF BldgName1 = BldgName2 THEN
 16          RETURN 0;
 17        ELSIF BldgName1 < BldgName2 THEN
 18          RETURN -1;
 19        ELSE
 20          RETURN 1;
 21        END IF;
 22      END;
 23  END;
 24  /
Type body created.
SQL>
SQL> CREATE TABLE appartments OF appartment;
Table created.
SQL>
SQL> set echo on
SQL>
SQL> INSERT INTO appartments values (appartment("Headquarters appartment",address("1 J","","Detroit","MI","48226",""),599));
1 row created.
SQL>
SQL> SELECT * from appartments;
BLDGNAME
----------------------------------------
BLDGADDRESS(STREET_1, STREET_2, CITY, STATE_ABBR,
--------------------------------------------------
   BLDGMGR
----------
Headquarters appartment
ADDRESS("1 J", NULL, "Detroit", "MI", "48226", NUL
L)
       599

1 row selected.
SQL>
SQL>
SQL> DECLARE
  2    this_appartment   appartment;
  3
  4    CURSOR all_appartments IS
  5      SELECT value (b) AS bldg
  6        FROM appartments b
  7       ORDER BY b.BldgName;
  8
  9  BEGIN
 10    FOR one_appartment IN all_appartments LOOP
 11      this_appartment := one_appartment.bldg;
 12      dbms_output.put_line(this_appartment.BldgName || " is located in "
 13                       || this_appartment.BldgAddress.city
 14                       || " " || this_appartment.BldgAddress.state_abbr);
 15    END LOOP;
 16
 17    COMMIT;
 18  END;
 19  /
Headquarters appartment is located in Detroit MI
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table appartments;
Table dropped.
SQL>
SQL> drop type appartment;
Type dropped.
SQL>
SQL> --

Cursor of user-defined objects

 
SQL>
SQL> CREATE TABLE MyTable (
  2    f1 NUMBER,
  3    f2 VARCHAR2(50)
  4  );
Table created.
SQL>
SQL> CREATE or replace TYPE objType AS OBJECT (
  2    f1 NUMBER,
  3    f2 VARCHAR2(50)
  4  );
  5  /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE TABLE obj_tab OF objType;
Table created.
SQL>
SQL> DECLARE
  2
  3    CURSOR c_AllRows IS
  4      SELECT * FROM obj_tab;
  5  BEGIN
  6    FOR v_Row IN c_AllRows LOOP
  7      DBMS_OUTPUT.PUT_LINE(v_Row.f1 || " " || v_Row.f2);
  8    END LOOP;
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE MyTable;
Table dropped.
SQL> DROP TABLE obj_tab;
Table dropped.
SQL>