Oracle PL/SQL Tutorial/Object Oriented/Member Function — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
- 1 A sample object type with a MAP member function
- 2 A sample object type with an ORDER member function.
- 3 Call Object member function
- 4 Compare two type object instances
- 5 This script builds a sample object type with constructor.
- 6 This script demonstrates the static method.
- 7 Type with member function
- 8 Type with toString and mapping functions
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>
Call Object member function
<source lang="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 MEMBER FUNCTION getByDate RETURN DATE 9 ); 10 /
Type created. SQL> SQL> CREATE or replace TYPE BODY ProductType AS
2 MEMBER FUNCTION getByDate RETURN DATE IS 3 v_by_date DATE; 4 BEGIN 5 SELECT SYSDATE 6 INTO v_by_date 7 FROM dual; 8 9 RETURN v_by_date; 10 END; 11 END; 12 /
Type body created. SQL> SQL> CREATE TABLE products (
2 product ProductType, 3 count NUMBER 4 );
Table created. SQL> SQL> created. SQL> SQL> SQL> INSERT INTO products (product,count) VALUES (
2 ProductType(1, "AA", "BBB", 3.95, 10),50 3 );
1 row created. SQL> SQL> INSERT INTO products (product,count) VALUES (
2 ProductType(2, "CC", "DDDD", 2.99, 5),25 3 );
1 row created. SQL> SQL> SELECT p.product.getByDate()
2 FROM products p;
P.PRODUCT
05-JUN-07 05-JUN-07 SQL> SQL> drop table products; Table dropped. SQL> SQL></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>
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 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 function
<source lang="sql">
SQL> SQL> SQL> CREATE TYPE cd_keyword_tab_t AS TABLE OF VARCHAR2 (60);
2 /
Type created. SQL> SQL> CREATE TYPE cd_t AS OBJECT(
2 id INTEGER, 3 title VARCHAR2 (60), 4 artist VARCHAR2 (60), 5 label VARCHAR2 (60), 6 keywords cd_keyword_tab_t, 7 MAP MEMBER FUNCTION compare RETURN VARCHAR2 8 ); 9 /
Type created. SQL> drop type cd_t; Type dropped. SQL> drop type cd_keyword_tab_t; Type dropped. SQL></source>
Type with toString and mapping functions
<source lang="sql">
SQL> SQL> create or replace type Address_Type
2 as object 3 ( street_addr1 varchar2(25), 4 street_addr2 varchar2(25), 5 city varchar2(30), 6 state varchar2(2), 7 zip_code number 8 ) 9 /
Type created. SQL> SQL> alter type Address_Type
2 REPLACE 3 as object 4 ( street_addr1 varchar2(25), 5 street_addr2 varchar2(25), 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2, 10 map member function mapping_function return varchar2 11 ) 12 /
Type altered. SQL> SQL> create or replace type body Address_Type
2 as 3 member function toString return varchar2 4 is 5 begin 6 if ( street_addr2 is not NULL ) 7 then 8 return street_addr1 || " " || 9 street_addr2 || " " || 10 city || ", " || state || " " || zip_code; 11 else 12 return street_addr1 || " " || 13 city || ", " || state || " " || zip_code; 14 end if; 15 end; 16 17 map member function mapping_function return varchar2 18 is 19 begin 20 return to_char( nvl(zip_code,0), "fm00000" ) || 21 lpad( nvl(city," "), 30 ) || 22 lpad( nvl(street_addr1," "), 25 ) || 23 lpad( nvl(street_addr2," "), 25 ); 24 end; 25 end; 26 /
Type body created. SQL> SQL> SQL> create table people
2 ( name varchar2(10), 3 home_address address_type, 4 work_address address_type 5 ) 6 /
Table created. SQL> SQL> SQL> create or replace type Address_Array_Type
2 as varray(50) of Address_Type 3 /
Type created. SQL> SQL> alter table people add previous_addresses Address_Array_Type
2 /
Table altered. SQL> set echo on SQL> SQL> declare
2 l_prev_addresses address_Array_Type; 3 begin 4 select p.previous_addresses into l_prev_addresses from people p 5 where p.name = "Tom Kyte"; 6 7 l_prev_addresses.extend; 8 l_prev_addresses(l_prev_addresses.count) := Address_Type( "1 Street", null,"Reston", "VA", 45678 ); 9 10 update people set previous_addresses = l_prev_addresses where name = "Tom Kyte"; 11 end; 12 /
declare
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4
SQL> SQL> SQL> select name, prev.city, prev.state, prev.zip_code from people p, table( p.previous_addresses ) prev
2 /
no rows selected SQL> SQL> drop table people; Table dropped. SQL> drop type Address_Array_Type; Type dropped. SQL> drop type address_type; Type dropped.</source>