简单地说，存储过程就是一条或多条 SQL 语句的集合，可视为批文件，但是其作用不仅限于批处理。存储程序可以分为存储过程和函数。存储过程要用 CALL 语句来调用，并且只能用输出变量返回值。

1. 创建存储过程

语法格式：

CREATE PROCEDURE SP_name ( [ proc_parameter ] )

[ characteristics ... ] routine_body

【注释】

a.CREATE PROCEDURE:创建存储过程的关键字。

b.sp_name:存储过程的名字。

c.proc_parameter：为存储过程的参数列表，列表形式如下：

[ IN | out | INOUT ] param_name type

IN 表示输入参数；OUT 表示输出参数；INOUT 表示即可以输入也可以输出参数；param_name 表示参数名；type 表示参数类型 。

d.characteristics 指定存储过程的特性，有以下取值：

LANGUAGE SQL：说明 routine_body 部分是由 SQL语句组成。 [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出。 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’：注释信息，可以用来描述存储过程或函数。

e. routine body是SQL代码的内容，可以用BEGIN..END来表示SQL代码的开始和结束。

【例】创建查看 fruits 表的存储过程。SQL 语句如下：

mysql> DELIMITER //mysql> CREATE PROCEDURE proc()-> BEGIN-> SELECT * FROM fruits;-> END //Query OK, 0 rows affected (0.35 sec)mysql> DELIMITER ;

这个存储过程和使用 SELECT 语句查看表得到的结果是一样的， 当然存储过程也可以是很多语句的复杂组合，其本身也可以调用其他的函数，来组成更加复杂的操作。

【注】 "DELIMITER //”语句的作用是将 MySQL 的结束符设置为 // ,因为 MySQL 默认的语句结束符号为分号(;)，为了避免与存储过程中的 SQL 语句结束符冲突，需要使用 DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程，存储过程，定义完毕之后再使用 DELIMITER 恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。

【例】 创建名为 countproc 的存储过程，用于获取 fruits 表中的记录数。代码如下:

mysql> DELIMITER //mysql> CREATE PROCEDURE countproc ( OUT paraml INT )-> BEGIN-> SELECT COUNT(*) INTO paraml FROM fruits;-> END //Query OK, 0 rows affected (0.07 sec)mysql> DELIMITER ;

2. 创建存储函数

语法格式：

CREATE FUNCTION func_name ( [ func_parameter ] )

RETURNS type

[ characteristics ... ] 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.11 sec)mysql> DELIMITER ;

3. 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在 BEGIN ... END 程序中。

a. 定义变量

语法格式：

DECLARE var_namel [, var_name] ... date_type [ DEFAULT value ];

var_name 为局部变量的名称。DEFAULT value 子句给变最提供一个默认值，这个值除了可以声明为一个常数之外，还可以指定为一个表达式。如果没有DEFAULT 子句，初始值为NULL。

【例】定义名称为 myparam 的变量，类型为 INT，默认值为100。代码如下:

DECLARE myparam INT DEFAULT 100;

b. 为变量赋值

变量定义之后，为变量赋值可以改变量的默认值，MysSQL中使用 SET 语句为变量赋值，语法格式如下:

SET var_name = expr [ , var_name = expr ] ... ;

【例】声明3个变量，分别为 varl、var2 和 var3, 数据类型为 INT，使用 SET 为变量赋值。代码如下:

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

MySQL 中还可以通过 SELECT ... INTO 为一个或多个变量赋值， 语法如下:

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

col_name 表示字段名称;var_name 表示定义的变量名称；table_expr 表示查询条件表达式，包括表名称和 WHERE 子句。

【例】声明变量 fruit_name 和 fruitprice 通过 SELECT ... INTO 语句查询指定记录并为变量赋值。 代码如下:

DECLARE fruitname CHAR(50);DECLARE fruitprice DECIMAL(8,2);SELECT f_name,f_price INTO fruitname, fruitpriceFROM fruits WHERE f_id ='a1';

4. 定义条件和处理程序

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

a.定义条件

DECLAREcondition_nameCONDITIONFOR[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 1142(42000)中，sqlstate_value 的值是42000，mysql_ error_code的值是 1142。 这个语句用于指定需要特殊处理的条件，可以将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

【例】定义 ERROR 1148(42000) 错误， 名称为 command_not_allowed , 用两种不同的方法来定义。代码如下:

//方法一: 使用 sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';//方法二: 使用mysql_error_codeDECLARE command_not_allowed CONDITION FOR 1148

b.定义处理程序

语法格式如下：

DECLARE handler_type HANDLER FOR condition_value [ ,... ] sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [ VALUE ] sqlstate_value

|condition_name

|SQLWARNING

|NOT FOUND

|SQLEXCEPTION

|mysql_error_code

【注释】

(1) hander_type 为错误处理方式，参数取3个值: CONTINUE、 EXIT 和 UNDO. CONTINUE 表示遇到错误不处理，继续执行； EXIT 表示遇到错误马上退出； UNDO 表示遇到错误后撒回之前的操作，MySOL中暂时不支持这样的操作。

(2) condition_value 表示错误类型，可以有以下取值:

SQLSTATE [VALUE] sqlstate_value 表示包含5个字符的字符串错误值。condition_name 表示 DECLARE CONDITION 定义的错误条件名称。SQLWARNING 匹配所有以 01 开头的 SQLSTATE 错误代码。NOT FOUND 匹配所有以 02 开头的 SQLSTATE 错误代码。SQLEXCEPTION 匹配所有未被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码。 (3) mysql_error_code 匹配数值类型错误代码。

(4) sp_statement 参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

【例】定义处理程序的几种方式。代码如下:

//方法一:捕获sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

第一种方法是捕获 sqlstate_value 值。如果遇到 sqlstate_value 值为 42S02， 执行 CONTINUE 操作，并且输出 NO_ SUCH_TABLE 信息。

//方法二: 捕获 mysql_error_codeDECLARE CONTINUE HANDLER FOR 1146 SET @info= 'NO_SUCH_TABLE' ;

第二种方法是捕获 mysql_error_code值。如果遇到 mysql_error_code值为 1146， 执行 CONTINUE 操作，并且输出NO_ SUCH_TABLE 信息。

//方法三: 先定义条件，然后调用DECLARE no_such_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info= 'NO_SUCH_TABLE' ;

第三种方法是先定义条件，然后再调用条件。这里先定义 no_such_table 条件，遇到 1146 错误就执行 CONTINUE 操作。

//方法四: 使用 SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

第四种方法是使用 SQLWARNING。SQLWARNING 捕获所有以 01 开头的 sqlstate_value 然后执行 EXIT 操作，并且输出 ERROR 信息。

//方法五: 使用 NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @info= 'NO_SUCH_TABLE' ;

第五种方法是使用 NOT FOUND。NOT FOUND 捕获所有以 02 开头的 sqlstate_value 值然后执行 EXIT 操作，并且输出NO_SUCH_TABLE 信息。

//方法六: 使用 SOLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTON SET @infom='ERROR';

第六种方法是使用 SQLEXCEPTION。SQLEXCEPTION 捕获所有未被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_ value值， 然后执行 EXIT 操作，并且输出 ERROR 信息。

【例】定义条件和处理程序。

mysql> CREATE TABLE t(s1 int,primary key(s1));Query OK, 0 rows affectedmysql> DELIMITER //mysql> CREATE PROCEDURE handlerdemo()-> BEGIN-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;-> SET @x=1;-> INSERT INTO t VALUES(1);-> SET @x=2;-> INSERT INTO t VALUES(1);-> SET @x=3;-> END;-> //Query OK, 0 rows affectedmysql> DELIMITER ;mysql> CALL handlerdemo();Query OK, 0 rows affectedmysql> SELECT @x;+----+| @x |+----+| 3 |+----+1 row in set

5. 光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

(1) 声明光标

语法格式：

DECLARE cursor_name CURSOR FOR selec_statement

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

【例】 声明名称为 cursor_fruit 的光标。代码如下:

DECLARE cursor_fruits CURSOR FOR SELECT f_name,f_price FROM fruits;

光标的名称为 cursor_fruit, SELECT 语句部分从 fruits 表中查询出 f_name 和 f_price 字段的值。

(2) 打开光标

语法格式：

OPEN cursor_name

【例】打开名为 cursor_ fruit 的光标。代码如下:

OPEN cursor_fruits;

(3) 使用光标

语法格式：

FETCH cursor_name INTO var_name [ , var_name ] ... {参数名称}

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

【例】 使用名为 cursor_fruit 的光标查询，并将查询出来的数据存入 fruit_name 和fruit_price 两个变量中。

代码如下:

FETCH cursor_fruits INTO fruits_name,fruits_price;

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

(4) 关闭光标

语法格式：

CLOSE cursor_name{光标名称}

如果光标未被明确地关闭，就会在声明的复合语句的未尾被关闭。

【例】关闭名为 cursor_fruit 的光标。代码如下:

CLOSE cursor_fruit;

【注】 MySQL中光标只能在存储过程和函数中使用。

存储过程与自定义函数的区别

存储过程实现的功能要复杂一些;而函数的针对性更强存储过程可以返回多个值;函数只能有一个返回值存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分来出现。

【注】参考于清华大学出版社《MySQL数据库应用案例课堂》1月第1版