java oracle查询结果list取数,Oracle函数返回Table集合

Oracle table()函数查询函数返回的结果集

2015年12月13日 22:42:51 warrenjiang 阅读数：7452

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/warrenjiang/article/details/50287589

本文介绍如何利用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 --成绩 );

1

2

3

4

5

6

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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;

初始化值后还可以想注视行那样进行修改.

调用方式如下:

select * from table(f_normal(5));

OK, The End…

+加关注

5

0

posted on 2009-05-26 00:42 逐风者 阅读(18857) 评论(10) 编辑 收藏