Oracle函数返回Table集合

电玩女神 2022-04-02 11:08 605阅读 0赞

# 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 --成绩
                                                      );

*  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

![这里写图片描述][20151213224321562]

SELECT * FROM table(get_students_by_sex('女'));

*  1

![这里写图片描述][20151213224334943]

还可以带WHERE子句进行查询：

SELECT * FROM table(get_students_by_sex('男')) WHERE score > 90;

*  1

![这里写图片描述][20151213224534614]

注：使用此方法，在函数中不能包含DML操作，否则会报 PL/SQL“ ORA-14551: 无法在查询中执行 DML 操作”错误。除非将该函数定义为Oracle自治事务。即在声明函数时加上： PRAGMA AUTONOMOUS\_TRANSACTION; 并在执行完DML后COMMIT。

[https://blog.csdn.net/warrenjiang/article/details/50287589][https_blog.csdn.net_warrenjiang_article_details_50287589]

# Oracle函数返回Table集合 #

2013年01月11日 10:35:53 [FighterLiu][] 阅读数：512

在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。

管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。

1.使用自定义类型

1.  `/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */`
2.  `CREATE OR REPLACE TYPE objemp AS OBJECT (`
3.  `maxsal NUMBER,`
4.  `minsal NUMBER`
5.  `);`
6.   
7.   
8.  `/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */`
9.  `CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;`

2.使用Pipeline管道函数和Pipe row()

1.  `CREATEORREPLACEFUNCTIONgetmaxminsalary(department NUMBER)`
2.   
3.  `RETURN tabemp PIPELINED`
4.   
5.  `AS`
6.   
7.  `maximum_salary NUMBER;`
8.   
9.  `minimum_salary NUMBER;`
10.  
11. `v_errorcode NUMBER;`
12.  
13. `v_errortext VARCHAR2(200);`
14.  
15. `v objemp;`
16.  
17. `BEGIN`
18.  
19. `FOR myrow IN(SELECTMAX(sal) m_sal,MIN(sal) min_sal`
20.  
21. `FROMemp`
22.  
23. `WHERE deptno = departmnet)`
24.  
25. `LOOP`
26.  
27. `v :=(myrow.m_sal, myrow.min_sal);`
28.  
29. `PIPE ROW(v);`
30.  
31. `ENDLOOP;`
32.  
33.  
34.  
35. `RETURN;`
36.  
37. `EXCEPTION`
38.  
39. `WHENOTHERS`
40.  
41. `THEN`
42.  
43. `v_errorcode :=SQLCODE;`
44.  
45. `v_errortext :=SUBSTR(SQLERRM,1,200);`
46.  
47.  
48.  
49. `INSERTINTOlog_table`
50.  
51. `(code, MESSAGE, info`
52.  
53. `)`
54.  
55. `VALUES(v_errorcode, v_errortext,'getMaxMinSalary'`
56.  
57. `);`
58.  
59. `END;`
60.

3.使用Table操作符

1.  `SELECT * FROM TABLE(getMaxMinSalary(10));`
2.

[https://blog.csdn.net/xinghui\_liu/article/details/8492565][https_blog.csdn.net_xinghui_liu_article_details_8492565]

[博客园][Link 1]    [首页][Link 2]    [新随笔][Link 3]    [联系][Link 4]    [订阅][Link 5][![订阅][20181226143618155]][Link 5]    [管理][Link 6]

posts - 23,  comments - 40,  trackbacks - 0

[在Oracle的函数中,返回表类型][Oracle]

在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)、管道化表函数方式：

![复制代码][20181226143618253]

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;

![复制代码][20181226143618253]

注意:管道的方式必须使用空的return表示结束.

调用函数的方式如下：

select \* from table(f\_pipe(5));

2)、 普通的方式:

![复制代码][20181226143618253]

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;

![复制代码][20181226143618253]

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

调用方式如下:

select \* from table(f\_normal(5));

OK, The End...

分类: [DataBase][]

好文要顶 关注我 收藏该文 ![20181226143618307][] ![20181226143618325][]

[![20181226143618340][]][20181226143618340 1]

[逐风者][20181226143618340 1]  
[关注 - 0][- 0]  
[粉丝 - 10][- 10]

\+加关注

[« ][Link 7]上一篇：[在Word 中编辑文档的时候 有时鼠标点击无响应][Link 7]  
[» ][Link 8]下一篇：[使用自定义控件的方式实现一个分页控件][Link 8]

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

[http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html][http_www.cnblogs.com_liguiqing_archive_2009_05_26_1489243.html]

[warrenjiang]: https://me.csdn.net/warrenjiang
[20151213224321562]: /images/20220402/7e9ed5a0cc7a46639aa04b489caed8df.png
[20151213224334943]: /images/20220402/d596dfede9704b6fbb8f3bed7fff7f26.png
[20151213224534614]: /images/20220402/70eaf52708d54de3ac32118af80ae207.png
[https_blog.csdn.net_warrenjiang_article_details_50287589]: https://blog.csdn.net/warrenjiang/article/details/50287589
[FighterLiu]: https://me.csdn.net/xinghui_liu
[https_blog.csdn.net_xinghui_liu_article_details_8492565]: https://blog.csdn.net/xinghui_liu/article/details/8492565
[Link 1]: https://www.cnblogs.com/
[Link 2]: https://www.cnblogs.com/liguiqing/
[Link 3]: /images/20220402/dd4749c00a484c47921265fc8bf24167.pngEditPosts.aspx?opt=1
[Link 4]: https://msg.cnblogs.com/send/%E9%80%90%E9%A3%8E%E8%80%85
[Link 5]: https://www.cnblogs.com/liguiqing/rss
[20181226143618155]: https://img-blog.csdnimg.cn/20181226143618155
[Link 6]: /images/20220402/dd4749c00a484c47921265fc8bf24167.png
[Oracle]: https://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html
[20181226143618253]: /images/20220402/c6c3cc6e2c064fa68283b023f3ae79c3.png
[DataBase]: https://www.cnblogs.com/liguiqing/category/105614.html
[20181226143618307]: https://img-blog.csdnimg.cn/20181226143618307
[20181226143618325]: /images/20220402/0362dc84942745c6a59fb48f1979a5cd.png
[20181226143618340]: /images/20220402/a8c7607e4dee4953a28356c5d978ba21.png
[20181226143618340 1]: http://home.cnblogs.com/u/liguiqing/
[- 0]: http://home.cnblogs.com/u/liguiqing/followees
[- 10]: http://home.cnblogs.com/u/liguiqing/followers
[Link 7]: https://www.cnblogs.com/liguiqing/archive/2008/06/17/1223906.html
[Link 8]: https://www.cnblogs.com/liguiqing/archive/2009/05/30/1492380.html
[Link 9]: /images/20220402/dd4749c00a484c47921265fc8bf24167.pngEditPosts.aspx?postid=1489243
[Link 10]: http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html#
[http_www.cnblogs.com_liguiqing_archive_2009_05_26_1489243.html]: http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html