MySQL-存储过程

作者：马育民 • • 阅读：10129

介绍

有些操作，需要 执行多条sql语句，此时就应该使用存储过程

好处

提高代码重用性、简化操作、减少编译次数，减少和数据库连接次数

与函数的区别

  • 函数必须有返回值，而存储过程没有

  • 存储过程的参数可能使用 N、OUT、INOUT 类型，而函数的参数只能是 IN 类型

创建语法

一条语句

create PROCEDURE 存储过程名(过程参数列表)

一条SQL

多条语句

create PROCEDURE 存储过程名(过程参数列表)
BEGIN

多条SQL

END

多条复杂语句

delimiter $$ # 告诉程序使用 $$ 作为结束分隔符

CREATE PROCEDURE 存储过程名(过程参数列表)
BEGIN   
   ......
END

$$ # 表示创建存储过程结束

调用

call 存储过程名(参数)

删除

drop procedure 存储过程名

例子

准备工资

创建测试表：

create table t_test_procedure(

    t_id int 
)

查询该表：

select * from t_test_procedure

例子1

向表里插入一条记录，创建无参数，只有一条SQL的存储过程

创建存储过程，向该表插入1条数据：

create PROCEDURE my_pro1()
insert into t_test_procedure(t_id) values (1);

调用：

call my_pro1()

创建测试表：

create table t_test_procedure(

    t_id int 
)

可以查看到插入的数据

例子2

向表里循环插入10条记录

创建无参数，多条SQL的存储过程

create procedure my_pro2()
BEGIN

DECLARE i int ;
set i = 1;

while i<=10 DO
    insert into t_test_procedure(t_id) values (i);
    set i = i+1;
end while;
END

call my_pro2()

例子3

对第一个例子做优化
第一个例子缺点：只能插入数据1
改为可以插入任意数值

创建有输入参数，1条SQL的存储过程

create procedure my_pro3(IN i int)
    insert into t_test_procedure(t_id) values (i);

调用

call my_pro3(100)

案例

使用存储过程实现转账功能

创建账户表

create table t_account(
    aid int auto_increment primary key,
    aname varchar(20),
    amoney DECIMAL(9,2)

)

初始化数据

insert into t_account (name,money) values ('李雷',1000000)
insert into t_account (name,money) values ('韩梅梅',0)

创建存储过程

create procedure my_pro4(in m DECIMAL(9,2) ,in fromid int,in toid int )
BEGIN
    update t_account set money = money - m where aid = fromid;
    update t_account set money = money + m where aid = toid;

END

调用

call my_pro4(10000,1,2)

bug

李雷的账户只有 100万，如果如果转200万，账户就成负数了，这是不可能的

call my_pro4(2000000,1,2)

修复bug


create procedure my_pro6(in m DECIMAL(9,2) ,in fromid int,in toid int )
BEGIN
    declare from_money DECIMAL(9,2);
    -- 查询转出金额账户的余额，赋值给变量 from_money，使用into关键字
    select money into from_money from t_account where aid = fromid;

    if m > from_money THEN
        select '金额不足';
    else 
        update t_account set money = money - m where aid = fromid;
        update t_account set money = money + m where aid = toid;
        select '转账成功';
    end if;
END

select * from t_account

call my_pro6(1000,1,2)

call my_pro6(1000000,1,2)

缺点

上面例子中，执行失败，显示出结果，但程序不知道，为了方便程序做处理，要获取返回值

解决上面缺点

存储过程没有返回值，通过输出参数实现的返回值

create procedure my_pro7(in m DECIMAL(9,2) ,in fromid int,in toid int ,out code int)
BEGIN
    declare from_money DECIMAL(9,2);
    select money into from_money from t_account where aid = fromid; -- 查询转出金额账户

    if m > from_money THEN
        -- select '金额不足';
        set code = 100; -- 100 表示金额不足
    else 
        update t_account set money = money - m where aid = fromid;
        update t_account set money = money + m where aid = toid;
        -- select '转账成功'; 
        set code = 0; -- 0 表示转账
    end if;
END

调用时，需要传入 会话变量（用户变量），才能获取存储过程往外传的值：

提示： 在存储过程外，只能使用 会话变量（用户变量）

call my_pro7(1000000,1,2,@code);

call my_pro7(1000,1,2,@code)

select @code;

会话变量（用户变量）

一个客户端定义的 会话变量（用户变量） 不能被其他客户端看到或使用，当客户端退出时，该客户端连接的所有变量将自动释放。

具体的说就是一个被赋值的变量，它不受 begin…end 的代码块限制，介入方法形式与局部变量不同

提示：可以在存储过程、函数外使用

声明

不需要声明，可以直接赋值使用

赋值方式一

可以在存储过程外使用

set @var_name = 10

赋值方式二

将查询结果赋值给变量

select max(age) into @ma from student

查询

select @var_name

select @ma

案例

每个月分析各个岗位的最高工资、最低工资、平均工资、工资总和、人数，并存储到表中

创建表

保存分析出来的数据

CREATE TABLE `sal_anal` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `job` VARCHAR(50) NOT NULL DEFAULT '0',
    `max` DECIMAL(7,2) NOT NULL DEFAULT '0',
    `min` DECIMAL(7,2) NOT NULL DEFAULT '0',
    `avg` DECIMAL(7,2) NOT NULL DEFAULT '0',
    `sum` DECIMAL(7,2) NOT NULL DEFAULT '0',
    `count` INT NOT NULL DEFAULT '0',
    `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
;

传统方式

传统方式，需要先统计分析：

SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1)
FROM emp
GROUP BY job;

执行结果如下：

但统计分析后，没法通过一条sql将这些数据保存到表中

创建存储过程

注意：变量名不要与列名相同，否则可能报错，也可能不报错，但执行会丢数据

DROP PROCEDURE if exists pro_sal_anal;

delimiter $$ # 告诉程序使用 $$ 作为结束分隔符
create procedure pro_sal_anal()
begin
   -- 定义变量
    DECLARE s int DEFAULT 0;
    DECLARE vjob VARCHAR(50);
    DECLARE vmax DECIMAL(7,2);
    DECLARE vmin DECIMAL(7,2);
    DECLARE vavg DECIMAL(7,2);
    DECLARE vsum DECIMAL(7,2);
    DECLARE vcount int;

    DECLARE report CURSOR FOR  SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1)
    FROM emp
    GROUP BY job;

    -- 声明当游标遍历完后将标志变量置为某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
    -- 打开游标
    OPEN report;
    -- 将游标中的值赋值给变量，注意：变量名不要与列名相同，变量顺序要和sql结果列的顺序一致
    FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vCOUNT   ;
    WHILE s <> 1 DO  -- 当s等于1时代表遍历已完成，退出循环
    -- 执行业务逻辑
    INSERT INTO sal_anal (sjob,MAX_sal,MIN_sal,AVG_sal,SUM_sal,COUNT_emp) VALUES (vjob,vMAX,vMIN,vAVG,vSUM,vCOUNT) ; 
    -- 将游标中的值再赋值给变量，供下次循环使用
    FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vcount;
    END WHILE;
    -- 关闭游标
    CLOSE report;

END
$$ # 表示创建存储过程结束

调用

CALL pro_sal_anal()

原文出处：/show_1IX64Yj2YCAS.html

吉ICP备15003043号