Oracle PL/SQL Tutorial/Object Oriented/Constructor — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
Construct user-defined type with subquery
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>
Demonstrates object initialization.
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.
Inserting a row into an object table using constructor
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>
Use constructor to create new objects
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>
User-Defined Constructors
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>
Using the default constructor (the name of the class)
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>