跳至主要內容

MySQL存储过程与存储函数

大约 10 分钟约 2868 字

存储过程和存储函数是指在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和存储函数来执行已经定义好的SQL语句,以避免开发人员重复编写相同的SQL语句,增加开发的效率,减少客户端和服务端的数据传输。

创建存储过程和存储函数

创建存储过程

创建存储过程的语法如下:

CREATE PROCEDURE proc_name([proc_parameter[...]])
[characteristic] routine_body

其中,proc_name代表存储过程的名称;proc_charameter代表存储过程的参数列表;characteristic代表存储过程的特性;routine_body代表SQL代码的内容。proc_parameter参数由三部分组成,分别为输入或输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数;OUT表示输出参数;INOUT表示既可作为输入函数又可作为输出函数;param_type表示存储过程参数名称;type参数指存储过程的参数类型,该类型可以为MySQL中的任意数据类型。一个存储过程包含名称、参数列表、还可以包含很多SQL语句集。

注:characteristic部分的语法如下。

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
参数说明
sp_name存储过程或存储函数的名称
characteristic指定存储函数的特性
CONTRAINS SQL表示子程序包含SQL语句,但不包含读写数据的语句
NO SQL表示子程序不包含SQL语句
READS SQL DATA表示子程序中包含读数据的语句
MODIFIES SQL DATA表示子程序中包含写数据的语句
SQL SECURITY指明权限执行。DEFINER表示只有定义者才能执行;INVOKER表示调用者才能执行
COMMENT ‘string’注释信息

输入参数、输出参数、输入输出参数:

  • in 输入参数,意思说你的参数要传到存过过程的过程里面去,在存储过程中修改该参数的值不能被返回

  • out 输出参数:该值可在存储过程内部被改变,并向外输出,一般在调用时使用全局变量,即在变量名前加一个“@”符号,此时这个变量就存储起来了

  • inout 输入输出参数,既能输入一个值又能传出来一个值

在MySQL中存储过程体以CREATE PROCECURE开始,后面紧跟存储过程的名称和参数。MySQL中的存储过程名称不区分大小写,且存储过程不能与MySQL中的内置函数同名。存储过程体的语句块以begin开始,以end结束,语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程体内部要以分号结束,所以在定义存储过程体之前都要新定义一个结束符号,并且应降低该字符在语句块中的出现概率。

下面我们先创建一个数据表,该数据表中只包含一个字段用于存储编程语言名称的字段,创建存储过程的语句如下。

delimiter //
create procedure proc_name(in parameter int)
begin
declare variable varchar(50);
if parameter = 1 then
set variable = 'MySQL';
elseif parameter = 2 then
set variable = 'Java';
else set variable = 'Python';
end if;
insert into tb values (variable);
end
//

在此例中,我们将parameter作为输入量,然后通过判断parameter的值来改变变量的值,最后使用INSERT INTO语句实现快速插入数据的操作。

运行结果
运行结果

MySQL中默认的语句结束符为分号,存储过程中的SQL语句需要使用分号结束.为了避免冲突,首先用“delimiter //”将MySQL的结束符设置为”//“,再用“delimiter ;”来将结束符设置成分号。

创建存储函数

创建存储函数的语法和创建存储过程的语法类似,语法如下:

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

其中sp_name为存储函数的名称,func_parameter为存储函数的参数列表,RETURNS type指定返回值的类型,characteristic指定存储函数的特性,routine_body代表SQL代码内容。

下面仅演示如何创建存储函数。

delimiter //
create function func(i int)
returns varchar(50)
begin
if i=1 then return 'Java';
elseif i=2 then return 'Python';
else return 'MySQL';
end if;
end;
//

若MySQL提示:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 需要在MySQL的配置文件中找到[mysqld]位置,在该行下面设置log-bin-trust-function-creators=1。

运行结果
运行结果

变量的应用

局部变量

局部变量以关键字DECLARE声明,语法如下:

DECLARE variable_name type [DEFAULT value]

其中variable_name表示局部变量名,type表示类型,DEFAULT value可选,表示为局部变量设置默认值。

如:

delimiter //
create procedure p1()
begin
declare i varchar(10) default 'outer';
begin
declare i varchar(10) default 'inner';
select i;
end;
select i;
end;
//
运行结果
运行结果

全局变量

全局变量比较于局部变量,只需要在前面加上一个“@”符号。

delimiter //
create procedure p2()
begin
set @a=1;
begin
set @a=2;
select @a;
end;
select @a;
end;
//
运行结果
运行结果

为变量赋值

MySQL中可以使用关键字DECLARE来声明变量,可以使用关键字SET为变量赋值,可以同时为多个变量赋值,各个变量的赋值语句用“,”隔开。

另外,MySQL还支持使用下面的语句结构为变量赋值:

SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition;

其中col_name代表查询的字段名称;var_name代表变量的名称;table_name代表表的名称,如从stu表中查询name为‘张三’的记录,并将记录中的tel赋值为局部变量i,那么代码如下:

select tel into i from stu where name = '张三';

光标的应用

在MySQL中查询记录,其结果可能位多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。

光标的使用包括以下步骤:

声明光标

光标只能在存储过程中定义。声明光标使用关键字DECLARE,语法如下。

DECLARE cursor_name CURSOR FOR select_statement;

其中,cursor_name为光标的名称,光标使用和表是同样的规则;select_statement是一个SELECT语句,返回一行或多行数据,该语句也可以在存储过程中定义多个光标,但是必须保证光标的名称不能相同。

打开光标

在声明光标之后,若要使用光标需要先打开光标。打开光标的语法如下。

OPEN cursor_name;

使用光标

顺利打开光标后,可以使用FETCH...INTO语句读取数据,语法如下。

FETCH cursor_name INTO var_name[,var_name]...

关闭光标

使用CLOSE关键字关闭光标,语法如下。

CLOSE cursor_name;

现在举一个实例——更新表中信息:在员工表中将name字段由‘姓名’改成‘id-姓名’:

首先查看表中数据。

运行结果
运行结果

然后我们创建如下的存储过程。

delimiter //
create procedure proc()
begin
    declare e_id int;  # 存储字段id
    declare e_name varchar(50);  # 存储字段name
    declare isDone int default 0;  # 存储是否结束的标志
    declare cur_proc cursor for select id as e_id,name as e_id from employees;  # 声明光标
    declare continue handler for not found set isDone = 1;
    # 若光标没有成功查找到数据就将结束表示设为1
    open cur_proc;  # 开启光标
    while not isDone do  # 循环,若结束标志为0则继续循环
        fetch cur_proc into e_id,e_name;  # 使用光标,赋值
        update employees set name = concat(e_id,'-',e_name) where id = e_id; # 连接
        end while;
    close cur_proc; # 关闭光标
end //
运行结果
运行结果

然后调用存储过程,最后再查看表的数据。

运行结果
运行结果

可以看到表的数据被改动了,说明达到目的了。

调用存储过程和存储函数

调用存储过程

存储过程的调用在前面的示例中已多次使用。使用CALL关键字调用存储过程,然后存储过程会被执行,然后将结果输出给返回值。语法如下。

CALL sp_name([parameter[,...]]);

其中sp_name代表存储过程的名称,parameter是参数,且参数不能省略。

MySQL中,若存储过程的参数是OUT或者INOUT作为输出参数使用时,我们一般在传递参数的时候将全局变量作为参数,如我们定义一个如下的存储过程: “create procedure proc(out i int) begin ... end //” 那么我们在调用的时候这样调用:“call proc(@a) //” 这样调用后就会在MySQL中生成这个全局变量@a,之后我们使用“select @a;” 或者其他的语句就可以使用这个值了。

如我们先定义一个如下的存储过程,然后调用此存储过程。调用存储过程的语句如下:

call count_of_student(@total) //
select @total //
运行结果
运行结果

可以看到@total的值为4,说明employees表中存在4条数据行。

调用存储函数

存储函数的调用和MySQL的内置函数基本相同。语法如下:

SELECT function_name([parameter[,...]]);

如我们定义如下的存储函数,然后调用此存储函数。调用存储函数的语法如下:

select search_name(1) //
运行结果
运行结果

查看存储过程和存储函数

SHOW STATUS语句

使用SHOW STATUS语句查看存储过程和存储函数的语法如下:

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

如查看db数据库中所有的存储过程:

show procedure status;

查看所有以字母p开头的存储过程名:

show procedure status like 'p%';

SHOW CREATE语句

通过SHOW CREATE语句查看存储过程和存储状态的语法如下:

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

sp_name代表存储过程或者存储函数的名称。

如查询count_of_student的定义:

show create procedure count_of_student //
运行结果
运行结果

查询结果显示存储过程的定义、字符集等信息。

SHOW STATUS语句只能查看存储过程或存储函数所操作的数据库对象,如存储过程或存储函数的名称、类型、定义者、修改时间等信息,并不能查询存储过程或存储函数的具体定义。若要查看详细定义,可以使用SHOW CREATE语句。

修改存储过程和存储函数

使用ALTER关键字修改存储过程或存储函数的语法如下:

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
参数说明
sp_name存储过程或存储函数的名称
characteristic指定存储函数的特性
CONTRAINS SQL表示子程序包含SQL语句,但不包含读写数据的语句
NO SQL表示子程序不包含SQL语句
READS SQL DATA表示子程序中包含读数据的语句
MODIFIES SQL DATA表示子程序中包含写数据的语句
SQL SECURITY指明权限执行。DEFINER表示只有定义者才能执行;INVOKER表示调用者才能执行
COMMENT ‘string’注释信息

删除存储过程和存储函数

使用DROP PROCEDURE来删除存储过程,使用DROP FUNCTION删除存储函数,语法如下:

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;
上次编辑于:
贡献者: 棋.