Oracle函数返回Table集合
Oracle table()函数查询函数返回的结果集
2015年12⽉13⽇ 22:42:51 阅读数：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
oracle数据库怎么查询表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);
d;
tab_students(unt):= student_obj;
student_obj:=student_obj_type(2,'李四','男',88);
d;
tab_students(unt):= student_obj;
ELSE
student_obj:=student_obj_type(3,'⼩红','⼥',78);
d;
tab_students(unt):= student_obj;
student_obj:=student_obj_type(4,'⼩娟','⼥',95);
d;
tab_students(unt):= 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 阅读数：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.
1. v_errorcode NUMBER;
12.
3. v_errortext VARCHAR2(200);
14.
5. v objemp;
16.
7. BEGIN
18.
9. FOR myrow IN(SELECTMAX(sal) m_sal,MIN(sal) min_sal 20.
1. FROMemp
22.
3. WHERE deptno = departmnet)
24.
5. LOOP
26.
7. v :=(myrow.m_sal, myrow.min_sal);
28.
9. PIPE ROW(v);
30.
1. ENDLOOP;
32.
33.
34.
5. RETURN;
36.
7. EXCEPTION
38.
9. WHENOTHERS
40.
1. THEN
42.
3. v_errorcode :=SQLCODE;
44.
5. v_errortext :=SUBSTR(SQLERRM,1,200);
46.
47.
48.
9. INSERTINTOlog_table
50.
1. (code, MESSAGE, info
52.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论