目录
一、存储过程和函数概述
存储过程和函数具有以下优点：
1.允许标准组件式编程：存储过程和函数在创建后可以在程序中被多次调用，有效提高了SQL语句的重要性、共享性和可移植性。
2.较快的执行速度：如果某一操作包含大量的事务处理代码，并且被多次执行，那么存储过程要比批处理的执行执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器会对其进行分析优化，并将最终执行计划存储在系统中，而批处理的事务处理语句在每次运行时都要进行编译和优化。
3.减少网络流量：对于大量的SQL语句，将其组织成存储过程，会比一条一条的调用SQL语句要大大节省网络流量，降低网络负载。
4.安全：数据库管理员通过设置执行某一存储过程的权限，从而限制相应数据的访问权限，避免非授权用户对数据的访问，保证数据的安全。
存储过程和函数也存在一定的缺陷：
1.存储过程和函数的编写比单个SQL语句的编写要复杂很多，需要用户具有更高的技能和更丰富的经验。
2.在编写存储过程和函数时，需要创建这些数据库对象的权限。
二、创建并调用存储过程和函数
存储程序可以分为存储过程和函数。存储过程和函数的操作主要包括创建存储过程和函数、调用存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。
1.创建存储过程
创建存储过程使用SQL语句CREATE PROCEDURE来实现，其语法形式如下：
CREATE PROCEDURE proc_name([proc_parameter[,...]])
[characteristic...] routine_body
（1）CREATE PROCEDURE为创建存储过程的关键字。
（2）proc_name表示要创建的存储过程名。
（3）proc_parameter表示存储过程的参数，参数形式如下：
[IN|OUT|INOUT] parameter_name TYPE
其中IN表示输入参数，可把外界的数据存储到存储过程当中；OUT表示输出参数，可把存储过程的运算结果传递到外界；INOUT表示输入输出参数，既可以把外界的数据传递到存储过程当中，又可以把存储过程的运算结果传递到外界；parameter_name表示参数名；TYPE表示参数的数据类型。
注：存储过程中的参数名不要与数据表中的字段名重复，否则系统会报错。
（4）characteristic表示存储过程的特性，可取值及其意义如下：
LANGUAGE SQL：表示存储过程的routine_body部分使用SQL语言编写，当前系统支持的语言为SQL。
[NOT]DETERMINISTIC：DETERMINISTIC表示存储过程的执行结果是确定的，就是每次输入相同的参数并执行存储过程后，得到的结果是相同的；默认为NOT DETERMINISTIC，表示执行结果不确定，即相同的输入可能得到不同的结果。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。CONTAINS SQL为默认值，表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序不包含SQL语句；READS SQL DATA表示子程序包含读取数据的语句，但不包含写数据的语句；MODIFIES SQL DATA表示子程序包含写入数据的语句。
SQL SECURITY {DEFINER|INVOKER}：指定可执行存储过程的用户，DEFINER表示只有创建者才能执行，INVOKER表示拥有权限的调用者可以执行。
COMMENT ‘string’：表示存储过程或者函数的注释信息。
（5）routine_body表示需要执行的SQL语句的集合，可以使用BEGIN表示开始，使用END表示结束。
2.创建存储函数
创建存储函数使用SQL语句CREATE FUNCTION来实现，其语法形式如下：
CREATE FUNCTION func_name([parameter_name[,...]])
RETURNS TYPE
[characteristic...] routine_body
CREATE FUNCTION为创建存储函数的关键字；func_name表示存储函数名；parameter_name表示存储函数参数名；TYPE表示函数返回值的数据类型；characteristic指定存储函数的特性，取值与创建存储函数参数名；routine_body表示函数体。
3.调用存储过程和函数
存储过程必须使用关键字CALL调用，而存储函数与MySQL内置函数的调用相同，使用关键字SELECT。
1.调用存储过程
通常使用关键字CALL调用存储过程，其语法形式如下：
CAL procedure_name({parameter[,...]});
其中的parameter表示变量名，存储过程的返回值将赋予该变量。
2.调用存储函数
通常使用关键字SELECT调用存储函数，其语法形式如下：
SELECT function_name([parameter[,...]]);
三、关于存储过程和函数的表达式
1.变量
变量是表达式中最基本的元素，可用于存储临时数据。
1.变量的分类
用户变量：带有前缀@，只能被定义它的用户使用，作用于当前整个连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。
局部变量：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN...END中。其作用域仅限于该语句块，在语句块执行完毕后，局部变量就会被释放。局部变量使用前需要先通过DECLARE声明。如果没有声明，则初始值为NULL。
系统变量：带有前缀@@，MySQL有许多已经设置默认值的系统变量。系统变量包含全局变量和会话变量。全局变量会影响整个服务器，而会话变量只影响个人客户端连接。
2.在存储过程和函数中应用变量
局部变量可以在子程序中定义并应用，其作用范围是BEGIN...END语句块。
（1）定义变量
在存储过程中使用DECLARE语句定义局部变量，其语法形式如下：
DECLARE var_name[,...] type [DEFAULT value];
上述语句中，var_name为局部变量名称，type为变量的数据类型，DEFUALT value是为变量指定的默认值。如果没有DEFAULT value，初始值为NULL。
例如，定义一个INT类型的变量，名称为var1：
DECLARE var1 INT;
注：变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，DECLARE语句在存储过程和函数中使用时，必须出现在BEGIN...END语句块的最前面，并且变量名不区分大小写。可以一次声明多个相同类型的变量。
（2）为变量赋值
定义变量之后，可以使用SET关键字为变量赋值，语法形式如下：
SET var_name = expr [,var_name = expr]...;
变量值可以为常量或者表达式。
另外，也可以使用SELECT...INTO...查询语句将查询结果赋给变量，这要求查询结果必须只有一行，具体语法形式如下：
SELECT col_name[,......] INTO var_name[,......] FROM table_name;
Col_name为字段名，var_name为变量名。
2.定义条件和处理程序
条件和处理程序是MySQL提供的一种异常处理机制，定义条件是事先定义程序执行过程中可能会遇到的问题；定义处理程序是定义在遇到问题时执行的相应的处理方法，并且保证存储过程和函数在遇到问题时不终止。
1.定义条件
在MySQL中定义条件使用DECLARE...CONDITION语句，其语法形式如下：
DECLARE condition_name CONDITION FOR [condition_type];
上述语句中，condition_name表示条件名。Condition_type表示条件的类型，其可取值及其意义如下：
Mysql_error_code：表示数值类型错处代码。
Sqlstate_value：表示长度为5的字符串类型错误代码。
注：数值类型的错误代码不要使用0，因为0表示成功而不是错误；字符串类型的错误代码不要使用‘00’，因为‘00’表示成功而不是错误。
2.定义处理程序
在定义条件之后，可以使用DECLARE...HANDLER语句定义处理程序，语法形式如下：
DECLARE handler_type HANDLER FOR condition_value[...] statement
下面简单介绍上述语句中各组成部分及其意义。
（1）handler_type为异常处理方式，可取值及其意义如下：
CONTINUE：表示遇到错误不处理，程序继续执行。
EXIT：表示遇到错误立即退出程序。
UNDO：表示遇到错误后撤回之前的操作（目前MySQL暂不支持）。
（2）condition_value表示错误值，可取值及其意义如下：
Mysql_error_code：表示数值类型错处代码。
Sqlstate_value：表示包含5个字符串类型错误值。
Condition_name：表示使用DECLARE...CONDITION语句定义的条件名。
SQLWARNING：匹配所有01开头的SQLSTATE错误代码。
NOT FOUND：匹配所有02开头的SQLSTATE错误代码。
SQLEXCEPTION：匹配所有未被SQLWARNING和NOT FOUND捕获的SQLSTATE错误代码。
（3）statement为程序语句段，表示在遇到定义的异常条件时，需要执行的存储过程或函数。
定义处理程序有以下6中方法：
方法一：捕获sqlstate_value
方法二：捕获mysql_error_code
方法三：先定义条件，然后再调用条件
方法四：使用SQLWRENING
方法五：使用NOT FOUND
方法六：使用SQLEXCEPTION
3.游标的使用
在存储过程和函数中，当查询语句返回多条记录时，可以使用游标对结果集进行逐条读取。
1.定义游标
在MySQL中，使用DECLARE关键字来定义游标，其语法形式如下：
DECLARE cursor_name CURSOR FOR select_statement;
上述语句中，cursor_name表示游标名，select_statement表示SELECT语句，返回一个用于创建游标的结果集。
2.打开游标
打开游标的关键字为OPEN，其语法形式如下：
OPEN cursor_name;
注：在打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。
3.使用游标
使用游标的关键字是FETCH，其语法形式如下：
FETCH cursor_name INTO var_name [,var_name] ...
上述语句的作用是将定义游标cursor_name时查询出的数据赋予变量var_name。
4.关闭游标
关闭游标的关键字为CLOSE，其语法形式如下：
CLOSE cursor_name;
4.流程控制的使用
流程控制语句是指可以控制程序运行顺序的指令，程序运行顺序主要包括顺序执行、条件执行和循环执行。MySQL支持的流程控制语句包括IF语句、CASE语句、LOOP语句、REPEAT语句、WHILE语句、LEAVE语句、ITERATE语句和RETURN语句。
1.IF语句
IF实现条件判断，语句中可以包含多个判断条件，系统会根据条件的结果是否为TRUE执行相应的操作，语法形式如下：
IF search_condition THEN statement_list
[ELSEIT search_condition THEN statement_list]...
[ELSE statement_list]
END IF
上述语句中，search_condition为判断条件，statement_list为相应操作，如果所有判断条件均不为TRUE，则执行ELSE子句中的操作。
2.CASE语句
CASE语句可以实现比IF语句更复杂的条件操作，该语句有两种使用形式。
第1种语法形式如下：
CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
上述语句中，case_expr表示判断条件的表达式，将此表达式与每个WHEN子句中的when_value值进行比较，直到与其中一个相等，此时，执行相应THEN子句中的statement_list。如果表达式与所有when_value值都不相等，则执行ELSE子句中的statement_list。
第2种语法形式如下：
CASE case_expr
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list]...
[ELSE statement_list]
END CASE
上述语句中，系统会对每个WHEN子句中的search_condition表达式进行判断，直到某个search_condition表达式为TRUE，此时将执行其对应的THEN子句中的statement_list。如果所有search_condition表达式的值都不为TRUE，则执行ELSE子句中的statement_list。
3.LOOP语句和LEAVE语句
LOOP语句可以实现简单的循环，使得系统能够重复执行循环结构内的语句列表。该语句列表由一条或多条语句组成，每条语句使用（；）隔开。语法形式如下：
[loop_label:]LOOP
Statement_list
END LOOP[end_list]
上述语句中，loop_list表示LOOP语句的标注名称（可以省略），statement_list表示需要循环执行的SQL语句。
如果不在statement_list中增加退出循环的语句，LOOP语句可以实现简单的死循环。使用LEAVE语句可以退出循环。语法形式如下：
LEAVE label;
其中，label参数表示循环的标注名。
4.REPEAT语句
REPEAT语句可以实现一个带条件判断的循环结构。语法形式如下：
[repeat_label:]REPEAT
Statement_list
UNTIL search_condition
END REPEAT [repeat_label]
repeat_label表示REPEAT语句的标注名称（可以省略），每次SQL语句statement_list执行完毕后，会对条件search_condition进行判断，如果结果为TRUE，循环结束，否则继续执行循环中的语句。
5.WHILE语句
WHILE语句同样可以实现一个带条件判断的循环结构，但与REPEAT语句不同的是，WHILE语句会先对条件进行判断，如果为TRUE，才会执行需要循环的操作，否则终止循环，语法形式如下：
[while_label:]WHILE search_condition DO
Statement_list
END WHILE[while_label]
上述语句中，while_label为WHILE语句的标注名称，search_condition为判断条件，statement_list为需要循环的操作。
6.ITERATE语句
ITERATE语句只可以出现在LOOP语句、REPEAT语句和WHILE语句中，意义为再次执行循环，语法形式如下：
ITERATE label;
上述语句中，label表示循环的标志。
四、查看存储过程和函数
1.查看存储过程和函数的状态
使用SHOW STATUS语句可以查看存储过程和函数的状态。基本语法形式如下：
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pf_name’];
PROCEDURE或FUNCTION指定查看的是存储过程还是函数，LIKE语句指定存储过程和函数的名称。
主要参数及其意义如下：
Db：表示存储过程或函数所属数据库。
Name：表示存储过程或函数名。
Type：表示是存储过程还是函数。
Definer：表示创建存储过程或函数的用户。
Modified：表示最后修改日期。
Created：表示创建日期。
Security_type：表示MySQL在执行存储过程和函数的时候，是以创建函数的权限来执行，还是以调用者的权限来执行。
2.查看存储过程和函数的定义
使用SHOW CREATE语句可以查看存储过程和函数的定义语句，语法形式如下：
SHOW CREATE {PROCEDURE|FUNCTION} pf_name;
PROCEDURE或FUNCTION指定查看的是存储过程还是函数，pf_name指定存储过程或函数名。
主要参数及其意义如下：
Procedure：表示存储过程名。
Sql_name：表示SQL语句的模式。
Create Procedure：表示存储过程的定义语句。
3.查看存储过程和函数的信息
在MySQL中，存储过程和函数的信息存储在系统数据库information_schema中的routines表中，查看存储过程和函数详细信息的语法形式如下：
SELECT * FROM information_schema.routines
WHERE ROUTINE_NAME=’pf_name’;
上述语句中，ROUTINE_NAME指定存储过程或函数名，如果有存储过程和存储函数名相同，还可以使用ROUTINE_TYPE指定类型。
主要参数及其意义如下：
ROUTINE_CATALOG：表示存储过程或函数的目录。
ROUTINE_SCHEMA：表示存储过程或函数所属数据库。
ROUTINE_NAME：表示存储过程或函数名。
ROUTINE_TYPE：表示是存储过程还是存储函数。
ROUTINE_DEFINITION：表示BEGIN...END语句。
SECURITY_TYPE：表示MySQL在执行存储过程和函数的时候，是以创建用户的权限来执行，还是以调用者的权限来执行。
五、修改和删除存储过程和函数
1.修改存储过程和函数
在MySQL中，使用ALTER关键字可以修改存储过程和函数，基本语法形式如下：
ALTER {PROCEDURE|FUNCTION} pf_name [characteristic...];
上述语句中，pf_name表示存储过程或函数名。characteristic表示存储过程和函数的特性，其可取值有CONTAINS SQL，NO SQL，READS SQL DATA，MODIFIES SQL DATA，SQL SECURITY{DEFINER|INVOKER}，各值得意义与创建存储过程和函数时相同。
注：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句，这两个语句结构相同，参数也一样。并且它们与创建存储过程和函数得语句中的参数也基本一样。
不能使用关键字ALTER更改存储过程的参数或子程序，如果需要修改，必须删除存储过程后再重新创建。
2.删除存储过程和函数
在MySQL中，删除存储过程和函数可以使用DROP语句，语法形式如下：
DROP {PROCEDURE|FUNCTION} [IF EXISTS] pf_name;
pf_name为要删除的存储过程或函数名。使用IF EXISTS可以在执行删除操作时，先判断存储过程和函数是否存在，避免系统报错。
1723
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
