如何从存储过程返回多行?(Oracle PL/SQL) [英] How to return multiple rows from the stored procedure? (Oracle PL/SQL)

问题描述

我想创建一个带有一个参数的存储过程，它将根据参数返回不同的记录集.有什么方法可以做到这一点?我可以从普通 SQL 调用它吗?

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> create type emp_obj is object (empno number, ename varchar2(10));
  2  /

Type created.

SQL> create type emp_tab is table of emp_obj;
  2  /

Type created.

SQL> create or replace function all_emps return emp_tab
  2  is
  3     l_emp_tab emp_tab := emp_tab();
  4     n integer := 0;
  5  begin
  6     for r in (select empno, ename from emp)
  7     loop
  8        l_emp_tab.extend;
  9        n := n + 1;
 10       l_emp_tab(n) := emp_obj(r.empno, r.ename);
 11     end loop;
 12     return l_emp_tab;
 13  end;
 14  /

Function created.

SQL> select * from table (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

这篇关于如何从存储过程返回多行?(Oracle PL/SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！