Oracle PL/SQL Tutorial/Object Oriented/Nested Object Table
Содержание
- 1 A row is selected based on the inner column object
- 2 DELETE on whole Nested Tables
- 3 DML on whole Nested Tables
- 4 Inserting Rows into the nested-object table
- 5 Object References
- 6 Select a single row from the nested-object table
- 7 Selecting Rows from the nested-object table
- 8 SELECT on whole Nested Tables
- 9 Stored Nested Tables
- 10 UPDATE on whole Nested Tables
A row is selected based on the inner column object
SQL>
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 )
7 /
Type created.
SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 dob DATE,
6 phone VARCHAR2(12),
7 address AddressType
8 )
9 /
Type created.
SQL>
SQL> CREATE TABLE object_customers OF PersonType
2 /
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
2 PersonType(1, "John", "White", "04-FEB-1945", "800-555-5555",
3 AddressType("2 Ave", "City", "AA", "12345")
4 )
5 );
1 row created.
SQL>
SQL> INSERT INTO object_customers (
2 id, first_name, last_name, dob, phone,
3 address
4 ) VALUES (
5 2, "James", "Green", "05-FEB-1968", "800-555-4444",
6 AddressType("3 Ave", "Town", "CC", "12345")
7 );
1 row created.
SQL>
SQL>
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------
1 John White 04-FEB-45 800-555-5555 ADDRESSTYPE("2 Ave", "City", "AA", "12345")
2 James Green 05-FEB-68 800-555-4444 ADDRESSTYPE("3 Ave", "Town", "CC", "12345")
SQL>
SQL> SELECT *
2 FROM object_customers oc
3 WHERE oc.address.state = "MA";
no rows selected
SQL>
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
DELETE on whole Nested Tables
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (list_id VARCHAR2(6)PRIMARY KEY,
2 home_addresses numberTableType )
3 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL>
SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));
SQL>
SQL>
SQL>
SQL> DELETE FROM address_list
2 WHERE list_id ="H102";
SQL>
SQL> drop table address_list;
DML on whole Nested Tables
SQL> -- INSERT
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 home_addresses numberTableType )
4 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL>
SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));
SQL>
SQL> DECLARE
2 v_numberVarryType numberTableType :=numberTableType(2001,2002);
3 BEGIN
4 INSERT INTO address_list VALUES ("H102",v_numberVarryType);
5 END;
6 /
SQL>
SQL>
SQL>
SQL> drop table address_list;
SQL>
SQL>
Inserting Rows into the nested-object table
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 )
7 /
Type created.
SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 dob DATE,
6 phone VARCHAR2(12),
7 address AddressType
8 )
9 /
Type created.
SQL>
SQL> CREATE TABLE object_customers OF PersonType;
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
2 PersonType(1, "John", "White", "04-FEB-1945", "800-555-5555",
3 AddressType("2 Ave", "town", "AA", "12345")
4 )
5 );
1 row created.
SQL>
SQL> INSERT INTO object_customers (
2 id, first_name, last_name, dob, phone,
3 address
4 ) VALUES (
5 2, "James", "Green", "05-FEB-1968", "800-555-4444",
6 AddressType("3 Ave", "City", "CC", "12345")
7 );
1 row created.
SQL>
SQL>
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
---------------------------------------------------------------------------------------------------------
1 John White 04-FEB-45 800-555-5555 ADDRESSTYPE("2 Ave", "town", "AA", "12345")
2 James Green 05-FEB-68 800-555-4444 ADDRESSTYPE("3 Ave", "City", "CC", "12345")
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
Object References
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 END set_address;
24 END;
25 /
Type body created.
SQL> CREATE TABLE employee
2 (empid number(10)PRIMARY KEY,
3 lastname varchar2(30)NOT NULL,
4 firstname varchar2(30)NOT NULL,
5 middle_initial varchar2(2),
6 emp_address REF address);
Table created.
SQL>
SQL>
SQL> DROP TABLE employee;
Table dropped.
SQL>
Select a single row from the nested-object table
SQL>
SQL>
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 )
7 /
Type created.
SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 dob DATE,
6 phone VARCHAR2(12),
7 address AddressType
8 )
9 /
Type created.
SQL>
SQL> CREATE TABLE object_customers OF PersonType
2 /
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
2 PersonType(1, "John", "White", "04-FEB-1945", "800-555-5555",
3 AddressType("2 Street", "City", "MA", "12345")
4 )
5 );
1 row created.
SQL>
SQL>
SQL> INSERT INTO object_customers (
2 id, first_name, last_name, dob, phone,
3 address
4 ) VALUES (
5 2, "James", "Green", "05-FEB-1968", "800-555-4444",
6 AddressType("3 Ave", "City", "CA", "12345")
7 );
1 row created.
SQL>
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------
1 John White 04-FEB-45 800-555-5555 ADDRESSTYPE("2 Street", "City", "MA", "12345")
2 James Green 05-FEB-68 800-555-4444 ADDRESSTYPE("3 Ave", "City", "CA", "12345")
SQL>
SQL> SELECT *
2 FROM object_customers oc
3 WHERE oc.id = 1;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------
1 John White 04-FEB-45 800-555-5555 ADDRESSTYPE("2 Street", "City", "MA", "12345")
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
Selecting Rows from the nested-object table
SQL>
SQL>
SQL> CREATE Or Replace TYPE AddressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 )
7 /
Type created.
SQL>
SQL>
SQL> CREATE Or Replace TYPE PersonType AS OBJECT (
2 id NUMBER,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 dob DATE,
6 phone VARCHAR2(12),
7 address AddressType
8 )
9 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE object_customers OF PersonType;
Table created.
SQL>
SQL> INSERT INTO object_customers VALUES (
2 PersonType(1, "John", "White", "04-FEB-1999", "800-555-5555",
3 AddressType("2 Ave", "town", "AA", "12345")
4 )
5 );
1 row created.
SQL>
SQL> INSERT INTO object_customers (
2 id, first_name, last_name, dob, phone,
3 address
4 ) VALUES (
5 2, "James", "Green", "05-FEB-1968", "800-555-4444",
6 AddressType("3 Ave", "City", "CA", "12345")
7 );
1 row created.
SQL>
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS(STREET, CITY, STATE, ZIP)
-------------------------------------------------------------------------
1 John White 04-FEB-99 800-555-5555 ADDRESSTYPE("2 Ave", "town", "AA", "12345")
2 James Green 05-FEB-68 800-555-4444 ADDRESSTYPE("3 Ave", "City", "CA", "12345")
SQL>
SQL> drop table object_customers;
Table dropped.
SQL> drop type persontype;
Type dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
SELECT on whole Nested Tables
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL> CREATE TABLE address_list (list_id VARCHAR2(6)PRIMARY KEY,
2 home_addresses numberTableType )
3 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL>
SQL> INSERT INTO address_list VALUES ("H101",numberTableType(1001,1002,1003,1004));
SQL>
SQL>
SQL> DECLARE
2 v_numberVarryType numberTableType :=numberTableType(2001,2002);
3 BEGIN
4 INSERT INTO address_list VALUES ("H102",v_numberVarryType);
5 END;
6 /
SQL>
SQL> drop table address_list;
Stored Nested Tables
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 home_addresses numberTableType )
4 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL> drop table address_list;
SQL>
SQL>
SQL>
UPDATE on whole Nested Tables
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE numberTableType AS TABLE OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 home_addresses numberTableType )
4 NESTED TABLE home_addresses STORE AS home_addreses_tab;
SQL>
SQL>
SQL> INSERT INTO address_list VALUES("H101",numberTableType(1001,1002,1003,1004));
SQL>
SQL>
SQL> DECLARE
2 v_numberVarryType numberTableType :=numberTableType(1011,1012,1013);
3 BEGIN
4 UPDATE address_list
5 SET home_addresses =v_numberVarryType
6 WHERE list_id ="H102";
7 END;
8 /
SQL>
SQL> drop table address_list;
SQL>