Oracle PL/SQL Tutorial/Object Oriented/Member Function
Содержание
- 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
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.
Call Object member function
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>
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>
This script builds a sample object type with constructor.
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>
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 function
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>
Type with toString and mapping functions
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.