09.存储过程

创建存储过程的模板代码

delimiter $$
# 下面的 `root`@`%`表示允许任意机器上通过用户root使用本存储过程
CREATE PROCEDURE `usp_getEmployeeAllchildren`(IN inParentid INT)
BEGIN
  SELECT *
  FROM employee WHERE  pid = inParentid;
END;$$

存储过程是否存在

DROP PROCEDURE IF EXISTS usp_WhiteColorDaily;

调用存储过程的方法

call 存储过程名称(参数1，参数2);

带有拼接的存储过程

create definer = root@`%` procedure usp_Rpt_Cash(IN start date, IN end date, IN filter_field varchar(50), IN filter_value varchar(50))
BEGIN

#     SET filter_field = ifnull(filter_field,'');
#     SET filter_value = ifnull(filter_value,'');

    SET @filterSeg =
        CASE WHEN length(filter_field)>0 THEN
            CASE WHEN length(filter_value)>0 THEN
                concat(' and b.',`filter_field`," like '%",ifnull(`filter_value`,''),"%' ")  ELSE '' END
        ELSE '' END;


    SET @SQL = concat(
    "
    SELECT b.iid,b.fid,b.branch,b.inout,b.opposite,
    date_format(b.occ_date,'%Y-%m-%d') as occ_date,
    b.category,
    b.item_name,
    CASE WHEN `inout`='付款' THEN b.amount * -1 ELSE b.amount END AS amount,
    b.amount_type,b.evidence,b.invoice_number,b.remark,
    b.create_time,b.status,
    a.bill_code,
    date_format(a.bill_date,'%Y-%m-%d') as bill_date
    FROM cash_main a LEFT JOIN cash_detail b
    ON a.iid = b.fid
    WHERE DATE_FORMAT(a.bill_date,'%Y-%m-%d')
    BETWEEN","'", `start`, "'", " and ", "'", `end`, "'",

    @filterSeg,

    " ORDER BY b.branch,b.occ_date,b.iid"
    );


  prepare baseStatment from @SQL;
  execute baseStatment;
END;

局部变量的使用

在存储过程中不需要使用 declare 声明局部变量，直接使用 set @openId='sdf' 声明的同时赋值即可。如果要使用 select 进行赋值也不用 declare 声明，像下面代码的第8行即可。

CREATE
    DEFINER = root@`%` PROCEDURE usp_deleteUserAssets(IN vOpenId VARCHAR(300))
BEGIN
    # 2020年12月16日 13:51:52
    # 传入微信用户 openId ，删除该用户的所有数据
    SET @openId=vOpenId;

    SELECT id INTO @id FROM user_weixin WHERE openId=@openId;

    START TRANSACTION;
    DELETE FROM scan_scene WHERE openid = @openId;# 删除场景值
    DELETE FROM user_mill WHERE openid = @openId;# 删除用户和染厂的关联
    DELETE FROM user_convent WHERE user_weixin_id = @id;# 删除用户和染厂客户的关联
    DELETE FROM user_weixin WHERE id = @id;# 删除微信用户
    DELETE FROM sys_userprocess WHERE sys_userperm_id IN
    (
        SELECT id
        FROM sys_userpermission WHERE openid = @openId
    );
    DELETE FROM sys_userpermission WHERE openid = @openId;
    COMMIT;
END;

参数字符集

微信相关逻辑调用存储过程时可能设计字符集问题，需要为参数指定字符集

CREATE PROCEDURE sp_SetStyle(IN style_num VARCHAR(100) CHARACTER SET utf8)