九、MySQL存储过程和函数
存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。
创新互联自2013年起，是专业互联网技术服务公司，拥有项目成都网站制作、做网站、外贸营销网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元大兴安岭做网站,已为上家服务,为大兴安岭各地企业和个人服务,联系电话:18982081108
9.1、创建存储过程和函数
存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用( 即通过引用函数名)，也能返回标量值。存储过程也能调用其他存储过程。
创建存储过程
创建存储过程，需要使用CREATE PROCEDURE语句，基本语法为：
CREATE PROCEDURE sp_name ([proc_parameter]) [characteristic ...] routine_body
CREATE PROCEDURE：创建存储过程的关键字 |
| sp_name：存储过程的名称 |
proc_parameter：参数列表，列表形式为 [IN|OUT|INOUT] param_name type IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出； param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型 |
| characteristic：指定存储过程的特性 LANGUAGE SQL：说明routine_body部分是由SQL语句组成，SQL是LANGUAGE特性的唯一值 [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'：注释信息。 |
| routine_body：SQL代码的内容，可以用BEGIN...END来表示代码的开始和结束。 |
创建查看fruits表的存储过程
mysql> DELIMITER // mysql> CREATE PROCEDURE Proc() -> BEGIN -> SELECT * FROM fruits; -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;
创建名称为CountProc的存储过程
mysql> DELIMITER // mysql> CREATE PROCEDURE CountProc(OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM fruits; -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;
'DELIMITER //'作用是将MySQL的结束符设置为//，当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。
创建存储函数
创建存储函数，需要使用CREATE FUNCTION语句，基本语法为：
CREATE FUNCTION func_name ([func_parameter]) RETURNS type [characteristic ...] routine_body
| CREATE FUNCTION：创建存储函数的关键字 |
| func_name表示存储函数的名称 |
| func_parameter存储函数的参数列表，形式为 [IN|OUT|INOUT] param_name type IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出； param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型 |
| RETURNS type表示函数返回数据类型 |
| characteristic指定存储函数的特性，取值和与创建存储过程相同 |
创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型
mysql> DELIMITER // mysql> CREATE FUNCTION NameByZip() -> RETURNS CHAR(50) -> RETURN (SELECT s_name FROM suppliers WHERE s_call= '48075'); -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ;
变量的使用
在存储过程中使用DECLARE语句定义变量，语法格式为：
DECLARE var_name[,varname]... date_type [DEFAULT value];
定义名称为myparam的变量，类型为INT类型，默认值为100
DECLARE myparam INT DEFAULT 100;
定义变量后，为变量赋值可以改变变量的默认信息，MySQL使用SET语句为变量赋值，语法格式为：
SET var_name = expr [,var_name=expr]...
声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值
DECLARE var1, var2, var3 INT; SET var1 = 10, var2 = 20; SET var3 = var1 + var2;
MySQLhankeyishiyong SELECT...INTO为一个或多个变量赋值，语法为：
SELECT col_name[,...] INTO var_name[,...] table_expr;
声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值
DECLARE fruitname CHAR(50); DECLARE fruitprice DECIMAL(8,2); SELECT f_name,f_price INTO fruitname, fruitprice FROM fruits WHERE f_id ='a1';
定义条件和处理程序
特定条件需要特定处理。这些条件可以联系到错误，以及子程序的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。使用DECLARE关键字来定义条件和处理程序。定义条件的语法格式为：
DECLARE condition_name CONDITION FOR [condition_type] [condition_type]: SQLSTATE [VALUE] sqlstate_value | mysql_error_code
condition_name:条件名称 condition_type：条件的类型 sqlstate_value和mysql_error_code都可以表示MySQL错误 sqlstate_value为长度5的字符串类型错误代码 mysql_error_code为数值类型错误代码 |
定义"ERROR 1148(42000)"错误，名称为command_not_allowed
//方法一：使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; //方法二：使用mysql_error_code DECLARE command_not_allowed CONDITION FOR 1148
定义处理程序时，使用DECLARE语句的语法为：
DECLARE handler_action HANDLER FOR condition_value statement handler_action: CONTINUE| EXIT| UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
handler_action:处理错误方式，参数有3个取值：CONTINUE，EXIT，UNDO。 CONTINUE表示遇到错误不处理，继续执行 EXIT遇到错误马上退出 UNDO遇到错误后撤回之前的操作 |
| condition_value表示错误类型 SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值 condition_name：DECLARE CONDITION定义的错误条件名称 SQLWARNING：匹配所有01开头的SQLSTATE错误代码 NOT FOUND：匹配所有02开头的SQLSTATE错误代码 SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码 mysql_error_code：匹配数值类型错误代码 |
| statement参数为程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数 |
定义处理程序的几种方式
//方法一：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE'; //方法二：捕获mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @info=' NO_SUCH_TABLE '; //方法三：先定义条件，然后调用 DECLARE no_such_table CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info=' NO_SUCH_TABLE '; //方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; //方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info=' NO_SUCH_TABLE '; //方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
定义条件和处理程序
mysql> CREATE TABLE test.t (s1 int,primary key (s1)); Query OK, 0 rows affected (0.05 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test.t VALUES (1); -> SET @x = 2; -> INSERT INTO test.t VALUES (1); -> SET @x = 3; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL handlerdemo(); /*调用存储过程*/ Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> SELECT @x; /*查看调用过程结果*/ +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec)
'@var_name'表示用户变量，使用SET语句为其赋值，用户与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。客户端退出时，该客户端连接的所有变量自动释放。
光标的使用
查询语句可能返回多条记录，如果数据非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前声明。MySQL中光标只能在存储过程和函数中使用。
MySQL中使用DECLARE关键字声明光标，语法基本形式为：
DECLARE cursor_name CURSOR FOR select_statement
cursor_name表示光标名称 select_statement表示SELECT语句的内容 返回一个用户创建光标的结果集 |
声明名称为cursor_fruit的光标
DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits ;
打开光标的语法为：
OPEN cursor_name [光标名称]
打开名称为cursor_fruit的光标
OPEN cursor_fruit
使用光标的语法为：
FETCH cursor_name INTO var_name [,var_name] ...[参数名称]
使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量
FETCH cursor_fruit INTO fruit_name, fruit_price ;
关闭名称为cursor_fruit的光标
CLOSE cursor_fruit;
流程控制的使用
流程控制语句用来控制条件语句的执行。MySQL终于来控制流程的于具有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHERE语句。
IF
IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为：
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
IF语句的示例
IF val IS NULL THEN SELECT 'val is NULL'; ELSE SELECT 'val is not NULL'; END IF;
CASE
CASE是另一个进行条件判断的语句，该语句有两种格式：
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE Or: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE
使用CASE流程控制语句第1种格式，判断val值等于1、等于2，或者两者都不等
CASE val WHEN 1 THEN SELECT 'val is 1'; WHEN 2 THEN SELECT 'val is 2'; ELSE SELECT 'val is not 1 or 2'; END CASE; 当val值为1时，输出字符串“val is 1”；当val值为2时，输出字符串“val is 2”；否则输出字符 串“val is not 1 or 2”。
使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0
CASE WHEN val IS NULL THEN SELECT 'val is NULL'; WHEN val < 0 THEN SELECT 'val is less than 0'; WHEN val > 0 THEN SELECT 'val is greater than 0'; ELSE SELECT 'val is 0'; END CASE; 当val值为空，输出字符串“val is NULL”；当val值小于0时，输出字符串“val is less than 0”； 当val值大于0时，输出字符串“val is greater than 0”；否则输出字符串“val is 0”。
LOOP
LOOP循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环的过程，并不进行条件判断。LOOP内的语句一直重复只i系那个知道循环被退出，跳出循环过程使用LEAVE子句，LOOP语法格式为：
[begin_label:] LOOP statement_list END LOOP [end_label]
使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程
DECLARE id INT DEFAULT 0; add_loop: LOOP SET id = id + 1; IF id >= 10 THEN LEAVE add_loop; END IF; END LOOP add_ loop;
LEAVE
LEAVE语句用来退出任何被标注的流程控制构造，其语法格式为：
LEAVE label
使用LEAVE语句退出循环
add_num: LOOP SET @count=@count+1; IF @count=50 THEN LEAVE add_num ; END LOOP add_num ;
ITERATE
ITERATE语句将执行顺序转到语句段开头处，语法格式为：
ITERATE label
ITERATE只可以出现在LOOP、REPEAT、WHERE语句内。
ITERATE语句示例
CREATE PROCEDURE doiterate() BEGIN DECLARE p1 INT DEFAULT 0; my_loop: LOOP SET p1= p1 + 1; IF p1 < 10 THEN ITERATE my_loop; ELSEIF p1 > 20 THEN LEAVE my_loop; END IF; SELECT 'p1 is between 10 and 20'; END LOOP my_loop; END
REPEAT
REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果为真，则结束循环，其语法格式为：
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
REPEAT语句示例，id值小于等于10之前，将重复执行循环过程
DECLARE id INT DEFAULT 0; REPEAT SET id = id + 1; UNTIL id >= 10 END REPEAT;
WHILE
WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHERE在执行语句时，先对指定表达式进行判断，如果为真，则执行循环的语句，其语法格式为：
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
WHILE语句示例，id值小于等于10之前，将重复执行循环过程
DECLARE i INT DEFAULT 0; WHILE i < 10 DO SET i = i + 1; END WHILE;
9.2、调用存储过程和函数
调用存储过程
存储过程通过CALL语句进行调用，语法格式为：
CALL sp_name([parameter[,...]])
定义名为CountProc1的存储过程，然后调用这个存储过程
mysql> DELIMITER // mysql> CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT) -> BEGIN -> SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid; -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL CountProc1 (101, @num); Query OK, 1 row affected (0.00 sec) mysql> select @num; +------+ | @num | +------+ | 3 | +------+ 1 row in set (0.02 sec)
调用存储函数
定义存储函数CountProc2，然后调用这个函数
mysql> DELIMITER // mysql> CREATE FUNCTION CountProc2 (sid INT) -> RETURNS INT -> BEGIN -> RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid); -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> SELECT CountProc2(101); +--------------------+ | Countproc(101) | +--------------------+ | 3 | +-------------------+
9.3、查看存储过程和函数
MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句查看，也可以直接从系统information_schema数据库中查看。
使用SHOW STATUS语句可以查看存储过程和函数状态，基本语法为：
SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']
SHOW STATUS语句示例
mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G *************************** 1. row *************************** Db: test Name: CountProc Type: PROCEDURE Definer: root@localhost Modified: 2017-08-04 11:32:08 Created: 2017-08-04 11:32:08 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
使用SHOW CREATE语句可以查看存储过程和函数状态，基本语法为：
SHOW CREATE [PROCEDURE | FUNCTION] sp_name
SHOW CREATE语句示例
mysql> SHOW CREATE PROCEDURE test.CountProc \G *************************** 1. row *************************** Procedure: CountProc sql_mode: Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM fruits; END character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录来查询存储过程和函数，其基本语法为：
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME= 'sp_name';
从Routines表中查询名称为CountProc的存储过程的信息
mysql> SELECT * FROM information_schema.Routines -> WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'PROCEDURE' \G *************************** 1. row *************************** SPECIFIC_NAME: CountProc ROUTINE_CATALOG: def ROUTINE_SCHEMA: test ROUTINE_NAME: CountProc ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN SELECT COUNT(*) INTO param1 FROM fruits; END EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2017-08-04 11:32:08 LAST_ALTERED: 2017-08-04 11:32:08 SQL_MODE: ROUTINE_COMMENT: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.00 sec)
9.4、修改存储过程和函数
使用ALTER语句可以修改存储过程或函数的特性，基本语法为：
ALTER [PROCEDURE | FUNCTION ] sp_name [characteristic ...]
修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行
mysql> ALTER PROCEDURE CountProc -> MODIFIES SQL DATA -> SQL SECURITY INVOKER ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE -> FROM information_schema.Routines -> WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE'; +---------------+-------------------+---------------+ | SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE | +---------------+-------------------+---------------+ | CountProc | MODIFIES SQL DATA | INVOKER | +---------------+-------------------+---------------+ 1 row in set (0.00 sec)
9.5、删除存储过程和函数
删除存储过程和函数，可以使用DROP语句，其语法格式为：
DROP[PROCEDURE | FUNCTION ] [IF EXISTS] sp_name
删除存储过程和存储函数
mysql> DROP PROCEDURE CountProc; Query OK, 0 rows affected (0.00 sec) mysql> DROP FUNCTION CountProc; Query OK, 0 rows affected (0.00 sec)
当前文章：九、MySQL存储过程和函数
本文路径：http://myzitong.com/article/giphgd.html