MySql的存储过程-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySql的存储过程

简介: 存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的。

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

创建存储过程和存储函数

数据库系统中,为了保证数据的完整性、一致性,同时也为提高其应用性能,大多数据库常采用存储过程和存储函数技术。存储过程和存储函数经常是一组SQL语句的组合,这些语句被当作整体存入MySQL数据库服务器中。用户定义的存储函数不能用于修改全局库状态,但该函数可从查询中被唤醒调用,也可以像存储过程一样通过语句执行。

存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,……]]) [characteristic……] routine_body

sp_name参数是存储过程的名称;

proc_parameter表示存储过程的参数列表,proc_parameter中的参数由3部分组成,它们分别是输入输出类型参数名称参数类型。其形式为[IN|OUT|INOUT] param_name type。其中IN表示输入参数;OUT表示输出参数;INOUT表示既可以输入也可以输出;param_name参数是存储过程参数名称;type参数指定存储过程的参数类型,该类型可以为MySQL数据库的任意数据类型;

characteristic参数指定存储过程的特性;

routine_body参数是SQL代码的内容,可以用BEGIN..END来标识SQL代码的开始和结束。

由于存储过程内部语句要以分号结束,所以在定义存储过程前,应将语句结束标志“;”更改为其他字符,
并且应降低该字符在存储过程中出现的概率,更改结束标志可以用关键字“delimiter”定义
delimiter//
create
procedure proc_name (in parameter integer)
begin declare variable varchar20); if parameter=1 then set variable='MySQL' else set variable='PHP' end if insert into tb (name) values (variable); end;
//

存储函数

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

func_parameter可以由多个参数组成,其中每个参数均由参数名称和参数类型组成,其结构如下:

param_name参数是存储函数的函数名称;type参数用于指定存储函数的参数类型。该类型可以是MySQL数据库所支持的类型。

delimiter//
create function name_of_student(std_id INTreturns varchar50begin
        returnselect name from studentinfo where sid=std_id);
    end
//  

变量的应用

1.局部变量

局部变量以关键字declare声明,后跟变量名和变量类型,例如:

declare a int

当然在声明局部变量时也可以用关键字default为变量指定默认值,例如:

declare a int default 10

delimiter//
create procedure p1()
    begin
        declare x char10default'outer'begin
            declare x char10default'inner'select x;
        endselect x;
    end//

2.全局变量

MySQL中的会话变量不必声明即可使用,会话变量在整个过程中有效,会话变量名以字符“@”作为起始字符。下述代码为会话变量的使用方法。

设置全局变量的代码如下:
delimiter//
create procedure p2()
    begin
        set@t=1begin
            set@t=2select@tendselect@tend//

3.为变量赋值

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

DECLARE var_name ,…… type [DEFAULT value]

DECLARE是用来声明变量的;var_name参数是设置变量的名称。如果用户需要,也可以同时定义多个变量;type参数用来指定变量的类型;DEFAULT value的作用是指定变量的默认值,不对该参数进行设置时,其默认值为NULL。

MySQL中可以使用SET关键字为变量赋值。SET语句的基本语法如下:

SET var_name1=expr1,var_name2=expr2……

SET关键字是用来为变量赋值;var_name参数是变量的名称;expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用“,”隔开。例如:为变量mr_soft赋值,代码如下:

SET mr_soft=10;

另外,MySQL中还可以应用另一种方式为变量赋值。其语法结构如下:

SELECT col_name,…… INTO var_name,…… FROM table_name where condition

其中,col_name参数标识查询的字段名称;var_name参数是变量的名称;table_name参数为指定数据表的名称;condition参数为指定查询条件。

例如:从studentinfo表中查询name为“LeonSK”的记录。将该记录下的tel字段内容赋值给变量customer_tel。其关键代码如下:

SELECT tel INTO customer_tel FROM studentinfo WHERE name='LeonSK';

说明:上述赋值语句必须存在于创建的存储过程中,且需将赋值语句放置在BEGIN……END之间。若脱离此范围,该变量将不能使用或被赋值。

光标的运用

通过MySQL查询数据库,其结果可能为多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。光标使用包括声明光标(DECLARE CURSOR)、打开光标(OPEN CURSOR)、使用光标(FETCH CURSOR)和关闭光标(CLOSE CURSIR)。值得一提的是,光标必须声明在处理程序之前,且声明在变量和条件之后。

1.声明光标

在MySQL中,声明光标仍使用DECLARE关键字,其语法如下:

DECLARE cursor_name CURSOR FOR select_statement

cursor_name是光标的名称,光标名称使用与表名同样的规则;select_statement是一个SELECT语句,返回一行或多行数据。其中,这个语句也可以在存储过程中定义多个光标,但是必须保证每个光标名称的唯一性,即每一个光标必须有自己唯一的名称。

通过上述定义来声明光标info_of_student,代码如下:

DECLARE info_of_student CURSOR FOR

SELECT

sid, name, age, sex, age

FROM studentinfo

WHERE sid=1;

这里SELECT子句中不能包含INTO子句,并且光标只能在存储过程或存储函数中使用。

2.打开光标

在声明光标之后,要从光标中提取数据,必须首先打开光标。在MySQL中,使用OPEN关键字来打开光标。其基本的语法如下:

OPEN cursor_name

其中,cursor_name参数表示光标的名称。在程序中,一个光标可以打开多次。由于可能在用户打开光标后,其他用户或程序正在更新数据表。所以可能会导致用户在每次打开光标后,显示的结果都不同。

打开上面已经声明的光标info_of_student,其代码如下:

OPEN info_of_student

3.使用光标

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

FETCH cursor_name INTO var_name1,var_name2.....

其中,cursor_name代表已经打开光标的名称;var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name是存放数据的变量名,必须在声明光标前定义好。FETCH……INTO语句与SELECT……INTO语句具有相同的意义。

将已打开的光标info_of_student中SELECT语句查询出来的信息存入tmp_name和tmp_tel中。其中tmp_name和tmp_tel必须在使用前定义。

代码如下:

FETCH info_of_student INTO tmp_name, tmp_tel;

4.关闭光标

光标使用完毕后,要及时关闭,在MySQL中采用CLOSE关键字关闭光标,其语法格式如下:

CLOSE cursor_name

cursor_name参数表示光标名称。下面关闭已打开的光标info_of_student。代码如下:

CLOSE info_of_student

对于已关闭的光标,在其关闭之后则不能使用FETCH来使用光标。光标在使用完毕后一定要关闭。

流程控制语句

在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。其中包括IF语句CASE语句LOOP语句WHILE语句ITERATE语句LEAVE语句,它们可以进行流程控制。

IF语句

IF语句用来进行条件判断,根据不同的条件执行不同的操作。该语句在执行时首先判断IF后的条件是否为真,则执行THEN后的语句,如果为假则继续判断IF语句直到为真为止,当以上都不满足时则执行ELSE语句后的内容。IF语句表示形式如下:

IF condition THEN
……
[ELSE condition THEN]
……
[ELSE]
……
ENDIF

CASE语句

case语句为多分支语句结构,该语句首先从when后的value中查找与case后的value相等的值,如果查找到则执行该分支的内容,否则,执行else后的内容。case语句表示形式如下:

CASE x
WHEN value THEN……
[WHEN value THEN……]
[ELSE……]
END CASE
其中,x参数表示条件判断的变量;WHEN……THEN中的value参数表示变量的取值。

WHILE循环语句

while循环语句执行时首先判断condition条件是否为真,如果是,则执行循环体,否则退出循环。该语句表示形式:

while condition do
……
end while

LOOP循环语句

该循环没有内置的循环条件,但可以通过leave语句退出循环。loop语句表示形式:

loop
……
end loop

LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,其中中间省略的部分是需要重复执行的语句。在循环内的语句一直重复直至循环被退出,退出循环应用LEAVE语句。

LEAVE语句

LEAVE语句经常和BEGIN……END或循环一起使用,其结构如下:

LEAVE label

label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

delimiter//
    create procedure example_loop(out sum intbegin
            declare i int default 1declare s int default 0loop_label:loop
                set s=s+i;
                set i=i+1if i>100 then
                    leave loop_label;
                end ifend loop;
            set sum=s;
        end
//

call example_loop(@s)

select @s

REPEAT循环语句

该语句先执行一次循环体,之后判断condition条件是否为真,则退出循环,否则继续执行循环。repeat语句表示形式:

REPEAT
……
UNTIL condition
END REPEAT

ITERATE语句

它可以出现在LOOP、REPEAT和WHILE语句内,其意为“再次循环”。

ITERATE label

该语句的格式与LEAVE大同小异,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。

注意,与一般程序设计流程控制不同的是:存储过程并不支持FOR循环。

调用存储过程和存储函数

调用存储过程

CALL sp_name([parameter,……]);

其中,sp_name是存储过程的名称;parameter是存储过程的参数。

调用存储函数

在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法基本相同,用户自定义的存储函数与MySQL内部函数性质相同。

SELECT function_name([parameter,……]);

查看存储过程和函数

创建存储过程和函数以后,用户可以查看存储过程和函数的状态和定义。用户可以通过SHOW STATUS语句查看存储过程和函数状态,也可以通过SHOW CREATE语句来查看存储过程和函数的定义。

在MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法结构如下:

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

其中,PROCEDURE参数表示查询存储过程;FUNCTION参数表示查询存储函数;LIKE 'pattern'参数用来匹配存储过程或函数名称。

MySQL中可以通过SHOW CREATE语句来查看存储过程和函数的状态。其语法结果如下:

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

其中,PROCEDURE参数表示存储过程;FUNCTION参数表示查询存储函数;sp_name参数表示存储过程或函数的名称。

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

修改存储过程和函数

修改存储过程和存储函数是指修改已经定义好的存储过程和函数。在MySQL中可通过ALTER PROCEDURE语句来修改存储过程,通过ALTER FUNCTION语句来修改存储函数。

在MySQL中修改存储过程和函数的语句的语法形式如下:

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic……] 
characteristic:{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}|COMMENT'string'

ALTER FUNCTION name_of_student READS SQL DATA COMMENT'FIND NAME';

SELECT SPCIFIC_NAME, SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='name_of_student';

删除存储过程和函数

删除存储过程和函数指删除数据库中已经存在的存储过程或函数。在MySQL中可以使用DROP PROCEDURE语句来删除存储过程,通过DROP FUNCTION语句来删除存储函数。在删除之前,必须确认该存储过程或函数没有任何依赖关系,否则可能会导致其他与其关联的存储过程无法运行。

删除存储过程和函数的语法如下:

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

其中,sp_name参数表示存储过程或函数的名称;IF EXISTS是MySQL的扩展,判断存储过程或函数是否存在,以免发生错误。

当返回结果没有提示警告或报错时,则说明存储过程或存储函数已经被顺利删除。

捕获存储过程中的错误

在用户执行某些程序时,可能会产生一些问题,为了增强程序本身处理问题的能力,避免程序因异常而终止运行。我们往往在处理程序执行前,预测程序在执行过程中可能出现或遇到的问题。定义条件和处理程序来提示用户的同时,也为用户提出解决办法。MySQL通过DECLARE关键字来定义条件和处理程序。

定义条件

在MySQL中,应用DECLARE语句定义条件。其语法格式如下:

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value  |   mysql_error_code

其中,condition_name参数表示条件名称;condition_value参数表示类型:sqlstate_value参数可以表示MySQL的错误。另外也可以应用mysql_error_code来表示错误代码。其中第一种定义方法格式如下:

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';

第二种定义格式如下:

DECLARE can_not_find CONDITION FOR 1146

说明:两种表示MySQL错误的方法名称都为can_not_find,不同点只是获取的错误提示码不同。第一种方法设置sqlstate_value值为42S02,第二种方法设置mysql_error_code值为1146。

定义处理程序

在MySQL中可以使用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  | SQLEXCEPTION | mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,指定的语句被执行。

handler_type参数指明错误的处理方式,该参数有3个取值。它们分别是:

对于一个CONTINUE处理程序,当前子程序的执行在执行处理程序语句之后继续。

对于EXIT处理程序,当前BEGIN……END复合语句的执行被终止。

UNDO表示遇到错误后撤回之前的操作,MySQL不支持该处理方式。

condition_value参数指定错误类型,该参数有6个取值。

sqlstate_value:表示MySQL的错误。

mysql_error_code:表示错误代码。

condition_name:DECLARE定义的条件名称。

SQL WARNING:表示所有以01开头的sqlstate_value值。

NOT FOUND:表示所有以02开头的sqlstate_value值。

SQLEXCEPITION:表示所有没有被SQL WARNING或NOT FOUND捕获的sqlstate_value值。

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章