pl/sql函数如何返回多行数据?在线等......

Question

这个问题我以前问过类似的，但一直没得到如意的答案！在oracle 里soctt的用户下的emp表，比如写一个函数，传入的参数为部门编号，然后返回所有该部门人员信息的函数。要用到游标，且要封装信息，该如何写？且能在pl/sql developer里能调试的，谢谢。

Answer 1

用游标即可：Create or REPLACE FUNCTION getCursorList(
     P_USER_ID_I          IN   VARCHAR2              --接收输入参数
)
RETURN    SYS_REFCURSOR
AS
     P_RESULT_SET_O          SYS_REFCURSOR; --返回游标
     X_SQL VARCHAR2(200);
BEGIN
      X_SQL :='select user_id,user_name,email from user_mst where user_id like ''%' || P_USER_ID_I ||'%''';   --生成SQL语句
      OPEN P_RESULT_SET_O FOR X_SQL;
      RETURN P_RESULT_SET_O;    --返回游标
END getCursorList;

Answer 2

SQL> create or replace procedure emp_dept
  2     (pi_deptno    IN       emp.deptno%TYPE,
  3      po_result    OUT      SYS_REFCURSOR)
  4  IS
  5  begin
  6     OPEN po_result FOR
  7        SELECT * FROM emp WHERE deptno=pi_deptno;
  8  end ;
  9  /过程已创建。SQL> var r_cur refcursor
SQL> exec emp_dept(10,:r_cur);PL/SQL 过程已成功完成。SQL> print :r_cur     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10      7839 KING       PRESIDENT            17-11月-81           5000
        10      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10
SQL>pl/sql developer里调试，单独查看游标结果集。

Answer 3

suiziguo你好，存储过程在pl/sql developer一般是传入参数调试，然后在数据库里看结果，这里执行过程后，如何在pl/sql developer上看结果（这里是输出10部门的结果）？写函数还真不知道写呢

Answer 4

pl/sql测试窗，下面有所有参数（包括IN and OUT）。在测试窗运行过程后，下面的参数栏里有个变量为po_result的值一栏里是<Cursor>
点击其右边的...按钮，就可以在新窗口里看见你想要的结果集。

Answer 5

函数在PL/SQL里调试也一样。以下是SQLPLUS里调试：SQL> CREATE OR REPLACE FUNCTION func_emp_dept
  2        (pi_deptno    IN      emp.deptno%TYPE)
  3  RETURN SYS_REFCURSOR
  4  IS
  5     po_result   SYS_REFCURSOR;
  6  BEGIN
  7     OPEN po_result FOR
  8        SELECT * FROM emp WHERE deptno=pi_deptno;
  9     RETURN po_result;
 10  END;
 11  /函数已创建。SQL> select func_emp_dept(10) from dual;FUNC_EMP_DEPT(10)
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10      7839 KING       PRESIDENT            17-11月-81           5000
        10      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10SQL>

Answer 6

对了，我把函数改成这样会报错：
create or replace function func_emp_dept(v_deptno IN emp.deptno%TYPE)
  return cursor IS  c_cursor cursor;
BEGIN
  OPEN c_cursor FOR
    SELECT * FROM emp WHERE deptno = v_deptno;
  RETURN c_cursor;
END;
说必须申明表标CURSOR,这怎么理解?

Answer 7

SYS_REFCURSOR是ORACLE内置ref cursor类型。
而cursor，只是一个关键字。

Answer 8

cursor不是游标类型吗？不能写return cursor? c_cursor cursor这样申明一个游标类型的实例c_cursor  不是吗？我是这样理解的

Answer 9

将返回类型定义为一个table或者是一个cursor