mysql 存储过程中调用存储过程 mysql存储过程调用函数

文章目录

• 10 存储过程和函数

• 10.1 存储过程和函数

• 10.1.1 创建存储过程
• 10.1.2创建存储函数
• 10.1.3 变量的使用
• 10.1.4 定义条件和处理程序
• 10.1.5 光标的使用
• 10.1.6 流程控制的使用

• 10.2 调用存储过程和函数

• 10.2.1 调用存储过程
• 10.2.2 调用存储过程

• 10.3 查看存储过程和函数
• 10.4 修改存储过程和函数
• 10.5 删除存储过程和函数

10 存储过程和函数

10.1 存储过程和函数

• 存储程序可以存储过程和函数。
• 在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。
• 使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。
• 存储过程也可以调用其他存储过程。

10.1.1 创建存储过程

创建存储过程需要使用CREATE PROCEDURE语句，基本语法格式如下：

create procedure sp_name(proc_parameter])[characteristics...] routine_body

• CREATE PROCEDURE为用来创建存储函数的关键字；
• sp_name为存储过程的名称；
• proc_parameter为指定存储过程的参数列表，列表形式如下：
  [IN|OUT|INOUT] param_name type 其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
  param_name表示参数名称；
  type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
• characteristics指定存储过程的特性
• routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

characteristics 可以取的值
●　LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。
●　[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOTDETERMINISTIC。
●　{ CONTAINS SQL | NO SQL | READSSQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINSSQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
●　SQL SECURITY { DEFINER | INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
●　COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。

【例】存储过程的内容，名称为AvgFruitPrice，返回所有水果的平均价格，输入代码如下：

create procedure AvgFruitPrice()
begin
select avg(f_price) as avgprice from fruits;
end;

【例】创建名称为CountProc的存储过程，代码如下

create procedure CountProc (OUT paraml INT )
begin 
select count(*) into paraml from fruits
end

上述代码的作用是创建一个获取fruits表记录条数的存储过程，名称是CountProc，COUNT(*)计算后把结果放入参数param1中。

10.1.2创建存储函数

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下：

create function func_name{[func_parameter]}
returns type
[characteristic...] routine_body

• CREATE FUNCTION为用来创建存储函数的关键字
• func_name表示存储函数的名称
• func_parameter为存储过程的参数列表，参数列表形式如下：
• [IN|OUT|INOUT] param_name type

• IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出
• param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。RETURNS type语句表示函数返回数据的类型
• characteristic指定存储函数的特性，取值与创建存储过程时相同，这里不再赘述。

【例】创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型，代码如下：

create function NameByZip()
returns char(50)
return (select s_name from suppliers where s_call='48075');

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。
比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集相应的ENUM成员，从函数返回的值是字符串。

指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

10.1.3 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中，本小节主要介绍如何定义变量和为变量赋值。

• 定义变量
  在存储过程中使用DECLARE语句定义变量，语法格式如下：

declare var_name[，varname]... date_type[default value];

var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL
【例】定义名称为myparam的变量，类型为INT类型，默认值为100，代码如下：

declare myparam int default 100;

• 为变量赋值
  定义变量之后，为变量赋值可以改变变量的默认值。

1. 使用SET语句为变量赋值，语法格式如下：

set var_name=expr[,var_name=expr]...;

【例】声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值，代码如下：

declare var1,var2,var3 INT;
set var1=10,var2=20;
set var3=var1+var2;

2. 使用select...into 为一个或者多个变量赋值

select col_name[,...] into var_name[,...] table_expr;

这个SELECT语法把选定的列直接存储到对应位置的变量。col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。
【例】声明变量fruitname和fruitprice，通过SELECT … INTO语句查询指定记录并为变量赋值，代码如下：

declare fruitname  char(50);
declare fruitprice decimal(8,2);
select f_name,f_price into fruitname,fruitprice from fruits where f_id='a1';

10.1.4 定义条件和处理程序

• 定义条件
  定义条件使用DECLARE语句，语法格式如下：

declare condition_name condition for [condition_type]
[condition_type]:
SQLSTATE [VALUE] sqlstate_value|mysql_error_code

condition_name参数表示条件的名称；condition_type参数表示条件的类型；sqlstate_value和MySQL_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_error_code为数值类型错误代码。
例如，在ERROR 1142（42000）中，sqlstate_value的值是42000，MySQL_error_code的值是1142。
这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句

【例】定义"ERROR 1148(42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：

//方法1： 使用sqlstate_value
declare command_not_allowed condition for SQLSTATE '42000';
//方法2：使用mysql_error_code
declare command_not_allowed condition for 1148

• 定义处理程序
  定义处理程序时，使用DECLARE语句的语法如下：

declare handler_type handler for condition_value[,...] sp_statement
handler_type:
	CONTINUE|EXIT|UNDO
condition_value:
 SQLSTATE [VALUE] sqlstate_value
 |condition_name
 |SQLWARNING
 |NOT FOUND
 |mysql_error_code

其中，handler_type为错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。condition_value表示错误类型

10.1.5 光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

• 声明光标
  在MySQL中，使用DECLARE关键字来声明光标，其语法的基本形式如下：

declare cursor_name cursor for select_statement

cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集
【例】声明名称为cursor_fruit的光标，代码如下：

declare cursor_fruit cursor for select f_name,f_price form fruits;

在上面的示例中，光标的名称为cur_fruit，SELECT语句部分从fruits表中查询出f_name和f_price字段的值。

• 打开光标
  打开光标的语法如下：

open cursor_name{光标名称}

这个语句打开先前声明的名称为cursor_name的光标。
【例】打开名称为cursor_fruit的光标，代码如下：

open cursor_fruit;

• 使用光标

fetch cursor_name into var_name [,var_name]..{参数名称}

cursor_name参数表示光标的名称
var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。
【例】使用名称为cursor_fruit的光标将查询出来的数据存入fruit_name和fruit_price这两个变量中，代码如下：

fetch cursor_fruit into fruit_name,fruit_price;

上面的示例中，将光标cursor_fruit中用SELECT语句查询出来的信息存入fruit_name和fruit_price中。fruit_name和fruit_price必须在前面已经定义。

• 关闭光标
  关闭光标的语法

close cursor_name{光标名称}

这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾关闭
【例】关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

MySQL光标只能在存储过程和存储函数中使用

10.1.6 流程控制的使用

MySQL中用来构造控制流程的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

• IF语句
  IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下：

if expr_condition then statement_list
[elseif expr_condition then statement_list]
[else  statement_list]
end if

IF实现了一个基本的条件构造。如果expr_condition求值为真（TRUE），相应的SQL语句列表被执行；如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句
【例】IF语句的示例，代码如下

if val is null
	then select 'val is null'
	else select 'val is not null';
end if;

• case语句
  CASE是另一个进行条件判断的语句，有两种格式。

1. 第一种格式：

case case_expr
	when when_value then statement_list
	[when when_value then statement_list]..
	[else statement_list]
end case

其中，case_expr参数表示条件判断的表达式，决定了哪一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句；statement_list参数表示不同when_value值的执行语句。
【例】使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等，语句如下：

case val
	when 1 then select 'val is 1';
	when 2 then select 'val is 2';
end case;

当val值为1时，输出字符串“val is 1”；当val值为2时，输出字符串“val is 2”；否则输出字符串“val is not 1 or 2”。

2. 第2种格式

case when expr_condition then statement_list
[when expr_condition then statement_list]..
[when expr_condition then statement_list]..
[else statement_list]
end case

expr_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应THEN关键字后面的statement_list语句。如果没有条件匹配，ELSE子句里的语句被执行。
【例】使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0，语句如下：

case 
	when val is null then select 'val is null';
	when val<0 then select 'val is less than 0';
	when val>0 then select 'val is greater than 0'
	else select 'val is 0';
end case;

• loop 语句
  LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。LOOP语句的基本格式如下：

[loop_label:] LOOP
	statement_list
END LOOP [Loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略；statement_list参数表示需要循环执行的语句。

【例】使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程，代码如下：

declare id int default 0;
add_loop:loop
set id=id+1;
	if id>=10 then leave add_loop;
	end if;
end loop add_loop;

该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，使用LEAVE语句退出循环。LOOP循环都以END LOOP结束。

• leave语句
  LEAVE语句用来退出任何被标注的流程控制构造，基本格式如下：

leave label

LEAVE语句用来退出任何被标注的流程控制构造，基本格式如下：
label参数表示循环的标志。LEAVE和BEGIN … END或循环一起被使用。

add_num:LOOP 
set @count=@count +1;
if @count=50 then leave add_num;
end loop add_num;

该示例循环执行count加1的操作。当count的值等于50时，使用LEAVE语句跳出循环。

• iterate 语句
  ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下：

iterate label

ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面
【例】

create procedure doiterate()
begin
declare p1 int default 0;
my_loop:loop
	set p1=p1+1;
	if p1<10 then iterate my_loop;
	elseif p1>20 then leave my_loop;
	end if;
	select 'p1 is between 10 and 20';
end loop my_loop;
end;

初始化p1=0，如果p1的值小于10时，重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息“p1 is between 10 and 20”；当p1大于20时，退出循环。

• repeate 语句
  REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。REPEAT语句的基本格式如下：

[repeat_label:] repeat
	statement_list
	until expr_condition
	end repeat [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。
【例】REPEAT语句示例，id值小于10时将重复执行循环过程，代码如下：

declare id int default 0;
repeat 
	set id=id+1;
	until id>=10;
end repeat;

该实例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。REPEAT循环都以END REPEAT结束。

• while 语句
  WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

[while_label:] while expr_condition 
Do
	statement_list
END WHILE [while_label]

while_label为WHILE语句的标注名称；expr_condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环
【例】WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下：

declare i INT default 0;
while i<10 do
set i=i+1;
end while;

10.2 调用存储过程和函数

10.2.1 调用存储过程

存储过程是通过CALL语句进行调用的，语法如下：

call sp_name([parameter[,...]])

CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。
【例】定义名为CountProc1的存储过程，然后调用这个存储过程。

use test_db;
# 定义存储过程
delimiter
create procedure CountProc1(IN sid INT, OUT num INT)
begin
select count(*) into num form fruits where s_id = sid;
end;
delimiter;

# 调用存储过程
call CountProc1(101,@num);

10.2.2 调用存储过程

用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

delimiter 
create function CountProc2 (sid INT)
returns int
begin 
return (select count(*) from fruits where s_id=sid);
end;
delimiter;

# 调用存储函数
select CountProc2(101);

如果在创建存储函数中报错“you mightwant to use the less safelog_bin_trust_function_creators variable”，需要执行以下代码：
set global log_bin_trust_function_creators=1;

10.3 查看存储过程和函数

• show status语句
  SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：

show {procedure|function} status [LIKE 'pattern']

这个语句是一个MySQL的扩展，返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，那么根据使用的语句，所有存储程序或存储函数的信息都会被列出。其中，PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。
【例】获取数据库中所有名称以字母‘C’开头的存储过程的信息

show procedure status like 'C%'\G

• 使用show create 语句

show create {procedure|function} sp_name

• 从information_schema.Routines 表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

select * from information_schema.Routines
where routine_name='sp_name';

ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。
【例1】从Routines表中查询名称为CountProc2的存储函数的信息

select * form information_schema.Routines where routine_name='CountProc2' and routine_type='FUNCTION' \G

10.4 修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性

alter {procedure|funtion} sp_name [characteristic..]

sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性

characteristic 可能取值：
●　CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。●　NO SQL，表示子程序中不包含SQL语句。●　READS SQL DATA，表示子程序中包含读数据的语句。●　MODIFIES SQL DATA，表示子程序中包含写数据的语句。●　MODIFIES SQL DATA，表示子程序中包含写数据的语句。●　SQL SECURITY { DEFINER | INVOKER}，指明谁有权限来执行。●　DEFINER，表示只有定义者自己才能够执行。●　INVOKER，表示调用者可以执行。●　COMMENT ‘string’，表示注释信息。

【例】修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

alter procedure CountProc
modifies sql data
sql security invoke;

10.5 删除存储过程和函数

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

drop {procedure|function} [if exists] sp_name;

这个语句被用来移除一个存储过程或函数。sp_name为要移除的存储过程或函数的名称。
【例】删除存储过程和存储函数，代码如下：

drop procedure CountProc;
drop function CountProc;