1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as mni
SQL>
SQL> CREATE OR REPLACE TYPE EMP1_OT AS OBJECT
2 ( empno NUMBER(4),
3 ename VARCHAR2(10),
4 sal NUMBER(7,2),
5 up_ename VARCHAR2(10)
6 )
7 /
Type created
SQL> CREATE OR REPLACE TYPE EMP1_NTT AS TABLE OF emp1_ot
2 /
Type created
SQL> create or replace function emp_tranforme (p_deptno In dept.deptno%Type)
2 return EMP1_NTT pipelined Is
3 Begin
4 For crs In (Select e.empno, e.ename, e.sal
5 from emp e
6 Where e.deptno = p_deptno
7 )
8 Loop
9 pipe row(emp1_ot(crs.empno, crs.ename, crs.sal, Initcap(crs.ename)));
10 End Loop;
11 Return;
12 End;
13 /
Function created
SQL> Select * from table(emp_tranforme(10))
2 /
EMPNO ENAME SAL UP_ENAME
---------- ---------- ---------- ----------
7782 CLARK 2450 Clark
7839 KING 5000 King
7934 MILLER 1300 Miller
SQL> |
Partager