C:oracleora92sqlplusdemo>sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on 星期六 4月 2 11:09:06 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn scott/tiger@orcl connected. SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type, 2 out_curEmp out SYS_REFCURSOR) as 3 4 begin 5 open out_curEmp for 6 SELECT * FROM emp WHERE deptno = in_deptNo ; 7 EXCEPTION 8 WHEN OTHERS THEN 9 RAISE_APPLICATION_ERROR(-20101, 10 'Error in getEmpByDept' || SQLCODE ); 12 end getEmpByDept; 13 /已建立程序.SQL> var rset refcursor; SQL> exec getEmpByDept(10,:rset);PL/SQL 程序順利完成.SQL> print rset;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7782 CLARK MANAGER 7839 09-1月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10
在调用的时候能否改成 DECLARE 。 BEGIN 。 END; 的格式
在另外一个存储过程里,定义一个返回值变量 如 tRet int, 如果要有返回值的做好写成function,方便在其他存储过程里调用begin tRet := getEmpByDept(....); if tRet = 0 then return;end 里
--借用一下2楼的存储过程 CREATE OR REPLACE PROCEDURE GETEMPBYDEPT( IN_DEPTNO IN EMP.DEPTNO%TYPE, OUT_CUREMP OUT SYS_REFCURSOR ) AS BEGIN OPEN OUT_CUREMP FOR SELECT * FROM EMP WHERE DEPTNO = IN_DEPTNO; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20101, 'Error in getEmpByDept' || SQLCODE); END GETEMPBYDEPT; / DECLARE v_deptno VARCHAR(10) := '30'; o_cursor SYS_REFCURSOR; r_emp emp%ROWTYPE; BEGIN GETEMPBYDEPT(v_deptno,o_cursor); LOOP FETCH o_cursor INTO r_emp; --遍历写法 EXIT WHEN o_cursor%NOTFOUND; dbms_output.put_line('EMPNO:'||r_emp.EMPNO); dbms_output.put_line('ENAME:'||r_emp.ENAME); dbms_output.put_line('JOB:'||r_emp.JOB); dbms_output.put_line('MGR:'||r_emp.MGR); dbms_output.put_line('HIREDATE:'||r_emp.HIREDATE); dbms_output.put_line('SAL:'||r_emp.SAL); dbms_output.put_line('COMM:'||r_emp.COMM); dbms_output.put_line('DEPTNO:'||r_emp.DEPTNO); dbms_output.put_line(''); END LOOP; CLOSE o_cursor; EXCEPTION WHEN OTHERS THEN CLOSE o_cursor; END;
connected.
SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,
2 out_curEmp out SYS_REFCURSOR) as
3
4 begin
5 open out_curEmp for
6 SELECT * FROM emp WHERE deptno = in_deptNo ;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE_APPLICATION_ERROR(-20101,
10 'Error in getEmpByDept' || SQLCODE );
12 end getEmpByDept;
13 /已建立程序.SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);PL/SQL 程序順利完成.SQL> print rset;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7782 CLARK MANAGER 7839 09-1月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
DECLARE
。
BEGIN
。
END;
的格式
tRet := getEmpByDept(....);
if tRet = 0 then
return;end 里
--借用一下2楼的存储过程
CREATE OR REPLACE PROCEDURE GETEMPBYDEPT(
IN_DEPTNO IN EMP.DEPTNO%TYPE,
OUT_CUREMP OUT SYS_REFCURSOR
) AS
BEGIN
OPEN OUT_CUREMP FOR
SELECT * FROM EMP WHERE DEPTNO = IN_DEPTNO;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101, 'Error in getEmpByDept' || SQLCODE);
END GETEMPBYDEPT;
/
DECLARE
v_deptno VARCHAR(10) := '30';
o_cursor SYS_REFCURSOR;
r_emp emp%ROWTYPE;
BEGIN
GETEMPBYDEPT(v_deptno,o_cursor);
LOOP
FETCH o_cursor INTO r_emp; --遍历写法
EXIT WHEN o_cursor%NOTFOUND;
dbms_output.put_line('EMPNO:'||r_emp.EMPNO);
dbms_output.put_line('ENAME:'||r_emp.ENAME);
dbms_output.put_line('JOB:'||r_emp.JOB);
dbms_output.put_line('MGR:'||r_emp.MGR);
dbms_output.put_line('HIREDATE:'||r_emp.HIREDATE);
dbms_output.put_line('SAL:'||r_emp.SAL);
dbms_output.put_line('COMM:'||r_emp.COMM);
dbms_output.put_line('DEPTNO:'||r_emp.DEPTNO);
dbms_output.put_line('');
END LOOP;
CLOSE o_cursor;
EXCEPTION
WHEN OTHERS THEN
CLOSE o_cursor;
END;