数据库存储过程

发表于 更新于 分类于

什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程的优点

  • 提高性能： 存储过程在数据库服务器上预先编译和存储，可以减少每次执行的解析和编译时间，从而提高执行速度。
  • 减少网络流量： 客户端只需发送存储过程的调用请求，而不是发送大量的SQL语句，从而减少了网络流量和延迟。
  • 增强安全性： 可以限制用户直接访问数据库表，只允许他们通过存储过程进行数据操作，从而提高了数据的安全性和完整性。
  • 提高可维护性： 存储过程可以实现业务逻辑的封装，使得逻辑在数据库层面统一管理，易于维护和更新。
  • 提高代码复用： 存储过程可以被多个应用程序调用，实现了代码的复用，避免了重复编写相同的逻辑。

存储过程的缺点

  • 难以调试： 存储过程的调试相对复杂，特别是当存储过程逻辑较为复杂时，调试可能会变得非常困难。
  • 数据库厂商依赖性： 存储过程的语法和特性在不同的数据库管理系统中可能有所不同，导致代码的移植性受到影响。
  • 增加数据库复杂性： 过度使用存储过程可能会增加数据库的复杂性，使得数据库结构难以理解和维护。
  • 限制了平台独立性： 使用存储过程可能限制了应用程序的平台独立性，因为存储过程通常是特定数据库管理系统的特性。
  • 可能导致性能问题： 如果存储过程编写不当，可能会导致性能问题，例如过度的循环、大量的参数传递等都可能影响存储过程的性能表现。

存储过程的创建和调用

存储过程通过procedure关键字来进行定义，存储过程的开始和结束符号由BEGIN…END来标识

1
2
3
4
create procedure 存储过程名(参数)
    BEGIN
        ...
    END

声明语句结束符

1
2
3
DELIMITER $$
或
DELIMITER //

调用存储过程时通过call关键字来进行调用

1
call 存储过程名(参数)

变量赋值

1
SET @p_in=1

变量定义

1
DECLARE l_int int unsigned default 4000000;

存储过程使用实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 创建matches表
CREATE TABLE matches (
    MATCHNO INT,
    TEAMNO INT,
    PLAYERNO INT,
    WON INT,
    LOST INT
);
-- 表中插入数据
INSERT INTO matches ( MATCHNO, TEAMNO, PLAYERNO, WON, LOST )
VALUES
	( 1, 1, 6, 3, 1 ),
	( 7, 1, 57, 3, 0 ),
	( 8, 1, 8, 0, 3 ),
	( 9, 2, 27, 3, 2 ),
	( 11, 2, 112, 2, 3 );
	
-- 定义存储过程，删除指定球员参加的所有比赛
delimiter $$  -- 声明语句结束符，可以自定义
create procedure delete_matches(in p_playerno INTEGER)
BEGIN
	delete from matches where playerno = p_playerno;
END$$
delimiter ;

call delete_matches(57);  -- 执行存储过程,删除playerno为57的所有记录

存储过程的参数

1、in输入参数

1
2
3
4
5
6
7
8
9
10
11
12
delimiter $$
create procedure in_param(in p_in int)
BEGIN
	select p_in;
	set p_in=2;
	select p_in;
END$$
delimiter ;

set @p_in=1;
call in_param(@p_in); -- 这里会先打印1后打印2，p_in 在存储过程中被修改，但并不影响 @p_in   的值，因为前者为局部变量、后者为全局变量。
select @p_in;  --这里会输出1

2、out输出参数

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure out_param(out p_out int)
BEGIN
	select p_out;
	set p_out=2;
	select p_out;
END
//
delimiter ;

set @p_out=1;
call out_param(@p_out);  -- 这里会先输出null，再输出2，因为out是向调用者输出参数，不接收输入的参数，所以存储过程里第一次select p_out的时候会打印null
select @p_out;  -- 这里会输出2，因为我们调用了存储过程out_param并将参数@p_out传入，经过存储过程内部的处理将@p_out参数进行了修改并进行输出

3、inout输入输出参数

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure out_param(inout p_inout int)
BEGIN
	select p_inout;
	set p_inout=2;
	select p_inout;
END
//
delimiter ;

set @p_inout=1;
call inout_param(@p_inout);  -- 这里会先输出1再输出2，因为变量的类型为inout，所以既支持外部传入参数同时也会将参数输出出去
select @p_inout;  -- 这里仍然会打印2

注意:
1.即使存储过程中没有参数也必须在括号后面写上小括号
2.确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

MySQL存储过程的查询、修改与删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select name from mysql.proc where db='数据库名';
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？
SHOW CREATE PROCEDURE 数据库.存储过程名;

修改存储过程
ALTER PROCEDURE

删除存储过程
DROP PROCEDURE

MySQL存储过程的变量作用域及控制语句

1、变量的作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个声明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER //
	CREATE PROCEDURE proc3() 
		begin
			declare x1 varchar(5) default 'outer';
			begin
				declare x1 varchar(5) default 'inner';
				select x1;
			end;
			select x1;
		end;
//
DELIMITER ;

CALL proc3()  -- 先输出inner，再输出outer

2、控制语句

2.1 if-then-else 语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER //
	CREATE PROCEDURE proc2(IN parameter int) 
		begin
			declare var int;
			set var=parameter+1;
			if var=0 then
				insert into MATCHES values(17,9,9,9,9);
			end if; 
			if parameter=0 then
				update MATCHES set LOST=LOST+1;
			else
				update MATCHES set LOST=LOST+2;
			end if;
		end;
//
DELIMITER ;
2.2 case语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER //
	CREATE PROCEDURE proc3 (in parameter int)  
		begin
			declare var int;
			set var=parameter+1;
			case var
				when 0 then
					insert into matches values(17,9,9,9,9);
				when 1 then
					insert into matches values(18,9,9,9,9);
				else
					insert into matches values(19,9,9,9,9);
			end case; 
		end;
//
DELIMITER ;
2.3 循环语句

2.3.1 while ···· end while

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //
	CREATE PROCEDURE proc4 (in parameter int)  
		begin
			declare var int;
			set var=0;
			while var<6 do
				insert into matches values(var,9,9,9,9);
				set var=var+1;
			end while; 
		end;
//
DELIMITER ;

2.3.2 repeat···· end repeat
它在执行操作后检查结果，而 while 则是执行前进行检查。

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER //
	CREATE PROCEDURE proc5()
		begin
			declare v int;
			set v=0;
			repeat
				insert into matches values(v,8,8,8,8);
				set v=v+1;
			until v>=5
			end repeat;
		end;
//
DELIMITER ;

2.3.3 loop ·····endloop
loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER //
	CREATE PROCEDURE proc6()
		begin
			declare v int;
			set v=0;
			LOOP_LABLE:loop
				insert into matches values(v,7,7,7,7);
				set v=v+1;
				if v >=5 then
					leave LOOP_LABLE;
				end if;
			end loop;
		end;
//
DELIMITER ;

2.3.4 ITERATE迭代
ITERATE 通过引用复合语句的标号,来从新开始复合语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER //
	CREATE PROCEDURE proc10()
		begin
			declare v int;
			set v=0;
			LOOP_LABLE:loop
				if v=3 then
					set v=v+2;
					ITERATE LOOP_LABLE;
				end if;
				insert into matches values(v,5,5,5,5);
				set v=v+1;
				if v>=5 then
					leave LOOP_LABLE; 
				end if;
			end loop;
		end;
//
DELIMITER ;

通过数据存储过程批量创建用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
delimiter $$

create procedure insertUser(in insertCount int, in startUsername int)
begin
	declare i int default 0; -- 初始化循环计数器
	declare usernameno int default startUsername; -- 初始化用户名计数器
	while i < insertCount do  -- 循环插入数据直到达到指定的insertCount
		insert into user(username, email, phone, sex, password, create_time) values(
		concat('user_', usernameno),
		concat('user', usernameno, '@gmail.com'),
		concat('15', lpad(FLOOR(100000000 + RAND() * 900000000), 8, '0')),
		if(mod(usernameno, 2) = 0, 1, 0),
		'123456',
		now()
		);
		insert into user_role(user_id, role_id) values(last_insert_id(), 2); -- 使用last_insert_id()获取刚刚插入的user_id
		set i = i + 1;
		set usernameno = usernameno + 1;
	end while;
end$$

delimiter ;

call insertUser(2, 3);