в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:44Z

1 версия

Новая страница

==Call object constructor in an insert statement==



<source lang="sql">

SQL>
SQL>
SQL> create or replace
2 type person as object (
3 first_name varchar2(100),
4 last_name varchar2(100),
5 dob date,
6 phone varchar2(100),
7 member function get_last_name return varchar2,
8 member function get_phone_number return varchar2 )
9 not final
10 /
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> create or replace
2 type new_employee under person (
3 empno number,
4 hiredate date,
5 work_phone varchar2(100),
6 overriding member function get_phone_number return varchar2,
7 member function get_home_phone_number return varchar2 )
8 not final
9 /
Type created.
SQL>
SQL> create or replace
2 type body new_employee as
3 overriding member function get_phone_number return varchar2 is
4 begin
5 return self.work_phone;
6 end;
7 member function get_home_phone_number return varchar2 is
8 begin
9 return self.phone;
10 end;
11 end;
12 /
Type body created.
SQL>
SQL>
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL>
SQL> insert into person_table values
2 ( person( null, "Kyte", null, "703.555.5555" ) )
3 /
1 row created.
SQL>
SQL> insert into person_table values
2 ( new_employee( null, "Beck", null, "703.555.1111", 1234, null, "703.555.2222" ) )
3 /
1 row created.
SQL>
SQL> insert into person_table values
2 ( new_employee( null, "Viper", null, "703.555.3333", 5678, null, "703.555.4444" ) )
3 /
1 row created.
SQL>
SQL> select * from person_table;

P(FIRST_NAME, LAST_NAME, DOB, PHONE)
--------------------------------------------------------------------------------
person(NULL, "Kyte", NULL, "703.555.5555")
NEW_EMPLOYEE(NULL, "Beck", NULL, "703.555.1111", 1234, NULL, "703.555.2222")
NEW_EMPLOYEE(NULL, "Viper", NULL, "703.555.3333", 5678, NULL, "703.555.4444")
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type 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>

==Insert value with constructor method==



<source lang="sql">

SQL>
SQL> create or replace
2 type person as object (
3 first_name varchar2(100),
4 last_name 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 /
Warning: Type Body created with compilation errors.
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL> insert into person_table values ( person( "Sean", "Viper" ));
1 row created.
SQL>
SQL> select * from person_table;

P(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
person("Sean", "Viper")
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL>
SQL> drop type person;
Type dropped.

</source>

==This script builds a sample object type with constructor.==



<source lang="sql">

SQL> CREATE OR REPLACE TYPE myType
2 AUTHID CURRENT_USER IS OBJECT
3 ( fname VARCHAR2(20 CHAR)
4 , lname VARCHAR2(20 CHAR)
5 , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
6 , CONSTRUCTOR FUNCTION myType ( fname VARCHAR2, lname VARCHAR2 )RETURN SELF AS RESULT
7 , MEMBER PROCEDURE print_instance_variable
8 , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )INSTANTIABLE NOT FINAL;
9 /
Type created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
2 CONSTRUCTOR FUNCTION myType
3 RETURN SELF AS RESULT IS
4 fname VARCHAR2(20 CHAR) := NULL;
5 lname VARCHAR2(20 CHAR) := NULL;
6 BEGIN
7 SELF.fname := fname;
8 SELF.lname := lname;
9 RETURN;
10 END;
11
12 CONSTRUCTOR FUNCTION myType( fname VARCHAR2, lname VARCHAR2 )
13 RETURN SELF AS RESULT IS
14 BEGIN
15 SELF.fname := fname;
16 SELF.lname := lname;
17 RETURN;
18 END;
19 MEMBER PROCEDURE print_instance_variable IS
20 BEGIN
21 DBMS_OUTPUT.PUT_LINE("Name:"||SELF.fname||", "||SELF.lname);
22
23 END;
24
25 ORDER MEMBER FUNCTION equals( my_class myType )
26 RETURN NUMBER IS
27 false_value NUMBER := 0;
28 true_value NUMBER := 1;
29 BEGIN
30 IF SELF.fname = my_class.fname AND
31 SELF.lname = my_class.lname THEN
32 RETURN true_value;
33 ELSE
34 RETURN false_value;
35
36 END IF;
37
38 END;
39
40 END;
41 /
Type body created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL>

</source>

==This script demonstrates the user-defined constructor method.==



<source lang="sql">

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE PriceType AS OBJECT (
2 discount_rate NUMBER (10, 4),
3 price NUMBER (10, 2),
4 CONSTRUCTOR FUNCTION PriceType (price NUMBER)
5 RETURN SELF AS RESULT
6 )
7 INSTANTIABLE FINAL;
8 /

SQL>
SQL> CREATE OR REPLACE TYPE BODY PriceType
2 AS
3 CONSTRUCTOR FUNCTION PriceType (price NUMBER)
4 RETURN SELF AS RESULT
5 AS
6 BEGIN
7 SELF.price := price * .9;
8 RETURN;
9 END PriceType;
10 END;
11 /
Warning: Type Body created with compilation errors.
SQL> show errors
Errors for TYPE BODY PRICETYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/25 PLS-00539: subprogram "PRICETYPE" is declared in an object type
body and must be defined in the object type specification
3/25 PLW-07203: parameter "SELF" may benefit from use of the NOCOPY
compiler hint
5/20 PLS-00538: subprogram or cursor "DISCOUNT_PRICE" is declared in
an object type specification and must be defined in the object
type body
SQL>
SQL>
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 v_price PriceType := PriceType (75);
3 BEGIN
4 DBMS_OUTPUT.put_line (v_price.price);
5 END;
6 /

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>

==Use type Constructor to insert data to object table==



<source lang="sql">

SQL>
SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
2 /
Table created.
SQL>
SQL>
SQL> desc people1
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL>
SQL>
SQL> insert into people1 values ( "Tom", "15-mar-1965",
2 address_type( "Reston", "123 Main Street", "Va", "45678" ),
3 address_type( "Redwood", "1 Oracle Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL>
SQL> select name, p.home_address.city from people1 p;
NAME HOME_ADDRESS.CITY
-------------------- ------------------------------
Tom Reston
Tom Reston
2 rows selected.
SQL>
SQL> drop table people1;
Table dropped.
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Object Oriented Database/Constructor - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия