Oracle PL/SQL/Object Oriented Database/Object Method — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Access member variable in member function in a type
- 2 A sample object type with a MAP member function
- 3 A sample object type with an ORDER member function.
- 4 Compare two type object instances
- 5 Creating User-defined Functions for Column Objects
- 6 Demonstrates the member method.
- 7 This script builds a sample object type with member variables and functions
- 8 This script demonstrates the order method.
- 9 This script demonstrates the static method.
- 10 Type with member procedure
- 11 Use method from object in select command
Access member variable in member function in a type
SQL>
SQL>
SQL> create or replace
2 type employee as object(
3 name varchar2(100),
4 empno number,
5 hiredate date,
6 vacation_used number,
7 final member procedure vacation( p_days number ),
8 not instantiable member procedure give_raise( p_increase number ),
9 not instantiable member function yearly_compensation return number
10 )
11 not instantiable
12 not final
13 /
Type created.
SQL>
SQL> create or replace
2 type body employee as
3 final member procedure vacation( p_days number ) is
4 begin
5 if p_days + self.vacation_used <= 10 then
6 self.vacation_used := self.vacation_used + p_days;
7 else
8 raise_application_error(
9 -20001,
10 "You are " || to_char(p_days + self.vacation_used - 10) ||
11 " days over your vacation limit." );
12 end if;
13 end;
14 end;
15 /
Type body created.
SQL>
SQL>
SQL> create or replace
2 type sales_rep
3 under employee(
4 salary number,
5 commission number,
6 overriding member procedure give_raise( p_increase number ),
7 member procedure give_commission( p_increase number ),
8 overriding member function yearly_compensation return number
9 )
10 /
Type created.
SQL> create or replace
2 type body sales_rep as
3 overriding member procedure give_raise( p_increase number ) is
4 begin
5 self.salary := self.salary + (self.salary * (p_increase/100));
6 end;
7 member procedure give_commission( p_increase number ) is
8 begin
9 self.rumission := self.rumission + p_increase;
10 end;
11 overriding member function yearly_compensation return number is
12 begin
13 return self.salary + self.rumission;
14 end;
15 end;
16 /
Type body created.
SQL>
SQL> drop type sales_rep;
Type dropped.
SQL> drop type employee;
Type dropped.
SQL>
SQL>
A sample object type with a MAP member function
SQL>
SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT
2 ( my_number NUMBER
3 , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
4 , CONSTRUCTOR FUNCTION myType( my_number NUMBER )RETURN SELF AS RESULT
5 , MEMBER PROCEDURE print_instance_variable
6 , MAP MEMBER FUNCTION equals RETURN NUMBER )INSTANTIABLE NOT FINAL;
7 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
2 CONSTRUCTOR FUNCTION myType
3 RETURN SELF AS RESULT IS
4 my_instance_number NUMBER := 0;
5 BEGIN
6 SELF.my_number := my_instance_number;
7 RETURN;
8 END;
9 CONSTRUCTOR FUNCTION myType( my_number NUMBER )
10 RETURN SELF AS RESULT IS
11 BEGIN
12 SELF.my_number := my_number;
13 RETURN;
14 END;
15 MEMBER PROCEDURE print_instance_variable IS
16 BEGIN
17 DBMS_OUTPUT.PUT_LINE("Instance Variable ["||SELF.my_number||"]");
18 END;
19 MAP MEMBER FUNCTION equals
20 RETURN NUMBER IS
21 BEGIN
22 RETURN SELF.my_number;
23
24 END;
25
26 END;
27 /
Type body created.
SQL>
SQL>
SQL> DECLARE
2 obj1 myType := myType;
3 obj2 myType := myType(1);
4
5 BEGIN
6 obj1.print_instance_variable;
7
8 obj2.print_instance_variable;
9
10 IF obj1 = obj2 THEN
11 DBMS_OUTPUT.PUT_LINE("equal.");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("unequal.");
14 END IF;
15
16 END;
17 /
Instance Variable [0]
Instance Variable [1]
unequal.
PL/SQL procedure successfully completed.
A sample object type with an ORDER member function.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT
2 ( my_number NUMBER
3 , my_name VARCHAR2(20 CHAR)
4 , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
5 , CONSTRUCTOR FUNCTION myType ( my_number NUMBER, my_name VARCHAR2 )RETURN SELF AS RESULT
6 , MEMBER PROCEDURE print_instance_variable
7 , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )INSTANTIABLE NOT FINAL;
8 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
2 CONSTRUCTOR FUNCTION myType
3 RETURN SELF AS RESULT IS
4 my_instance_number NUMBER := 0;
5 my_instance_name VARCHAR2(20 CHAR) := "";
6
7 BEGIN
8 SELF.my_number := my_instance_number;
9 SELF.my_name := my_instance_name;
10 RETURN;
11
12 END;
13
14 CONSTRUCTOR FUNCTION myType( my_number NUMBER , my_name VARCHAR2 )
15 RETURN SELF AS RESULT IS
16 BEGIN
17 SELF.my_number := my_number;
18 SELF.my_name := my_name;
19 RETURN;
20 END;
21
22 MEMBER PROCEDURE print_instance_variable IS
23 BEGIN
24 DBMS_OUTPUT.PUT_LINE("Number:"||SELF.my_number);
25 DBMS_OUTPUT.PUT_LINE("Name :"||SELF.my_name);
26 END;
27 ORDER MEMBER FUNCTION equals( my_class myType )RETURN NUMBER IS
28 false_value NUMBER := 0;
29 true_value NUMBER := 1;
30 BEGIN
31 IF SELF.my_number = my_class.my_number AND SELF.my_name = my_class.my_name THEN
32 RETURN true_value;
33 ELSE
34 RETURN false_value;
35 END IF;
36 END;
37 END;
38 /
Type body created.
SQL>
SQL> DECLARE
2 obj1 myType := myType;
3 obj2 myType := myType(1,"My Object");
4
5 BEGIN
6 obj1.print_instance_variable;
7
8 obj2.print_instance_variable;
9
10 IF obj1.equals(obj2) = 1 THEN
11 DBMS_OUTPUT.PUT_LINE("equal.");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("unequal.");
14 END IF;
15
16 END;
17 /
Number:0
Name :
Number:1
Name :My Object
unequal.
PL/SQL procedure successfully completed.
Compare two type object instances
SQL> CREATE OR REPLACE TYPE empName AUTHID CURRENT_USER IS OBJECT
2 ( fname VARCHAR2(20 CHAR)
3 , lname VARCHAR2(20 CHAR)
4 , CONSTRUCTOR FUNCTION empName RETURN SELF AS RESULT
5 , CONSTRUCTOR FUNCTION empName ( fname VARCHAR2, lname VARCHAR2 )RETURN SELF AS RESULT
6 , MEMBER PROCEDURE print_instance_variable
7 , ORDER MEMBER FUNCTION equals( my_class empName ) RETURN NUMBER )
8 INSTANTIABLE NOT FINAL;
9 /
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY empName AS
2 CONSTRUCTOR FUNCTION empName RETURN SELF AS RESULT IS
3 fname VARCHAR2(20 CHAR) := NULL;
4 lname VARCHAR2(20 CHAR) := NULL;
5 BEGIN
6 SELF.fname := fname;
7 SELF.lname := lname;
8 RETURN;
9 END;
10 CONSTRUCTOR FUNCTION empName(fname VARCHAR2, lname VARCHAR2)RETURN SELF AS RESULT IS
11 BEGIN
12 SELF.fname := fname;
13 SELF.lname := lname;
14 RETURN;
15 END;
16 MEMBER PROCEDURE print_instance_variable IS
17 BEGIN
18 DBMS_OUTPUT.PUT_LINE("Name ["||SELF.fname||", "||SELF.lname||"]");
19 END;
20
21 ORDER MEMBER FUNCTION equals( my_class empName )RETURN NUMBER IS
22 false_value NUMBER := 0;
23 true_value NUMBER := 1;
24 BEGIN
25 IF SELF.fname = my_class.fname AND SELF.lname = my_class.lname THEN
26 RETURN true_value;
27 ELSE
28 RETURN false_value;
29 END IF;
30 END;
31 END;
32 /
Type body created.
SQL>
SQL> CREATE OR REPLACE TYPE empType AUTHID CURRENT_USER IS OBJECT
2 ( ename empName
3 , address VARCHAR2(20 CHAR)
4 , city VARCHAR2(14 CHAR)
5 , state VARCHAR2(2 CHAR)
6 , postal_code VARCHAR2(5 CHAR)
7 , CONSTRUCTOR FUNCTION empType RETURN SELF AS RESULT
8 , CONSTRUCTOR FUNCTION empType(ename empName,address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )RETURN SELF AS RESULT
9 , CONSTRUCTOR FUNCTION empType(fname VARCHAR2, lname VARCHAR2, address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )RETURN SELF AS RESULT
10 , MEMBER PROCEDURE print_instance_variable, ORDER MEMBER FUNCTION equals( my_class empType ) RETURN NUMBER )
11 INSTANTIABLE NOT FINAL;
12 /
Type created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY empType AS CONSTRUCTOR FUNCTION empType RETURN SELF AS RESULT IS
2 fname VARCHAR2(20 CHAR) := NULL;
3 lname VARCHAR2(20 CHAR) := NULL;
4 address VARCHAR2(20 CHAR) := NULL;
5 city VARCHAR2(14 CHAR) := NULL;
6 state VARCHAR2(2 CHAR) := NULL;
7 postal_code VARCHAR2(5 CHAR) := NULL;
8 BEGIN
9 SELF.ename := empName(fname,lname);
10 SELF.address := address;
11 SELF.city := city;
12 SELF.state := state;
13 SELF.postal_code := postal_code;
14 RETURN;
15 END;
16 CONSTRUCTOR FUNCTION empType(ename empName, address VARCHAR2, city VARCHAR2, state VARCHAR2, postal_code VARCHAR2 )
17 RETURN SELF AS RESULT IS
18 BEGIN
19 SELF.ename := ename;
20 SELF.address := address;
21 SELF.city := city;
22 SELF.state := state;
23 SELF.postal_code := postal_code;
24 RETURN;
25
26 END;
27
28 CONSTRUCTOR FUNCTION empType(fname VARCHAR2,lname VARCHAR2,address VARCHAR2,city VARCHAR2,state VARCHAR2,postal_code VARCHAR2 )
29 RETURN SELF AS RESULT IS
30 BEGIN
31 SELF.ename := empName(fname,lname);
32 SELF.address := address;
33 SELF.city := city;
34 SELF.state := state;
35 SELF.postal_code := postal_code;
36 RETURN;
37
38 END;
39 MEMBER PROCEDURE print_instance_variable IS
40 BEGIN
41 SELF.ename.print_instance_variable;
42 DBMS_OUTPUT.PUT_LINE("First Name : "||SELF.ename.fname);
43 DBMS_OUTPUT.PUT_LINE("Last Name : "||SELF.ename.lname);
44 DBMS_OUTPUT.PUT_LINE("Street Address: "||SELF.address);
45 DBMS_OUTPUT.PUT_LINE("City : "||SELF.city);
46 DBMS_OUTPUT.PUT_LINE("State : "||SELF.state);
47 DBMS_OUTPUT.PUT_LINE("Postal Code : "||SELF.postal_code);
48
49 END;
50
51 ORDER MEMBER FUNCTION equals( my_class empType)RETURN NUMBER IS
52 false_value NUMBER := 0;
53 true_value NUMBER := 1;
54 BEGIN
55 IF SELF.ename.equals(my_class.ename) = true_value AND SELF.address = my_class.address AND SELF.city = my_class.city AND SELF.state = my_class.state AND
56 SELF.postal_code = my_class.postal_code THEN
57 RETURN true_value;
58 ELSE
59 RETURN false_value;
60
61 END IF;
62
63 END;
64
65 END;
66 /
Type body created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 obj1 empType := empType("A","B","1 str","CA","E","111");
3 obj2 empType := empType(empName("A","B"),"1 rd","city","TX","222");
4 BEGIN
5 obj1.print_instance_variable;
6
7 obj2.print_instance_variable;
8
9 IF obj1.equals(obj2) = 1 THEN
10 DBMS_OUTPUT.PUT_LINE("equal.");
11 ELSE
12 DBMS_OUTPUT.PUT_LINE("unequal.");
13 END IF;
14 END;
15 /
Name [A, B]
First Name : A
Last Name : B
Street Address: 1 str
City : CA
State : E
Postal Code : 111
Name [A, B]
First Name : A
Last Name : B
Street Address: 1 rd
City : city
State : TX
Postal Code : 222
unequal.
PL/SQL procedure successfully completed.
SQL>
Creating User-defined Functions for Column Objects
SQL> -- Creating User-defined Functions for Column Objects
SQL>
SQL> CREATE OR REPLACE TYPE aobj AS object (
2 state CHAR(2),
3 amt NUMBER(5),
4
5 MEMBER FUNCTION mult (times in number) RETURN number,
6 PRAGMA RESTRICT_REFERENCES(mult, WNDS));
7 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY aobj AS
2 MEMBER FUNCTION mult (times in number) RETURN number
3 IS
4 BEGIN
5 RETURN times * self.amt; /* SEE BELOW */
6 END;
7 END;
8 /
Type body created.
SQL>
SQL> CREATE TABLE aobjtable (arow aobj);
Table created.
SQL>
SQL>
SQL> DESC aobjtable;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW AOBJ
SQL>
SQL> drop table aobjtable;
Table dropped.
SQL>
SQL>
Demonstrates the member method.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
2 rebate NUMBER (10, 4),
3 price NUMBER (10, 2),
4 MEMBER FUNCTION discount_price
5 RETURN NUMBER
6 )
7 INSTANTIABLE FINAL;
8 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY BookType
2 AS
3 MEMBER FUNCTION discount_price
4 RETURN NUMBER
5 IS
6 BEGIN
7 RETURN (SELF.price * (1 - SELF.rebate));
8 END discount_price;
9 END;
10 /
Type body created.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
2 v_price BookType := BookType(.1, 75.00);
3 BEGIN
4 dbms_output.put_line(v_price.discount_price);
5 END;
6 /
67.5
PL/SQL procedure successfully completed.
SQL>
This script builds a sample object type with member variables and functions
SQL>
SQL> CREATE OR REPLACE TYPE myType AUTHID CURRENT_USER IS OBJECT
2 ( fname VARCHAR2(20 CHAR)
3 , lname VARCHAR2(20 CHAR)
4 , CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT
5 , CONSTRUCTOR FUNCTION myType ( fname VARCHAR2, lname VARCHAR2 )RETURN SELF AS RESULT
6 , MEMBER PROCEDURE print_instance_variable
7 , ORDER MEMBER FUNCTION equals( my_class myType ) RETURN NUMBER )
8 INSTANTIABLE NOT FINAL;
9 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY myType AS
2 CONSTRUCTOR FUNCTION myType RETURN SELF AS RESULT IS
3 fname VARCHAR2(20 CHAR) := NULL;
4 lname VARCHAR2(20 CHAR) := NULL;
5 BEGIN
6 SELF.fname := fname;
7 SELF.lname := lname;
8 RETURN;
9 END;
10 CONSTRUCTOR FUNCTION myType(fname VARCHAR2, lname VARCHAR2)RETURN SELF AS RESULT IS
11 BEGIN
12 SELF.fname := fname;
13 SELF.lname := lname;
14 RETURN;
15 END;
16 MEMBER PROCEDURE print_instance_variable IS
17 BEGIN
18 DBMS_OUTPUT.PUT_LINE("Name ["||SELF.fname||", "||SELF.lname||"]");
19 END;
20
21 ORDER MEMBER FUNCTION equals( my_class myType )RETURN NUMBER IS
22 false_value NUMBER := 0;
23 true_value NUMBER := 1;
24 BEGIN
25 IF SELF.fname = my_class.fname AND SELF.lname = my_class.lname THEN
26 RETURN true_value;
27 ELSE
28 RETURN false_value;
29 END IF;
30 END;
31 END;
32 /
Type body created.
SQL>
SQL> DECLARE
2 obj1 myType := myType;
3 obj2 myType := myType("A","B");
4 BEGIN
5
6 obj1.print_instance_variable;
7
8 obj2.print_instance_variable;
9
10 IF obj1.equals(obj2) = 1 THEN
11 DBMS_OUTPUT.PUT_LINE("equal.");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("unequal.");
14 END IF;
15 END;
16 /
Name [, ]
Name [A, B]
unequal.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
This script demonstrates the order method.
SQL> CREATE OR REPLACE TYPE book_obj AS OBJECT (
2 isbn CHAR (10),
3 title VARCHAR2 (100),
4 num_pages NUMBER,
5 ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
6 RETURN NUMBER
7 );
8 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY book_obj
2 AS
3 ORDER MEMBER FUNCTION compare_book (p_isbn IN BOOK_OBJ)
4 RETURN NUMBER
5 IS
6 BEGIN
7 IF p_isbn.isbn < SELF.isbn
8 THEN
9 RETURN 1;
10 ELSIF p_isbn.isbn > SELF.isbn
11 THEN
12 RETURN -1;
13 ELSE
14 RETURN 0;
15 END IF;
16 END compare_book;
17 END;
18 /
Type body created.
SQL>
This script demonstrates the static method.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE PriceType AS OBJECT (
2 discount_rate NUMBER (10, 4),
3 price NUMBER (10, 2),
4 STATIC FUNCTION new_price (p_price IN NUMBER,rebate IN NUMBER DEFAULT .1)
5 RETURN NUMBER
6 )
7 INSTANTIABLE FINAL;
8 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY PriceType
2 AS
3 STATIC FUNCTION new_price(p_price IN NUMBER,rebate IN NUMBER DEFAULT .1)
4 RETURN NUMBER
5 IS
6 BEGIN
7 RETURN (p_price * (1 - rebate));
8 END new_price;
9 END;
10 /
Type body created.
SQL>
SQL> -- exec DBMS_OUTPUT.PUT_LINE(PriceType.new_price(75));
SQL>
Type with member procedure
SQL>
SQL> CREATE TABLE emp
2 (id VARCHAR2(10) NOT NULL,
3 course VARCHAR2(10),
4 year VARCHAR2(4),
5 PRIMARY KEY (id));
Table created.
SQL> CREATE OR REPLACE TYPE empType AS OBJECT
2 (id VARCHAR2(10),
3 course VARCHAR2(20),
4 year VARCHAR2(4),
5 MEMBER PROCEDURE Delete_emp )
6 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY empType AS
2 MEMBER PROCEDURE
3 Delete_emp IS
4 BEGIN
5 DELETE FROM emp
6 WHERE emp.id = self.id;
7 END Delete_emp;
8 END;
9 /
Type body created.
SQL> drop table emp;
Table dropped.
Use method from object in select command
SQL> CREATE OR REPLACE TYPE aobj AS object (
2 state CHAR(2),
3 amt NUMBER(5),
4
5 MEMBER FUNCTION mult (times in number) RETURN number,
6 PRAGMA RESTRICT_REFERENCES(mult, WNDS));
7 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY aobj AS
2 MEMBER FUNCTION mult (times in number) RETURN number
3 IS
4 BEGIN
5 RETURN times * self.amt; /* SEE BELOW */
6 END;
7 END;
8 /
Type body created.
SQL>
SQL> CREATE TABLE aobjtable (arow aobj);
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO aobjtable VALUES (aobj("FL",25));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("AL",35));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("OH",15));
1 row created.
SQL>
SQL> select * from aobjtable;
AROW(STATE, AMT)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AOBJ("FL", 25)
AOBJ("AL", 35)
AOBJ("OH", 15)
SQL>
SQL> DESC aobjtable;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW AOBJ
SQL>
SQL> drop table aobjtable;
Table dropped.
SQL>
SQL>