Oracle PL/SQL Tutorial/Object Oriented/Constructor
Содержание
Construct user-defined type with subquery
<source lang="sql">
SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> create type addressType as object
2 ( street varchar2(20) 3 , nr varchar2(5) 4 , pcode varchar2(6) 5 , city varchar2(20) 6 ) ; 7 /
Type created. SQL> SQL> describe addressType
Name Null? Type ----------------------------------------- -------- ---------------------------- STREET VARCHAR2(20) NR VARCHAR2(5) PCODE VARCHAR2(6) CITY VARCHAR2(20)
SQL> SQL> select type_name, typecode from user_types; TYPE_NAME TYPECODE
------------------------------
DEBUG_O OBJECT NUMBERLIST_T COLLECTION ADDRESS2 OBJECT ZIP_CODE OBJECT NUM_VARRAY COLLECTION NUM_TABLE COLLECTION ADD_LIST COLLECTION HOME_ADD_LIST COLLECTION SYS_PLSQL_14797_54_1 COLLECTION SYS_PLSQL_14797_DUMMY_1 COLLECTION SYS_PLSQL_14797_9_1 OBJECT VCARRAY COLLECTION STRINGS_NT COLLECTION RIVER OBJECT WATERFALL OBJECT SQLMONTH_TABLETYPE COLLECTION ORD_TYPE OBJECT ORD_TABLE COLLECTION DTARRAY COLLECTION TYPE_NAME TYPECODE
------------------------------
NMARRAY COLLECTION EMP_T OBJECT ASCII_TABLE_TYPE OBJECT PHONE_NUM_REC_TYPE OBJECT NUMBERVARRAY COLLECTION ADDRESSTYPE OBJECT 25 rows selected. SQL> SQL> create table o as select course, begindate, trainer
2 from course_schedule;
Table created. SQL> SQL> alter table o add (address addressType); Table altered. SQL> SQL> update o
2 set o.address =
3 addressType("","","",
4 (select initcap(x.location)
5 from course_schedule x
6 where x.course = o.course
7 and x.begindate = o.begindate)
8 );
13 rows updated. SQL> SQL> drop table o; Table dropped. SQL> drop type addressType force; Type dropped. SQL> SQL> drop table course_schedule; Table dropped. SQL> SQL></source>
Demonstrates object initialization.
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
2 rebate NUMBER (10, 4), 3 price NUMBER (10, 2), 4 CONSTRUCTOR FUNCTION BookType (price NUMBER) 5 RETURN SELF AS RESULT 6 ) 7 INSTANTIABLE FINAL; 8 /
SP2-0816: Type created with compilation warnings SQL> SQL> CREATE OR REPLACE TYPE BODY BookType
2 AS 3 CONSTRUCTOR FUNCTION BookType (price NUMBER) 4 RETURN SELF AS RESULT 5 AS 6 BEGIN 7 SELF.price := price * .9; 8 RETURN; 9 END BookType; 10 END; 11 /
SP2-0818: Type Body created with compilation warnings SQL> SQL> SQL> DECLARE
2 v_price BookType; 3 BEGIN 4 v_price.price := 75; 5 DBMS_OUTPUT.put_line (v_price.price); 6 END; 7 /
75 PL/SQL procedure successfully completed.</source>
Inserting a row into an object table using constructor
<source lang="sql">
SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
2 id NUMBER, 3 name VARCHAR2(15), 4 description VARCHAR2(22), 5 price NUMBER(5, 2), 6 days_valid NUMBER 7 ) 8 /
Type created. SQL> SQL> CREATE TABLE object_products OF ProductType
2 /
Table created. SQL> SQL> SQL> INSERT INTO object_products VALUES (
2 ProductType(1, "AAA", "BBB", 3.95, 10) 3 );
1 row created. SQL> SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 3.95 10
SQL> SQL> drop table object_products; Table dropped. SQL> SQL></source>
Use constructor to create new objects
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
2 rebate NUMBER (10, 4), 3 price NUMBER (10, 2), 4 CONSTRUCTOR FUNCTION BookType (price NUMBER) 5 RETURN SELF AS RESULT 6 ) 7 INSTANTIABLE FINAL; 8 /
SP2-0816: Type created with compilation warnings SQL> SQL> CREATE OR REPLACE TYPE BODY BookType
2 AS 3 CONSTRUCTOR FUNCTION BookType (price NUMBER) 4 RETURN SELF AS RESULT 5 AS 6 BEGIN 7 SELF.price := price * .9; 8 RETURN; 9 END BookType; 10 END; 11 /
SP2-0818: Type Body created with compilation warnings SQL> SQL> DECLARE
2 v_price BookType := BookType (NULL, NULL); 3 BEGIN 4 v_price.price := 75; 5 DBMS_OUTPUT.put_line (v_price.price); 6 END; 7 /
75 PL/SQL procedure successfully completed. SQL> SQL></source>
User-Defined Constructors
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE EmployeeType AS OBJECT (
2 id NUMBER, 3 first_name VARCHAR2(10), 4 last_name VARCHAR2(10), 5 dob DATE, 6 phone VARCHAR2(12), 7 CONSTRUCTOR FUNCTION EmployeeType( 8 p_id NUMBER, 9 p_first_name VARCHAR2, 10 p_last_name VARCHAR2 11 ) RETURN SELF AS RESULT, 12 CONSTRUCTOR FUNCTION EmployeeType( 13 p_id NUMBER, 14 p_first_name VARCHAR2, 15 p_last_name VARCHAR2, 16 p_dob DATE, 17 p_phone VARCHAR2 18 ) RETURN SELF AS RESULT 19 ); 20 /
Type created. SQL> CREATE OR REPLACE TYPE BODY EmployeeType AS
2 CONSTRUCTOR FUNCTION EmployeeType( 3 p_id NUMBER, 4 p_first_name VARCHAR2, 5 p_last_name VARCHAR2 6 ) RETURN SELF AS RESULT IS 7 BEGIN 8 SELF.id := p_id; 9 SELF.first_name := p_first_name; 10 SELF.last_name := p_last_name; 11 SELF.dob := SYSDATE; 12 SELF.phone := "555-1212"; 13 RETURN; 14 END; 15 CONSTRUCTOR FUNCTION EmployeeType( 16 p_id NUMBER, 17 p_first_name VARCHAR2, 18 p_last_name VARCHAR2, 19 p_dob DATE, 20 p_phone VARCHAR2 21 ) RETURN SELF AS RESULT IS 22 BEGIN 23 SELF.id := p_id; 24 SELF.first_name := p_first_name; 25 SELF.last_name := p_last_name; 26 SELF.dob := p_dob; 27 SELF.phone := p_phone; 28 RETURN; 29 END; 30 END; 31 /
Type body created. SQL> SQL> DESC EmployeeType;
Name Null? Type
ID NUMBER FIRST_NAME VARCHAR2(10) LAST_NAME VARCHAR2(10) DOB DATE PHONE VARCHAR2(12)
METHOD
FINAL CONSTRUCTOR FUNCTION EMPLOYEETYPE RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_ID NUMBER IN P_FIRST_NAME VARCHAR2 IN P_LAST_NAME VARCHAR2 IN
METHOD
FINAL CONSTRUCTOR FUNCTION EMPLOYEETYPE RETURNS SELF AS RESULT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_ID NUMBER IN P_FIRST_NAME VARCHAR2 IN P_LAST_NAME VARCHAR2 IN P_DOB DATE IN P_PHONE VARCHAR2 IN
SQL> SQL> CREATE TABLE emp OF EmployeeType; Table created. SQL> SQL> desc emp;
Name Null? Type
ID NUMBER FIRST_NAME VARCHAR2(10) LAST_NAME VARCHAR2(10) DOB DATE PHONE VARCHAR2(12)
SQL> SQL> drop table emp; Table dropped. SQL> SQL></source>
Using the default constructor (the name of the class)
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE ADDRESS_OBJ as OBJECT(
2 street VARCHAR2(20), 3 city VARCHAR2(20), 4 state CHAR(2), 5 zip CHAR(5)) 6 /
Type created. SQL> SQL> CREATE TABLE emp (
2 empno NUMBER(3), 3 name VARCHAR2(20), 4 address ADDRESS_OBJ) 5 /
Table created. SQL> INSERT INTO emp VALUES (101, "A",ADDRESS_OBJ("1 St.","M","AL","36608")); 1 row created. SQL> SQL> select * from emp;
EMPNO NAME
--------------------
ADDRESS(STREET, CITY, STATE, ZIP)
101 A
ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL> SQL> drop table emp; Table dropped. SQL> drop type ADDRESS_OBJ; Type dropped. SQL> SQL></source>
