MYSQL存储过程的用法

存储过程

存储过程（PROCEDURE）是多表SQL的语句的集合，可以独立执行。

存储过程的优势：

存储过程把经常使用的SQL语句或业务逻辑封装起来，预编译保存在数据库中。当需要时可以从数据库中直接调用，省去了编译的过程，提高了运行速度，同时降低网络数据传输量。

存储过程与自定义函数的区别：

1. 存储过程实现的过程要复杂一些，而函数的针对性较强。
2. 存储过程可以有多个返回值，而自定义函数只有一个返回值。
3. 存储过程一般可独立执行，而函数往往是作为其他SQL语句的一部分来使用。
4. 无参数的存储过程执行过程中可以不加()，函数必须加()。

MYSQL创建存储过程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出

param_name表示参数名称

type表示参数的类型

查看存储过程列表

SHOW PROCEDURE STATUS;

查看存储过程定义

SHOW CREATE PROCEDURE sp_name;

调用存储过程

CALL sp_name ([ proc_parameter [,proc_parameter ...]]);
#无参数时,可以省略"()"。有参数时,不可省略"()"。

存储过程修改

ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西，不能修改存储过程体，所以要修改存储过程，方法就是删除重建。

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

范例1：创建无参存储过程

delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;

CALL showTime;

范例2：创建含参存储过程，只有一个IN参数

delimiter //
CREATE PROCEDURE selectById(IN suid SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE id = suid;
END//
delimiter ;

CALL selectById(2);

范例3：计算1+2+3..+n的值

#普通变量只在当前会话中有效，可用@var_name引用
delimiter //
CREATE PROCEDURE dorepeat(n INT)
BEGIN
SET @i = 0;
SET @sum = 0;
REPEAT SET @sum = @sum+@i;
SET @i = @i + 1;
UNTIL @i > n END REPEAT;
END//
delimiter ;

CALL dorepeat(100);
SELECT @sum;

范例4: 创建含参存储过程,包含IN参数和OUT参数

#row_count() 是系统内置函数，存放前一条SQL语句修改过的表的记录数。
delimiter //
CREATE PROCEDURE deleteById(IN suid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE id >= suid;
SELECT row_count() into num;
END//
delimiter ;

CALL deleteById(20,@Line);
SELECT @Line;

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。

• IF：用来进行条件判断。根据是否满足条件，执行不同语句。
• CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断。
• LOOP：重复执行特定的语句，实现一个简单的循环。
• LEAVE：用于跳出循环控制，相当于SHELL中break。
• ITERATE：跳出本次循环，然后直接进入下一次循环，相当于SHELL中continue。
• REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。
• WHILE：有条件控制的循环语句。