Oracle PL/SQL/PL SQL/Object Type

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

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

</source>
   
  


Create object type in PL/SQL block

   <source lang="sql">

SQL> SQL> set serverout on SQL> SQL> create or replace

 2  type person as object (
 3   last_name varchar2(100),
 4   phone varchar2(100),
 5   member function get_last_name return varchar2,
 6   member function get_phone_number return varchar2 )
 7  not final
 8  /

Type created. SQL> SQL> SQL> create or replace

 2  type body person as
 3    member function get_last_name return varchar2 is
 4    begin
 5      return self.last_name;
 6    end;
 7    member function get_phone_number return varchar2 is
 8    begin
 9      return self.phone;
10    end;
11  end;
12  /

Type body created. SQL> SQL> SQL> SQL> declare

 2   l_person person;
 3  begin
 4   l_person := person( "C", "B" );
 5   dbms_output.put_line( l_person.last_name );
 6  end;
 7  /

C PL/SQL procedure successfully completed.

</source>
   
  


Multilevel collections.

   <source lang="sql">

SQL> SQL> DECLARE

 2    TYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3
 4    TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_INTEGER;
 5
 6    TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers;
 7
 8    TYPE t_MultiNested IS TABLE OF t_Numbers;
 9
10    v_MultiNumbers t_MultiNumbers;
11  BEGIN
12    v_MultiNumbers(1)(1) := 12345;
13  END;
14  /

PL/SQL procedure successfully completed. SQL>

</source>
   
  


Reference object type attribute in PL/SQL block

   <source lang="sql">

SQL> SQL> SQL> set serverout on SQL> SQL> create or replace

 2  type person as object (
 3   last_name varchar2(100),
 4   phone varchar2(100),
 5   member function get_last_name return varchar2,
 6   member function get_phone_number return varchar2 )
 7  not final
 8  /

Type created. SQL> SQL> SQL> create or replace

 2  type body person as
 3    member function get_last_name return varchar2 is
 4    begin
 5      return self.last_name;
 6    end;
 7    member function get_phone_number return varchar2 is
 8    begin
 9      return self.phone;
10    end;
11  end;
12  /

Type body created. SQL> declare

 2
 3   l_person person;
 4  begin
 5   l_person := person( "A","sean");
 6   dbms_output.put_line( l_person.last_name );
 7  end;
 8  /

A PL/SQL procedure successfully completed.

</source>
   
  


Use REF column in PL/SQL

   <source lang="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> 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 echo on SQL> 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> CREATE TABLE employee(

 2           emp_id           INTEGER,
 3           emp_name         VARCHAR2(32),
 4           supervised_by    INTEGER,
 5           pay_rate         NUMBER(9,2),
 6           pay_type         CHAR,
 7           emp_bldg         REF appartment);

Table created. SQL> SQL> CREATE TABLE appartments OF appartment; Table created. SQL> SQL> set echo on SQL> SQL> INSERT INTO appartments

 2    values (appartment("Victor Building",
 3            address("203 Washington Square"," ","Lansing",
 4                    "MI","48823"," "),
 5            597));

1 row created. SQL> SQL> INSERT INTO appartments

 2    values (appartment("East Storage Shed",
 3            address("1400 Abbott Rd","","Lansing","MI","48823",""),
 4            598));

1 row created. SQL> INSERT INTO appartments

 2    values (appartment("Headquarters Building",
 3            address("150 West Jefferson","","Detroit","MI","48226",""),
 4            599));

1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE AssignEmpToBldg (

 2    EmpNumIn IN employee.emp_id%TYPE,
 3    BldgNameIn IN appartments.BldgName%TYPE
 4    ) AS
 5  BEGIN
 6    UPDATE employee
 7       SET emp_bldg = (SELECT REF(b)
 8                        FROM appartments B
 9                       WHERE BldgName = BldgNameIn)
10     WHERE emp_id = EmpNumIn;
11     IF SQL%NOTFOUND THEN
12      RAISE_application_error(-20000,"Employee " || EmpNumIn
13                             || " could not be assigned to appartment "
14                             || BldgNameIn);
15    END IF;
16  END;
17  /

Procedure created. SQL> SQL> BEGIN

 2    AssignEmpToBldg (598,"Victor Building");
 3    AssignEmpToBldg (597,"East Storage Shed");
 4  END;
 5  /

BEGIN

ERROR at line 1: ORA-20000: Employee 598 could not be assigned to appartment Victor Building ORA-06512: at "sqle.ASSIGNEMPTOBLDG", line 12 ORA-06512: at line 2

SQL> SQL> drop table employee; Table dropped. SQL> drop table appartments; Table dropped. SQL> drop type appartment; Type dropped. SQL> SQL> --

</source>