I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?
推荐答案
这里是如何构建一个函数，该函数返回一个可以像表一样查询的结果集:
Here is how to build a function that returns a result set that can be queried as if it were a table:
SQL> createtype emp_obj isobject (empno number, ename varchar2(10));
2 /
Type created.
SQL> createtype emp_tab istableof emp_obj;
2 /
Type created.
SQL> createorreplacefunction all_emps return emp_tab
2is3 l_emp_tab emp_tab := emp_tab();
4 n integer := 0;
5 begin6for r in (select empno, ename from emp)
7loop8 l_emp_tab.extend;
9 n := n + 1;
10 l_emp_tab(n) := emp_obj(r.empno, r.ename);
11 endloop;
12 return l_emp_tab;
13 end;
14 /
Function created.
SQL> select * fromtable (all_emps);
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7902 FORD
7934 MILLER
