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

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

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("男"));

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

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

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

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