Mysql 存储过程实例详解

2021-11-21 17:24:54

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，存储和和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型。存储过程再简单点来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。本次博客就来讲一下存储过程

存储过程的操作

语法如下：

创建：

CREATE PROCEDURE sp_name([proc_parameter[,...]])

    [characteristic...] routine_body

proc_parameter:

[IN|OUT|INOUT] param_name type    #type: Any valid MySQL data type

characteristic:

LANGUAGE SQL

|[NOT] DETERMINISTIC|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

修改：

ALTER PROCEDURE sp_name [characteristic...]

characteristic:

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'

调用:

CALL sp_name([parameter[,...]])

删除:

DROP PROCEDURE sp_name

查看:

show PROCEDURE STATUS [like 'pattern']

SHOW CREATE PROCEDURE sp_name

MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交或者回滚，但是存储过程和函数不允许执行LOAD DATA INFILE语句，存储过程和函数可以调用其他的过程或者函数。

插入小知识点@:

.用户变量：以"@"开始，形式为"@变量名"

用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。

.全局变量：定义方式 set GLOBAL 变量名  或者  set @@global.变量名

对所有客户端生效，只有具有super权限才可以设置全局变量。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。

在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。

为什么要使用存储过程

  1. 通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。
  2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
  3. 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

users表如下：

Mysql 存储过程实例详解

创建存储过程，传入性别（男或女），显示对应性别的用户id，返回对应性别的人数(我的是在mysql front中操作)：

#DELIMITER $$

CREATE PROCEDURE user_procedure(IN sex VARCHAR(2) character set utf8,OUT num INT)

BEGIN

    SELECT  id FROM users WHERE gender=sex;

    SELECT FOUND_ROWS() INTO num;

END #$$

#DELIMITER ;

如果大家用的navicat版本，应该改成是：

DELIMITER $$

CREATE PROCEDURE user_procedure(IN sex VARCHAR(2) character set utf8,OUT num INT)

BEGIN

    SELECT  id FROM users WHERE gender=sex;

    SELECT FOUND_ROWS() INTO num;

END $$

DELIMITER ;

上面记得中文字符字段，一定要设置编码：character set utf8，这里自己被坑了好久才觉悟过来...

调用

CALL user_procedure('女',@num);

select @num;

Mysql 存储过程实例详解

定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

语法如下：

条件定义：

DECLARE condition_name CONDITION FOR condition_value

condition_value:

    SQLSTATE [VALUE] sqlstate_value

    |mysql_error_code

条件处理:

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:

    CONTINUE|EXIT|UNDO

condition_value:

     SQLSTATE [VALUE] sqlstate_value

     |condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

继续用users表举个例子吧！
现在有数据如下：

Mysql 存储过程实例详解

（1）当没有进行条件处理的时候：

#delimiter $$

create procedure user_insert()

begin

set @x=1;

insert into users(id,gender,name) values(1,'男','常贵');

set @x=2;

insert into users(gender,name) values('女','大脚');

set @x=3;

END #$$

Mysql 存储过程实例详解

上面的例子可以看出，当插入id=1，主键重复了，直接退出了，并没有执行余下的语句，所以@x的值为1。

Mysql 存储过程实例详解

（2）可以对主键重复进行处理：

#delimiter $$

create procedure user_insert2()

begin

declare continue handler for sqlstate '' set @x2=1;

set @x=1;

insert into users(id,gender,name) values(3,'男','jack');

set @x=2;

insert into users(id,gender,name) values(1,'男','mary');

set @x=3;

end #$$

#delimiter ;

调用call user_insert2();

Mysql 存储过程实例详解

这次在调用存储过程的时候，并没有报错，而是在遇到主键重复的时候，会安装定义的continue去执行，所以继续向下执行。

condition_value的值可以是通过declare定义的condition_name，可以是SQLSTATE的值或者mysql_error_code的值会在是SQLWARNING、NOT FOUND、SQLEXCEPTION，这个3个值是3种定义好的错误类别，分别代表不同的含义：

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记

NOT FOUND是对所有以02开头的SQLSTATE代码的速记

SQLEXCEPTION是对所有没有被SQLWARNING或者NOT FOUND捕获的SQLSTATE代码的速记。

以上的declare continue handler for sqlstate '23000' set @x2=1;也可以用以下几种方式来写：

#捕获mysql-error-code

declare continue handler for 1062 set @x2=1;

#事先定义condition_name

declare duplicatekey condition for sqlstate '23000';

declare continue handler for duplicatekey set @x2=1;

#捕获sqlexception

declare continue handler for sqlexception set @x2=1;

流程控制

mysql支持的流程控制有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHILE语句。

1.IF

语法如下：

IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list]...

    [ELSE statement_list]

END IF

举例：求两个数的最大值

#DELIMITER $$

CREATE PROCEDURE compare(IN n1 INT,IN n2 INT)

BEGIN

    SET @res=0;

    IF n1 > n2 THEN

        SET @res=n1;

    ELSEIF n1 = n2 THEN

        SET @res=n1;

    ELSE

        SET @res=n2;

    END IF;

END #$$

#DELIMITER ;

调用后查询结果如下：

Mysql 存储过程实例详解

2.CASE语句

语法如下：

CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list]...

    [ELSE statement_list]

END CASE

或者：

CASE

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list]...

    [ELSE statement_list]

END CASE

我们将以上例子使用case来实现：

#DELIMITER $$

CREATE PROCEDURE compare2(IN n1 INT,IN n2 INT)

BEGIN

    SET @res=0;

    CASE

        WHEN n1>n2 THEN

            SET @res=n1;

        WHEN n1=n2 THEN

            SET @res=n1;

        ELSE

            SET @res=n2;

    END CASE;

END #$$

#DELIMITER ;

测试：
Mysql 存储过程实例详解

3.LOOP和LEAVE语句

LOOP可以实现简单的循环，通常和LEAVE一起使用,LOOP语法如下：

[begin_label:]LOOP

    statement_list

END LOOP[end_label]

我们还是以users表为例，当前users表按照 id desc 数据如下：

Mysql 存储过程实例详解

使用循环向里面插入100行数据：

#DELIMITER $$

CREATE PROCEDURE userinset()

BEGIN

    SET @x=0;

    ins: LOOP    #标签为ins

        SET @x=@x+1;

        IF @x=100 THEN

            LEAVE ins;   #当@x=100的时候，则退出循环

        END IF;

        INSERT INTO users(name,gender) values('周伯通', '男');

    END LOOP ins;

END #$$

#DELIMITER ;

测试：

call userinset();

select count(1) from users;

Mysql 存储过程实例详解

4.ITERATE语句

必须在循环中使用，作用是跳过当前循环的剩下的语句，直接进入下一轮循环，相当于一些高级语言中的continue。

只向表中插入奇数行：(仍以users为例)：

#delimiter $$

CREATE PROCEDURE inserinfo()

BEGIN

    set @x=1000103;

    ins: LOOP

        SET @x=@x+1;

        IF @x=1000113 THEN

        LEAVE ins;

        ELSEIF mod(@x,2)=0 THEN

        ITERATE ins;

        END IF;

        INSERT INTO users(id,name) VALUES(@x,'乔峰');

    END LOOP ins;

END #$$

#delimiter ;

测试：call inserinfo();

Mysql 存储过程实例详解

5.REPEAT语句

有条件的循环控制语句，当满足条件的时候退出循环，语法如下：

[begin_label:]REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

举例:再在上面例子中插入10行：

#delimiter $$

CREATE PROCEDURE inserinfo2()

BEGIN

    DECLARE x INT DEFAULT 9;

    ins: REPEAT

       SET x=x+1;

       INSERT INTO users(name,gender) VALUES(x,'保密');

    UNTIL x>18 END REPEAT;

END #$$

#delimiter ;

调用:call inserinfo2();查看数据如下

Mysql 存储过程实例详解

6.WHILE语句
WHILE是满足条件才执行。
语法如下： 
[begin_lable:]WHILE search_condition DO

    statement_list

END WHILE [end_label]

以上的例子如果用while来实现如下：

#delimiter $$

CREATE PROCEDURE inserinfo3()

BEGIN

    DECLARE x INT DEFAULT 9;

    ins: WHILE x<=18 DO

       SET x=x+1;

       INSERT INTO users(name,gender) VALUES(x,'女');

    END WHILE;

END #$$

#delimiter ;

测试：call inserinfo3();

Mysql 存储过程实例详解

光标的使用

在存储过程和函数中，可以使用光标对结果进行循环的处理，语法如下：

声明光标:

DECLARE cursor_name CURSOR FOR select_statement

OPEN光标:

OPEN cursor_name

FETCH光标:

FETCH cursor_name INTO var_name[,var_name]...

CLOSE光标:

CLOSE cursor_name

举例：

还是以users表为例，好吧。夜深人静。。不早了，未完待续....

上一篇：mysql运维必会的一些知识点整理


下一篇：【腾讯bugly干货】QQ空间直播秒开优化实践