时间：2021-07-01 10:21:17 帮助过：13人阅读
语法如下：
创建： 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语句，存储过程和函数可以调用其他的过程或者函数。
插入小知识点@:
1.用户变量：以"@"开始，形式为"@变量名" 用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。 2.全局变量：定义方式 set GLOBAL 变量名 或者 set @@global.变量名 对所有客户端生效，只有具有super权限才可以设置全局变量。
现在有表如下：
mysql> select * from student; +-----+--------+----------+--------+ | sid | gender | class_id | sname | +-----+--------+----------+--------+ | 1 | 男 | 1 | 李杰 | | 2 | 女 | 1 | 钢蛋 | | 3 | 男 | 1 | 张三 | | 4 | 男 | 1 | 张一 | | 5 | 女 | 1 | 张二 | | 6 | 男 | 1 | 张四 | | 7 | 女 | 2 | 铁锤 | | 8 | 男 | 2 | 李三 | | 9 | 男 | 2 | 李一 | | 10 | 女 | 2 | 李二 | | 11 | 男 | 2 | 李四 | | 12 | 女 | 3 | 如花 | | 13 | 男 | 3 | 刘三 | | 14 | 男 | 3 | 刘一 | | 15 | 女 | 3 | 刘二 | | 16 | 男 | 3 | 刘四 | | 17 | 男 | 1 | 刘一 | +-----+--------+----------+--------+ 17 rows in set (0.00 sec)
创建存储过程，传入性别（男或女），显示对应性别的学生id，返回对应性别的人数：
DELIMITER $$ CREATE PROCEDURE myprocedure(IN sex CHAR,OUT num INT) BEGIN SELECT sid FROM student WHERE gender=sex; SELECT FOUND_ROWS() INTO num; END $$ DELIMITER ;
调用:
CALL myprocedure(‘女‘,@num)
查看人数@num：
SELECT @num
条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。
语法如下：
条件定义： 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
举个例子吧！
现在有表如下：
mysql> select * from student; +-----+--------+----------+--------+ | sid | gender | class_id | sname | +-----+--------+----------+--------+ | 1 | 男 | 1 | 李杰 | | 2 | 女 | 1 | 钢蛋 | | 3 | 男 | 1 | 张三 | | 4 | 男 | 1 | 张一 | | 5 | 女 | 1 | 张二 | | 6 | 男 | 1 | 张四 | | 7 | 女 | 2 | 铁锤 | | 8 | 男 | 2 | 李三 | | 9 | 男 | 2 | 李一 | | 10 | 女 | 2 | 李二 | | 11 | 男 | 2 | 李四 | | 12 | 女 | 3 | 如花 | | 13 | 男 | 3 | 刘三 | | 14 | 男 | 3 | 刘一 | | 15 | 女 | 3 | 刘二 | | 16 | 男 | 3 | 刘四 | | 17 | 男 | 1 | 刘一 | +-----+--------+----------+--------+ 17 rows in set (0.00 sec)
（1）当没有进行条件处理的时候：
mysql> delimiter $$ mysql> create procedure student_insert() -> begin -> set @x=1; -> insert into student(sid,gender,class_id,sname) values(18,‘男‘,1,‘frank‘); -> set @x=2; -> insert into student(sid,gender,class_id,sname) values(1,‘男‘,1,‘coco‘); -> set @x=3; -> END $$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> call student_insert(); ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘PRIMARY‘ mysql> select @x; +------+ | @x | +------+ | 2 | +------+ 1 row in set (0.00 sec)
从上面的例子可以看出，当插入sid=1，主键重复了，直接退出了，并没有执行余下的语句，所以@x的值为2。
（2）可以对主键重复进行处理：
mysql> delimiter $$ mysql> mysql> mysql> create procedure student_insert() -> begin -> declare continue handler for sqlstate ‘23000‘ set @x2=1; -> set @x=1; -> insert into student(sid,gender,class_id,sname) values(19,‘男‘,1,‘jack‘); -> set @x=2; -> insert into student(sid,gender,class_id,sname) values(1,‘男‘,1,‘bob‘); -> set @x=3; -> end $$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call student_insert; Query OK, 0 rows affected (0.00 sec) mysql> select @x,@x2; +------+------+ | @x | @x2 | +------+------+ | 3 | 1 | +------+------+ 1 row in set (0.00 sec)
这次在调用存储过程的时候，并没有报错，而是在遇到主键重复的时候，会安装定义的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 comp(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> call comp(100,2); Query OK, 0 rows affected (0.00 sec) mysql> select @res; +------+ | @res | +------+ | 100 | +------+ 1 row in set (0.00 sec) mysql> call comp(100,100); Query OK, 0 rows affected (0.00 sec) mysql> select @res; +------+ | @res | +------+ | 100 | +------+ 1 row in set (0.00 sec)
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 comp1(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> call comp1(10,2); Query OK, 0 rows affected (0.00 sec) mysql> select @res; +------+ | @res | +------+ | 10 | +------+ 1 row in set (0.00 sec) mysql> call comp1(100,100); Query OK, 0 rows affected (0.00 sec) mysql> select @res; +------+ | @res | +------+ | 100 | +------+ 1 row in set (0.00 sec) mysql> call comp1(2,11); Query OK, 0 rows affected (0.00 sec) mysql> select @res; +------+ | @res | +------+ | 11 | +------+ 1 row in set (0.00 sec)
3.LOOP和LEAVE语句
LOOP可以实现简单的循环，通常和LEAVE一起使用,LOOP语法如下：
[begin_label:]LOOP statement_list END LOOP[end_label]
现在有表如下：
mysql> select * from userinfo; +----+--------+--------+ | id | uname | passwd | +----+--------+--------+ | 1 | alex | 123 | | 2 | frank | 123 | | 3 | rose | 312 | | 4 | tom | qqq | | 5 | jack | qwer | | 6 | coco | 123 | | 7 | lancer | 123 | +----+--------+--------+ 7 rows in set (0.00 sec)
使用循环向里面插入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 userinfo(uname,passwd) values(‘test‘,‘123‘); END LOOP ins; END $$ DELIMITER ;
测试：
mysql> call userinset(); Query OK, 0 rows affected (0.17 sec) mysql> select count(1) from userinfo; +----------+ | count(1) | +----------+ | 106 | +----------+ 1 row in set (0.00 sec)
行数增加到了106行，表示成功。
4.ITERATE语句
必须在循环中使用，作用是跳过当前循环的剩下的语句，直接进入下一轮循环，相当于一些高级语言中的continue。
现在有表如下：
mysql> desc info; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from info; Empty set (0.00 sec)
只向表中插入奇数行：
delimiter $$ CREATE PROCEDURE inserinfo() BEGIN set @x=0; ins: LOOP SET @x=@x+1; IF @x=10 THEN LEAVE ins; ELSEIF mod(@x,2)=0 THEN ITERATE ins; END IF; INSERT INTO info(id,name) VALUES(@x,‘test‘); END LOOP ins; END $$ delimiter ;
测试：
mysql> call inserinfo(); Query OK, 0 rows affected (0.01 sec) mysql> select * from info; +------+------+ | id | name | +------+------+ | 1 | test | | 3 | test | | 5 | test | | 7 | test | | 9 | test | +------+------+ 5 rows in set (0.00 sec)
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 info(id,name) VALUES(x,‘test‘); UNTIL x>18 END REPEAT; END $$ delimiter ;
测试：
mysql> call inserinfo2(); Query OK, 1 row affected (0.03 sec) mysql> select count(1) from info; +----------+ | count(1) | +----------+ | 15 | +----------+ 1 row in set (0.00 sec)6.WHILE语句 WHILE是满足条件才执行。 语法如下：
[begin_lable:]WHILE search_condition DO statement_list END WHILE [end_label]
以上的例子如果用while来实现如下：
delimiter $$ CREATE PROCEDURE inserinfo2() BEGIN DECLARE x INT DEFAULT