Oracle存储过程（Procedure）--------------提高程序执行的效率

一、什么是存储过程

所谓存储过程，就是一段存储在数据库中实现某种功能的程序，其中包含一条或多条SQL语句，但是它的定义方式和PL/SQL中的块、包等有所区别。存储过程可以通俗地理解为存储在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块。在数据库中有一些系统默认的存储过程，可以直接通过存储过程的名称进行调用。另外，存储过程还可以在其他编程语言中调用，例如：Java，C#，VB等。

 

二、存储过程的作用

储过程的编写相对比较复杂，但很多单位或个人都在使用它，显然这不是因为存储过程编写简单，而是因为它有着一系列的优点：
■ 简化复杂的操作。存储过程可以把需要执行的多条SQL语句封装到一个独立单元中，用户只需调用这个单元就能达到目的。这样就实现了一人编写多人调用，同时缩短了程序的平均开发周期，为公司节省了成本。
■ 增加数据独立性。与视图的效果类似，利用存储过程可以把数据库基础数据和程序（或用户）隔离开来，当基础数据的结构发生变化时，可以只修改存储过程，这样对程序来说基础数据的变化是不可见的，也就不需要修改程序代码了。
■ 提高安全性。使用存储过程可以有效降低错误出现的几率。如果想不使用存储过程实现某项操作可能需要执行多条单独的SQL语句，而过多的执行步骤很可能造成更高的出错几率。不仅如此，在实际工作中开发人员的水平参差不齐，由高水平的人编写存储过程，水平较低的人员直接调用，这样就能避免很多不必要的错误发生。此外，对存储过程也可以进行权限设置。
■ 提高性能。完成一项复杂的功能可能需要多条SQL语句，同时SQL每次执行都需要编译，而存储过程可以包含多条SQL语句，在创建完成后只需要编译一次就可以直接调用，从这方面来看存储过程可以提高性能。如果程序语言要实现某项比较复杂的功能，它会多次连接数据库，在使用存储过程的情况下，程序只需连接一次数据库就能达到目的。

 

三、存储过程的语法

建存储过程和创建视图相比稍微复杂，但也有它固有的模式，所以小伙伴们也能够很快上手，但想运用自如平时还需要多加练习。下面介绍存储过程的主要语法：

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
   [parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
                 | { OUT | IN OUT } [ NOCOPY ] datatype 
                 ] [,… ]
   { IS | AS }
   BODY  ;

其中各项参数介绍如下：
■ OR REPLACE：表示如果指定的过程已经存在，则覆盖同名的存储过程。
■ schema：表示该存储过程的所属模式。
■ procedure_name：创建的存储过程的名称。
■ parameter_name：存储过程中的参数名称。
■ [ IN ] datatype [ { := | DEFAULT } expression ]：表示传入参数的数据类型及其默认值。其中，datatype项表示参数的数据类型，[ { := | DEFAULT } expression ]项表示参数的默认值的写法。
■ { OUT | IN OUT } [ NOCOPY ] datatype：表示存储过程的参数类型，不过和上面介绍的IN有所区别。其中，OUT表示输出参数，IN OUT表示既可输入也可输出的参数，datatype依旧表示参数的数据类型。
■ { IS | AS }：连接词。
■ BODY：表示函数体，是存储过程的具体操作部分，通常包含在begin…end中。
注意    存储过程的参数默认类型是IN型的，也就是说是传入型的。

 

四、存储过程的创建和使用

 

1、创建一个最简单的存储过程

 

【示例1】创建第一个存储过程

01  CREATE PROCEDURE TEST
02  AS
03  BEGIN
04    DBMS_OUTPUT.PUT_LINE('我的第一个存储过程！');
05  END;
06  /

【代码解析】
■ 第1行表示创建存储过程，名为TEST。
■ 第4行表示输出一行字符串“我的第一个存储过程！”。

到目前为止，只是成功创建了过程，接下来执行该存储过程，查看输出结果。执行命令如下

BEGIN
  TEST;
END;

【技巧】可以通过在关键字EXEC后面加上存储过程名来执行已经存在的存储过程。

 

2、无参存储过程

无参存储过程就是创建的存储过程不带任何参数，通常这种存储过程被用做数据转换的几率比较大，如果小伙伴们认真地学习了前面的章节，相信很容易理解这部分内容。

示例中还是以PRODUCTINFO（产品信息表）和CATEGORYINFO（产品类型表）为基础，如下图：

【示例2】编写无参存储过程

把表PRODUCTINFO中价格最低的3件商品的DESPERATION字段设置成“促销商品”。实现步骤如下：                                                      

1）将PRODUCTINFO表中产品价格最低的3件产品查询出来。
2）把价格最低的3件产品DESPERATION字段加上“促销商品”字样。

01  CREATE PROCEDURE PRODUCT_UPDATE_PRC
02   AS
03   BEGIN
04   UPDATE PRODUCTINFO SET DESPERATION = '促销产品'
05   WHERE PRODUCTID IN
06   (
07      SELECT PRODUCTID FROM
08       ( SELECT * FROM PRODUCTINFO ORDER BY PRODUCTPRICE ASC )
09      WHERE ROWNUM < 4
10    );
11  COMMIT;
12   END;
13   /

【代码解析】
这是一个结构相对简单的存储过程，它的函数体中只是一个稍微复杂点的SQL语句。下面简单说明一下该脚本。
■ 第1行表示创建存储过程，其名为PRODUCT_UPDATE_PRC。
■ 第4～10行实现了示例的要求。
■ 第8行表示把表中所有记录根据产品价格进行升序排列并提取数据。
■ 第7～9行表示查询出价格最低的三条记录，这也是实现步骤中的第一步。
■ 第11行表示提交更改。

此时的存储过程还没有得到执行，只是通过了编译，下面开始执行该存储过程

EXEC PRODUCT_UPDATE_PRC

示例的流程及结果如下图（图中的命令，完全可以在PL/SQL Developer中执行）：

 

下面开始尝试一个稍微复杂的存储过程

【示例3】使用游标处理数据

要求把PRODUCTINFO表中数据根据不同的产品类型把数据分类输出到屏幕。

01  CREATE PROCEDURE PRODUCT_CUR_PRC
02  AS
03  cur_ctgy productinfo.category%TYPE;       --存放产品类型编码
04  cur_ctgyname categoryinfo.categoryname%TYPE;    --存放产品类型名称
05  cur_prtifo productinfo%ROWTYPE;         --存放表productinfo的行记录
06  
07  CURSOR cur_category             
08  IS 
09  SELECT CATEGORY  FROM PRODUCTINFO GROUP BY CATEGORY;
10  
11  BEGIN
12    OPEN cur_category;
13      LOOP
14        FETCH cur_category INTO cur_ctgy;
15        EXIT WHEN cur_category%NOTFOUND;
16             SELECT CATEGORYINFO.CATEGORYNAME INTO cur_ctgyname 
17             FROM CATEGORYINFO 
18             WHERE CATEGORYID = cur_ctgy;    --根据类型编码得到产品类型名称
19             
20                 IF SQL%FOUND THEN
21                     DBMS_OUTPUT.PUT_LINE('--------------------------------------');
22                     DBMS_OUTPUT.PUT_LINE(cur_ctgyname || ':');
23                 END IF;
24                 
25             FOR my_prdinfo_rec IN 
26               ( 
27                 SELECT * FROM PRODUCTINFO WHERE CATEGORY = cur_ctgy 
28               )
29                 LOOP
30                  DBMS_OUTPUT.PUT_LINE(
31                                        '产品名称： ' || my_prdinfo_rec.PRODUCTNAME
32                                       || '产品价格： ' ||  my_prdinfo_rec.PRODUCTPRICE
33                                       || '产品数量： ' ||  my_prdinfo_rec.QUANTITY    
34                                       );
35                 END LOOP;
36      END LOOP;
37     CLOSE cur_category;
38  END ;
39  /

【代码解析】
■ 第1行表示创建存储过程，名称是PRODUCT_CUR_PRC。
■ 第3～5行表示变量名称以及变量类型，脚本的具体含义可参考游标部分的讲解。其中cur_ctgy存放产品类型编码，cur_ctgyname存放产品类型名称，cur_prtifo存放表productinfo的行记录。
■ 第7～9行表示创建一个游标，表示从PRODUCTINFO表中查询已有的产品类型。
■ 第12～15行表示打开游标cur_category并进入循环流提取数据，当数据提取完毕会退出循环。
■ 第16～18行表示在CATEGORYINFO表中，根据产品类型编码得到产品类型名称，并把得到的结果赋值给变量cur_ctgyname。
■ 第20～23行利用隐式游标的%FOUND属性判断第16～18行的查询是否有结果，如果有则把产品类型名称输出到屏幕。
■ 第25～35行表示利用隐式游标获取某类型的所有产品，并把产品信息分类输出到屏幕上。

 

3、有参存储过程

存储过程允许带有参数，参数的使用将增加存储过程的灵活性，给数据库编程带来极大的方便。本小节将介绍如何使用输入类型参数、输出类型参数和输入输出类型参数。

过程有输入参数、输出参数、输入输出参数三种（这里不是指参数的数据类型）。其中，输入参数是默认的参数，也叫IN类型的参数。下面通过示例演示存储过程中使用输入参数的方法。

【示例4】使用输入参数的存储过程

该示例将根据输入的产品类型从表PRODUCTINFO中搜索符合要求的数据，并将其打印到屏幕

01  CREATE PROCEDURE PRODUCT_INTYPE_PRC (parm_ctgyname IN VARCHAR2)
02  AS
03  cur_ctgyid categoryinfo.categoryid%TYPE;                      --存放产品类型编码
04  cur_prtifo productinfo%ROWTYPE;                            --存放表productinfo的行记录
05  
06  BEGIN
07             SELECT CATEGORYINFO.CATEGORYID INTO cur_ctgyid 
08             FROM CATEGORYINFO                                  
09             WHERE CATEGORYINFO.CATEGORYNAME = parm_ctgyname;      
10             --根据类型编码得到产品类型名称
11           
12             IF SQL%FOUND THEN
13                 DBMS_OUTPUT.PUT_LINE('------------------------------------------');
14                 DBMS_OUTPUT.PUT_LINE(parm_ctgyname || ':');
15            END IF;
16             FOR my_prdinfo_rec IN 
17               ( 
18                 SELECT * FROM PRODUCTINFO WHERE CATEGORY = cur_ctgyid 
19               )
20              LOOP
21                  DBMS_OUTPUT.PUT_LINE(
22                                          '产品名称： ' || my_prdinfo_rec.PRODUCTNAME
23                                       || '产品价格： ' ||  my_prdinfo_rec.PRODUCTPRICE
24                                       || '产品数量： ' ||  my_prdinfo_rec.QUANTITY    
25                                       );
26               END LOOP;
27
28           EXCEPTION 
29              WHEN NO_DATA_FOUND THEN
30                DBMS_OUTPUT.PUT_LINE('没有数据！'); 
31              WHEN TOO_MANY_ROWS THEN  
32                DBMS_OUTPUT.PUT_LINE('数据过多！'); 
33  END ;
34  /

【代码解析】
■ 第1～2行表示创建存储过程。存储过程包括IN类型的参数，表示该参数为输入类型的参数。此时可以省略关键字IN。
■ 第3～4行表示声明存储过程的内部变量。其中，cur_ctgyid表示产品类型编码，而cur_prtifo表示行类型的记录。
■ 第7～9行表示用参数parm_ctgyname传进的值作为查询条件从表CATEGORYINFO中找出对应的编码类型，并把该类型编码放到变量cur_ctgyid中。
■ 第12～15行表示利用隐式游标的属性进行判断：如果有记录则把该类型名称输出到屏幕；如果没有记录或根据条件查询的记录过多，则会进入异常块部分。
■第16～26行表示根据产品类型编码从表PRODUCTINFO中查询数据，并把得到的数据输出到屏幕。
■ 第28～32行表示异常块部分，当出现NO_DATA_FOUND或TOO_MANY_ROWS异常时会中断操作进入该异常部分。

【示例5】输出类型参数的使用

完成该示例需要两个步骤，第一步就是创建被调用的存储过程，它里面包含OUT类型参数；第二步就是调用程序，这里也使用存储过程。详细实现过程如下：
1）创建被调用的存储过程。该过程提供了根据输入的产品类型查询出对应的产品类型编码的功能，并将得到的编码放到输出参数中。相关脚本如下：

01  CREATE PROCEDURE PRODUCT_OUTTYPE_PRC(parm_ctgyname IN VARCHAR2,
02                        parm_ctgyid OUT VARCHAR2)
03  AS
04  BEGIN
05        SELECT CATEGORYINFO.CATEGORYID INTO parm_ctgyid 
06      FROM CATEGORYINFO                                  
07      WHERE CATEGORYINFO.CATEGORYNAME = parm_ctgyname;  
08          IF SQL%FOUND THEN
09               DBMS_OUTPUT.PUT_LINE('传出参数是：' || parm_ctgyid);
10          END IF;  
11      
12      EXCEPTION 
13      WHEN NO_DATA_FOUND THEN
14        DBMS_OUTPUT.PUT_LINE('没有数据！'); 
15      WHEN TOO_MANY_ROWS THEN  
16        DBMS_OUTPUT.PUT_LINE('数据过多！'); 
17  END PRODUCT_OUTTYPE_PRC;
18  /

【代码解析】
■ 第1～3行表示创建存储过程PRODUCT_OUTTYPE_PRC，该过程有两个参数，其中第一个参数parm_ctgyname为输入参数，表示输入的是产品类型名称，而parm_ctgyid是输出参数，表示的是产品类型编码。
■ 第5～7行表示根据产品名称查询产品编码。
■ 第12～16行表示异常部分。

2）创建调用存储过程。该过程根据输入的产品类型以及价格从表PRODUCTINFO中查询符合要求的数据并输出到屏幕。详细脚本如下：

01  CREATE PROCEDURE PRODUCT_CLOUTTYPE_PRC(parm_ctgyname IN VARCHAR2,
02                         parm_pric NUMBER)
03  AS
04  cur_ctgyid categoryinfo.categoryid%TYPE;                       --存放产品类型编码
05  cur_prtifo productinfo%ROWTYPE;                             --存放表productinfo的行记录
06  
07  BEGIN           
08    PRODUCT_OUTTYPE_PRC(parm_ctgyname,cur_ctgyid);
09            IF SQL%FOUND THEN
10                 DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
11                 DBMS_OUTPUT.PUT_LINE(parm_ctgyname || '对应的编码是：' || cur_ctgyid);
12            END IF;
13             FOR my_prdinfo_rec IN 
14               ( 
15                 SELECT * FROM PRODUCTINFO 
16                 WHERE CATEGORY = cur_ctgyid 
17                 AND PRODUCTINFO.PRODUCTPRICE < parm_pric
18               )
19                 LOOP
20                  DBMS_OUTPUT.PUT_LINE(
21                                        ' 产品名称： ' || my_prdinfo_rec.PRODUCTNAME
22                                       || ' 产品价格： ' ||  my_prdinfo_rec.PRODUCTPRICE
23                                       || ' 产品数量： ' ||  my_prdinfo_rec.QUANTITY    
24                                       );
25                 END LOOP;
26  END ;
27  /

【代码解析】
■ 第1～3行是创建过程，该过程有两个参数，分别为parm_ctgyname和parm_pric，这两个参数都是输入类型的参数。
■ 第4～5行表示声明两个变量。
■ 第8行表示调用过程PRODUCT_OUTTYPE_PRC，该过程需要两个参数，其中，cur_ctgyid占用了输出参数的位置。也就是说，该过程的返回值将会初始化变量cur_ctgyid。
■ 第9～12行判断并输出产品类型名称对应编码。
■ 第13～25行表示循环输出符合条件记录的数据。其中，第15～17行表示查询指定产品类型及低于指定价格的数据。

使用OUT类型参数时需要注意以下两点：
1）OUT类型的参数需要用变量填充，而不能用常量或表达式。
2）如果过程中被调用的过程因发生未处理的异常而退出，那么调用程序通常得不到任何OUT参数的值（包括发生异常之前已经得到的OUT类型的参数值）；如果在发生的异常被处理的情况下退出，那么之前得到的OUT的参数值将会被调用程序获得。

【示例6】使用输入输出类型的参数

下面演示如何使用输入输出类型参数。完成该示例同样需要两个步骤，详细实现过程如下：
1）创建被调用的存储过程。该过程根据输入的产品类型编码和降价比例对表PRODUCTINFO的数据进行降价修改，并返回修改的记录数。脚本如下：

01  CREATE PROCEDURE PRODUCT_INOUTTYPE_PRC(parm_ctgyid IN VARCHAR2,
02                        parmparm_pric IN OUT NUMBER)
03  AS
04  BEGIN
05      UPDATE PRODUCTINFO 
06      SET PRODUCTPRICE = PRODUCTINFO.PRODUCTPRICE*(1-parmparm_pric) 
07      WHERE PRODUCTINFO.CATEGORY = parm_ctgyid;
08      IF SQL%FOUND THEN
09           parmparm_pric := SQL%ROWCOUNT;
10      END IF;  
11  END PRODUCT_INOUTTYPE_PRC;

【代码解析】
■ 第1～3行表示创建存储过程PRODUCT_INOUTTYPE_PRC，包含两个参数：其中parm_ctgyid表示产品类型编码，是输入参数；parmparm_pric在用来输入时表示降价比例，而用来输出时则表示修改语句的记录数。
■ 第5～7行表示根据提供的产品类型编码和降价比例修改PRODUCTINFO表中的价格记录。
■ 第9行表示利用游标的%ROWCOUNT属性得到修改的记录数。该记录数实际赋值给了该存储过程的输入输出参数parmparm_pric。

2）创建调用存储过程。在该过程中输入产品类型名称，根据产品类型名称查询出产品类型编码并调用PRODUCT_INOUTTYPE_PRC存储过程。最终输出修改的记录数。脚本如下：

01  CREATE PROCEDURE PRODUCT_CLINOUTTYPE_PRC(parm_ctgyname IN VARCHAR2)
02  
03  AS
04  cur_ctgyid categoryinfo.categoryid%TYPE;                    --存放产品类型编码
05  cur_pric number;
06  
07  BEGIN             
08      SELECT CATEGORYINFO.CATEGORYID INTO cur_ctgyid 
09      FROM CATEGORYINFO                                  
10      WHERE CATEGORYINFO.CATEGORYNAME = parm_ctgyname;          
11                  
12      PRODUCT_INOUTTYPE_PRC(cur_ctgyid,cur_pric);
13      IF cur_pric > 0 THEN
14           DBMS_OUTPUT.PUT_LINE('共修改' || cur_pric || '条记录。');
15      END IF; 
16                 
17      EXCEPTION 
18      WHEN NO_DATA_FOUND THEN
19        DBMS_OUTPUT.PUT_LINE('没有数据！'); 
20      WHEN TOO_MANY_ROWS THEN  
21        DBMS_OUTPUT.PUT_LINE('数据过多！'); 
22  END ;
23  /

【代码解析】
■ 第1～3行创建存储过程PRODUCT_CLINOUTTYPE_PRC，其参数表示产品类型名称，为输入型参数。
■ 第4～5行声明变量。cur_ctgyid用于存放产品类型编码，cur_pric用于存储调用存储过程PRODUCT_INOUTTYPE_PRC所返回的记录数。
■ 第8～10行表示根据产品类型查询产品编码。
■ 第12行表示调用存储过程PRODUCT_INOUTTYPE_PRC，此时利用返回值初始化变量cur_pric。
■ 第13～15行表示利用条件语句判断后输出修改的记录数。
■ 第17～21行表示异常块。

到此，存储过程学习告一段落，欢迎纠正，欢迎补充。

本博客主要引自：《零基础学Oracle》 — 赵雪 胡可 王建强