比较常用的Oracle存储过程

#输出多行多列
create or replace procedure sp_for_emp(v_empno in number,
                                       v_out out sys_refcursor)
as
begin
     open v_out for select * from emp where empno;
     exception
       when others then
       dbms_output.put_line('error');
end;
/


#执行sp
declare
  c1 sys_refcursor;
begin	  
	  sp_for_emp(7788,c1);	  
end;
/


#返回一个值
create or replace procedure sp_for_emp_001(v_empno in number,
                                           v_out out varchar)
as
begin
    select ename into v_out from emp where empno=v_empno;
     exception
       when others then
       dbms_output.put_line('error');
end;
/



#执行sp
declare
  c1 varchar2(10);
begin	  
	  sp_for_emp_001(7788,c1);
	  dbms_output.put_line(c1);
end;
/




#批量绑定(游标+table_type)
CREATE OR REPLACE PROCEDURE sp_for_emp_002
as
TYPE C1 IS TABLE OF EMP%ROWTYPE;
MY_C2 C1;
CURSOR C14 IS  SELECT * FROM EMP;
BEGIN
   OPEN C14;
   LOOP
   FETCH C14 BULK COLLECT INTO MY_C2 LIMIT 20;
   FOR I IN 1..MY_C2.COUNT LOOP
   DBMS_OUTPUT.PUT_LINE(MY_C2(I).ENAME);
   END LOOP;
   EXIT WHEN C14%NOTFOUND;
   END LOOP;
END;
/

BEGIN
	   sp_for_emp_002;
END;
/

SCOTT@PROD>BEGIN
  2     sp_for_emp_002;
  3  END;
  4  /
			SMITH
			ALLEN
			WARD
			JONES
			MARTIN
			BLAKE
			CLARK
			SCOTT
			KING
			TURNER
			ADAMS
			JAMES
			FORD
			MILLER



# 利用pipeline函数
CREATE OR REPLACE PACKAGE s1 
AS
TYPE C1 IS TABLE OF NUMBER;
FUNCTION C2(X NUMBER) RETURN C1 PIPELINED;
END s1;
/
CREATE OR REPLACE PACKAGE BODY s1 AS
FUNCTION C2(X NUMBER) 
RETURN C1 
PIPELINED 
IS
BEGIN
    FOR I IN 1..X LOOP
    PIPE ROW(I);
    END LOOP;
    RETURN;
END;
END s1;
/

SCOTT@PROD>select * from table(s1.c2(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
          


#捕获错误,使得程序执行完成
create or replace procedure sp_for_emp002
as
v_empno number;
begin
	   select empno into v_empno from emp;
	   exception
	   when others then
	   dbms_output.put_line(sqlerrm);	  
end;
/

#我们可以建立一张日志表,当执行出错的时候把该过程的名称 执行时间 错误原因 记录到日志表里面.
SCOTT@PROD>begin
  2  sp_for_emp002;
  3  end;
  4  /
ORA-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed