Mysql 存储过程

DELIMITER 定义 sql 语句执行符号，系统默认是分号，系统看见分号就执行，使用 DELIMITER 可以修改

创建存储过程

DELIMITER $$
CREATE PROCEDURE proceduce_name([in|out|inout arg0 dataType], [in|out|inout arg1 dataType]...)
BEGIN
    # SQL
END$$
DELIMITER ;

in 表示输入参数；out 表示输出参数；inout 表示即可输入也可输出

创建变量

格式：DECLARE 变量名 变量类型 DEFAULT 默认值;

示例1（声明一个变量）：DECLARE score_totle INTEGER DEFAULT 0;

示例2（两个变量）：DECLARE score_totle,score_avg INTEGER DEFAULT 0;

为变量赋值

直接修改：SET score_totle = 10;

把查询结果放进变量：SELECT AVG(score) INTO score_avg FROM students;

流程控制

IF 表达式 THEN
    -- 表达式为真执行
    SQL
ELSE
    -- 表达式为假执行
    SQL
END IF;

CASE 变量或表达式
    WHEN 值1 THEN SQL
    WHEN 值2 THEN SQL
    ELSE SQL
END CASE;

IF (表达式, t, f)

上面第一种类似 java 中的 if else 流程控制，第二种类似 switch case，第三种类似三元运算

操作存储过程

查看所有库中的存储过程：SHOW PROCEDURE STATUS;

查看指定库中的存储过程：SHOW PROCEDURE STATUS WHERE db = 'data_name';

查看存储过程 sql：SHOW CREATE PROCEDURE procudure_name;

删除：DROP PROCEDURE procedure_nam;

调用：CALL proceduce_name(arg0, arg1...);

示例1（in）：

-- 创建存储过程，输入 id 和 name 进行查询
DELIMITER $$
CREATE PROCEDURE getInfoByIdName(IN stuId INTEGER, IN stuName VARVHAR(255))
BEGIN
    SELECT * FROM students WHERE stu_id = stuId AND stu_name = stuName;
END$$
DELIMITER ;
-- 调用
CALL getInfoByIdName(1001, 'Marry');

示例2（out）：

-- 创建存储过程，输入 id 返回 对应的 name
DELIMITER $$
CREATE PROCEDURE getNameById(IN stuId INTEGER, OUT stuName VARCHAR(255))
BEGIN
    -- 表示把查询结果放入变量 stuName 中
    SELECT stuName INTO stuName FROM students WHERE stu_id = stuId;
END$$
DELIMITER ;
-- 调用，调用完成后，把值放进变量 stuName 里
CALL getNameById(1001, @stuName);
-- 查询变量
SELECT @stuName;

示例3（相对复杂点的，以前项目上写的存储过程）：

-- 查询应用账户
DELIMITER $$
CREATE PROCEDURE searchAppAccount(in appAccountName VARCHAR(50), 
in multipleId VARCHAR(50),
in idType INTEGER,
in concat VARCHAR(50),
in concatType INTEGER,
in startDate VARCHAR(50),
in endDate VARCHAR(50),
in balControl INTEGER,
in currentPage INTEGER,
in pageSize INTEGER
)
BEGIN

        -- 查询应用账户
        SELECT * FROM app_account
        WHERE 1=1 and
        -- 应用账户名
        if (appAccountName is null, 1=1, app_account_name like concat('%',appAccountName,'%'))
        -- 时间段
        and if (startDate is null, 1=1, create_date > str_to_date(startDate,'%Y-%m-%d %H:%i:%s'))
        and if (endDate is null, 1=1, create_date < str_to_date(endDate, '%Y-%m-%d %H:%i:%s'))
        -- 余额控制
        and if (balControl is null, 1=1, bal_control = balControl)
        -- id
        and if (multipleId is null, 1=1, 
            if(idType = 0, (
                cus_account_id in (
                    select cus_account_id from app_appid where app_id = multipleId
                )
            ), null)
            or if(idType = 20, (
                cus_account_id in (
                    multipleId
                )
            ), null)
            or if(idType = 30, (
                cus_account_id in (
                    select cus_account_id from main_account where ope_sub_id = multipleId
                )
            ), null)
            or if(idType = 10, (
                app_account_id in(
                    multipleId
                )
            ), null)
            or if(idType = 0, (
                app_account_id in(
                    select app_account_id from app_appid where app_id = multipleId
                )
            ), null)    

        )
        -- 联系方式
        and if (concat is null, 1=1,
            if(concatType = 10, (
                cus_account_id in(
                    -- 主体用户
                    select cus_account_id from main_account where ope_sub_id in (
                        select for_id from user_info where email = concat or tel = concat
                    )
                    union
                    -- 主账户用户
                    select for_id from user_info where email = concat or tel = concat
                )
            ), null)
            or if(concatType = 20, (
                cus_account_id in(
                    select cus_account_id from main_account where email = concat or concat_tel = concat
                )
            ), null)
            or if(concatType = 30, (
                cus_account_id in(
                    select cus_account_id from main_account where ope_sub_id in (
                        select operation_id from operation_subject where email = concat or concat_tel = concat
                    )
                )
            ), null)
            or if(concatType = 10, (
                app_account_id in(
                    -- user 联系方式 ---- 应用账户用户
                    if(concatType = 10, (
                        select for_id from user_info where email = concat or tel = concat 
                    ), 1=0)
                )
            ), null)
            
        )
        order by create_date asc
        limit currentPage, pageSize;
END $$
DELIMITER ;