A PL/SQL block demonstrating the use of the address object : Object Type « PL SQL « Oracle PL / SQL






A PL/SQL block demonstrating the use of the address object

 
SQL>
SQL> set echo on
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>
SQL>
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    address_1   address;
  3    address_2   address;
  4    address_3   address;
  5  BEGIN
  6    --Instantiate a new address object named address_1,
  7    --and assign a copy of it to address_2.
  8    address_1 := address ('2700 Peerless Road','Apt 1',
  9                          'Cleveland','TN','37312','4235551212');
 10    address_2 := address_1;
 11
 12    --Change address #1
 13    address_1.ChangeAddress ('2800 Peermore Road','Apt 99',
 14                             'Detroit','MI','48823');
 15
 16    --Instantiate a second object.
 17    address_3 := address ('2700 Eaton Rapids Road','Lot 98',
 18                          'Lansing','MI','48911','5173943551');
 19
 20    --Now print out the attributes from each object.
 21    dbms_output.put_line('Attributes for address_1:');
 22    dbms_output.put_line(address_1.getStreet(1));
 23    dbms_output.put_line(address_1.getStreet(2));
 24    dbms_output.put_line(address_1.getCity || ' ' || address_1.getStateAbbr || ' ' || address_1.getPostalCode);
 25    dbms_output.put_line(address_1.getPhone);
 26
 27    dbms_output.put_line('Attributes for address_2:');
 28    dbms_output.put_line(address_2.getStreet(1));
 29    dbms_output.put_line(address_2.getStreet(2));
 30    dbms_output.put_line(address_2.getCity || ' ' || address_2.getStateAbbr || ' ' || address_2.getPostalCode);
 31    dbms_output.put_line(address_2.getPhone);
 32
 33    dbms_output.put_line('Attributes for address_3:');
 34    dbms_output.put_line(address_3.street_1);
 35    dbms_output.put_line(address_3.street_2);
 36    dbms_output.put_line(address_3.city || ' ' || address_3.state_abbr || ' ' || address_3.zip_code);
 37    dbms_output.put_line(address_3.phone_number);
 38  END;
 39  /
Attributes for address_1:
2800 Peermore Road
Apt 99
Detroit MI 48823
4235551212
Attributes for address_2:
2700 Peerless Road
Apt 1
Cleveland TN 37312
4235551212
Attributes for address_3:
2700 Eaton Rapids Road
Lot 98
Lansing MI 48911
5173943551

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --

 








Related examples in the same category

1.Create object type in PL/SQL block
2.Reference object type attribute in PL/SQL block
3.Use REF column in PL/SQL
4.Multilevel collections.