Oracle PL/SQL Tutorial/Object Oriented/Select
Содержание
- 1 COLUMN Formatting in SELECT for Object
- 2 Create type and use it in inner query
- 3 ORA-00932: inconsistent datatypes: expected REF
- 4 Querying Rows from the object Table
- 5 Query table column with user defined type
- 6 Select an individual column object from a table
- 7 SELECTing Only One Column in the Composite
- 8 Selecting Rows from the Table with object type column
- 9 Use table function to convert type to a "table"
- 10 Use user-defined type to combine query logic
COLUMN Formatting in SELECT for Object
SQL>
SQL> COLUMN address FORMAT a50;
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE ADDRESS_OBJ 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 (
2 empno NUMBER(3),
3 name VARCHAR2(20),
4 address ADDRESS_OBJ);
Table created.
SQL>
SQL> INSERT INTO emp VALUES (101, "A",ADDRESS_OBJ("1 St.","M","AL","36608"));
1 row created.
SQL>
SQL> select * from emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
---------- -------------------- --------------------------------------------------
101 A ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL>
SQL> select address from emp;
ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------
ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop type ADDRESS_OBJ;
Type dropped.
SQL>
Create type and use it in inner query
SQL> create or replace type myScalarType as object( cnt number, average number )
2 /
Type created.
SQL>
SQL> select username, a.data.cnt, a.data.average
2 from (
3 select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
4 /
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>
SQL>
ORA-00932: inconsistent datatypes: expected REF
SQL>
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 line2 :=addressLine2;
16 city :=address_city;
17 state_code :=address_state;
18 zip :=address_zip;
19 END set_address;
20 END;
21 /
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> DECLARE
2 addressValue address;
3 BEGIN
4 SELECT emp_address INTO addressValue FROM employee WHERE lastname ="LAKSHMAN";
5 DBMS_OUTPUT.PUT_LINE("The address of the employee LAKSHMAN is");
6 DBMS_OUTPUT.PUT_LINE(addressValue.line1||" "||addressValue.line2);
7 DBMS_OUTPUT.PUT_LINE(addressValue.city||", "||addressValue.state_code||" "||addressValue.zip);
8 END;
9 /
SELECT emp_address INTO addressValue FROM employee WHERE lastname ="LAKSHMAN";
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PL/SQL: ORA-00932: inconsistent datatypes: expected REF
sqle.ADDRESS got sqle.ADDRESS
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
SQL>
SQL> SELECT * FROM employee e WHERE e.emp_address.city ="Vancouver";
no rows selected
SQL>
SQL> SELECT e.emp_address.get_address() FROM employee e;
no rows selected
SQL>
SQL> DROP TABLE employee;
Table dropped.
Querying Rows from the object Table
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>
Query table column with user defined type
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> create table people
2 ( name varchar2(10),
3 home_address address_type,
4 work_address address_type
5 )
6 /
Table created.
SQL>
SQL> declare
2 l_home_address address_type;
3 l_work_address address_type;
4 begin
5 l_home_address := Address_Type( "1 Street", null,"R", "VA", 45678 );
6 l_work_address := Address_Type( "1 Way", null,"R", "CA", 23456 );
7
8 insert into people( name, home_address, work_address )values ( "Tom Kyte", l_home_address, l_work_address );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> column "HOME_ADDRESS.STATE" format a20
SQL> column "WORK_ADDRESS.STATE" format a20
SQL> select name, P.home_address.state, P.work_address.state from people P
2 /
NAME HOME_ADDRESS.STATE WORK_ADDRESS.STATE
---------- -------------------- --------------------
Tom Kyte VA CA
SQL> drop table people;
Table dropped.
SQL> drop type Address_Type;
Type dropped.
Select an individual column object from a table
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 products (
2 product ProductType,
3 count NUMBER
4 );
Table created.
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
2 FROM products p
3 WHERE p.product.id = 1;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
---------------------------------------------------
PRODUCTTYPE(1, "AA", "BBB", 3.95, 10)
SQL>
SQL> drop table products;
Table dropped.
SQL>
SELECTing Only One Column in the Composite
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE ADDRESS_OBJ 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 (
2 empno NUMBER(3),
3 name VARCHAR2(20),
4 address ADDRESS_OBJ)
5 /
Table created.
SQL>
SQL> INSERT INTO emp VALUES (101, "A",ADDRESS_OBJ("1 St.","M","AL","36608"));
1 row created.
SQL>
SQL> select * from emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
---------- -------------------- --------------------------------------------------
101 A ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL>
SQL> select address from emp;
ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------
ADDRESS_OBJ("1 St.", "M", "AL", "36608")
SQL> SELECT name, e.address.city
2 FROM emp e;
NAME ADDRESS.CITY
-------------------- --------------------
A M
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop type ADDRESS_OBJ;
Type dropped.
SQL>
SQL>
SQL>
Selecting Rows from the Table with object type column
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 products (
2 product ProductType,
3 count NUMBER
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO products (product,count) VALUES (
2 ProductType(2, "CC", "DDDD", 2.99, 5),25
3 );
1 row created.
SQL>
SQL> select * from products;
PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) COUNT
----------
PRODUCTTYPE(2, "CC", "DDDD", 2.99, 5) 25
SQL>
SQL>
SQL> drop table products;
Table dropped.
SQL>
Use table function to convert type to a "table"
SQL>
SQL>
SQL> create or replace type myRecordType as object
2 ( seq int,
3 a int,
4 b varchar2(10),
5 c date
6 )
7 /
Type created.
SQL>
SQL>
SQL> create or replace type myTableType
2 as table of myRecordType
3 /
Type created.
SQL>
SQL> create or replace function my_function return myTableType
2 is
3 l_data myTableType;
4 begin
5 l_data := myTableType();
6
7 for i in 1..5
8 loop
9 l_data.extend;
10 l_data(i) := myRecordType( i, i, "row " || i, sysdate+i );
11 end loop;
12 return l_data;
13 end;
14 /
Function created.
SQL>
SQL> select *
2 from TABLE ( cast( my_function() as mytableType ) )
3 where c > sysdate+1
4 order by seq desc
5 /
SEQ A B C
---------- ---------- ---------- ---------
5 5 row 5 30-JUL-08
4 4 row 4 29-JUL-08
3 3 row 3 28-JUL-08
2 2 row 2 27-JUL-08
SQL>
SQL> drop type myTableType;
Type dropped.
SQL> drop type myRecordType;
Type dropped.
SQL>
SQL>
Use user-defined type to combine query logic
SQL> create or replace type myScalarType as object( cnt number, average number )
2 /
Type created.
SQL>
SQL> select username, a.data.cnt, a.data.average
2 from (
3 select username, (select myScalarType( count(*), avg(object_id) ) from all_objects b where b.owner = a.username ) data from all_users a ) A
4 /
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME DATA.CNT DATA.AVERAGE
------------------------------ ---------- ------------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>
SQL>