MYSQL 存储过程
参考:https://www.cnblogs.com/mark-chan/p/5384139.html
参考:https://www.runoob.com/w3cnote/mysql-stored-procedure.html
参考:https://blog.csdn.net/Servlet905/article/details/73087819
一、定义
MySQL 5.0 版本开始支持存储过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
二、优点
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程保存在数据库,而不是本地,可以共享,方便协作。
- 存储过程因为SQL语句已经预编绎过了,因此运行的速度比较快。
- 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
- 使用了存过程,很多相似性的删除,更新,新增等操作就变得轻松了,并且以后也便于管理。
- 存储过程运行比较稳定,不会有太多的错误。只要一次成功,以后都会按这个程序运行。
- 存储过程主要是在服务器上运行,减少对客户机的压力。
- 存储过程可以包含程序流、逻辑以及对数据库的查询。
- 存储过程可以在单个存储过程中执行一系列SQL语句。存储过程可以从自己的存储过程内引用其它存储过程,这可以简化一系列复杂语句。
三、创建
CREATE DEFINER = CURRENT_USER PROCEDURE `sp_name`() BEGIN #Routine body goes here... END;
CURRENT_USER:比如我的账号名是:“sql” .那么 CURRENT_USER就是:`sql`@`%`;如果账号名是“root”,
那么 CURRENT_USER就是:`root`@`%`
sp_name:存储过程的名称。
(): 里面需要书写参数,输入、输出参数或者输入输出参数。
比如:
CREATE DEFINER=`sql`@`%` PROCEDURE `演示`(IN `start_time` date,OUT `end_time` date, INOUT `day` INT) BEGIN #Routine body goes here... END
IN `start_time` date: IN:输入类型(OUT:输出类型,INOUT输入输出类型);`start_time`:参数 date:参数类型
四、调用
call sp_name()
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递
五、删除存储过程
DROP PROCEDURE sp_name
六、更改存储过程
有专门的语句。但是我更喜欢进入到sql存储过程中更改。
七、查询
SHOW PROCEDURE STATUS WHERE db='db_name' -- db_name 数据库名
八、定义变量与IN、OUT、INOUT
搞清楚DECLARE;SELECT;SET就行了。
使用SET或者SELECT直接赋值,变量名以@开头
可以在会话的任何地方声明,作用域是整个会话,称为会话变量,也是全局变量。
以DECLARE关键字声明的变量,只能在存储过程中使用,称为存储过程变量。 这种变量需要设置变量类型,而且只存在 begin....end 这段内
两者区别:
在调用存储过程时,DECLARE声明的变量都会别初始化为null,而会话变量(以@开头的变量)则不会再被初始化,在一个会话内只被初始化一次,之后是对上一次会话的计算结果,相当于这个会话的全局变量。
下面是四个同样结果的存储过程:
8.1 DECLARE
CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle`() BEGIN declare begin_date date ; declare end_date date ; set end_date = DATE_FORMAT('2020-06-10','%Y-%m-%d'); set begin_date = date_sub(end_date,interval 10 day); while begin_date <= end_date do insert into text.`循环测试` values (begin_date); set begin_date = date_add(begin_date,interval 1 day); end while; END
8.2 SET
CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle_set`() BEGIN set @end_date = DATE_FORMAT('2020-06-11','%Y-%m-%d'); set @begin_date = date_sub(@end_date,interval 10 day); while @begin_date <= @end_date do insert into text.`循环测试` values (@begin_date); set @begin_date = date_add(@begin_date,interval 1 day); end while; END
8.3 SELECT
注意select赋值方式由两种。
CREATE DEFINER=`sql`@`%` PROCEDURE `date_circle_select`() BEGIN SELECT DATE_FORMAT('2020-06-10','%Y-%m-%d') INTO @end_date; SELECT date_sub(@end_date,interval 10 day) INTO @begin_date; SELECT count(*) into @cout_row from text.`循环测试`; -- 第一种从表格取数的方法,不会在调用时进行输出 while @begin_date <= @end_date do insert into text.`循环测试` values (@begin_date); set @begin_date = date_add(@begin_date,interval 1 day); end while; SELECT @cout_row:= count(*) from text.`循环测试`; -- 第二种从表格取数的方法,会在调用时进行输出 END
8.4 说明
个人比较喜欢declare方式,但是大部分情况下是混用。
8.5 IN、OUT、 INOUT
IN为输入,OUT为输出、INOUT为输入输出。
下面没有INOUT的例子。这里写一个INOUT的例子。主要在调用的时候。
8.5.1 存储过程创建
CREATE DEFINER=`sql`@`%` PROCEDURE `example_inout`( INOUT num int ) begin set num=num+1; end
8.5.1 结果输出
SET @num=100; CALL example_inout(@num); SELECT @num; -- 输出101
九、控制语句
相对于普通语句,实现复制逻辑的关键所在。
包括:IF语句、CASE语句、WHILE循环语句、LOOP循环语句、REPEAT循环语句、LEAVE语句和ITERATE语句,它们可以进行流程控制。
9.1 变量作用域。
内部变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,不再可见了,在存储
过程外再也找不到这个内部变量,但是可以通过out参数或者将其值指派给会话变量来保存其值。
9.2 IF语句
9.2.1 存储过程创建
CREATE DEFINER=`sql`@`%` PROCEDURE `example_if`(IN x VARCHAR(50)) BEGIN IF x = 'IF' THEN SELECT 'IF'; ELSEIF x = 'ELSEIF' THEN SELECT 'ELSEIF'; ELSE SELECT 'ELSE'; END IF; END;
9.2.2 结果输出
CALL example_if("if"); -- 输出:IF CALL example_if("ELSEIF") ; -- 输出:ELSEIF CALL example_if(5) -- 输出:ELSE
9.2 CASE语句
CASE语句为多分支语句结构,该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值,如果查找到则执行该分支的内容,否则执行ELSE后的内容。CASE语句表示形式如下:
9.2.1 存储过程创建
有两种形式
CREATE DEFINER=`sql`@`%` PROCEDURE `example_case`(IN x INT) BEGIN CASE x/ABS(X) WHEN 1 THEN SELECT 1; WHEN -1 THEN SELECT -1; ELSE SELECT 0; END CASE; END
CREATE DEFINER=`sql`@`%` PROCEDURE `example_case`(IN x INT) BEGIN CASE WHEN x/ABS(X)=1 THEN SELECT 1; WHEN x/ABS(X)=-1 THEN SELECT -1; ELSE SELECT 0; END CASE; END
9.2.2 结果输出
CALL example_case(10); -- 输出:1 CALL example_case(-10) ; -- 输出:-1 CALL example_case(0) ; -- 输出:0
9.3 WHILE循环语句
9.3.1 存储过程创建
CREATE DEFINER=`sql`@`%` PROCEDURE `example_while`(IN `a` INT, IN `b` INT, OUT `c` INT) BEGIN SET c = 0; WHILE a <= b DO set c = c + a; set a = a + 1; END WHILE; END
9.3.2 结果输出
CALL example_while(1,10,@a); SELECT @a; -- 输出: 55
9.4 LOOP循环语句
LOOP循环没有内置的循环条件,但可以通过LEAVE语句退出循环。
示例:使用LOOP循环语句求前100的和。
9.4.1 存储过程创建
CREATE DEFINER=`sql`@`%` PROCEDURE `example_loop`(OUT sum INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE s INT DEFAULT 0; loop_label:LOOP SET s = s+i; SET i = i+1; IF i>100 THEN -- 退出LOOP循环 LEAVE loop_label; END IF; END LOOP; SET sum = s; END
9.4.2 结果输出
CALL example_loop(@a); SELECT @a; -- 输出5050
9.5 REPEAT循环语句
示例:使用REPEAT循环语句求前100的和。
9.5.1 存储过程创建
CREATE PROCEDURE example_repeat(OUT sum INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE s INT DEFAULT 0; REPEAT SET s = s+i; SET i = i+1; UNTIL i > 100 END REPEAT; SET sum = s; END;
9.5.2 结果输出
CALL example_repeat(@a); SELECT @a; -- 输出5050
9.6 iterate语句
ITERATE语句可以出现在LOOP、REPEAT和WHILE语句内,其意为“再次循环”;
该语句的格式与LEAVE大同小异,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。
示例:求10以内奇数值的和。
9.6.1 存储过程创建
CREATE DEFINER=`sql`@`%` PROCEDURE `example_iterate`(OUT sum INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE s INT DEFAULT 0; loop_label:LOOP SET i = i+1; IF i > 10 THEN -- 退出整个循环 LEAVE loop_label; END IF; IF (i mod 2) THEN SET s = s+i; ELSE -- 退出本次循环,继续下一个循环 ITERATE loop_label; END IF; END LOOP; SET sum = s; END
9.6.2 结果输出
CALL example_iterate(@a); SELECT @a; -- 输出: 25