Oracle存储过程

数据库  /  管理员 发布于 3年前   147

1. 简介
    存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。

2. 存储过程的优点
（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（2）当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
（3）存储过程可以重复使用,可减少数据库开发人员的工作量
（4）安全性高,可设定只有某用户才具有对指定存储过程的使用权

3. 创建存储过程
语法：
CREATE [OR REPLACE] PROCEDURE 存储过程名
[(参数名 [IN | OUT | IN OUT] datatype [,...])]
{IS | AS}
BEGIN
过程体
END 存储过程名;

说明：
[OR REPLACE]：表示如果过程已经存在，则替换已有的过程。
[IN | OUT | IN OUT]：定义了参数的模式，默认为IN。
{IS | AS}：这两个关键字等价
datatype ：指定参数的类型

示例：
CREATE OR REPLACE PROCEDURE P001 IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(systimestamp);
END;
调用：
SQL> set serveroutput on  // 打开Oracle自带的输出方法DBMS_OUTPUT
SQL> exec p001; 
SQL> call p001();

4. 修改存储过程
示例：
CREATE OR REPLACE PROCEDURE P001 IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('当前时间：')
    DBMS_OUTPUT.PUT_LINE(systimestamp);
END;

5. 删除存储过程
DROP PROCUDURE P001;

6. 存储过程的参数传递

CREATE OR REPLACE PROCEDURE P001(dno NUMBER, dname VARCHAR2 DEFAULT NULL, loc VARCHAR2 DEFAULT NULL) IS
BEGIN
    INSERT INTO dept VALUES(dno, dname, loc);
END;

（1）按位置传递参数
    即调用时的实参数据类型、个数与形参在相应位置上要保持一致，如果位置没有对应，那么将产生错误。
    调用：SQL> EXEC P001(1,'nnn','ddd');

（2）按名称传递参数
    使用带名称的参数，调用时用“=>”符号把实参和形参关联起来，按名称传递参数可以避免按位置传递参数引发的问题，而且代码更容易阅读和维护。
    调用：SQL> EXEC P001(dname=>'nnn', dno=>2, loc=>'ddd');

（3）混合方式传递参数
    指开头的参数使用按位置传递参数，其余参数使用按名称传递参数。
    调用：SQL> EXEC P001(3, loc=>'ddd', dname=>'nnn');

7. 存储过程的参数模式
（1）建立带有IN参数的存储过程（默认）
    IN模式的参数，用于向存储过程传入一个值，如果参数没有指定参数模式默认为输入参数，当调用该存储过程时，除了具有默认值的参数之外，其余参数必须要提供数值。

CREATE OR REPLACE PROCEDURE P001(dno NUMBER, dname VARCHAR2 DEFAULT NULL, loc VARCHAR2 DEFAULT NULL) IS
BEGIN
    INSERT INTO dept VALUES(dno, dname, loc);
END;

（2）建立带有OUT参数的存储过程
    OUT模式的参数，用于从被调用存储过程返回一个值，如下：

CREATE OR REPLACE PROCEDURE P001
(dno NUMBER, dname OUT VARCHAR2, loc OUT VARCHAR2) IS
BEGIN
    SELECT name, oc INTO dname, loc from dept WHERE id=dno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RALSE_APPLICATION_ERROR(-20000, 'no data');
END;

（3）建立带有IN OUT参数的存储过程
    IN OUT模式的参数，在调用过程之前用于向过程传入一个参数，在调用结束之后，Oracle会通过变量将过程结果传递给应用程序。

CREATE OR REPLACE PROCEDURE P001
(num1 IN OUT NUMBER, num2 IN OUT NUMBER) IS
v1 NUMBER;
v2 NUMBER;
BEGIN
    v1:=num1/num2;
    v2:=MOD(num1,num2);
    num1:=v1;
    num2:=v2;
END;

调用：
SQL >var n1 NUMBER
SQL >var n2 NUMBER
SQL >EXEC :n1 :=100
SQL >EXEC :n2 :=30
SQL >EXEC P001(:n1, :n2)
SQL >print n1 n1