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;