Mysql(十二)

存储过程和函数的引入

存储过程和函数是在数据库中定义一些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]