Oracle 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

• 创建一个简单的存储过程

CREATE OR REPLACE PROCEDURE p_test  --创建一个p_test 存储过程, 如果存在就覆盖它;
IS  --表明后面是一个pl/sql体
BEGIN  --pl/sql体开始
NULL;
END;  --pl/sql体结束

• 执行储存过程

EXECUTE p_test;

• 执行储存过程

BEGIN
p_test;
END;

控制台输出显示

SET SERVEROUTPUT ON;

创建一个带输入参数的存储过程，把查询结果显示出来

CREATE OR REPLACE PROCEDURE p_test(in_id IN Integer)
IS  
p_ename Varchar2(20);
Error_Message Varchar2(100);
BEGIN
select ename into p_ename from emp where empno = in_id;
Exception
  When Others Then
    Error_Message := Sqlerrm;
    Dbms_Output.Put_Line (Error_Message);
    Raise;
Dbms_Output.Put_Line(p_ename);
END;  

创建一个带输入参数和输出参数的存储过程

Create Or Replace Procedure p_test(in_id IN Integer, out_total out Number)
IS  
Error_Message Varchar2(100);
Begin
select Nvl(sal, 0) + Nvl(comm, 0) into out_total from emp where empno = in_id;
Exception
  When Others Then
    Error_Message := Sqlerrm;
    Dbms_Output.Put_Line (Error_Message);
    Raise;
Dbms_Output.Put_Line(out_total);
End;

调用带输入和输出参数的存储过程

Declare
in_id Integer;
out_total Number;
Begin
in_id:=7369;
p_test(in_id, out_total);
Dbms_Output.Put_Line(out_total);
End;

使用sys_refcursor游标创建一个结果集存储过程

Create Or Replace Procedure p_test(out_return out sys_refcursor)
Is
Begin
open out_return for 'select * from emp';
End;

调用带结果集的存储过程（Fetch...into...）

Declare
cur SYS_REFCURSOR;
rowinfo emp%rowtype;
Begin
p_test(cur);
Loop
Fetch cur into rowinfo;
Exit when cur%notfound;
Dbms_Output.Put_Line(rowinfo.empno||','||rowinfo.ename);
End Loop;
close cur;
End;

Cursor游标for...in...loop循环游标例子

Declare
Cursor cur is select * from emp;
rowinfo emp%rowtype;
Begin
for rowinfo in cur loop
SYS.Dbms_Output.Put_Line(rowinfo.empno||','||rowinfo.ename);
end loop;
end;

Cursor游标while循环

Declare 
Cursor cur Is Select * From emp;
v_myemp emp%rowtype;  
Begin  
Open cur;   
Fetch cur Into v_myemp;   
While cur%found Loop   
dbms_output.put_line(v_myemp.ename);   
Fetch cur Into v_myemp;   
End Loop;  
End; 

bulk collectg高效循环游标

Declare
Cursor cur Is Select * From emp;
Type rowin Is Table Of emp%rowtype;  -- Type 声明是类型, Is Table Of  指定是一个集合的表的数组类型,emp表上%ROWTYPE 指在表上的行的数据类型.
rowinfo rowin;
Begin
  Open cur;
  Loop
    Fetch cur bulk collect into rowinfo limit 3;  -- BULK COLLECT INTO 指是一个成批聚合类型,它可以存储一个多行多列存储类型
      For i In 1..rowinfo.count Loop
        Dbms_Output.Put_Line (rowinfo(i).empno||','||rowinfo(i).ename);  --(i)表示下标号  
      End Loop;
    Exit when cur%notfound;  --判断游标结束   
  End loop;
End;

BULK COLLECT 子句会批量检索结果，即一次性将结果集绑定到一个集合变量中，并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT.

BULK COLLECT的限制

1. 不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2. 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3. BULK COLLECT INTO 的目标对象必须是集合类型。
4. 复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5. 如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6. 如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

---

• xx_cursor%ISOPEN 游标打开，其值为True;
• xx_cursor%FOUND Fetch语句返回一行记录，其值为True;
• xx_cursor%ROWCOUNT 返回已经从游标中取出的记录数；
• xx_cursor%NOTFOUND Fetch语句无返回，其值为True;

---

维护存储过程

1、查看过程状态

 SELECT object_name,status FROM USER_OBJECTS WHERE object_type='PROCEDURE';

2、重新编译过程

ALTER PROCEDURE xs_proc COMPILE;

3、查看过程的源代码

 SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';

4、删除存储过程

 DROP PROCEDURE xs_proc;