12. 存储过程和函数

最新推荐文章于 2024-01-14 10:15:00 发布

_dolphin

最新推荐文章于 2024-01-14 10:15:00 发布

阅读量192

点赞数

分类专栏： database 文章标签： mysql

本文链接：https://blog.csdn.net/qq_30591155/article/details/108626321

版权

database 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

MySQL官方文档有存储例程(Store routines)一说，它的两种类型就是存储过程和函数。

12.1 什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。可以把一些复杂的数据处理逻辑封装在存储过程和函数，这样就减轻了后端人员编写SQL语句的负担，提高了数据处理的效率。

存储过程和函数的区别：

函数必须有返回值，而存储过程没有。
存储过程的参数可以使用IN、OUT、INOUT类型，函数的参数只能是IN类型。省略则默认参数类型为IN。

12.2 存储过程和函数的相关操作

对存储过程和函数操作时，需要用户有相应的权限。创建存储过程或者函数需要 CREATE ROUTINE 权限，修改或者删除存储过程或者函数需要 ALTER ROUTINE 权限，执行存储过程或者函数需要 EXECUTE 权限。

通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。

begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。

12.2.1 存储过程或函数创建

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

调用存储过程和函数

# 存储过程
call proc_name(...);
# 函数：直接在表达式中引用即可，和调用SQL内部函数一样
func_name();

下面的示例显示了一个简单的存储过程，该存储过程给定了国家（地区）代码，计算了出现在国家表中相同国家代码的城市数。使用IN参数传递国家/地区代码，并使用OUT参数返回城市计数：

CREATE TABLE "country" (
  "country_code" varchar(11) DEFAULT NULL,
  "city" varchar(11) DEFAULT NULL
);

# 修改定界符,防止mysql在遇到“;”被解释为结束符, code是mysql保留的关键字
delimiter //;
CREATE PROCEDURE citycount(in ccode varchar(11), out cities varchar(11))
begin
select count(*) into cities from country
where country_code=ccode;
end
//
# 将结束定界符复原
delimiter ;
# 调用存储过程,这里的@cities相当于一个全局变量，在执行完毕之后，可以通过
# select @cities;查看结果
call citycount('CN', @cities);

定界符因需选择，比如下面存储函数就没有使用，因为就只有单条语句。

mysql> create function hello(s char(20))
    -> returns char(50) deterministic
    -> return concat('hello, ',s,'!');

mysql> select hello('world');
+----------------+
| hello('world') |
+----------------+
| hello, world!  |
+----------------+

关于characteristic特征值的说明这里不再记录，详情可以看书或者官方文档。

12.2.2 删除存储过程或者函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;

12.2.3 查看存储过程或者函数

show create {PROCEDURE | FUNCTION} sp_name;

show {PROCEDURE | FUNCTION} status like \G;

# ...具体就是结果不同
# show create 查询的是 某结构的 定义
# show status 查询的是 某结构的 状态

12.2.4 变量的声明及使用

MySQL变量的分类

MySQL变量分为：局部变量、用户变量、会话变量和全局变量。全局变量和会话变量又称为系统变量。

(1)局部变量

一般用于sql语句块，比如store_routines的begin/end。作用域仅限于该语句块，生命周期随语句块而存活。

一般用法：declare声明，set或者select …into 赋值。

(2)用户变量

@var_name形式的变量为用户变量，用户变量的生命周期与本次连接（session）有关，当本次会话结束，连接中所有定义的用户变量都会消失，且各个连接中的用户变量互不可见。

一般用法：set @var_name=value; set @var_name:=value;或者 select @var_name:=value;select @num:=字段名 from 表名 where ……

用户变量不需要声明，赋初值的过程即声明和定义，一起做了。声明及定义然后再赋值

(3)会话变量和全局变量

session和globall。

1、变量的定义

格式：DECLARE var_name[,...] type [DEFAULT value]

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用 DEFAULT 赋默认值。

2、变量的赋值

变量可以直接赋值，或者通过查询赋值

格式：

# set赋值可以是常量或者表达式
set var_name = expr [,var name = expr]...

# 查询赋值，要求查询返回的结果必须只有一行
select col_name[,...] into var_name[,...] table_expr;

declare 声明变量，set或者select…into为变量赋值。

# set方式赋值
delimiter //
create function addSum(a int,b int)
returns int
begin
declare sum int;
set sum = a + b;
return sum;
end
//
# 查询赋值
delimiter //
create function addSum(a int,b int)
returns int
begin
declare sum int;
select a+b into sum;
return sum;
end
//

12.2.5 定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

1、条件的定义

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

# mysql_error_code: An integer literal indicating a MySQL error code.
# SQLSTATE [VALUE] sqlstate_value: A 5-character string literal indicating an SQLSTATE value.

2、条件的处理

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
}

当调用存储过程执行其中的SQL时发生内部错误时，如果使用了条件处理，那么就会触发条件处理的action，它有三个值分别代表：

CONTINUE 表示继续执行下面的语句
EXIT 则表示执行终止
UNDO 现在还不支持

condition_value 的值可以是通过 DECLARE 定义的 condition_name，可以是 SQLSTATE 的值或者 mysql-error-code 的值或者 SQLWARNING、NOT FOUND、SQLEXCEPTION，这 3 个值是 3 种定义好的错误类别，分别代表不同的含义。

SQLWARNING 是对所有以 01 开头的 SQLSTATE 代码的速记。
NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记。
SQLEXCEPTION 是对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记。

向staff表中插入重复主键的记录，如果没有异常处理，x=3就不会执行，我们希望异常发生后，继续往后执行，这时就需要异常处理。

CREATE DEFINER="stronger"@"%" PROCEDURE "staff_insert"()
BEGIN
	declare continue handler for sqlstate '23000' set @x2=1;
	set @x = 1;
	insert into staff values(2, 1, 'Qing','Q');
	set @x = 2;
	insert into staff values(1, 1, 'Long','L');
	set @x = 3;
END

12.2.6 光标的使用

在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

声明光标

declare cursor_name cursor for select statement;

打开光标
```
open cursor_name;
```
fetch光标，循环遍历
```
fetch cursor_name into var_name [, var_name]...
```
作用：将遍历的每条记录赋值到var_name
关闭光标
```
close cursor_name;
```

例：统计账单金额大于100的账单数量，判断循环结束的条件是捕获NOT FOUND的条件，当FETCH光标找不到下一条记录的时候，就会关闭光标然后退出过程。
在这里插入图片描述

drop PROCEDURE payment_proc;
delimiter //
create procedure payment_proc()
begin
	# 一定不要定义和fetch捕获的列同名的变量比如declare amount int;
	declare i_amount int;
	declare amount_cur cursor for select amount from payment;

	declare exit handler for not found close amount_cur;
	
	set @count = 0;
	# 打开光标
	open amount_cur;
	REPEAT
		# 使用光标
		fetch amount_cur into i_amount;
			if i_amount>100 then
				set @count = @count + 1;# 注意赋值必须加set
			end if;
	UNTIL 0 END REPEAT;
	
	# 关闭光标
	close amount_cur;

end
//

call payment_proc();
select @count;

【注】

navicate中定义存储过程一定要通过delimiter //改变定界符，不然会报错。
fetch游标将参数赋值给之前定义的变量，需要注意定义的变量不要和游标中捕获的参数同名。
变量、条件、处理程序、光标都是通过 DECLARE 定义的，它们之间是有先后顺序的要求的。变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明。

12.2.7 流程控制

1、IF语句

语法：

if condition then statement_list
	[elseif condition then statement_list]...s
	[else statement_list]
endif;

2、CASE语句

CASE 实现比 IF 更复杂一些的条件构造，具体语法如下：

case case_value
	when when_value then statement_list
	[when when_value then statement_list]...
	[else statement_list]
end case
or:
case 
	when search_condition then statement_list
	[when search_condition then statement_list]...
	[else statement_list]
end case;

3、LEAVE和ITERATE

(1)LEAVE

用于退出具有给定标签的流控制构造。如果标签用于最外面存储的程序块，则LEAVE退出程序。

LEAVE可以在BEGIN … END或循环构造（LOOP，REPEAT，WHILE）中使用。

#格式
LEAVE label;

(2)ITERATE

ITERATE 语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。

#格式
ITERATE label;

# 循环向表中插入值，如果是偶数跳过当前循环，奇数则插入
create procedure test()
begin
	declare i int default 10;
	ins: loop
		set i = i + 1;
		if i > 20 then
			leave ins;
		elseif mod(i,2)=0 then
			iterate ins;
		else 
			insert into t values(i);
		end if;
		#set i = i + 1;刚开始把这句放后面了，导致死循环
	end loop;
end
//

4、循环

MySQL中循环的定义有while、repeat、loop。

(1)while

#格式
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

delimiter //
# 存储过程括号一定不要忘记加
create procedure test()
begin
	declare i int default 1;
	while i <= 10 do
		insert into t values(i);
		set i = i + 1;
	end while;
end
//
delimiter ;
call test();

(2)repeat

# 格式
[begin_label:] REPEAT
    statement_list
UNTIL search_condition # 注意它的意思，是直到满足什么条件才跳出，有点像do...while
END REPEAT [end_label]

create procedure test()
begin
	declare i int default 1;
	repeat
		insert into t values(i);
		set i = i + 1;
	until i > 10 end repeat;
end

(3)loop

通常loop配合leave，循环的终止需要leave，或者如果在存储函数中也可以通过return终止循环。

# 格式
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

create procedure test()
begin
	declare i int default 1;
	lp: loop
		if i > 10 then
			leave lp;
		end if;
		insert into t values(i);
		set i = i + 1;
	end loop;
end
//