存储过程(Stored Procedure)

2024-02-06 10:16:28

(一) 存储过程概述

1.定义：

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

2.优点:

存储过程可封装，并隐藏复杂的业务逻辑
存储过程可以回传值，并可以接受参数
存储过程无法使用select语句运行，因为他是子程序
存储过程可以用于数据检验，强制实行业务逻辑等

3.缺点

存储过程用在特定的数据库上，因为支持的编程语言不通，当切换数据库时需要重新编写存储过程
存储过程的性能调校与撰写，受限于不同的数据库系统

（二）存储过程的创建和调用

创建存储过程

I.主要语法：

(1)声明语句结束符

DELIMITER $$
或
DELIMITER //

(2)声明存储过程

CREATE PROCEDURE demo_in_parameter(IN p_in int)

(3)存储过程开始和结束的符号

BEGIN .... END

(4)给变量赋值

SET @p_in=1

(5)定义一个变量

DECLARE l_int int unsigned default 4000000;

(6)mysql创建存储过程，存储函数

create procedure 存储过程名(参数)

(7)存储过程体

create function 存储函数名(参数)

II.实例

已存在表users：

select * from users；
+---------+--------+----------+
|    id   |  name  |  address |
+---------+--------+----------+
|       1 |      a |      aaa |
|       2 |      b |      bbb |
|       3 |      c |      ccc |
+---------+--------+----------+

编写存储过程：

CREATE PROCEDURE delete_user(IN user_id INTEGER)
BEGIN
	DELETE FROM users
	WHERE id = user_id;
END;

调用存储过程delete_user,传入参数user_id = 3：

call delete_user(3);

再次查看表users：

select * from users；
+---------+--------+----------+
|    id   |  name  |  address |
+---------+--------+----------+
|       1 |      a |      aaa |
|       2 |      b |      bbb |
+---------+--------+----------+

III.存储过程体

包含了在过程调用时必须执行的语句，例如dml语句（Data Manipulation Language，数据操纵语言），ddl语句（Data Definition Languages，数据定义语言），if-then-else，while-do语句，声明变量的declare语句等
过程体格式：以begin开始，以end结束，可以嵌套

BEGIN
	BEGIN
		BEGIN
			statements;
		END
	END
END

每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块不需要分号

# 标签格式：
[begin_lable:]BEGIN
	[statement_list]
END[end_lable]

# 例如：
lable1: BEGIN
	lable2: BEGIN
		lable3: BEGIN
			statements;
		END lable3;
	END lable2;
END lable1;

标签的优点：

增强代码可读性
某些语句需要用到标签

（三）存储过程的参数

mysql存储类型参数有三种类型，定义格式如下：

CREATE PROCEDURE 存储过程名([[IN | OUT | INOUT] 参数名 参数类型...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

I.in传入参数

CREATE PROCEDURE in_param(in p_in int)
begin
	select p_in;
	# 给变量p_in赋值（局部变量）
	set p_in=2;
	select p_in;
end;

# 给变量p_in赋值（全局变量）
set @p_in=1;

# 调用存储过程
call in_param(@p_in);
+------+     
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

# p_in在存储过程中被修改，但不影响@p_in的值，因为存储过程中为局部变量
select @p_in;
+-------+     
| @p_in |
+-------+
|     1 |
+-------+

II.out输出参数

CREATE PROCEDURE out_param(out p_out int)
begin
	select p_out;
	set p_out=2;
	select p_out;
end

set @p_out=1;

call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
# 因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

select @p_out
# 调用了out_param存储过程，输出参数，改变了p_out变量的值
+--------+
| @p_out |
+--------+
|      2 |
+--------+

III.inout输入参数

CREATE PROCEDURE inout_param(inout p_inout int)
begin
	select p_inout;
	set p_inout=2;
	select p_inout;
end

set @p_inout = 1;

call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+

select @p_inout;
# 调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

确保村属名字不等于列名，否则在过程体中，被当作列名处理
如果过程中没有参数，也必须在过程名后面写小括号
传入值使用in，返回值使用out，尽量少用inout

（四）变量

I.变量定义

局部变量声明放在存储过程体开始：

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
# datatype为mysql数据类型，例如：int， float， varchar， date....

例如:

DECLARE l_int int unsigned default 4000000;   
DECLARE l_numeric number(8,2) DEFAULT 9.95;   
DECLARE l_date date DEFAULT '1999-12-31';   
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';   
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

II.变量赋值

set 变量名 = 表达式 [,variable_name = expression ...]

III.用户变量

select 'hello world' into @x;

select @x;
+-------------+  
|   @x        |  
+-------------+  
| hello world |  
+-------------+  

set @y='hello'
+-------+  
|   @y  |  
+-------+  
| hello |  
+-------+  

set @z=1+2+3;
select @z;
+------+  
| @z   |  
+------+  
|  6   |  
+------+

IV.在存储过程中使用用户变量

CREATE PROCEDURE Hello() select concat(@hello, 'world');
set @hello='hello';
call Hello();
+------------------------+  
| CONCAT(@hello,'world') |  
+------------------------+  
|  hello world           |  
+------------------------+

（五）存储过程的其他使用

存储过程的查询

select * from mysql.proc where db='数据库名'
#查看单个存储过程的详细信息
show create procedure study.delete_user;

存储过程的修改

ALTER PROCEDURE

存储过程的删除

DROP PROCEDURE 存储过程名

存储过程的控制语句

1.if-then-else语句

create procedure proc(IN parameter int)
begin
	declare var int;
	set var=parameter+1;
	if var=0 then
		insert into t values(17);
	end if;
	if parameter=0 then
		update t set s1=s1+1;
		else
		update t set s1=s1+2;
	end if;
end;

2.case语句

create procedure proc(in parameter int)
begin
	declare var int;
	set var=parameter+1;
	case var
  	when 0 then
  		insert into t values(17);
  	when 1 then
  		insert into t values(18);
 	  else
 	 		insert into t values(19);
  end case;
end;

case
	when var=0 then
		insert into t values(30);
	when var>0 then
	when var<0 then
	else
end case

3.循环语句

# while 条件 do
#		--循环体
# end while
create procedure proc()
begin
	declare var int;
	set var=0;
	while var < 6 do
		insert into t values(var);
		set var=var+1;
	end while;
end;

# repeat .... end repeat
create procedure proc()
begin
	declare v int;
	set v=0;
	repeat
		insert into t values(v);
		set v=v+1;
	until v>=5
	end repeat;
end;

# loop .... end loop
create procedure proc()
begin 
	declare v int;
	set v=0;
	LOOP_lable: loop
		insert into t values(v);
		set v=v+1;
		if v >= 5 then
			leave LOOP_LABLE;
		end if;
	end loop;
end;

4.ITERATE迭代

create procedure proc()
begin
	declare v int;
	set v=0;
	LOOP_LABLE: loop
		if v=3 then
			set v=v+1;
			ITERATE LOOP_LABLE;
		end if;
		insert into t values(v);
		set v=v+1;
		if v>=5 then
			leave LOOP_LABLE;
		end if;
	end loop;
end;

（六）存储过程的使用场景

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。

码农公寓