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

以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(方法名(参数));