Oracle PL/SQL/Object Oriented Database/table function — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Use table function in from clause
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>
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> select d.dname, e.empno, ename
2 from dept_and_emp d, table(d.emps) e
3 where d.deptno in ( 10, 20 );
DNAME EMPNO ENAME
-------------- ---------- ----------
ACCOUNTING 7369 SMITH
ACCOUNTING 7499 ALLEN
ACCOUNTING 7521 WARD
ACCOUNTING 7566 JONES
ACCOUNTING 7654 MARTIN
ACCOUNTING 7698 BLAKE
ACCOUNTING 7782 CLARK
ACCOUNTING 7788 SCOTT
ACCOUNTING 7839 KING
ACCOUNTING 7844 TURNER
ACCOUNTING 7876 ADAMS
ACCOUNTING 7900 JAMES
ACCOUNTING 7902 FORD
ACCOUNTING 7934 MILLER
RESEARCH 7369 SMITH
RESEARCH 7499 ALLEN
RESEARCH 7521 WARD
RESEARCH 7566 JONES
RESEARCH 7654 MARTIN
RESEARCH 7698 BLAKE
RESEARCH 7782 CLARK
RESEARCH 7788 SCOTT
RESEARCH 7839 KING
RESEARCH 7844 TURNER
RESEARCH 7876 ADAMS
RESEARCH 7900 JAMES
RESEARCH 7902 FORD
RESEARCH 7934 MILLER
28 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>
SQL>
SQL> --
Use table function to user-defined type column
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>
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
---------- -------------- ---------- ---------- --------- ---------- -------------------- ---------- ----------
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>
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>
SQL> --