MySQL存储过程

1.简介

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

• 优点
  • 在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑或bug,而不是重启服务器
  • 执行速度快,存储过程经过编译后会比单独一条一条执行要快
  • 减少网络传输流量
  • 方便优化
• 缺点
  • 过程化编程,复杂业务处理的维护成本高
  • 调试不便
  • 不同数据库之间的可移植性查(不同数据库的语法不一致)

2.语法结构

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

举例:

测试表 hero
SQL脚本文件链接：https://pan.baidu.com/s/1XMbZ6j2pVgtRSN3W7PySyA
提取码：1209

-- delimiter 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突,因为使用的是Navicat,所以就没有声明结束符
CREATE PROCEDURE test()
BEGIN
	SELECT * FROM hero WHERE name = '佐伊';
END;

-- 调用存储过程
CALL test();

3.变量及赋值

3.1 局部变量

用户自定义,在begin/end语句块中有效

语法:

declare var_name type [default var_value];

举例:

DECLARE price INT;

CREATE PROCEDURE price_test()
BEGIN
		DECLARE price_test INT DEFAULT 0;
		-- set 赋值
		SET price_test = 7800;
		SELECT price_test;
		-- into 赋值
		SELECT price into price_test FROM hero WHERE name = '佐伊';
		SELECT price_test;
END;

3.2 用户变量

用户自定义,当前会话(连接)有效

语法:

-- 不用提前声明,使用及声明
@price

举例:

CREATE PROCEDURE price_test()
BEGIN
	SET @price = 500;
END;

-- 调用存储过程
CALL price_test();

SELECT @price; 

3.3 会话变量

由系统提供，当前会话（连接）有效

语法:

@@session.var_name

-- 查看会话变量
show session variables; 
 -- 查看某会话变量
select @@session.unique_checks;
 --修改会话变量
set @@session.unique_checks = 0;

3.4 全局变量

由系统提供，整个MySQL服务器有效

语法:

@@global.var_name

举例:

-- 查看全局变量中变量名有char的记录
show global variables like '%char%'; 

-- 查看全局变量character_set_client的值
select @@global.character_set_client; 

4. 入参出参

语法:

in | out | inout param_name type

4.1 IN(入参)

CREATE PROCEDURE in_test(IN price INT)
BEGIN
	SET @price = price;
END;

CALL in_test(3150);

SELECT @price;

4.2 OUT(出参)

CREATE PROCEDURE out_test(IN name_in VARCHAR(32) , OUT price_out INT)
BEGIN
	-- 要么表起别名，要么入参名与字段名不一致
	SELECT price INTO price_out FROM hero WHERE name = name_in;
END;

CALL out_test('佐伊',@price);

SELECT @price; 

4.3 INOUT(出入参)

CREATE PROCEDURE inout_test(INOUT price_inout INT)
BEGIN
	set price_inout = price_inout / 2;
END;

SET @price = 6300;

CALL inout_test(@price);

SELECT @price; 

5. 流程控制-判断

5.1 if

语法:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

举例:

-- 需求: 英雄价格是500为便宜,3150为中等,6300为昂贵,其他价格为其他
CREATE PROCEDURE if_test (IN price INT) BEGIN
	DECLARE type VARCHAR ( 5 );	
	IF price = 500 THEN
		SET type = '便宜';
	ELSEIF price = 3150 THEN
		SET type = '中等';
	ELSEIF price = 6300 THEN
		SET type = '昂贵';
	ELSE 
		SET type = '其他';		
	END IF;
	SELECT type;
END;

CALL if_test (6300)

5.2 case

语法:

-- 语法一（类比java的switch）：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 语法二：
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

举例:

-- 需求: 英雄价格是500为便宜,3150为中等,6300为昂贵,其他价格为其他
CREATE PROCEDURE case_test ( IN price INT ) BEGIN
	DECLARE type VARCHAR ( 5 );
	CASE		
		WHEN price = 500 THEN
			SET type = '便宜';		
		WHEN price = 3150 THEN		
			SET type = '中等';		
		WHEN price = 6300 THEN		
			SET type = '昂贵';
		ELSE 
			SET type = '其他';		
	END CASE;
	SELECT type;
END;

CALL case_test (6300)

6. 流程控制-循环

6.1 loop

loop是死循环，需要手动退出循环，我们可以使用leave来退出。

语法:

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

举例:

-- 需求:打印'1,2,3,4,5,6,7,8,9,10'
CREATE PROCEDURE loop_test () BEGIN
	DECLARE c_index int DEFAULT 1;
	DECLARE string VARCHAR (30) DEFAULT '1';
	
	label: LOOP
	
		IF c_index >= 10 THEN
		LEAVE label; 
	END IF; 
	
	SET c_index = c_index + 1;
	SET string = CONCAT(string,',',c_index);

END LOOP label;

	SELECT string;
	
END;

CALL loop_test ()

6.2 repeat

直到…为止，才退出循环

语法:

[begin_label:] REPEAT
    statement_list
UNTIL search_condition	
END REPEAT [end_label]

举例:

-- 需求:打印'1,2,3,4,5,6,7,8,9,10'
CREATE PROCEDURE repeat_test () BEGIN
	DECLARE c_index int DEFAULT 1;
	DECLARE string VARCHAR (30) DEFAULT '1';
	
REPEAT

	SET c_index = c_index + 1;
	SET string = CONCAT(string,',',c_index);
	
UNTIL c_index >= 10 END REPEAT;

	SELECT string;
	
END;

CALL repeat_test ()

6.3 while

类比java的while循环

语法:

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

举例:

-- 需求:打印'1,2,3,4,5,6,7,8,9,10'
CREATE PROCEDURE while_test () BEGIN
	DECLARE c_index int DEFAULT 1;
	DECLARE string VARCHAR (30) DEFAULT '1';
	
	WHILE c_index < 10	DO
	
		SET c_index = c_index + 1;
		SET string = CONCAT(string,',',c_index);
	
	END WHILE;

	SELECT string;
	
END;

CALL while_test ()

7. 流程控制-退出、继续循环

7.1 leave

类比java的breake

语法:

-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label

7.2 iterate

类比java的continue

语法:

-- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label

8. 游标

用游标得到某一个结果集，逐行处理数据。

特别注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

语法:

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name ...]
-- 关闭语法
CLOSE cursor_name

举例:

-- 需求:查询价格为6300的英雄,并依次输出英雄的名字和职业(仅展示游标用法)
CREATE PROCEDURE cursor_test () BEGIN
	DECLARE h_name VARCHAR (30);
	DECLARE h_job VARCHAR (10);
	
	DECLARE flag boolean DEFAULT TRUE;
	
	DECLARE hero_cursor cursor for
	SELECT name,job 
	FROM hero 
	WHERE price = 6300;
	-- 若没有数据返回,程序继续,并将变量flag的值设为false
	DECLARE CONTINUE HANDLER FOR NOT found SET flag = FALSE;
	
	OPEN hero_cursor;
	
	label: LOOP
	
	FETCH hero_cursor INTO h_name,h_job;
	
	IF flag THEN
	
		SELECT CONCAT(h_name,'的职业是',h_job) 结果;
		
	ELSE 
		
		LEAVE label; 
		
	END IF; 
	
	END LOOP label;

	CLOSE hero_cursor;

END;

CALL cursor_test ()

9. 存储过程中的handler

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}


CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.


SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

-- 各种写法：
	DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
	DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
	DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';