用proc返回cursor

来源：互联网发布：单片机检测高电平输入编辑：程序博客网时间：2024/05/16 17:59

今天有人在问proc返回cursor的问题，下面给个示例

通过out返回cursor

CREATE OR REPLACE PROCEDURE getcity(v_citycode IN VARCHAR2,                                    ref_cursor OUT SYS_REFCURSOR ASBEGIN  OPEN ref_cursor FOR    SELECT * FROM emp WHERE deptno = v_citycode;END;

下面是引用方法

SET serveroutput onDECLARE  v_ref_cursor SYS_REFCURSOR;  v_emp        emp%ROWTYPE;BEGIN  getcity(10, v_ref_cursor);  LOOP    FETCH v_ref_cursor      INTO v_emp;    EXIT WHEN v_ref_cursor%NOTFOUND;    dbms_output.put_line('empno:' || v_emp.empno || '  ename:' || v_emp.ename);  END LOOP;END;/

结果

SQL> SET serveroutput onSQL> DECLARE  2    v_ref_cursor SYS_REFCURSOR;  3    v_emp        emp%ROWTYPE;  4  BEGIN  5    getcity(10, v_ref_cursor);  6    LOOP  7      FETCH v_ref_cursor  8        INTO v_emp;  9      EXIT WHEN v_ref_cursor%NOTFOUND; 10      dbms_output.put_line('empno:' || v_emp.empno || '  ename:' || v_emp.ename); 11    END LOOP; 12  END; 13  /empno:7782  ename:CLARKempno:7839  ename:KINGempno:7934  ename:MILLERPL/SQL procedure successfully completed

0 0