MySQL存储过程

最新推荐文章于 2022-01-01 22:59:01 发布

go2shell

最新推荐文章于 2022-01-01 22:59:01 发布

阅读量924

点赞数

分类专栏： DB 文章标签： mysql procedure 存储过程

本文链接：https://blog.csdn.net/we_shell/article/details/38622543

版权

DB 专栏收录该内容

4 篇文章

订阅专栏

1. 创建 PROCEDURE

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name (parameters)
BEGIN
[statement_list]
END

(1) 存储过程注释:MySQL支持采用--或者/**/注释，其中前者是行注释，后者是段式注释。
(2) 变量首先用declare申明，其中临时变量可以直接以@前缀修饰以供引用。
(3) MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
(4) 存储过程根据需要可能会有输入、输出、输入输出参数，每个参数默认都是一个IN参数，要指定为其它参数，可在参数名之前使用关键词OUT或INOUT，如果有多个参数用","分割开。
(5) 过程体的开始与结束使用BEGIN与END进行标识。

1.1 BEGIN ... END（复合语句）

BEGIN
[statement_list]
END

a. 存储子程序可以使用BEGIN ... END复合语句来包含多个语句。
b. statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

1.2 DECLARE语句（用来声明局部变量）

DECLARE var_name[,...] type [DEFAULT value]

a. DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
b. 要给变量提供一个默认值，请包含一个DEFAULT子句。如果没有DEFAULT子句，初始值为NULL。
c. 值可以被指定为一个表达式，不需要为一个常数。

1.3 SET语句（变量赋值）

SET var_name = expr [, var_name = expr]

1.4 SELECT ... INTO语句（把选定的列直接存储到变量）

SELECT col_name[,...] INTO var_name[,...] table_expr

a. SELECT语法只有单一的行可以被取回。
b. 用户变量名一般以@开头。
c. 滥用用户变量会导致程序难以理解及管理。
d. SQL变量名不能和列名一样。

例：

SELECT id, name INTO x, y FROM demo LIMIT 1;

1.5 MySQL 存储过程参数类型（in、out、inout）

MySQL 存储过程 in 参数，把数据传给存储过程，存储过程内部可能会修改此参数，但对in类型参数的修改，对调用者来说是不可见的。
MySQL 存储过程 out 参数，从存储过程内部传值给调用者。在存储过程内部，该参数初始值为null，无论调用者是否给存储过程参数设置值。
MySQL 存储过程 inout 参数跟 out 类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过 inout 参数传递值给存储过程。

a. 如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；
b. 如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；
c. 如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。

1.6 if语句

if var = 0 then
    update demo set s1 = s1+1;
else
    update demo set s1 = s1+2;
end if;

1.7 case语句

case var
    when 0 then
        update demo set s1 = s1+1;
    when 1 then
        update demo set s1 = s1+2;
    else
        update demo set s1 = s1+3;
end case;

1.8 while、repeat、loop

while在执行前进行检查条件

    declare var int;
    set var = 0;
    while var < 6 do
        insert into demo values(var);
        set var = var + 1;
    end while;

repeat在执行操作后检查条件

    declare var int;
    set var = 0;
    repeat
        insert into demo values(var);
        set var = var + 1;
    until var >= 5
    end repeat;

loop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

    declare var int;
    set var = 0;
    LOOP_LABLE: loop
        insert into demo values(var);
        set var = var + 1;
        if var >=5 then
            leave LOOP_LABLE;
        end if;
    end loop;

2. 查看、删除、调用存储过程

（1）删除存储过程
DROP PROCEDURE sp_name;
在一个存储过程中不能删除另一个存储过程，只能调用另一个存储过程

（2）显示数据库中所有存储的存储过程基本信息
SHOW PROCEDURE STATUS;

（3）显示某一个存储过程的详细信息
SHOW CREATE PROCEDURE sp_name;

（4）调用存储过程
call sp_name();
存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

3. 使用游标

3.1 创建游标

游标用declare语句创建。如下面的例子所示:

create procedure test()
begin
    declare cursorTest cursor for select * from demo;
end;

3.2 打开和关闭游标

游标用OPEN CURSOR语句来打开，用CLOSE CURSOR来关闭

open cursorTest;
close cursorTest;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。在一个游标关闭后，如果没有重新打开，则不能使用它。但是，声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

3.3 使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH用来检索当前行的IntersectionName列到您定义的局部变量中。

BEGIN
    DECLARE current_id INT(10);                        -- 声明一个局部变量
    DECLARE cursorTest CURSOR FOR select id from demo; -- 声明一个游标
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
    OPEN cursorTest;                       -- 打开游标
        FETCH cursorTest INTO current_id;  -- 获取IntersectionName
        REPEAT                             -- 遍历所有的行
        UPDATE demo SET s1 = s1+1 WHERE id = current_id;
        FETCH cursorTest INTO current_id;  -- 获取IntersectionName
        UNTIL Done END REPEAT;             -- 结束循环
    CLOSE cursorTest;                  -- 关闭游标
END;

Done默认是0，CONTINUE HANDLER 是在条件出现时被执行的代码，SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

4. 例子

（1）表结构

 create table `record`(
    `id` int(10) not null primary key auto_increment,
    `content` varchar(255),
    `created` datetime not null,
    `updated` datetime not null default '0000-00-00 00:00'
);

(2)根据参数往record表插入N条数据

DELIMITER $$
DROP PROCEDURE IF EXISTS `record_batch` $$
CREATE PROCEDURE `record_batch`(IN record_count int(10))
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < record_count DO
INSERT INTO `record` (`content`,`created`,`updated`) VALUES ('1234567',Now(),Now());
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;

CALL record_batch(10);

(3)修改content列的值

DELIMITER $$
DROP PROCEDURE IF EXISTS `change_record_batch` $$
CREATE PROCEDURE `change_record_batch`()
BEGIN
DECLARE current_record_id INT(10);
DECLARE current_content VARCHAR(255);
DECLARE Done INT DEFAULT 0;
DECLARE res_record CURSOR FOR SELECT record_id, content FROM `record`;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

OPEN res_record;
FETCH res_record INTO current_record_id, current_content;
REPEAT
UPDATE `record` SET content = CONCAT('hello, ', current_content) WHERE record_id = current_record_id;
FETCH res_record INTO current_record_id, current_content;
UNTIL Done END REPEAT;
CLOSE res_record;
END$$
DELIMITER ;

CALL change_record_batch();