Oracle table()函数查询函数返回的结果集
2015年12月13日 22:42:51 warrenjiang 阅读数：7452
版权声明：本文为博主原创文章，未经博主允许不得转载。
本文介绍如何利用Oracle的table()函数，查询函数返回的结果集。使用此技术，就可以用以下形式如同查询普通表一样查询函数返回的结果集了。这对于Java程序就可以像使用普通SELECT语句一样，使用Oracle函数了。SELECT * FROM table(func(参数1,参数2))
以下举例说明如何实现返回学生信息结果集。
1.创建一个object类型的数据库类型对象。表示学生实体类型。(注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。否则，函数外部代码是无法识别该类型的。)
CREATE OR REPLACE TYPE student_obj_type AS OBJECT ( stu_no NUMBER, --学号 stu_name VARCHAR2(255), --姓名 stu_sex VARCHAR2(2),--性别 score NUMBER --成绩 );2.创建一个嵌套表类型的数据库类型对象。表示学生实体集合类型。该类型也将用作函数中定义的返回类型。(注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。否则，函数外部代码是无法识别该类型的。)
CREATE OR REPLACE TYPE student_tab_type IS TABLE OF student_obj_type;
1
3.定义一个Oracle函数，根据性别返回学生列表信息，具体实现如下：
CREATE OR REPLACE FUNCTION get_students_by_sex(in_sex VARCHAR2) RETURN student_tab_type IS student_obj student_obj_type; tab_students student_tab_type:=student_tab_type(); BEGIN IF in_sex = '男' THEN student_obj:=student_obj_type(1,'张三','男',98); tab_students.extend; tab_students(tab_students.count):= student_obj; student_obj:=student_obj_type(2,'李四','男',88); tab_students.extend; tab_students(tab_students.count):= student_obj; ELSE student_obj:=student_obj_type(3,'小红','女',78); tab_students.extend; tab_students(tab_students.count):= student_obj; student_obj:=student_obj_type(4,'小娟','女',95); tab_students.extend; tab_students(tab_students.count):= student_obj; END IF; RETURN tab_students; END get_students_by_sex;4.验证结果如下：
SELECT * FROM table(get_students_by_sex('男'));
1
SELECT * FROM table(get_students_by_sex('女'));
1
还可以带WHERE子句进行查询：
SELECT * FROM table(get_students_by_sex('男')) WHERE score > 90;
1
注：使用此方法，在函数中不能包含DML操作，否则会报 PL/SQL“ ORA-14551: 无法在查询中执行 DML 操作”错误。除非将该函数定义为Oracle自治事务。即在声明函数时加上： PRAGMA AUTONOMOUS_TRANSACTION; 并在执行完DML后COMMIT。
Oracle函数返回Table集合
2013年01月11日 10:35:53 FighterLiu 阅读数：512
在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集(通常作为一个集合)的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。
管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
1.使用自定义类型
/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE objemp AS OBJECT (
maxsal NUMBER,
minsal NUMBER
);
/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;
2.使用Pipeline管道函数和Pipe row()
CREATEORREPLACEFUNCTIONgetmaxminsalary(department NUMBER)
RETURN tabemp PIPELINED
AS
maximum_salary NUMBER;
minimum_salary NUMBER;
v_errorcode NUMBER;
v_errortext VARCHAR2(200);
v objemp;
BEGIN
FOR myrow IN(SELECTMAX(sal) m_sal,MIN(sal) min_sal
FROMemp
WHERE deptno = departmnet)
LOOP
v :=(myrow.m_sal, myrow.min_sal);
PIPE ROW(v);
ENDLOOP;
RETURN;
EXCEPTION
WHENOTHERS
THEN
v_errorcode :=SQLCODE;
v_errortext :=SUBSTR(SQLERRM,1,200);
INSERTINTOlog_table
(code, MESSAGE, info
)
VALUES(v_errorcode, v_errortext,'getMaxMinSalary'
);
END;
3.使用Table操作符
SELECT * FROM TABLE(getMaxMinSalary(10));
posts – 23, comments – 40, trackbacks – 0在SQL Server中有表变量,可以在function中方便地返回,习惯SQL Server或者需要把脚本从SQL Server转到Oracle中的朋友可以都会碰到这个问题.
Oracle的function中怎么返回表变量?
太晚了,过多的理论知识就不说了,下面简单地说实现吧!..
1、创建表对象类型。
在Oracle中想要返回表对象，必须自定义一个表类型，如下所示：
create or replace type t_table is table of number;
上面的类型定义好后，在function使用可用返回一列的表，如果需要多列的话，需要先定义一个对象类型。然后把对象类型替换上面语句中的number；
定义对象类型：
create or replace type obj_table as object
(
id int,
name varchar2(50)
)修改表对象类型的定义语句如下：
create or replace type t_table is table of obj_table;
2、 创建演示函数
在函数的定义中,可以使用管道化表函数和普通的方式,下面提供两种使用方式的代码:
1)、管道化表函数方式：
create or replace function f_pipe(s number)
return t_table pipelined
as
v_obj_table obj_table;
begin
for i in 1..s loop
v_obj_table := obj_table(i,to_char(i*i));
pipe row(v_obj_table);
end loop;
return;
end f_pipe;
注意:管道的方式必须使用空的return表示结束.
调用函数的方式如下：
select * from table(f_pipe(5));
2)、 普通的方式:
create or replace function f_normal(s number)
return t_table
as
rs t_table:= t_table();
begin
for i in 1..s loop
rs.extend;
rs(rs.count) := obj_table(rs.count,'name'||to_char(rs.count));
–rs(rs.count).name := rs(rs.count).name || 'xxxx';
end loop;
return rs;
end f_normal;
初始化值后还可以想注视行那样进行修改.
调用方式如下:
