MySql 存储过程和函数

一、存储过程

        简単地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限 于批处理。
        存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是： CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

1.1 创建存储过程

1.1.1 语法

CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics...] routine body

1.1.2 参数说明

• CREATE PROCEDURE为用来创建存储函数的关键字;
• sp_name为存储过程的名称;
• proc_parameter为指定存储过程的参数列表，列表形式：[IN | OUT | INOUT ] param name type
  其中，
  IN表示输入参数,
  OUT表示输出参数，
  INOUT表示既可以输入也可以输岀；
  param name 表示参数名称；
  type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
• characteristics指定存储过程的特性，有以下取值：
  LANGUAGE SQL：
  说明routine_body部分是由SQL语句组成的，当前系统支持的语言为 SQL, SQL是LANGUAGE特性的唯一值
  [NOT] DETERMINISTIC：
  指明存储过程执行的结果是否正确.
  DETERMINISTIC表示结果是确定的每次执行存储过程时，相同的输入会得到相同的输i出。
  NOT 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表明子程序包含写数据的语句。
  默认情况下，系统会指定 为 CONTAINS SQL。
  SQL SECURITY {DEFINER | INVOKER }：
  指明谁有权限来执行。
  DEFINER表示只有定义者才能执行。
  INVOKER表示拥有权限的调用者可以执行。
  默认情况下，系统指定为 DEFINER。
  COMMENT ‘string’：
  注释信息，可以用来描述存储过程或函数。
• routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

1.1.3 示列

-- DELIMITER //用来将mysql的结束符设置为//，存储过程定义完成在使用`DELIMITER ;`来回复
DELIMITER //
CREATE PROCEDURE AvgFruitPrice()
BEGIN
	SELECT AVG(f_price) AS avgprice
	FROM fruits;
END//

1.1.4 创建带参数的存储过程

CREATE PROCEDURE CountProc (OUT paraml INT)
BEGIN
	SELECT COUNT(*) INTO paraml FROM fruits;
END;

上述代码的作用是创建一个获取fruits表记录条数的存储过程，名称是CountProc, COUNT(*) 计算后把结果放入参数paramI中。

1.2 创建存储函数

1.2.1 语法

CREATE FUNCTION func_name ([func_parameter])
	RETURNS type
[characteristic...] routine body

1.2.2 参数说明

• CREATE FUNCTION 为用来创建存储函数的关键字；
• func_name 表示存储函数的名称；
• func_parameter 为存储过程的参数列表，参数列表形式：[IN | OUT | INOUT] param_name type
  其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name 表示参数名称：type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
• RETURNS type语句表示函数返冋数据的类型;
• characteristic 指定存储函数的特性，取值与 创建存储过程时相同;
• routine_body 是SQL代码的内容。

1.2.3 示列：创建存储函数

        创建存储函数，名称为NameByZip,该函数返回SELECT语句的査询结果，返回类型为字符串型。
        指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。
        RETURNS子句只能对FUNCTION做指定，对函数而言这是强制提示的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call= '48075');

二、变量的使用

2.1 定义变量

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中。
在存储过程中使用DECLARE语句定义变量。

2.1.1 语法格式

DECLARE var_name1[, var_mame2]... date_type [DEFAULT value];

2.1.2 参数说明

• var_name1为局部变量的名称；
• date_type为一个mysql支持的数据类型；
• DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

2.1.3 示列

DECLARE myparam INT DEFAULT 100;

2.2 为变量赋值

2.2.1 语法

SET var name = expr [, var_name = expr]

2.2.2 示列

DECLARE varl, var2, var3 INT;
SET varl = 10, var2 = 20;
SET var3 = varl + var2;

2.2.3 为多个变量赋值

这个SELECT语法把选定的列直接存储到对应位置的变量Ocol_name表示字段名称；var_name 表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

SELECT col_name[,...] INTO var_name[,...] table_expr; 

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name, f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='al';

三、定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条 件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当釆取的处理方 式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的 能力，避免程序异常停止运行。

3.1 定义条件

3.1.1 语法

DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
	SQLSTATE [VALUE] sqlstete_value | mysql_error_code

3.1.2 参数说明

• condition_name参数表示条件的名称；
• condition_type参数表示条件的类型；
• sqlstate_value 和 mysql_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码， mysql_error_code 为数值类型错误代码。例如：ERROR 1142(42000)4*, sqlstate_value 的值是 42000, mysql_error_code 的值是 1142。

3.1.3 示列

--方法一：使用 sqlstete_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE 42000;

-- 方法二：使用 mysql_error_code
DECLARE command not allowed CONDITION FOR 1148;

3.2 定义处理程序

3.2.1 语法

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handlertype:
	CONTINUE | EXIT | UNDO
	
conditionvalue:
	SQLSTATE [VALUE] sql_state_value
	|condition_name
	| SQLWARNING
	| NOT FOUND
	| SQLEXCEPTION
	| mysql_error_code

3.2.2 参数说明

• handler_type 为 错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。
  CONTINUE 表示遇到错误不处理，继续执行；
  EXIT遇到错误马上退出；
  UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
• 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匹配数值类型错误代码。
• sp_statement 参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

3.2.3 示列

-- 方法一：捕获 sql_state_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42802' 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';

-- 方法六：使用SQL EXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代码是6种定义处理程序的方法。
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为“42S02”，执行CONTINUE 操作，并且输出“NO_SUCH_TABLE”信息。
第二种方法是捕获mysqlerrorcode值。如果遇到mysqlerrorcode值为1146,执行CONTINUE 操作，并且输出“NQ_SUCH_TABLE”信息。
第三种方法是先定义条件，然后再调用条件。这里先定义no_such_table条件，遇到1146错误 就执CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值， 然后执行EXIT操作，并且输出“ERROR”信息。
第五种方法是使用NOT FOUND。 NOT FOUND捕获所有以02开头的sqlstate_value值，然后 执行EXIT操作，并且输出“NO_SUCH_TABLE”信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或 NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。

四、光标的使用

査询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标 来逐条读取査询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。

4.1 声明光标

-- cursor_name参数表示光标的名称；
-- select_slatement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。
DECLARE cursor_name CURSOR FOR select_statement

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruit;

4.2 打开光标

OPEN cursor_name;

4.3 使用光标

4.3.1 语法

FETCH cursor_name INTO var_name1［, var_name2］...{参数名称}

4.3.2 参数描述

• cursor_name参数表示光标的名称；
• var_name参数表示将光标中的SELECT语句査询岀来的信息存入该参数中，var_name必须在声明光标之前就定义好。

4.3.3 示列

将光标cursor_fruit中SELECT语句査询出来的信息存入fruit_name 和 fruit_price 中。fruit_name和fruit_price必须在前面已经定义

FETCH cursor_fruit INTO fruit_name, fruit_price;

4.4 关闭光标

CLOSE cursor_name;

五、流程控制的使用

5.1 IF语句

5.1.1 语法

IF实现了一个基本的条件构造。如果expr_condition求值为真(TRUE),相应的SQL语句列 表被执行：如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement list可以 包括一个或多个语句。

IF expr_condition THEN statement list
	[ELSEIF expr_condition THEN statement_list]...
	[ELSE statement_list]
END IF

5.1.2 示列

IF val IS NULL THEN SELECT 'val is NULL1';
	ELSE SELECT 'val is not NULL';
END IF;

== 5.2 CASE语句==

5.2.1 语法1

-- case_expr参数表示条件判断的表达式，决定了那一个WHEN子句会被执行;
-- when_value 参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句;
-- statement_list参数表示不同when_value值的执行语句。
CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE

5.2.2 语法2

-- expr_condition参数表示条件判断语句；
-- statement_ist参数表示不同条件的执行语句; 该语句中，WHEN语句将被逐个执行，直到某个expr_condilion表达式为真，则执行对应THEN关键字后面的statement_ist语句。如果没有条件匹配，ELSE子句里的语句被执行。
CASE
	WHEN expr condition THEN statement list
	[WHEN expr condition THEN statement list]...
	[ELSE statementjist]
END CASE

5.2.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;

CASE
	WHEN val IS NULL THEN SELECT 'Pal is NULL1';
	WHEN val < 0 THEN SELECT 'val is less than O';
	WHEN val > 0 THEN SELECT 'val is greater than 0';
	ELSE SELECT 'val is 0';
END CASE;

5.3 LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程， 使用LEAVE子句。

5.3.1 语法

[loop_label:] LOOP
	statement_list
END LOOP [loop_label]

5.3.2 示列

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;

5.4 LEAVE语句

LEAVE语句用来退岀任何被标注的流程控制构造。

5.4.1 语法

LEAVE label;

5.4.2 示列

add_num: LOOP
	SET @count = @count+l;
	IF @count = 50 THEN LEAVE add_num;
END LOOP add num;

5.5 ITERATE 语句

ITERATE语句将执行顺序转到语句段开头处。
ITERATE只可以出现在LOOP、REPEAT和WHILE语句内„ ITERATE的意思为"再次循环"， label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

5.5.1 语法

ITERATE label;

5.5.2 示列

pl=0,如果pl的值小于10时，重复执行pl加1操作；当pl大于等于10并且小于20时， 打印消息"pl is between 10 and 20";.当pl大于20时，退出循环。

CREATE PROCEDURE doiterate()
BEGIN
	DECLARE pl INT DEFAULT 0;
	my_loop: LOOP
		SET pl = pl + 1;
		IF pl < 10 THEN ITERATE my_loop;
		ELSEIF pl > 20 THEN LEAVE my_loop;
		END IF;
		SELECT 'pl is between 10 and 20';
	END LOOP my_loop;
END

5.6 REPEAT 语句

REPEAT语句创建-个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进 行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

5.6.1 语法

repeat_label为REPEAT语句的标注名称，该参数可以省略；
REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

[repeat label:] REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

5.6.2 示列

该示例循环执行id加1的操作。当id值小于10时，循环重复执行：当id值大于或者等于10 时，使用LEAVE语句退出循环。REPEAT循环都以END REPEAT结束。

DECLARE id INT DEFAULT 0;
REPEAT
	SET id = id + 1;
	UNTIL id >= 10
END REPEAT;

5.7 WHILE 语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时， 先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。

5.7.1 语法

while_label为WHILE语句的标注名称；
expr_condition为进行判断的表达式，如果表达式结 果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

[while_label:] WHILE expr_condition DO
	statement_list
END WHILE [while_label]

5.7.2 示列

DECLARE iINT DEFAULT 0;
WHILE i< 10 DO
	SET i = i + 1;
END WHILE;

六、调用存储过程和函数

6.1 调用存储过程

CALL sp_name([parameter[,...]])

DELIMITER//

CREATE PROCEDURE CountProc (IN sid INT, OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //
DELIMITER;

CALL CountProc (101, @num);
select @num;

6.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之,用户 自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的， 而内部函数是MySQL的开发者定义的。

DELIMITER //

CREATE FUNCTION CountProc (sid INT)
RETURNS INT
BEGIN
	RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
END//

DELIMITER;

 SELECT CountProc(101);

七、查看存储过程和函数

7.1 SHOW STATUS语句查看存储过程和函数的状态

7.1.1 语法

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库、名字、类型、创建者及创 建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。 PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数 的名称。

7.1.2 示列：查看数据库中所有名称以字母’C’开头的存储过程信息

SHOW PROCEDURE STATUS LIKE 'C%'\G;

7.2 SHOW CREATE语句查看存储过程和函数的定义

7.2.1 语法

这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创 建巳命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数：LIKE 语句表示匹配存储过程或函数的名称。

SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

7.2.2 示列：查看test数据库中名称为CountProc 的存储过程

SHOW CREATE FUNCTION test.CountProc \G;

7.3 从information_schema.Routines表中查看存储过程和函数的信息

从Routines表中查询名称为CountProc的存储函数的信息。

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'FUNCTION' \G

八、修改存储过程和函数

8.1 语法

ALTER (PROCEDURE | FUNCTION} sp_name [characteristic ...]

8.2 参数解释

• sp_name 参数表示存储过程或函数的名称;
• characteristic 参数指定存储函数的特性，可能的取值有：
  • CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句.
  • NO SQL表示子程序中不包含SQL语句.
  • READS SQL DATA表示子程序中包含读数据的语句.
  • MODIFIES SQL DATA表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行.
  • DEFINER表示只有定义者自己才能够执行.
  • INVOKER表示调用者可以执行.
  • COMMENT 'string’表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION 语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。而且，它们与创建存储过程或函数中的参数也是基本一样的。

8.3 示列

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA,并指 明调用者可以执行。

ALTER PROCEDURE CountProc 
MODIFIES SQL DATA
SQL SECURITY INVOKER;

九、删除存储过程

9.1 语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

9.2 示列

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;