【转】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)
)
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;
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;
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;
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(方法名(参数));
![linux](/linuxfile/logo.gif)
==================================原文链接===此文章由博客转发小工具转发==================================
- 学习本是一个不断抄袭、模仿、练习、创新的过程。
- 虽然,园中已有本人无法超越的同主题博文,为什么还是要写。
- 对于自己,博文只是总结。在总结的过程发现问题,解决问题。
- 对于他人,在此过程如果还能附带帮助他人,那就再好不过了。
- 由于博主能力有限,文中可能存在描述不正确,欢迎指正、补充!
- 感谢您的阅读。如果文章对您有用,那么请轻轻点个赞,以资鼓励。
- 工控物联Q群:995475200