Oracle PL/SQL/Object Oriented Database/Object Method — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16: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
<source lang="sql">
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>
</source>
A sample object type with a MAP member function
<source lang="sql">
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.
</source>
A sample object type with an ORDER member function.
<source lang="sql">
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.
</source>
Compare two type object instances
<source lang="sql">
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>
</source>
Creating User-defined Functions for Column Objects
<source lang="sql">
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>
</source>
Demonstrates the member method.
<source lang="sql">
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>
</source>
This script builds a sample object type with member variables and functions
<source lang="sql">
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>
</source>
This script demonstrates the order method.
<source lang="sql">
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>
</source>
This script demonstrates the static method.
<source lang="sql">
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>
</source>
Type with member procedure
<source lang="sql">
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.
</source>
Use method from object in select command
<source lang="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> 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>
</source>