MySQL存储过程

MySQL存储过程

一、简单应用

1. 结束符

DELIMITER $$  #应用于shell指令行

2. 声明存储过程

CREATE PROCEDURE 名称(入参或回参)

3. 开始与结束符

BEGIN
...
END

4. 变量赋值

SET @变量名 = 1

5. 定义变量

DECLARE 变量名 int unsigned default 100;

6. 存储过程体

create function 存储函数名(参数)

7. 调用存储过程

call 存储过程名(入参)

8. 删除存储过程

drop procedure 名称

9. 查看某库中存储过程

show procedure status where db = '数据库名'

10. 查看特定存储过程

show create procedure 数据库名.存储过程名

二、参数

存储过程的参数用在存储过程定义时，共有三种参数类型。

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

1. in输入参数

create PROCEDURE test(in data_in int)
begin
     SELECT data_in;
		 set data_in = 5;
		 select data_in;
end

set @data = 1

call test(@data)  #首先查出data_in = 1 修改之后 data_in = 5

select @data #data = 1,因为存储过程中修改的是局部变量，不影响全局

2. out回传参数

create PROCEDURE test(out data_out int)
begin
     set data_out = 5;
end

set @data = 1

call test(@data)

select @data  #data被修改，因为是回传参数。

3. inout输出输出参数

create PROCEDURE test(inout data_inout int)
begin
     select data_inout;   #data_inout = 1
     set data_inout = 5;
end

set @data = 1

call test(@data) 

select @data  #data = 5

三、变量

在存储过程中声明变量一定要将声明语句放在存储体的开始部分。

1. 定义变量

declare 名称 类型 默认值(可选)

declare data int default 50
#或
declare data int
set data = 50

2. 变量赋值

set 变量名 = 

3. 使用用户变量

create PROCEDURE test() 
begin
     select @data;
     set @data = 5;
     select @data;
end

set @data = 1

call test()

不可滥用全局变量。

四、流程控制

1. if-then-else

create PROCEDURE test()
begin
      	DECLARE data int default 5;
	    if data=5 then
		set data = data - 1;
		else
		set data = data + 1;
		end if;
		select data;
end

2. case

create PROCEDURE test()
BEGIN
     DECLARE data int default 1;
		 case data
		 when data < 0 then
		 set data = data + 1;
		 when 1 then
		 set data = data - 1;
		 else
		 set data = 5;
		 end case;
		 select data;
end

3. while

create PROCEDURE test()
BEGIN
     DECLARE data int DEFAULT(5);
		 while data < 10 do
		 select data;
		 set data = data + 1;
		 end while;
end

4. repeat

create PROCEDURE test()
BEGIN
     DECLARE data int DEFAULT(5);
		 REPEAT
	select data;
	set data = data + 1;
UNTIL data > 10 END REPEAT;
end

5. loop

create PROCEDURE test()
BEGIN
     DECLARE data int DEFAULT(5);
		 loop_name:loop
		 select data;
		 set data = data + 1;
		 if data = 10 then
		 leave loop_name;
		 end if;
		 end loop loop_name;
end

五、存储过程中使用事物

MySQL的InnoDB中存储过程不是原子操作，而Oracle是原子的。

所以对于MySQL中的存储结构，一定要确保数据一致性使用事务。

假设有表：

CREATE TABLE `transaction`  (
  `id` int(11) NOT NULL,
  `name` varchar(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

1. 不使用事务

CREATE PROCEDURE test()
BEGIN
		 DECLARE id int DEFAULT(1);
		 DECLARE name varchar(11) DEFAULT('小明');
         INSERT INTO `transaction` VALUES(id,name);  #插入成功
		 INSERT INTO `transaction` VALUES(id,name);  #报错
END

由于id为primary key唯一，所以第二句插入会执行失败，但这并没有阻止语句一的提交。

因为MySQL是AutoCommit的，为了保证数据一致，使用事务。

2. 使用事务

CREATE PROCEDURE test()
BEGIN
		 DECLARE id int DEFAULT(1);
		 DECLARE name varchar(11) DEFAULT('小明');
		 START TRANSACTION;
         INSERT INTO `transaction` VALUES(id,name); 
		 INSERT INTO `transaction` VALUES(id,name); 
		 COMMIT;
END

只需要在需要保证事务的语句上下用START TRANSACTION包裹即可。

由于第二句插入报错，导致存储过程终端，所以无法执行到Commit，故无法提交。

3. 回滚

CREATE PROCEDURE test()
BEGIN
		 DECLARE id int DEFAULT(1);
		 DECLARE name varchar(11) DEFAULT('小明');
		 DECLARE error_info INT DEFAULT(0);  #定义状态码
		 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_info = 1;  #如果报错修改状态码为1
		 START TRANSACTION;
         INSERT INTO `transaction` VALUES(id,name);  #插入成功
		 INSERT INTO `transaction` VALUES(id,name);  #报错
		 if	error_info = 1 then
		     ROLLBACK;
		 ELSE
				 COMMIT;
		 END IF;
		 SELECT error_info;
END

这样我们可以手动控制回滚，和取得状态码。

而不是直接报错结束。