SQL> set lines 150
SQL> set pages 50
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> column names_list format a80
SQL> select deptno,collect(ename) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 SYSTPTuDBVpKQTfitWChB4kLTxg==('CLARK', 'KING', 'MILLER')
20 SYSTPTuDBVpKQTfitWChB4kLTxg==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30 SYSTPTuDBVpKQTfitWChB4kLTxg==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAM
ES')
SQL> create type t_varchar2 as table of varchar2(4000);
2 /
Type created.
SQL> break on deptno skip 1
SQL> select deptno,cast(collect(ename) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('CLARK', 'KING', 'MILLER')
20 T_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
30 T_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
SQL> select deptno,cast(collect(ename order by ename) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('CLARK', 'KING', 'MILLER')
20 T_VARCHAR2('ADAMS', 'FORD', 'JONES', 'SCOTT', 'SMITH')
30 T_VARCHAR2('ALLEN', 'BLAKE', 'JAMES', 'MARTIN', 'TURNER', 'WARD')
SQL> select deptno,cast(collect(ename order by sal) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('MILLER', 'CLARK', 'KING')
20 T_VARCHAR2('SMITH', 'ADAMS', 'JONES', 'SCOTT', 'FORD')
30 T_VARCHAR2('JAMES', 'WARD', 'MARTIN', 'TURNER', 'ALLEN', 'BLAKE')
SQL> select deptno,cast(collect(ename order by sal desc) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('KING', 'CLARK', 'MILLER')
20 T_VARCHAR2('SCOTT', 'FORD', 'JONES', 'ADAMS', 'SMITH')
30 T_VARCHAR2('BLAKE', 'ALLEN', 'TURNER', 'WARD', 'MARTIN', 'JAMES')
SQL> select deptno,cast(collect(job) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('MANAGER', 'PRESIDENT', 'CLERK')
20 T_VARCHAR2('CLERK', 'MANAGER', 'ANALYST', 'CLERK', 'ANALYST')
30 T_VARCHAR2('SALESMAN', 'SALESMAN', 'SALESMAN', 'MANAGER', 'SALESMAN', 'CLERK')
SQL> select deptno,cast(collect(distinct job) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')
20 T_VARCHAR2('ANALYST', 'CLERK', 'MANAGER')
30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN')
SQL> select deptno,cast(collect(unique job) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')
20 T_VARCHAR2('ANALYST', 'CLERK', 'MANAGER')
30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN')
SQL> select deptno,cast(collect(all job) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('MANAGER', 'PRESIDENT', 'CLERK')
20 T_VARCHAR2('CLERK', 'MANAGER', 'ANALYST', 'CLERK', 'ANALYST')
30 T_VARCHAR2('SALESMAN', 'SALESMAN', 'SALESMAN', 'MANAGER', 'SALESMAN', 'CLERK')
SQL> select deptno,cast(collect(distinct job order by job) as t_varchar2) as names_list from emp group by deptno;
DEPTNO NAMES_LIST
---------- --------------------------------------------------------------------------------
10 T_VARCHAR2('CLERK', 'MANAGER', 'PRESIDENT')
20 T_VARCHAR2('ANALYST', 'ANALYST', 'CLERK', 'CLERK', 'MANAGER')
30 T_VARCHAR2('CLERK', 'MANAGER', 'SALESMAN', 'SALESMAN', 'SALESMAN', 'SALESMAN')
SQL> --using complex types
SQL> create type empsal_ot as object (ename varchar2(100),sal number);
2 /
Type created.
SQL> create type empsal_ntt as table of empsal_ot;
2 /
Type created.
SQL> col empsals format a50 wrap
SQL> break on deptno skip 1
SQL> select deptno,cast(collect(empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
10 EMPSAL_NTT(EMPSAL_OT('CLARK', 2450), EMPSAL_OT('KI
NG', 5000), EMPSAL_OT('MILLER', 1300))
20 EMPSAL_NTT(EMPSAL_OT('SMITH', 800), EMPSAL_OT('JON
ES', 2975), EMPSAL_OT('SCOTT', 3000), EMPSAL_OT('A
DAMS', 1100), EMPSAL_OT('FORD', 3000))
30 EMPSAL_NTT(EMPSAL_OT('ALLEN', 1600), EMPSAL_OT('WA
RD', 1250), EMPSAL_OT('MARTIN', 1250), EMPSAL_OT('
BLAKE', 2850), EMPSAL_OT('TURNER', 1500), EMPSAL_O
T('JAMES', 950))
SQL> select deptno,cast(collect(empsal_ot(ename,sal) order by ename) as empsal_ntt) as empsals from emp group by deptno;
DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
10 EMPSAL_NTT(EMPSAL_OT('CLARK', 2450), EMPSAL_OT('KI
NG', 5000), EMPSAL_OT('MILLER', 1300))
20 EMPSAL_NTT(EMPSAL_OT('ADAMS', 1100), EMPSAL_OT('FO
RD', 3000), EMPSAL_OT('JONES', 2975), EMPSAL_OT('S
COTT', 3000), EMPSAL_OT('SMITH', 800))
30 EMPSAL_NTT(EMPSAL_OT('ALLEN', 1600), EMPSAL_OT('BL
AKE', 2850), EMPSAL_OT('JAMES', 950), EMPSAL_OT('M
ARTIN', 1250), EMPSAL_OT('TURNER', 1500), EMPSAL_O
T('WARD', 1250))
SQL> select deptno,cast(collect(empsal_ot(ename,sal) order by empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
select deptno,cast(collect(empsal_ot(ename,sal) order by empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
SQL> select deptno,cast(collect(distinct empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
select deptno,cast(collect(distinct empsal_ot(ename,sal)) as empsal_ntt) as empsals from emp group by deptno
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
SQL> drop type empsal_ntt;
Type dropped.
SQL> create or replace type empsal_obj as object(ename varchar2(50),sal number,
2 map member function sort_key return varchar2);
3 /
Type created.
SQL> edit
Wrote file afiedt.buf
1 create or replace type body empsal_obj as
2 map member function sort_key return varchar2 is
3 begin
4 return ename||to_char(sal,'fm0000');
5 end;
6* end;
SQL> /
Type body created.
SQL> create type empsal_ntt is table of empsal_obj;
2 /
Type created.
SQL> select deptno,cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
10 EMPSAL_NTT(EMPSAL_OBJ('CLARK', 2450), EMPSAL_OBJ('
KING', 5000), EMPSAL_OBJ('MILLER', 1300))
20 EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
FORD', 3000), EMPSAL_OBJ('JONES', 2975), EMPSAL_OB
J('SCOTT', 3000), EMPSAL_OBJ('SMITH', 800))
30 EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600), EMPSAL_OBJ('
BLAKE', 2850), EMPSAL_OBJ('JAMES', 950), EMPSAL_OB
J('MARTIN', 1250), EMPSAL_OBJ('TURNER', 1500), EMP
SAL_OBJ('WARD', 1250))
SQL> INSERT INTO emp (empno, ename, sal) VALUES (9999, 'ALLEN', 1600);
1 row created.
SQL> select deptno,cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp group by deptno;
DEPTNO EMPSALS(ENAME, SAL)
---------- --------------------------------------------------
10 EMPSAL_NTT(EMPSAL_OBJ('CLARK', 2450), EMPSAL_OBJ('
KING', 5000), EMPSAL_OBJ('MILLER', 1300))
20 EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
FORD', 3000), EMPSAL_OBJ('JONES', 2975), EMPSAL_OB
J('SCOTT', 3000), EMPSAL_OBJ('SMITH', 800))
30 EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600), EMPSAL_OBJ('
BLAKE', 2850), EMPSAL_OBJ('JAMES', 950), EMPSAL_OB
J('MARTIN', 1250), EMPSAL_OBJ('TURNER', 1500), EMP
SAL_OBJ('WARD', 1250))
EMPSAL_NTT(EMPSAL_OBJ('ALLEN', 1600))
SQL> select cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp;
EMPSALS(ENAME, SAL)
--------------------------------------------------
EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
ALLEN', 1600), EMPSAL_OBJ('BLAKE', 2850), EMPSAL_O
BJ('CLARK', 2450), EMPSAL_OBJ('FORD', 3000), EMPSA
L_OBJ('JAMES', 950), EMPSAL_OBJ('JONES', 2975), EM
PSAL_OBJ('KING', 5000), EMPSAL_OBJ('MARTIN', 1250)
, EMPSAL_OBJ('MILLER', 1300), EMPSAL_OBJ('SCOTT',
3000), EMPSAL_OBJ('SMITH', 800), EMPSAL_OBJ('TURNE
R', 1500), EMPSAL_OBJ('WARD', 1250))
SQL> rollback;
Rollback complete.
SQL> select cast(collect(distinct empsal_obj(ename,sal)) as empsal_ntt) as empsals from emp;
EMPSALS(ENAME, SAL)
--------------------------------------------------
EMPSAL_NTT(EMPSAL_OBJ('ADAMS', 1100), EMPSAL_OBJ('
ALLEN', 1600), EMPSAL_OBJ('BLAKE', 2850), EMPSAL_O
BJ('CLARK', 2450), EMPSAL_OBJ('FORD', 3000), EMPSA
L_OBJ('JAMES', 950), EMPSAL_OBJ('JONES', 2975), EM
PSAL_OBJ('KING', 5000), EMPSAL_OBJ('MARTIN', 1250)
, EMPSAL_OBJ('MILLER', 1300), EMPSAL_OBJ('SCOTT',
3000), EMPSAL_OBJ('SMITH', 800), EMPSAL_OBJ('TURNE
R', 1500), EMPSAL_OBJ('WARD', 1250))
SQL> -- http://www.oracle-developer.net/display.php?id=514
No comments:
Post a Comment