编写mysql存储过程

https://blog.csdn.net/weixin_45577435/article/details/120499496

一、存储过程定义
​ 存储过程（Stored Procedure）：一组为了完成特定功能的SQL语句集，存储在数据库中，经过一次编译后不需要再次编译。

二、存储过程特点
1、可以完成复杂的判断和运算

2、执行速度快

3、可重复使用

4、减少网络之间的数据传输，节省开销

通过代码调用存储过程时只需要传存储过程名称以及所需参数即可

三、存储过程语法
1、创建

delimiter $$ --定义存储过程结束标记，可自定义
--创建 使用procedure
create procedure [procedure_name]([param_type][param_name][data_type],...)
begin
[存储体];--分号不可少
end $$
delimiter;

-- 创建存储过程示例
delimiter $$
create procedure getUserById(in userId int)
begin
select * from tb_user where user_id=userId;
end $$
delimiter;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
delimiter：表示sql语句执行的结束

参数类型说明：param_type[in|out|inout]

IN：表示调用者需要对存储过程传入参数。
OUT：表示调用者到一个或多个返回值。
INOUT：表示调用者既要传入值，又要传出值。
2、删除

-- 删除procedure
drop procedure if exists [procedure_name];

-- 示例
drop procedure if exists getUserById;
1
2
3
4
5
3、调用

-- 无参
call procedure_name();
-- 有参
call procedure_name(param1,param2,...)
1
2
3
4
四、定义变量
变量声明语句必须放在存储体的开始部分

-- 定义变量 declare
-- 语法
-- declare [declare_name][type][default_value(可选)]
declare userId int default 66;
declare userId int;
-- 变量赋值
-- set declare_name=new_value;
set userId=77;
1
2
3
4
5
6
7
8
五、流程控制语句
1、条件控制语句
1、IF语句
语法

IF search_condition_1 THEN statement_list_1
[ELSEIF search_condition_2 THEN statement_list_2] ...
[ELSE statement_list_n]
END IF
1
2
3
4
实例

DROP PROCEDURE IF EXISTS get_max_num ;

DELIMITER $$
CREATE PROCEDURE get_max_num (IN a INT, IN b INT, OUT c)
BEGIN
IF a>b
THEN set c = a ;

ELSE set c = b;

END IF ;

END $$
DELIMITER ;

SELECT @a;
CALL get_max_num(3,4);
SELECT @a;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2、case语句
存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行

语法

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

1
2
3
4
5
6
7
8
9
10
11
12
13
实例

-- case 语句的使用
DROP PROCEDURE test_case;

DELIMITER $$
CREATE PROCEDURE test_case (IN param INT)
BEGIN
DECLARE result VARCHAR (10) ;
CASE param
WHEN param = 1
THEN SET result = "星期一" ;
WHEN param = 2
THEN SET result = "星期二" ;
WHEN param = 3
THEN SET result = "星期三" ;
ELSE SET result = "error";
END CASE ;
SELECT result AS "星期" ;
END $$
DELIMITER ;

CALL test_case(11);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2、循环控制语句
1、while语句
WHILE语句内的语句或语句群被重复，直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的

先判断，后执行

语法

[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
1
2
3
实例

-- while语句
DROP PROCEDURE IF EXISTS test_while;
DELIMITER $$

CREATE PROCEDURE test_while (IN a INT)
BEGIN
DECLARE param INT DEFAULT 5 ;
SET param=a;
SELECT "yyyy";
WHILE
param > 0 DO
SELECT
param ;
SET param = param - 1 ;

END WHILE ;

END $$
DELIMITER ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2、repeat
REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

先执行，后判断

语法

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
1
2
3
4
实例

-- repeat语句
DROP PROCEDURE IF EXISTS test_repeat;
DELIMITER $$
CREATE PROCEDURE test_repeat(IN param INT)
BEGIN
SET @param=1;
REPEAT
SET @param=@param+1;
SELECT @param;
UNTIL @param>param
END REPEAT;
END $$
DELIMITER;

CALL test_repeat(2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
3、loop语句
LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

语法

[begin_label:] LOOP
statement_list
END LOOP [end_label]
1
2
3
实例

-- loop语句
DROP PROCEDURE test_loop;
DELIMITER$$
CREATE PROCEDURE test_loop (IN param INT)
BEGIN
f_loop :
LOOP
IF param < 1
THEN LEAVE f_loop ;
ELSE SET param = param - 1 ;
SELECT
param ;
END IF ;
END LOOP f_loop ;
END $$

DELIMITER;
CALL test_loop(4);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
4、leave语句
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用

5、iterate
ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END