Oracle PL/SQL Tutorial/Object Oriented/Object Instance

Call User-defined constructors

SQL> CREATE OR REPLACE TYPE address2 AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7               country VARCHAR(3),
  8    CONSTRUCTOR FUNCTION address2(addressLine1 VARCHAR2,
  9                  addressLine2 VARCHAR2,
 10                  address_city VARCHAR2,
 11                  address_state VARCHAR2,
 12                  address_zip VARCHAR2)
 13    RETURN SELF AS RESULT);
 14  /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address2 IS
  2    CONSTRUCTOR FUNCTION address2(addressLine1 VARCHAR2,
  3                  addressLine2 VARCHAR2,
  4                  address_city VARCHAR2,
  5                  address_state VARCHAR2,
  6                  address_zip VARCHAR2)
  7    RETURN SELF AS RESULT
  8    IS
  9    BEGIN
 10      SELF.line1 :=addressLine1;
 11      SELF.line2 :=addressLine2;
 12      SELF.city :=address_city;
 13      SELF.state_code :=address_state;
 14      SELF.zip :=address_zip;
 15      SELF.country :="USA";
 16      RETURN;
 17    END;
 18  END;
 19  /
Type body created.
SQL> declare
  2    addressValue address2;
  3  begin
  4    addressValue :=address2("Suite 288","S Street","San Jose","CA","00000");
  5
  6    dbms_output.put_line(addressValue.line1);
  7
  8    dbms_output.put_line(addressValue.line2);
  9
 10    dbms_output.put_line(addressValue.city||", "||addressValue.state_code||" "||addressValue.zip);
 11
 12    dbms_output.put_line(addressValue.country);
 13  end;
 14  /
Suite 288
S Street
San Jose, CA 00000
USA
PL/SQL procedure successfully completed.

Check nullment for object instance

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  /
Type created.
SQL>
SQL> DECLARE
  2    addressValue address;
  3  BEGIN
  4    IF (addressValue IS NULL) THEN
  5
  6      DBMS_OUTPUT.PUT_LINE("addressValue is atomically null ");
  7
  8    END IF;
  9
 10    IF (addressValue.line1 IS NULL) THEN
 11
 12      DBMS_OUTPUT.PUT_LINE("line1 is null ");
 13
 14    END IF;
 15
 16    addressValue :=address("27 Rd","A","E","NJ","00000");
 17
 18    addressValue :=NULL;
 19
 20    IF (addressValue IS NULL) THEN
 21      DBMS_OUTPUT.PUT_LINE("addressValue is atomically null ");
 22    END IF;
 23
 24    addressValue.line1 :="1 Road";
 25
 26  EXCEPTION WHEN ACCESS_INTO_NULL THEN
 27
 28    DBMS_OUTPUT.PUT_LINE("Access Into Null");
 29  END;
 30  /
addressValue is atomically null
line1 is null
addressValue is atomically null
Access Into Null
PL/SQL procedure successfully completed.
SQL>

Invoking a method

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> 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||", "||
  6              SELF.state_code||" "||SELF.zip);
  7    END get_address;
  8    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  9                  addressLine2 VARCHAR2,
 10                  address_city VARCHAR2,
 11                  address_state VARCHAR2,
 12                  address_zip VARCHAR2)
 13    IS
 14    BEGIN
 15      line1 :=addressLine1;
 16
 17      line2 :=addressLine2;
 18
 19      city :=address_city;
 20
 21      state_code :=address_state;
 22
 23      zip :=address_zip;
 24
 25    END set_address;
 26  END;
 27  /
Type body created.
SQL>
SQL>
SQL> DECLARE
  2    off_add address;
  3    home_address address;
  4  BEGIN
  5    off_add :=address("10 Avenue","Suite 111","Vancouver","NJ","00000");
  6
  7    home_address :=address(null,null,null,null,null);
  8
  9    DBMS_OUTPUT.PUT_LINE(off_add.get_address());
 10
 11    home_address.set_address("161 Rd","#000","Vancouver","NJ","00000");
 12
 13    DBMS_OUTPUT.PUT_LINE(home_address.get_address());
 14  END;
 15  /
10 Avenue Suite 111 Vancouver, NJ 00000
161 Rd #000 Vancouver, NJ 00000
PL/SQL procedure successfully completed.

NULL objects and NULL attributes

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  /
Type created.
SQL> DECLARE
  2    addressValue address;
  3  BEGIN
  4
  5    addressValue := address("27 Rd","#99","E","NJ","08820");
  6    addressValue := NULL;
  7    IF (addressValue IS NULL) THEN
  8      DBMS_OUTPUT.PUT_LINE("addressValue is atomically null");
  9    END IF;
 10  END;
 11  /
addressValue is atomically null
PL/SQL procedure successfully completed.
SQL>