в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:44Z

1 версия

Новая страница

== Accessing the object table by reference==

The built-in function ref returns a pointer to the object.
The variable you need to declare is not of type employeeType, but ref employeeType.



<source lang="sql">
SQL>
SQL>
SQL> create type employeeType is object (
2 empNo NUMBER,
3 eName VARCHAR2(10),
4 job VARCHAR2(9),
5 mgr NUMBER,
6 hireDate DATE,
7 sal NUMBER,
8 comm NUMBER,
9 deptNo NUMBER,
10 member procedure p_changeName (i_newName_tx VARCHAR2),
11 member function f_getIncome_nr return VARCHAR2
12 )
13 /
SP2-0816: Type created with compilation warnings
SQL>
SQL> create or replace type body employeeType as
2 member function f_getIncome_nr return VARCHAR2 is
3 begin
4 return sal+comm;
5 end f_getIncome_nr;
6 member procedure p_changeName(i_newName_tx VARCHAR2) is
7 begin
8 eName:=i_newName_tx;
9 end p_changeName;
10 end;
11 /
SP2-0818: Type Body created with compilation warnings
SQL>
SQL> create table t_emp of employeeType;
Table created.
SQL>
SQL>
SQL>
SQL> declare
2 v_emp_oref ref employeeType;
3 v_employeeType employeeType;
4
5 begin
6 v_employeeType:=employeeType(100,"TestEmp",null,null,sysdate,1000,500,10);
7 insert into t_emp values v_employeeType;
8
9
10 select ref(t) into v_emp_oref from t_emp t where empNo=100;
11
12 update t_emp t set sal=sal+100 where ref(t)=v_emp_oref;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
100 TestEmp 09-JUN-07 1100 500 10
SQL>
SQL> drop table t_emp;
Table dropped.
SQL> drop type employeeType;
Type dropped.
SQL></source>

== Alter table to add a user-defined type column==



<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> select name, prev.city, prev.state, prev.zip_code
2 from people p, table( p.previous_addresses ) prev
3 where prev.state = "PA";
no rows selected
SQL> /
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>

== Create a table based on an Object with methods==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
2 (line1 VARCHAR2(20),
3 line2 VARCHAR2(20),
4 city VARCHAR2(20),
5 state_code VARCHAR2(2),
6 zip VARCHAR2(13),
7 MEMBER FUNCTION get_address RETURN VARCHAR2,
8 MEMBER PROCEDURE set_address
9 (addressLine1 VARCHAR2,
10 addressLine2 VARCHAR2,
11 address_city VARCHAR2,
12 address_state VARCHAR2,
13 address_zip VARCHAR2)
14 );
15 /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER FUNCTION get_address RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
6 END get_address;
7 MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
8 addressLine2 VARCHAR2,
9 address_city VARCHAR2,
10 address_state VARCHAR2,
11 address_zip VARCHAR2)
12 IS
13 BEGIN
14 line1 :=addressLine1;
15
16 line2 :=addressLine2;
17
18 city :=address_city;
19
20 state_code :=address_state;
21
22 zip :=address_zip;
23
24 END set_address;
25 END;
26 /
Type body created.
SQL>
SQL> CREATE TABLE address_master OF address;
Table created.
SQL>
SQL> INSERT INTO address_master VALUES (address("19 J","R Rd","Vancouver","NJ","00000"));
1 row created.
SQL>
SQL> select * from address_master;
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J R Rd Vancouver NJ
00000

1 row selected.
SQL>
SQL> drop table address_master;
Table dropped.
SQL></source>

== Insert data just as one would ordinarily do with a common SQL table:==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address addressType)
4 /
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam",
2 addressType("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> select * from emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
---------- -------------------- --------------------------------------------------
101 Adam ADDRESSTYPE("1 A St.", "Mobile", "AL", "36608")
SQL>
SQL>
SQL> CREATE TABLE address_table OF addressType;
Table created.
SQL>
SQL>
SQL> INSERT INTO address_table VALUES ("4 D St.", "Gulf","FL","32563");
1 row created.
SQL>
SQL>
SQL> SELECT city
2 FROM address_table;
CITY
--------------------
Gulf
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL></source>

== Inserting a row into an object table==



<source lang="sql">
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 /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
2 /
Table created.
SQL>
SQL>
SQL> INSERT INTO object_products VALUES (
2 ProductType(1, "AAA", "BBB", 3.95, 10)
3 );
1 row created.
SQL>
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 3.95 10
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL></source>

== INSERT Values into a Table that Contains Row Objects (TCRO)==

One way to begin is to insert into the client table and null the addressType:



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
2 /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table)
3 /
Table created.
SQL>
SQL> DESC client;
Name Null? Type
----------
NAME VARCHAR2(20)
ADDRESS REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
2 FROM client;
NAME ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL></source>

== One-step INSERTs into a Table that Contains Row Objects==



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
2 /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
Name Null? Type
----------
NAME VARCHAR2(20)
ADDRESS REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client
2 SELECT "Walsh", REF(aa)
3 FROM address_table aa
4 WHERE zip = "32563";
0 rows created.
SQL>
SQL> SELECT name, DEREF(address)
2 FROM client;
no rows selected
SQL>
SQL>
SQL> select * from client;
no rows selected
SQL>
SQL> --DEREF (Dereference) the Row Addresses
SQL> SELECT name, DEREF(address)
2 FROM client;
no rows selected
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addressType;
Type dropped.</source>

== SELECT from the object-oriented table==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
2 /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL> DESC client;
Name Null? Type
----------
NAME VARCHAR2(20)
ADDRESS REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
2 FROM client;
NAME ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> UPDATE client SET address =
2 (SELECT REF(aa)
3 FROM address_table aa
4 WHERE aa.city LIKE "Mob%")
5 WHERE name = "Jones"
6
SQL> select * from client;
NAME ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
SQL></source>

== SELECTing Individual Columns in Table that Contains Row Objects==



<source lang="sql">
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
2 /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
Name Null? Type
---------
NAME VARCHAR2(20)
ADDRESS REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client
2 SELECT "Walsh", REF(aa)
3 FROM address_table aa
4 WHERE zip = "32563";
0 rows created.
SQL>
SQL> SELECT c.name, c.address.city
2 FROM client c;
no rows selected
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addressType;
Type dropped.
SQL></source>

== Selecting Rows from the object Table==



<source lang="sql">
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 /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
2 /
Table created.
SQL>
SQL> INSERT INTO object_products (
2 id, name, description, price, days_valid
3 ) VALUES (
4 2, "AAA", "BBB", 2.99, 5
5 );
1 row created.
SQL>
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
2 AAA BBB 2.99 5
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL></source>

== UPDATE a Table that Contains Row Objects (TCRO)==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5))
6 /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType
2 /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
Name Null? Type
----------
NAME VARCHAR2(20)
ADDRESS REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
2 FROM client;
NAME ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> UPDATE client SET address =
2 (SELECT REF(aa)
3 FROM address_table aa
4 WHERE aa.city LIKE "Mob%")
5 WHERE name = "Jones"
6
SQL> select * from client;
NAME ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL> drop type addresstype;
Type dropped.</source>

== Updating a Row in the object Table==



<source lang="sql">
SQL>
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 /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
2 /
Table created.
SQL>
SQL> INSERT INTO object_products (
2 id, name, description, price, days_valid
3 ) VALUES (
4 1, "AAA", "BBB", 2.99, 5
5 );
1 row created.
SQL>
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA BBB 2.99 5
SQL>
SQL> UPDATE object_products
2 SET description = "new"
3 WHERE id = 1;
1 row updated.
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
1 AAA new 2.99 5
SQL>
SQL> drop table object_products;
Table dropped.
SQL></source>

== You can also use an object type to define an entire table, and the table is known as an object table.==



<source lang="sql">
SQL>
SQL>
SQL> CREATE or replace TYPE EmployeeType AS OBJECT (
2 id NUMBER,
3 name VARCHAR2(15),
4 description VARCHAR2(22),
5 salary NUMBER(5, 2)
6 );
7 /
Type created.
SQL> CREATE TABLE object_employee OF EmployeeType;
Table created.
SQL>
SQL> desc object_employee;
Name Null? Type
ID NUMBER
NAME VARCHAR2(15)
DESCRIPTION VARCHAR2(22)
SALARY NUMBER(5,2)
SQL>
SQL> drop table object_employee;
Table dropped.
SQL>
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Object Oriented/Object Table - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия