Oracle PL/SQL Tutorial/Object Oriented/Object Instance

Call User-defined constructors

   <source lang="sql">

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.</source>

Check nullment for object instance

   <source lang="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  /

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></source>

Invoking a method

   <source lang="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> 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.</source>

NULL objects and NULL attributes

   <source lang="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  /

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></source>