【Oracle系列】- 存储过程（Stored Procedure）

矫情吗；* 2023-10-10 23:07 44阅读 0赞

## 【Oracle系列】- 存储过程（Stored Procedure） ##

#### 文章目录 ####

*  【Oracle系列】- 存储过程（Stored Procedure）
 *   *   *  一、什么是存储过程
         *  二、存储过程优缺点
         *   *  存储过程优点
             *  存储过程缺点
         *  三、存储过程创建示例
         *   *  3.1 创建语法
             *  3.2 创建存储过程步骤
             *  3.3 创建一个简单无参存储过程
             *  3.4 创建一个有入参的存储过程
             *  3.5 创建一个存储过程并返回结果集
             *  3.6 存储过程中SELECT INFO
             *   *  语法
                 *  参数说明：
             *  3.7 存储过程中FETCH INTO
             *   *  语法
                 *  参数说明
             *  3.8 存储过程中RETURNING
             *   *  语法：
                 *  参数说明：
             *  3.9 存储过程中FORALL
             *   *  语法：
                 *  参数说明:
             *  3.10 存储过程中SAVE EXCEPTIONS
             *  3.11 存储过程中 INDICES OF
             *   *  语法：
             *  3.12 存储过程中VALUES OF
             *   *  语法：
         *  四、总结

#### 一、什么是存储过程 ####

**存储过程**（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储过程在数据库中，经过第一次编译后再次调用不需要二次编译。用户通过指定的存储过程名称应传给参数来调用完成。

存储过程就是解决特定问题，有先后顺序的一组SQL语句集合。

在Oracle数据库中存储过程是Procedure。

#### 二、存储过程优缺点 ####

*  ##### 存储过程优点 #####
    
    1.  效率高
        
        存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的SQL语句在代码中，都要先分析编译才会执行。所以想对而言存储过程效率更高。
    2.  降低网络流量
        
        存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的SQL语句。
    3.  复用性高
        
        存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。
    4.  可维护性高
        
        当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。
    5.  安全性高
        
        完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。
 *  ##### 存储过程缺点 #####
    
    1.  增加数据库负荷： 存储过程编译存储在数据库服务端增加了数据库的负荷。
    2.  存储过程编辑编译困难：因存储过程编写一般在DBMS中完成，相对于代码编辑器，调试编写都不那么友好。
    3.  可移植性差：不同的数据库有独特的编写方法，不同达到一次编译多个数据库同时使用。

#### 三、存储过程创建示例 ####

##### 3.1 创建语法 #####

create or replace procedure schema.过程名(变量名 in|out 数据类型) 
    is | as -- 此处 is 和 as 等同
       声明语句段;
    begin
       执行语句段;
    exception 
       异常处理语句段;
    end 过程名;

##### 3.2 创建存储过程步骤 #####

Oracle存储过程大体分为这么几个部分：

1.  创建语句：create or replace procedure 存储过程名
    
    如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
2.  存储过程名定义：包括存储过程名和参数列表
    
    参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT
    
    **IN** 表示输入参数，按值传递方式。
    
    **OUT** 表示输出参数，可以理解为按引用传递方式。可以作为存储过程的输出结果，供外部调用者使用。
    
    **IN OUT** 即可作输入参数，也可作输出参数。
    
    >  *  参数的数据类型只需要指明类型名，不需要指明参数类型
    >  *  参数类型的宽度由外部调用者决定
    >  *  过程可以有参数，也可以没有参数
3.  变量声明块
    
    紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量；
    
    变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
4.  过程语句块
    
    从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
5.  异常处理块
    
    关键字为exception ，为处理语句产生的异常。该部分为可选
6.  结束块
    
    由end关键字结果

##### 3.3 创建一个简单无参存储过程 #####

create or replace procedure pro_noparameters
    is
      v_depte_id integer:= '';
    begin
       select dept_id into v_depte_id  from sys_dept where dept_id = 100;
       dbms_output.put_line('v_depte_id:' || v_depte_id);
    end;

##### 3.4 创建一个有入参的存储过程 #####

create or replace procedure pro_parameters(
       in_deptid in number
    )
    as
      v_dept_name varchar2(30):='';
    begin
       select DEPT_NAME into v_dept_name  from sys_dept where dept_id= in_deptid;
       dbms_output.put_line('v_dept_name:' || v_dept_name);
    end pro_parameters;

##### 3.5 创建一个存储过程并返回结果集 #####

create or replace procedure pro_showdata(data out sys_refcursor)
    as
     begin
        open data for  select * from sys_dept;
     end pro_showdata;

##### 3.6 存储过程中SELECT INFO #####

查出来一个结果集合赋值给一个集合变量。

*  ###### 语法 ######

SELECT field BULK COLLECT INTO var_conllect FROM table where whereColStatement;

*  ###### 参数说明： ######

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>field</td> 
   <td>要查询的字段，可以是一个或多个（要保证和后面的集合变量要向对应）</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>var_collect</td> 
   <td>集合变量（联合数组等），用来存放查到的结果</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>table</td> 
   <td>表名，要查询的表</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>whereColStatement</td> 
   <td>后面过滤条件语句</td> 
  </tr> 
 </tbody> 
</table>

##### 3.7 存储过程中FETCH INTO #####

从一个集合中抓取一部分数据赋值给一个集合变量

*  ###### 语法 ######

FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

*  ###### 参数说明 ######

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>cur1</td> 
   <td>数据集合</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>var_collect</td> 
   <td>数据集合</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>[LIMIT rows]</td> 
   <td>可有可无，限制每次抓取的数据量。不写的话，默认每次一条数据</td> 
  </tr> 
 </tbody> 
</table>

##### 3.8 存储过程中RETURNING #####

BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用，可以返回这些DML语句执行后所影响的记录内容（某些字段）

*  ###### 语法： ######

DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

*  ###### 参数说明： ######

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>DMLStatement</td> 
   <td>是一个DML语句</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>field</td> 
   <td>是这个表的某个字段，当然也可以写多个逗号隔开（field1，field2, field3）</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>var_field</td> 
   <td>一个类型为该字段类型的集合，多个的话用逗号隔开，如：（var_field1, var_field2, var_field3）</td> 
  </tr> 
 </tbody> 
</table>

##### 3.9 存储过程中FORALL #####

*  ###### 语法： ######

FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement；

*  ###### 参数说明: ######

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>index</td> 
   <td>执行过程游标</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bounds</td> 
   <td>游标边界，形式是start…end</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>[SAVE EXCEPTIONS]</td> 
   <td>可选值，（后面详细介绍）</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>sqlStatement</td> 
   <td>一个DML语句，这里有且仅有一个sql语句</td> 
  </tr> 
 </tbody> 
</table>

##### 3.10 存储过程中SAVE EXCEPTIONS #####

在执行DML时，遇到异常，导致某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句，那么DML语句会在执行到一半的时候停下来；如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句，当在执行过程中如果遇到异常，数据处理会继续向下进行，发生的异常信息会保存到SQL%BULK\_EXCEPTONS的游标属性中，该游标属性是个记录集合，每条记录有两个字段分别是：ERROR\_INDEX和ERROR\_CODE

**ERROR\_INDEX**：该字段会存储发生异常的FORALL语句的迭代编号

**ERROR\_CODE**：存储对应异常的，oracle错误代码

SQL%BULK\_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。

而这个异常记录集合异常的个数则由它的COUNT属性表示，即:SQL%BULK\_EXCEPTONS.COUNT，SQL%BULK\_EXCEPTIONS有效的下标索引范围在1到%BULK\_EXCEPTIONS.COUNT之间。

##### 3.11 存储过程中 INDICES OF #####

在Oracle数据库10g之前有一个重要的限制，该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容，如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：ORA-22160: element at index \[N\] does not exist。针对这一问题，Oracle后续又提供了两个新语句：INDICES OF 和 VALUES OF

*  ###### 语法： ######

FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

##### 3.12 存储过程中VALUES OF #####

VALUES OF适用情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是，VALUES OF在使用时有一些限制：

如果VALUES OF子句中所使用的集合是联合数组，则必须使用PLS\_INTEGER和BINARY\_INTEGER进行索引，VALUES OF 子句中所使用的元素必须是PLS\_INTEGER或BINARY\_INTEGER；

当VALUES OF 子句所引用的集合为空，则FORALL语句会导致异常；

*  ###### 语法： ######

FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

#### 四、总结 ####

Oracle存储过程相对于Sql Service 和 Mysql比较复杂，难度较大。在解决实际问题需要根据场景选择响应的存储过程方案。