存储过程篇2--深入了解

今天药忘吃喽~ 2022-05-20 08:37 169阅读 0赞

继上篇了解完存储过程基本结构后，本次我们继续讨论存储过程的深度用法。

本次讨论存储过程是建立在Oracle环境下，其他SQL语言格式大体相同，遇到不一样的百度下就可以了，其他SQL这里就不做具体分析。

上篇我学习到了存储过程基本结构，回顾下：

create or replace procedure A (参数b int类型) as(后边接变量，也可以不接) v\_C int；（设定一个int

类型的参数v\_C）

begin（执行体开始）

需执行条件；

end；

以上是存储过程本体；

我们要执行存储过程的语句是：

begin

procedure A；

end；

这是上次学习的基本知识，我们提到了参数，和变量，两个概念，

这次我们就从参数开始讨论，参数是我们定义的一个实际数据的容器。那么我们怎么样才能接受数据呢，接受数据的方式有多少种？

一般我们传入参数值（后文简称传参）的方式有三种；in  /  out  /  in out

这三个分别代表：只读  //  内部赋值   // 只读 内部赋值 可传出

这里大家可能很难理解什么叫只读，什么叫内部赋值等，我以画图说明：

![70][]

这里我在定义参数 create or replace procedure XX (A IN INT,D OUT VARCHAR,C IN OUT VARCHAR) AS B VARCHAR

我创建了存储过程XX，定义其参数A为in类型，参数D为out类型，C为in out 类型 执行提定义变量为B；

那么in类型为只读，我在运行存储过程，begin procedure xx（100，101，102),in可以把值传给执行体中进行计算，

但是执行题B变量值无法反馈到参数A，这就是in的只读，只能直接读取A值进行传入，不能让执行体内的变量进行计算输出到A；

out则是属于D参数传入值到执行体B，输出B值为 102，可以返还给到D参数，从而改变使得D参数=102，而不是我们给的101；而in out 则可以看作in和out方式的集合。系统在设定参数不加传参模式的情况下默认是in 类型，这点一定要注意；sql 的赋值写法：B := A，意为把A值赋值给B；

总结下来：

IN （参数值传过来给存储过程使用）

OUT（存储过程返回结果给该参数）

IN OUT（存储过程调用该参数的同时，最后还返回结果给调用的参数）

为什么要了解参数，因为存储过程的外部执行条件是通过参数接进来的，之后在给执行体计算，（不是变量接进存储过程体内）；存储过程体内的计算则可以去选择调用传入参数值。和变量的区别是，变量是存储过程内部定义的一个可变参数，在存储过程体内可以使用。

说完了参数及其传入模式，我们可以系统的总结下，写了一个存储过程，定义好参数后，我在执行存储过程procedure A(B,C,D)时，我给的数据B C D进入到存储过程参数中，基于传递参数类型判定是否可以给到存储过程计算体能进行计算和返回值。

一般使用情况分析：

第一种（无参）：也就是无参数传递，此种一般用于固定数据的处理，比如一张表，或者一个查询语句等。

第二种（in参）：由于对于存储过程来说不写参数模式，默认为IN，此种一般处理给定的特定条件的数据处理。

第三种（in out参）：此种是同一个参，具有两种形式，IN和OUT，这种方式我一般不使用，因为比较容易搞混，造成输出值不是自己想要的，一般使用第四种。

第四种（out参）：此种是一个参数一种参数模式，能够比较清晰的处理代码。

当然，使用存储过程的时候我们大多是对固定的数据或者给定的特定条件的数据进行处理（例如：插入，更新，删除等），并且存储过程是放入包中去使用的，而且一般不会有返回值。

接下来一起讨论下游标cursor；

定义：Cursor就是当我们在数据库一块未命名的存储数据的工作空间进行多行查询的时候对其进行命名。当你对一块工作空间命名之后，你就可以获取并处理这些查询的数据

简单的理解就是按条件筛选出一个值的临时表，比如我select \* from t\_temp where ext 1=1；

我就筛选出了temp表中ext1字段值=1的数据集合成了一个临时表；而游标的作用就是把这个临时表的每个值都给你取出来让你使用一次（不会漏掉任何一个）；游标主要用作循环语句中，让起把一张表的数据都跑一次；

cursor C\_temp is        \--定义cursor  
select \* from t\_temp where ext1=1;          \--将数据空间指向C\_temp  
begin  
  for I inC\_temploop

这里我们定义了个C\_temp游标，它代表了ext1=1的临时表；然后我循环语句中用一个I 参数去获取这个临时表中的每一条数据各一次。（各一次是游标一个常采用的特点，当然也能多游历几次，只是一般使用都是用来遍历数据一次而已）

最后在存储过程中我们常用的就是控制语句了，

if/case when /loop /for

if 和case when就不做过多说明，下次我们在继续学习loop 循环和for循环；

学完循环后，我们总结起来用常用存储过程的玩法结构是：

创建一个存储过程    定义参数

定义一个变量

定义一个游标

执行体开始执行

先确定我要使用的条件是在那个范围，用循环带入游标去获取范围的每一个值；

把每个值给到变量，让变量去参与计算；

得出并输出结果，或者是执行insert / update等等修改语句；

结束存储过程执行。

这里我要强调下的是：参数 变量 游标 等等不是存储过程必要条件，只是让存储过程变得更加实用的一些方法，请勿在没有需求的情况下随意乱加参数、变量等等，这是不正确的，条件要按需求添加。

下面我举个实际应用存储过程的例子；

故事背景是nike订单要开发票，但是公司内部做了业务逻辑变更把发票开票表换成了一张新表，一些历史数据还未开票并且在新表中没有数据，为了使得这些迁移前的历史数据要开出发票，最快的方法就是把数据复制到新表中，由于两表结构又有所不同，我们只能插入相同的数据，不同的数据进行了默认值设定，整个存储过程如下：

CREATE OR REPLACE PROCEDURE

SP\_BLUE\_NIKE\_INVOICE AS --创建存储过程，由于不需要从外部获取参数，我们不定义参数  
  v\_tax\_id     NUMBER(19);--创建一个变量  
  CURSOR CUR\_INVOICE IS--创建一个游标，范围是老的历史数据中还没有开出发票的数据集合  
    select ID  
    from t\_Td\_Invoice  
   where shop\_code in ('GFGlobal', 'GF-GlobalSwoosh'）  
     and is\_reversed = 1  
     and rb\_type = 0;

BEGIN  
  OPEN CUR\_INVOICE; --打开游标  
  LOOP--进行loop循环，循环范围是把游标的值赋给变量，如果找不到值就跳出循环  
    FETCH CUR\_INVOICE  
      INTO v\_tax\_id;--游标赋值给变量  
    EXIT WHEN CUR\_INVOICE%NOTFOUND;--跳出游标搜索

\--1.已经找到循环要做的事情和范围了，接下来我们就insert老的值到新表中  
    insert into t\_Td\_Invoice  
      (ID,  
       version,  
       create\_time,  
       code,  
       status,  
       shop\_code,  
       type,  
       rb\_type,  
       tax\_payer\_id,  
       tax\_payer\_name,  
       tax\_payer\_addr\_tel,  
       buyer\_name,  
       mobile,  
       drawer,  
       total,  
       total\_af\_tax,  
       tax\_amt,  
       slip\_code,  
       root\_so\_code,  
       pf\_order\_code,  
       is\_reversed,  
       remark,  
       invoice\_supplier,  
       is\_split,  
       tax\_payer\_bank\_count,  
       company\_tax\_payer\_id,  
       output\_count,  
       nike\_remark,  
       business\_type)  
      select S\_T\_TD\_INVOICE.nextval,  
             77,--设定默认值  
             t.create\_time,--插入老值（后边值可以自行判断是默认还是复制的）  
             t.code,  
             1,  
             t.shop\_code,  
             t.type,  
             '0',  
             t.tax\_payer\_id,  
             t.tax\_payer\_name,  
             t.tax\_payer\_addr\_tel,  
             t.buyer\_name,  
             t.mobile,  
             t.drawer,  
             t.total,  
             t.total\_af\_tax,  
             t.tax\_amt,  
             t.slip\_code,  
             t.root\_so\_code,  
             t.pf\_order\_code,  
             0,  
             t.remark,  
             t.invoice\_supplier,  
             '0',  
             t.tax\_payer\_bank\_count,  
             t.company\_tax\_payer\_id,  
             '99',  
             t.nike\_remark,  
             t.business\_type  
        from t\_Td\_Invoice t  
       where t.id = v\_tax\_id;  
  END LOOP;--跳出循环  
  CLOSE CUR\_INVOICE;--关闭游标  
END;--结束存储过程

[70]: /images/20220520/f3ef7e77e4ad4aad9f52805d1fa6a5e0.png