存储过程和函数的引入
存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用执行这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且。存储过程和函数在Mysql服务器中的存储和执行,减少客户端和服务器的数据传输。
创建存储过程和函数
创建存储过程
CREATE PROCEDURE sp_name([proc_procameter[,.....]])
[characteristic....] routine_body
@total全局变量
sp_name参数是存储过程的名称;
proc_pramente参数表示存储过程的参数列表;
characteristice参数指定存储过程的特性;
Routine_body参数是SQL代码的内容,可以用BEGIN....END来标志SQL代码的开始和结束。
[IN|OUT|INOUT]param_name type
其中,IN表示输入参数;OUT表示输出参数;INOUT表示既可以表示输出,又可以表示输入;param_name参数是存储过程的名称;type参数指定存储过程的参数类型,该类型可以是Mysql数据的任意类型;
characteristic参数有多个取值。其取值说明如下:
LANGUAGE SQL:说明routine_body部分是由SQL语言语句组成,这也是数据库系统默认的语言;
[NOT]DETERMINISTIC:指明存储过程的执行结果是否确定。DETERMINSTIC表示结果是确定的。每一次执行存储过程时,相同的输入会得到相同的输出结果。NOT DETERMINSTIC表示结果是不确定的,相同的输入可能得到不同的输出。默认情况下是非确定的。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:指明子程序使用SQL语句的限制;CONTAINS SQL表示子程序包含SQL语句,但是不包含读或写数据的语句;NO SQL 表示子程序中不包含SQL语句;READS SQL DATA表示子程序中包含读数据的语句;MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下,系统会指定CONTAINS SQL;
SQL SECURITY{DEFINER|INVOKER};指明谁有权限来执行。DEFINER表示只有定义自己才能够执行;INVOKER表示调用者可以执行。默认情况下,系统指定的权限是DEFINER.
COMMENT’string’:注释信息;
创建存储函数
CREATE FUNCTION sp_name([func_parameter])
RETURNS type
[characteristic....] routine_body
Sp_name参数是存储函数的名字;
func_parameter表示存储函数的参数列表;
RETURNS type指定返回值的类型;
characteristic参数指定存储过程的特性,该参数的取值与存储过程是一样的;routine_body参数是SQL代码的内容。可以用BEGN...END来标志代码的开始和结束;
Func_paramenter可以由多个参数组成,其中每一个参数由参数名称和参数类型组成,其形式如下:
Param_name type 其中,param_name参数是存储函数的参数名称;type参数指定存储函数的参数类型,该类型可以是Mysql数据库的任意数据类型;
变量的使用
定义变量
DECLARE var_name[,.....] type[DEFAULT value]
为变量赋值
SET var_name = expr[,var_name = expr]....
SELECT col_name[,....] INTO var_name[,....]
FROM table name WHERE condition
游标的使用
查询语句可能查询出多条记录,在存储过程和函数中使用游标来逐条读取查询结果集中记录的记录。游标的使用包括声明游标、打开游标、使用游标和隔壁游标。游标必须声明在处理程序之前,并且声明在变量和条件之后。
声明游标
DECLARE cursor_name CURSOR FOR select_statement;
打开游标
OPEN cusor_name;
使用游标
FETCH cursor_name INTO var_name[,var_name...];
关闭游标
CLOSE coursor_name;
流程控制的使用
存储过程和函数可以使用流程控制来控制执行语句。Mysql中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]....
[ELSE statement_list]
END IF
CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]....
[ELSE statement_list]
END CASE
LOOP,LEAVE语句
LOOP语句可以使某些特定的语句重复执行,实习一个简单的循环。但是Loop语句本身没有停止循环的语句,必须遇到LEAVE语句等才能停止循环。Loop语句的语法的基本形式如下:
[begin_label: ]LOOP
Statement_list
END LOOP[end_label]
LEAVE语句主要用于跳出循环控制。语法形式如下:
LEAVE label
ITERATE语句
ITERATE语句也是用来跳出循环语句。但是,ITERATE是跳出本次循环语句。REPEAT语句的基本语法形式如下:
[begin_lable:]REPEAT
Statement_list
UNTIL search_condition
END REPEAT[end_label]
WHILE语句
[bengin_label:]WHILE_search_condition DO
Statenment_list
END WHILE[end_label]