Part1 存储过程

存储过程是什么

存储过程是一系列的sql语句，目的是像函数一样返回一个结果。既然是可以看作为一个函数来使用，也就意味着这个函数能够满足某个功能，该功能需要输入参数，而不需要给你一个超长的select语句让你改数字。

好处：简单、安全、高性能

其他细节：对存储过程进行编写和访问执行是分开来的

 

执行存储过程

执行比较简单，用call + 存储过程名称就可以，还需要提供参数，前面记得加@

call productpricing(@pricelow,@pricehigh,@priceaverage); --productpricing是创建的一个存储过程的名称

 

创建、删除存储过程

关键词是create procedure + 存储过程名称，然后begin开始end结束，中间是希望这个“函数”要做的事情

删除操作不需要加括号，其他没什么要注意的

-- 创建存储过程
create procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end;

-- 删除存储过程
drop procedure productpricing;

这里注意，上面的这个创建不返回结果，是无效的，所以我们接下去看下如何将其变得更加完整。

 

含参数的存储过程

下面代码中创建了一个包含参数的存储过程，其中：

  1. ordertotal中含3个参数，前两个用IN是定义来两个表示存储过程要用到的变量，后一个OUT是定义了一个作为输出使用的变量ototal，格式是：[IN/OUT] 变量名 类型名称
  2. comment不是必要的语句，只是在你使用show procedure status查看所有存储过程时可以看到针对这个存储过程的注释
  3. 定义变量用declare，后面格式同1
  4. 在用select得到结果好，需要将结果INTO，即赋值到已经定义好数据类型的变量中，注意封号的使用
  5. IF语句用法较简单，IF xxx THEN xxx END IF，或IF xxx ELSE XXX END IF，或者 IF xxx ELSEIF XXX THEN xxx END IF，注意封号的使用
create procedure ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) comment 'obtain order total, alternatively whether taxable'
begin
    -- 定义变量，decimal中8是数字总长度，2是小数部分长度
    declare total DECIMAL(8,2);
    declare taxrate INT DEFAULT 6;

    -- get the order total
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    INTO total;

    -- is this taxable?
    IF taxable THEN
        select total+total*taxrate/100 INTO total;
    END IF;

    select total INTO ototal;
end;

既然创建好存储过程了，我们来看看如何执行：

call ordertotal(2005,0,@total1);
select @total1;

cal ordertotal(2333,1,@total2);
select @total2

可以看到，2005对应onumber参数，0对应taxable参数，这里想着重提一下@total。你会发现我这里用的是@total，而我参数定义用的是ototal，那我可以定义为@ototal吗？其实没问题，但别忘加@即可。另外需要注意的是，当你执行call语句后，由于创建的存储过程有一个输出，而这个total输出是赋值到了@total这个变量中，所以不会返回值。如果要返回值，必须再加一个select将结果以查询的方式显示出来。

 

 

 Part2 使用游标

游标是什么

在我看来，游标是对select查询到的结果再做细分处理。具体来说，游标可以对select查询出来的全部结果（也称：结果集）中选择性地选择行数据。之所以游标和存储过程写在一块，是因为在MySQL中，游标只能在存储过程中使用。

使用步骤

  1. 先定义游标变量，以及要对什么select语句对查询结果下手
  2. 打开游标，准备使用
  3. 对含有数据对游标，按需求检索出需要的行数据（用循环）
  4. 结束使用后关闭游标

我们来看下伪代码：

-- 第一步，declare 变量名 为 cursor for 某个select查询结果
declare mycursor cursor for 
select xxx from xxxtable;

-- 第二步，打开这个叫mycursor的游标开始使用
open mycursor

--第三步，使用
因情况而不同

-- 第四步，关闭游标
close mycursor;

这基本就是游标的使用方法，接下去来看一个进阶的版本，其中用到了循环和条件语句

 

一个实例

首先我们需要这个存储过程：ordertotal，也就是上面讲存储过程的最后一个例子。ordertotal的输入是订单号，是否要税，以及将输出的结果赋值到一个变量。

create procedure ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) comment 'obtain order total, alternatively whether taxable'
begin
    -- 定义变量，decimal中8是数字总长度，2是小数部分长度
    declare total DECIMAL(8,2);
    declare taxrate INT DEFAULT 6;

    -- get the order total
    select sum(item_price*quantity)
    from orderitems
    where order_num = onumber
    INTO total;

    -- is this taxable?
    IF taxable THEN
        select total+total*taxrate/100 INTO total;
    END IF;

    select total INTO ototal;
end;

那接下来如何将游标和存储过程一起使用呢，我们来设想这样一个情况：我们需要将所有订单，根据订单号所对应的货品总价加税后形成一张税后货价表，我们这么来做：

create procedure processorders()
begin
    -- 定义需要的变量
    declare done boolean default 0;
    declare o int;
    declare t decimal(8,2);
    
    -- 定义一个游标
    declare ordernumbers cursor
    for
    select ordernumber from orders;

    -- 这行很重要，是说只有sql状态码是02000，指的是未找到。当未找到时，将原本默认done为0的数据赋值为1
    declare continue handler for sqlstate '02000' set done=1;

    -- 新建一个表，等会数据结果都会写入这个新表
    create table if not exists ordertotals
    (order_num int, total_price decimal(8,2));

    open ordernumbers;
    
    repeat
        -- 获取指针指着的行对应的货品id
        fetch ordernumber into o;
        -- 调用名为ordertotal的存储过程返回税后价格，并存入变量t
        call ordertotal(o,1,t);
        -- 讲新数据添加入新表ordertotals
        insert into ordertotals (order_num,total_price)
        values(o,t);
    until done end repeat;
    -- 上面这句的意思是until done为1，即True时才退出循环

    close ordernumbers;
end;

其他一些玩意：关于循环，除了repeat xxx until [condition] end repeat，还可以这样写：while [condition] do xxx end while