Oracle PL/SQL/Object Oriented Database/Select — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 demonstrates SQL operations on object types.
- 2 Display the New Table (SELECT * and SELECT by Column Name)
- 3 Query data from table based on object
- 4 Query object table
- 5 SELECTing Individual Columns in TCROs
- 6 SELECT Only One Column in the Composite
- 7 Select the object type from the table, rather than the columns.
- 8 SELECT with a WHERE Clause
- 9 Use %ROWTYPE to select from the relational table.
- 10 Use the function we created: use the table alias in our SELECT as well as the qualifier
- 11 Use * to reference column with user-defined type
- 12 Use type member function in select statement
demonstrates SQL operations on object types.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE Point AS OBJECT (
2 x NUMBER,
3 y NUMBER,
4
5 MEMBER FUNCTION ToString RETURN VARCHAR2,
6 PRAGMA RESTRICT_REFERENCES(ToString, RNDS, WNDS, RNPS, WNPS),
7
8 MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
9 RETURN NUMBER,
10 PRAGMA RESTRICT_REFERENCES(Distance, RNDS, WNDS, RNPS, WNPS),
11
12 MEMBER FUNCTION Plus(p IN Point) RETURN Point,
13 PRAGMA RESTRICT_REFERENCES(Plus, RNDS, WNDS, RNPS, WNPS),
14
15 MEMBER FUNCTION Times(n IN NUMBER) RETURN Point,
16 PRAGMA RESTRICT_REFERENCES(Times, RNDS, WNDS, RNPS, WNPS)
17 );
18 /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY Point AS
2 MEMBER FUNCTION ToString RETURN VARCHAR2 IS
3 myResult VARCHAR2(20);
4 v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x), 1, 8);
5 v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y), 1, 8);
6 BEGIN
7 myResult := "(" || v_xString || ", ";
8 myResult := myResult || v_yString || ")";
9 RETURN myResult;
10 END ToString;
11
12 MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
13 RETURN NUMBER IS
14 BEGIN
15 RETURN SQRT(POWER(x - p.x, 2) + POWER(y - p.y, 2));
16 END Distance;
17
18 MEMBER FUNCTION Plus(p IN Point) RETURN Point IS
19 myResult Point;
20 BEGIN
21 myResult := Point(x + p.x, y + p.y);
22 RETURN myResult;
23 END Plus;
24
25 MEMBER FUNCTION Times(n IN NUMBER) RETURN Point IS
26 myResult Point;
27 BEGIN
28 myResult := Point(x * n, y * n);
29 RETURN myResult;
30 END Times;
31 END;
32 /
Type body created.
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_Point1 Point := Point(1, 2);
3 v_Point2 Point;
4 v_Point3 Point;
5 BEGIN
6 v_Point2 := v_Point1.Times(4);
7 v_Point3 := v_Point1.Plus(v_Point2);
8 DBMS_OUTPUT.PUT_LINE("Point 2: " || v_Point2.ToString);
9 DBMS_OUTPUT.PUT_LINE("Point 3: " || v_Point3.ToString);
10 DBMS_OUTPUT.PUT_LINE("Distance between origin and point 1: " ||
11 v_Point1.Distance);
12 DBMS_OUTPUT.PUT_LINE("Distance between point 1 and point 2: " ||
13 v_Point1.Distance(v_Point2));
14 END;
15 /
Point 2: (4, 8)
Point 3: (5, 10)
Distance between origin and point 1: 2.23606797749978969640917366873127623544
Distance between point 1 and point 2: 6.70820393249936908922752100619382870632
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> CREATE TABLE point_object_tab OF Point;
Table created.
SQL>
SQL>
SQL> CREATE TABLE point_column_tab (
2 key VARCHAR2(20),
3 value Point);
Table created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_Point Point := Point(1, 1);
3 v_NewPoint Point;
4 v_Key point_column_tab.key%TYPE;
5 v_XCoord NUMBER;
6 v_YCoord NUMBER;
7 BEGIN
8 INSERT INTO point_object_tab VALUES (v_Point);
9 INSERT INTO point_column_tab VALUES ("My Point", v_Point);
10
11 SELECT *
12 INTO v_XCoord, v_YCoord
13 FROM point_object_tab;
14 DBMS_OUTPUT.PUT_LINE("Relational query of object table: " ||
15 v_XCoord || ", " || v_YCoord);
16
17 SELECT VALUE(ot)
18 INTO v_NewPoint
19 FROM point_object_tab ot;
20 DBMS_OUTPUT.PUT_LINE("object table: " || v_NewPoint.ToString);
21
22 SELECT key, value
23 INTO v_Key, v_NewPoint
24 FROM point_column_tab;
25 DBMS_OUTPUT.PUT_LINE("column table: " || v_NewPoint.ToString);
26
27 END;
28 /
Relational query of object table: 1, 1
object table: (1, 1)
column table: (1, 1)
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 v_PointRef REF Point;
3 v_Point Point;
4 BEGIN
5 DELETE FROM point_object_tab;
6
7 INSERT INTO point_object_tab (x, y)
8 VALUES (0, 0);
9 INSERT INTO point_object_tab (x, y)
10 VALUES (1, 1);
11
12 SELECT REF(ot)
13 INTO v_PointRef
14 FROM point_object_tab ot
15 WHERE x = 1 AND y = 1;
16
17 SELECT DEREF(v_PointRef)
18 INTO v_Point
19 FROM dual;
20 DBMS_OUTPUT.PUT_LINE("Selected reference " ||
21 v_Point.ToString);
22
23 INSERT INTO point_object_tab ot (x, y)
24 VALUES (10, 10)
25 RETURNING REF(ot) INTO v_PointRef;
26 END;
27 /
Selected reference (1, 1)
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE point_column_tab;
Table dropped.
SQL> DROP TABLE point_object_tab;
Table dropped.
SQL>
Display the New Table (SELECT * and SELECT by Column Name)
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> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> SELECT * FROM emp;
EMPNO NAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 Adam
ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Query data from table based on object
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 address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL> INSERT INTO address_table VALUES ("4 D St.", "Gulf Breeze","FL","32563");
1 row created.
SQL>
SQL> SELECT *
2 FROM address_table;
STREET CITY ST ZIP
-------------------- -------------------- -- -----
4 D St. Gulf Breeze FL 32563
SQL>
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
Query object table
SQL>
SQL>
SQL> create or replace
2 type address as object(
3 id number,
4 street varchar2(100),
5 state varchar2(2),
6 zipcode varchar(11)
7 )
8 /
Type created.
SQL>
SQL> create table address_table of address
2 /
Table created.
SQL>
SQL>
SQL> insert into address_table values ( 1, "Oracle Way", "VA", "21090" )
2 /
1 row created.
SQL>
SQL> select * from address_table;
ID
----------
STREET
--------------------------------------------------------------------------------
ST ZIPCODE
-- -----------
1
Oracle Way
VA 21090
SQL> drop table address_table;
Table dropped.
SQL>
SELECTing Individual Columns in TCROs
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 address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF address_obj scope is address_table);
Table created.
SQL>
SQL> INSERT INTO address_table VALUES (ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
1 row created.
SQL>
SQL>
SQL> -- SELECTing Individual Columns in TCROs
SQL>
SQL>
SQL> SELECT * FROM client;
NAME ADDRESS
--------- --------------------------------------------------
Walsh 0000220208589A813178324DF39D575B3701C24CE19751BEFE
0AF34AC18C9B571EDA8D0418
SQL>
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>
SELECT Only One Column in the Composite
SQL> -- SELECTing Only One Column in the Composite
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 (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> COLUMN name FORMAT a9
SQL> COLUMN empno FORMAT 999999
SQL> COLUMN address FORMAT a50
SQL>
SQL>
SQL> SELECT name, e.address.city FROM emp e;
NAME ADDRESS.CITY
--------- --------------------
Adam Mobile
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
Select the object type from the table, rather than the columns.
SQL>
SQL>
SQL> CREATE TABLE rel_tab (
2 f1 NUMBER,
3 f2 VARCHAR2(50)
4 );
Table created.
SQL>
SQL> CREATE or replace TYPE objType AS OBJECT (
2 f1 NUMBER,
3 f2 VARCHAR2(50)
4 );
5 /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE TABLE obj_tab OF objType;
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_Row obj_tab%ROWTYPE;
3 CURSOR c_AllRows IS
4 SELECT VALUE(o) FROM obj_tab o;
5 BEGIN
6 OPEN c_AllRows;
7 LOOP
8 FETCH c_AllRows INTO v_Row;
9 EXIT WHEN c_AllRows%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE(v_Row.f1 || " " || v_Row.f2);
11 END LOOP;
12 CLOSE c_AllRows;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table rel_tab;
Table dropped.
SQL> drop table obj_tab;
Table dropped.
SELECT with a WHERE Clause
SQL> --SELECT with a WHERE Clause
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 (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> COLUMN name FORMAT a9
SQL> COLUMN empno FORMAT 999999
SQL> COLUMN address FORMAT a50
SQL>
SQL> SELECT name, e.address.city FROM emp e WHERE e.address.state = "AL";
NAME ADDRESS.CITY
--------- --------------------
Adam Mobile
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Use %ROWTYPE to select from the relational table.
SQL>
SQL> CREATE TABLE rel_tab (
2 f1 NUMBER,
3 f2 VARCHAR2(50)
4 );
Table created.
SQL>
SQL> CREATE or replace TYPE objType AS OBJECT (
2 f1 NUMBER,
3 f2 VARCHAR2(50)
4 );
5 /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE TABLE obj_tab OF objType;
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_Row rel_tab%ROWTYPE;
3 CURSOR c_AllRows IS
4 SELECT * FROM rel_tab;
5 BEGIN
6 OPEN c_AllRows;
7 LOOP
8 FETCH c_AllRows INTO v_Row;
9 EXIT WHEN c_AllRows%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE(v_Row.f1 || " " || v_Row.f2);
11 END LOOP;
12 CLOSE c_AllRows;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table rel_tab;
Table dropped.
SQL> drop table obj_tab;
Table dropped.
SQL>
Use the function we created: use the table alias in our SELECT as well as the qualifier
SQL> CREATE OR REPLACE TYPE aobj AS object (
2 state CHAR(2),
3 amt NUMBER(5),
4
5 MEMBER FUNCTION mult (times in number) RETURN number,
6 PRAGMA RESTRICT_REFERENCES(mult, WNDS));
7 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY aobj AS
2 MEMBER FUNCTION mult (times in number) RETURN number
3 IS
4 BEGIN
5 RETURN times * self.amt; /* SEE BELOW */
6 END;
7 END;
8 /
Type body created.
SQL>
SQL> CREATE TABLE aobjtable (arow aobj);
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO aobjtable VALUES (aobj("FL",25));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("AL",35));
1 row created.
SQL> INSERT INTO aobjtable VALUES (aobj("OH",15));
1 row created.
SQL>
SQL> -- Use the function we created: use the table alias in our SELECT as well as the qualifier, arow:
SQL>
SQL> SELECT x.arow.state, x.arow.amt, x.arow.mult(2)
2 FROM aobjtable x;
AR AROW.AMT X.AROW.MULT(2)
-- ---------- --------------
FL 25 50
AL 35 70
OH 15 30
SQL>
SQL>
SQL> DESC aobjtable;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
AROW AOBJ
SQL>
SQL> drop table aobjtable;
Table dropped.
SQL>
SQL>
Use * to reference column with user-defined type
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.
SQL>
SQL> create or replace type emp_tab_type as table of emp_type;
2 /
Type created.
SQL> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
SQL>
SQL>
SQL> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type )
4 from dept
5 /
4 rows created.
SQL>
SQL>
SQL> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp
3 /
DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- -------------- ---------- ---------- --------- ---------- -------------------- ---------- ----------
DEPTNO DNAME EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- -------------- ---------- ---------- --------- ---------- -------------------- ---------- ----------
10 ACCOUNTING 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800
10 ACCOUNTING 7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300
10 ACCOUNTING 7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500
10 ACCOUNTING 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975
10 ACCOUNTING 7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400
10 ACCOUNTING 7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850
10 ACCOUNTING 7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450
10 ACCOUNTING 7788 SCOTT ANALYST 7566 09-DEC-1982 00:00:00 3000
10 ACCOUNTING 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
10 ACCOUNTING 7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0
10 ACCOUNTING 7876 ADAMS CLERK 7788 12-JAN-1983 00:00:00 1100
10 ACCOUNTING 7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950
10 ACCOUNTING 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000
10 ACCOUNTING 7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300
20 RESEARCH 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800
20 RESEARCH 7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300
20 RESEARCH 7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500
20 RESEARCH 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975
20 RESEARCH 7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400
20 RESEARCH 7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850
20 RESEARCH 7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450
20 RESEARCH 7788 SCOTT ANALYST 7566 09-DEC-1982 00:00:00 3000
20 RESEARCH 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
20 RESEARCH 7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0
20 RESEARCH 7876 ADAMS CLERK 7788 12-JAN-1983 00:00:00 1100
20 RESEARCH 7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950
20 RESEARCH 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000
20 RESEARCH 7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300
30 SALES 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800
30 SALES 7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300
30 SALES 7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500
30 SALES 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975
30 SALES 7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400
30 SALES 7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850
30 SALES 7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450
30 SALES 7788 SCOTT ANALYST 7566 09-DEC-1982 00:00:00 3000
30 SALES 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
30 SALES 7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0
30 SALES 7876 ADAMS CLERK 7788 12-JAN-1983 00:00:00 1100
30 SALES 7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950
30 SALES 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000
30 SALES 7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300
40 OPERATIONS 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800
40 OPERATIONS 7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300
40 OPERATIONS 7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500
40 OPERATIONS 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975
40 OPERATIONS 7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400
40 OPERATIONS 7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850
40 OPERATIONS 7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450
40 OPERATIONS 7788 SCOTT ANALYST 7566 09-DEC-1982 00:00:00 3000
40 OPERATIONS 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
40 OPERATIONS 7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0
40 OPERATIONS 7876 ADAMS CLERK 7788 12-JAN-1983 00:00:00 1100
40 OPERATIONS 7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950
40 OPERATIONS 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000
40 OPERATIONS 7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300
56 rows selected.
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL> --
Use type member function in select statement
SQL>
SQL>
SQL> create or replace
2 type person as object (
3 first_name varchar2(100),
4 last_name varchar2(100),
5 dob date,
6 phone varchar2(100),
7 member function get_last_name return varchar2,
8 member function get_phone_number return varchar2 )
9 not final
10 /
Type created.
SQL>
SQL>
SQL> create or replace
2 type body person as
3 member function get_last_name return varchar2 is
4 begin
5 return self.last_name;
6 end;
7 member function get_phone_number return varchar2 is
8 begin
9 return self.phone;
10 end;
11 end;
12 /
Type body created.
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL>
SQL> create or replace
2 type new_employee under person (
3 empno number,
4 hiredate date,
5 work_phone varchar2(100),
6 overriding member function get_phone_number return varchar2,
7 member function get_home_phone_number return varchar2 )
8 not final
9 /
Type created.
SQL>
SQL> create or replace
2 type body new_employee as
3 overriding member function get_phone_number return varchar2 is
4 begin
5 return self.work_phone;
6 end;
7 member function get_home_phone_number return varchar2 is
8 begin
9 return self.phone;
10 end;
11 end;
12 /
Type body created.
SQL>
SQL>
SQL> select x.p.get_last_name() last_name,
2 x.p.phone phone,
3 treat( x.p as new_employee ).work_phone work_phone,
4 x.p.get_phone_number() "GET_PHONE_NUMBER()",
5 treat( x.p as new_employee ).get_phone_number() treat_as_new_employee
6 from person_table x
7 /
no rows selected
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>