Oracle 方法返回集合类型[table]

select  *  from table(fn_test2('7698'));的含义：

1. Does oracle support calling a procedureas a derived table,

  Eg

  select * from (call myprocedure) ....

2. Also, does it support creation of aview over a procedure ,

  Eg,

 Create view as (call myprocedure)

以scott.emp 为例，根据职位(MGR)编号，查出该职位编号的所有信息

先创建类型：

相当于每一行记录的类型 可以理解为 scott.emp%rowtype

1.CREATE OR REPLACE TYPE my_record IS OBJECT(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

 

2. CREATE OR REPLACE TYPE mtab IS TABLE OF my_record;

可以理解为 mtab集合里每一行的记录类型为my_record.，相当于my_record类型记录的集合，也就是表（table）

 

3.

方法一： 使用 bulk collect into 一次性插入到集合变量里

CREATE OR REPLACE FUNCTION fn_test1(num VARCHAR2)
RETURN mtab
IS
mytab mtab;
BEGIN
SELECT my_record(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) BULK COLLECT INTO mytab FROM scott.emp WHERE mgr=num;
RETURN mytab;
END;

 

注意：

my_record(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 如果写成EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 会报 “没有足够的值” 错误。强制类型一致就没问题了。

 

方法二： 以集合下标的方式

CREATE OR REPLACE FUNCTION fn_test2(num VARCHAR2)
RETURN mtab
IS
mytab mtab:=mtab();--object 对象类型
BEGIN
FOR i IN (SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp WHERE mgr=num)
LOOP
mytab.EXTEND();
mytab(mytab.COUNT):=my_record(i.EMPNO,i.ENAME,i.JOB,i.MGR,i.HIREDATE,i.SAL,i.COMM,i.DEPTNO);
END LOOP;
RETURN mytab;
END;

 

方法三：以管道方式

CREATE OR REPLACE FUNCTION fn_test3(num VARCHAR2)
RETURN mtab Pipelined --使用管道方式
IS
BEGIN
FOR i IN (SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp WHERE mgr=num)
LOOP
PIPE ROW(my_record(i.EMPNO,i.ENAME,i.JOB,i.MGR,i.HIREDATE,i.SAL,i.COMM,i.DEPTNO)); --- PIPE ROW 一行一行插入
END LOOP;
RETURN; --这里不需要返回值，只需要写return就OK。
END;

 

测试：

select * from table(方法名(参数));

三种方法结果都一样