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
这样我们可以手动控制回滚,和取得状态码。
而不是直接报错结束。