存储过程和函数是在数据库中定义一些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 varchar(20); 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 INT) returns varchar(50) begin return(select 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 char(10)default'outer'; begin declare x char(10)default'inner'; select x; end; select x; end; //
2.全局变量
MySQL中的会话变量不必声明即可使用,会话变量在整个过程中有效,会话变量名以字符“@”作为起始字符。下述代码为会话变量的使用方法。
设置全局变量的代码如下: delimiter// create procedure p2() begin set@t=1; begin set@t=2; select@t; end; select@t; end; //
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 int) begin declare i int default 1; declare s int default 0; loop_label:loop set s=s+i; set i=i+1; if i>100 then leave loop_label; end if; end 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值。