Oracle PL/SQL/Object Oriented Database/Object — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:02, 26 мая 2010
Содержание
- 1 Behavior of dependent objects.
- 2 Build data type with another user type
- 3 Combine user-defined type to create new type
- 4 Create a stored type which is visible to SQL and PL/SQL.
- 5 Create Object
- 6 CREATE OR REPLACE TYPE
- 7 Create the object and collection types
- 8 Create type and use it in inner query
- 9 Create types and then use it in pl/sql block
- 10 Name type
- 11 One to list using object references
- 12 Point type
- 13 PriceType becomes the datatype of the price attribute in the ProductType object type
- 14 reference user-defined data type in another block
- 15 Student type
- 16 This script demonstrates complex objects
- 17 Use self to reference member variable in constructor
- 18 Use user-defined type as parameter
Behavior of dependent objects.
SQL>
SQL> COLUMN object_name FORMAT a20
SQL>
SQL> CREATE TABLE simple_table (f1 NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER);
3 END Dependee;
4 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER) IS
3 BEGIN
4 INSERT INTO simple_table VALUES (p_Val);
5 END Example;
6 END Dependee;
7 /
Package body created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Depender(p_Val IN NUMBER) AS
2 BEGIN
3 Dependee.Example(p_Val + 1);
4 END Depender;
5 /
Procedure created.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY VALID
DEPENDER PROCEDURE VALID
SIMPLE_TABLE TABLE VALID
4 rows selected.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Dependee AS
2 PROCEDURE Example(p_Val IN NUMBER) IS
3 BEGIN
4 INSERT INTO simple_table VALUES (p_Val - 1);
5 END Example;
6 END Dependee;
7 /
Package body created.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY VALID
DEPENDER PROCEDURE VALID
SIMPLE_TABLE TABLE VALID
4 rows selected.
SQL>
SQL> DROP TABLE simple_table;
Table dropped.
SQL>
SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ("DEPENDER", "DEPENDEE","SIMPLE_TABLE");
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- ----------
DEPENDEE PACKAGE VALID
DEPENDEE PACKAGE BODY INVALID
DEPENDER PROCEDURE VALID
3 rows selected.
SQL>
Build data type with another user type
SQL> create or replace type address_type as object
2 ( city varchar2(30),
3 street varchar2(30),
4 state varchar2(2),
5 zip number
6 )
7 /
SQL> create or replace type person_type as object
2 ( name varchar2(30),
3 dob date,
4 home_address address_type,
5 work_address address_type
6 )
7 /
Type created.
SQL> create table people of person_type
2 /
Table created.
SQL>
SQL> select sys_nc_rowinfo$ from people;
no rows selected
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
Combine user-defined type to create new type
SQL> create type addressType as object
2 (Street VARCHAR2(50),
3 City VARCHAR2(25),
4 State CHAR(2),
5 Zip NUMBER);
6 /
SQL> create type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create or replace type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> drop type personType force;
SQL>
SQL> drop type addressType force;
SQL>
SQL>
Create a stored type which is visible to SQL and PL/SQL.
SQL> CREATE OR REPLACE TYPE NameList AS
2 VARRAY(20) OF VARCHAR2(30);
3 /
Type created.
SQL>
SQL> DECLARE
2 -- This type is local to this block.
3 TYPE DateList IS VARRAY(10) OF DATE;
4
5 -- We can create variables of both DateList and NameList here.
6 v_Dates DateList;
7 v_Names NameList;
8 BEGIN
9 NULL;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Create Object
SQL>
SQL>
SQL> --User-defined types
SQL>
SQL> CREATE TYPE address_typ AS OBJECT
2 (StreetNo NUMBER(10),
3 StreetName VARCHAR2(100),
4 AptNo NUMBER(5),
5 City VARCHAR2(100),
6 State VARCHAR2(100),
7 ZipCode NUMBER(9),
8 Country VARCHAR2(100));
9 /
Type created.
SQL>
SQL> CREATE TABLE people
2 (ID NUMBER(5),
3 FirstName VARCHAR2(100),
4 LastName VARCHAR2(100),
5 Address address_typ);
Table created.
SQL>
SQL>
SQL> INSERT INTO people
2 VALUES(10,
3 "John",
4 "Smith",
5 address_typ(123,"Happy Lane", NULL,
6 "Smalltown","Alaska", 12345,"USA") );
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM people;
ID FIRSTNAME
------------------------ ----------------------------------------------------------------------------------------------------
LASTNAME
----------------------------------------------------------------------------------------------------
ADDRESS(STREETNO, STREETNAME, APTNO, CITY, STATE, ZIPCODE, COUNTRY)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10.00 John
Smith
ADDRESS_TYP(123.00, "Happy Lane", NULL, "Smalltown", "Alaska", 12345.00, "USA")
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
CREATE OR REPLACE TYPE
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE TYPE emp_chg AS OBJECT (
2 emp_id NUMBER,
3 pay_rate NUMBER(9,2),
4 pay_type CHAR(1),
5 leave_dept NUMBER,
6 join_dept NUMBER
7 );
8 /
SQL>
SQL> drop type emp_chg;
SQL>
SQL>
SQL> --
Create the object and collection types
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 );
7 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created.
SQL>
SQL> -- varray in temporary table example
SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeVArray
6 );
SQL>
SQL> CREATE TABLE empTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeNestedTable
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses2 TABLESPACE users;
Table created.
SQL>
SQL>
SQL> -- equal/not equal example
SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("A", "B", "C");
10 emp3 := charTable("B", "C", "D");
11
12
13 result := emp1 <> emp3;
14 IF result THEN
15 DBMS_OUTPUT.PUT_LINE("emp1 not equal to emp3");
16 END IF;
17 END equal_example;
18 /
Procedure created.
SQL>
SQL> drop type addressType force;
Type dropped.
SQL> drop type addressTypeVArray force;
Type dropped.
SQL> drop TYPE addressTypeNestedTable force;
Type dropped.
SQL> drop TABLE empTable;
Table dropped.
Create type and use it in inner query
SQL> create or replace type myScalarType as object( cnt number, average number )
2 /
Type created.
SQL>
SQL> select username, a.data.cnt, a.data.average
2 from (
3 select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
4 /
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>
SQL>
Create types and then use it in pl/sql block
SQL>
SQL> CREATE OR REPLACE TYPE unitType AS VARRAY(13) OF VARCHAR2(5 CHAR);
2 /
Type created.
SQL> CREATE OR REPLACE TYPE categoryType AS VARRAY(4) OF VARCHAR2(8 CHAR);
2 /
Type created.
SQL> CREATE OR REPLACE TYPE charArrayType AS TABLE OF VARCHAR2(17 CHAR);
2 /
Type created.
SQL>
SQL> DECLARE
2 counter INTEGER := 0;
3
4 suits categoryType :=categoryType("A","B","C","D");
5 units unitType :=unitType("1","2","3","4","Five","Six","Seven");
6
7 charArray charArrayType := charArrayType();
8 BEGIN
9
10
11
12 FOR i IN 1..counter LOOP
13 dbms_output.put_line("["||charArray(i)||"]");
14 END LOOP;
15 END;
16 /
PL/SQL procedure successfully completed.
Name type
SQL> create type nameType as object(
2 fname VARCHAR2(25),
3 Middle_Initial CHAR(1),
4 lname VARCHAR2(30),
5 Suffix VARCHAR2(5));
6 /
SQL>
SQL> drop type nameType;
One to list using object references
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE courseType AS OBJECT
2 (course_id VARCHAR2(10),
3 course_name VARCHAR2(30))
4 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE bookType AS OBJECT
2 (book_id VARCHAR2(10),
3 book_title VARCHAR2(30),
4 course_book REF courseType)
5 /
Type created.
SQL>
SQL> CREATE TABLE Course OF courseType
2 (course_id NOT NULL,
3 PRIMARY KEY (course_id));
Table created.
SQL>
SQL> CREATE TABLE Book OF bookType
2 (book_id NOT NULL,
3 PRIMARY KEY (book_id));
Table created.
SQL>
SQL> CREATE TABLE Require
2 (Book REF bookType,
3 Index_Book NUMBER NOT NULL,
4 Course REF courseType);
Table created.
SQL>
SQL> drop type courseType force;
Type dropped.
SQL> drop type bookType force;
Type dropped.
SQL> drop table Course;
Table dropped.
SQL> drop table Book;
Table dropped.
SQL> drop table Require;
Table dropped.
Point type
SQL>
SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE Point AS OBJECT (
2 x NUMBER,
3 y NUMBER,
4
5 MEMBER FUNCTION ToString RETURN VARCHAR2,
6 PRAGMA RESTRICT_REFERENCES(ToString, RNDS, WNDS, RNPS, WNPS),
7
8 MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
9 RETURN NUMBER,
10 PRAGMA RESTRICT_REFERENCES(Distance, RNDS, WNDS, RNPS, WNPS),
11
12 MEMBER FUNCTION Plus(p IN Point) RETURN Point,
13 PRAGMA RESTRICT_REFERENCES(Plus, RNDS, WNDS, RNPS, WNPS),
14
15 MEMBER FUNCTION Times(n IN NUMBER) RETURN Point,
16 PRAGMA RESTRICT_REFERENCES(Times, RNDS, WNDS, RNPS, WNPS)
17 );
18 /
SQL>
SQL> CREATE OR REPLACE TYPE BODY Point AS
2 MEMBER FUNCTION ToString RETURN VARCHAR2 IS
3 myResult VARCHAR2(20);
4 v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x), 1, 8);
5 v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y), 1, 8);
6 BEGIN
7 myResult := "(" || v_xString || ", ";
8 myResult := myResult || v_yString || ")";
9 RETURN myResult;
10 END ToString;
11
12 MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
13 RETURN NUMBER IS
14 BEGIN
15 RETURN SQRT(POWER(x - p.x, 2) + POWER(y - p.y, 2));
16 END Distance;
17
18 MEMBER FUNCTION Plus(p IN Point) RETURN Point IS
19 myResult Point;
20 BEGIN
21 myResult := Point(x + p.x, y + p.y);
22 RETURN myResult;
23 END Plus;
24
25 MEMBER FUNCTION Times(n IN NUMBER) RETURN Point IS
26 myResult Point;
27 BEGIN
28 myResult := Point(x * n, y * n);
29 RETURN myResult;
30 END Times;
31 END;
32 /
Type body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> DECLARE
2 v_P1 Point := Point(-1, 5);
3 v_P2 Point := Point(5, 2);
4 myResult Point;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("p1: " || v_P1.toString);
7 DBMS_OUTPUT.PUT_LINE("p2: " || v_P2.toString);
8
9 DBMS_OUTPUT.PUT_LINE("Distance between p1 and p2 = " || v_P1.Distance(v_P2));
10
11 DBMS_OUTPUT.PUT_LINE("Distance between p1 and the origin = " ||
12 v_P1.Distance);
13
14 myResult := v_P1.Times(n => 2.5);
15 DBMS_OUTPUT.PUT_LINE("p1 * 2.5: " || myResult.toString);
16 myResult := v_P1.Plus(p => v_P2);
17 DBMS_OUTPUT.PUT_LINE("p1 + p2: " || myResult.toString);
18 END;
19 /
p1: (-1, 5)
p2: (5, 2)
Distance between p1 and p2 = 6.70820393249936908922752100619382870632
Distance between p1 and the origin = 5.09901951359278483002822410902278198956
p1 * 2.5: (-2.5, 12.5)
p1 + p2: (4, 7)
PL/SQL procedure successfully completed.
SQL>
SQL>
PriceType becomes the datatype of the price attribute in the ProductType object type
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 /
Type created.
SQL>
SQL>
SQL>
SQL> -- Body
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 /
Type body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
reference user-defined data type in another block
SQL>
SQL> CREATE OR REPLACE TYPE NameList AS
2 VARRAY(20) OF VARCHAR2(30);
3 /
Type created.
SQL>
SQL> DECLARE
2 v_Names2 NameList;
3 BEGIN
4 NULL;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Student type
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE Student AS OBJECT (
2 ID NUMBER(5),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3),
7
8 MEMBER FUNCTION FormattedName
9 RETURN VARCHAR2,
10 PRAGMA RESTRICT_REFERENCES(FormattedName, RNDS, WNDS, RNPS, WNPS),
11
12 MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2),
13 PRAGMA RESTRICT_REFERENCES(ChangeMajor, RNDS, WNDS, RNPS, WNPS),
14
15 MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class),
16 PRAGMA RESTRICT_REFERENCES(UpdateCredits, RNDS, WNDS, RNPS, WNPS),
17
18 ORDER MEMBER FUNCTION CompareStudent(p_Student IN Student)
19 RETURN NUMBER
20 );
21 /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY Student AS
2 MEMBER FUNCTION FormattedName
3 RETURN VARCHAR2 IS
4 BEGIN
5 RETURN first_name || " " || last_name;
6 END FormattedName;
7
8 MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2) IS
9 BEGIN
10 major := p_NewMajor;
11 END ChangeMajor;
12
13 MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class) IS
14 BEGIN
15 current_credits := current_credits +
16 p_CompletedClass.num_credits;
17 END UpdateCredits;
18
19 ORDER MEMBER FUNCTION CompareStudent(p_Student IN Student)
20 RETURN NUMBER IS
21 BEGIN
22 IF p_Student.last_name = SELF.last_name THEN
23 IF p_Student.first_name < SELF.first_name THEN
24 RETURN 1;
25 ELSIF p_Student.first_name > SELF.first_name THEN
26 RETURN -1;
27 ELSE
28 RETURN 0;
29 END IF;
30 ELSE
31 IF p_Student.last_name < SELF.last_name THEN
32 RETURN 1;
33 ELSE
34 RETURN -1;
35 END IF;
36 END IF;
37 END CompareStudent;
38 END;
39 /
Type body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_Student Student; -- This assigns NULL to v_Student by default
3 BEGIN
4 v_Student.ID := 10020;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
This script demonstrates complex objects
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE PriceType AS OBJECT (
2 discount_rate NUMBER(10,4),
3 price NUMBER(10,2),
4
5 MEMBER FUNCTION discount_price RETURN NUMBER)
6 INSTANTIABLE
7 FINAL;
8 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE inventory_obj AS OBJECT (
2 item_id NUMBER(10),
3 num_in_stock NUMBER(10),
4 reorder_status VARCHAR2(20),
5 price REF PriceType);
6 /
Type created.
SQL>
Use self to reference member variable in constructor
SQL> CREATE OR REPLACE TYPE myType
2 AUTHID CURRENT_USER IS OBJECT
3 ( my_number NUMBER
4 , my_name VARCHAR2(20 CHAR)
5 , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
6 , CONSTRUCTOR FUNCTION myType (my_number NUMBER, my_name VARCHAR2 )RETURN SELF AS RESULT
7 , MEMBER PROCEDURE print_instance_variable
8 , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )
9 INSTANTIABLE NOT FINAL;
10 /
Type created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
2
3
4 CONSTRUCTOR FUNCTION myType
5 RETURN SELF AS RESULT IS
6
7
8 my_instance_number NUMBER := 0;
9 my_instance_name VARCHAR2(20 CHAR) := "";
10
11 BEGIN
12
13
14 SELF.my_number := my_instance_number;
15 SELF.my_name := my_instance_name;
16
17
18 RETURN;
19
20 END;
21
22
23 CONSTRUCTOR FUNCTION myType( my_number NUMBER , my_name VARCHAR2 )
24 RETURN SELF AS RESULT IS
25
26 BEGIN
27
28
29 SELF.my_number := my_number;
30 SELF.my_name := my_name;
31
32
33 RETURN;
34
35 END;
36
37
38 MEMBER PROCEDURE print_instance_variable IS
39
40 BEGIN
41
42 DBMS_OUTPUT.PUT_LINE("Instance Number ["||SELF.my_number||"]");
43 DBMS_OUTPUT.PUT_LINE("Instance Name ["||SELF.my_name||"]");
44
45 END;
46
47
48 ORDER MEMBER FUNCTION equals( my_class myType )
49 RETURN NUMBER IS
50
51
52 false_value NUMBER := 0;
53 true_value NUMBER := 1;
54
55 BEGIN
56
57
58 IF SELF.my_number = my_class.my_number AND
59 SELF.my_name = my_class.my_name THEN
60
61
62 RETURN true_value;
63
64 ELSE
65
66
67 RETURN false_value;
68
69 END IF;
70
71 END;
72
73 END;
74 /
Type body created.
SQL>
SQL> SHOW ERRORS
No errors.
SQL>
SQL>
Use user-defined type as parameter
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000);
3 BEGIN
4 IF set_in IS NULL THEN
5 dbms_output.put_line("Null");
6 END IF;
7 RETURN returnValue;
8 END format_list;
9 /
Function created.
SQL>