存储过程的创建

        在开发过程中经常会遇到重复使用某一功能的情况，为此，MySQL引入了存储过程。存储过程就是一条或多条语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用，大大减少数据库开发人员的工作量。本节将针对如何创建存储过程及创建存储过程时需要用到的变量、光标、流程控制等进行详细的讲解。

在mysql下

mysql> CREATE PROCEDURE myproc() BEGIN END;

出现：

Error Code: 1655

Cannot create stored routine `myproc`. Check warnings

解決方式：

修改my.ini的sql-mode(sql-mode="ANSI,TRADITIONAL")，重启MySQL，即可。

原始资料来源：http://bugs.mysql.com/bug.php?id=34794

---

创建存储过程

        想要使用存储过程，首先要创建一个存储过程。创建存储过程，需要使用CREATE PROCEDURE语句，创建存储过程的基本语法格式如下。

CREATE PROCEDURE sp_name([proc_parameter])

[characteristics ...] routine_body

        上述语法格式中，CREATE PROCEDURE为用来创建存储过程的关键字；sp_name为存储过程的名称；proc_parameter 为指定存储过程的参数列表，该参数列表的形式如下：

[IN|OUT|INOUT] param_name type

        上述参数列表的形式中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数类型，它可以是MySQL数据库中的任意类型。

        在创建存储过程的语法格式中，characteristics用于指定存储过程的特性，它的取值说明具体如下。

        （1）LANGUAGE SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE的唯一值。

        （2）[NOT] DETERMINISTIC：指名存储过程的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出；NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

        （3）{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。

        （4）SQL SECURITY {DEFINER | INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

        （5）COMMENT'string'：注释信息，可以用来描述存储过程。

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

        接下来通过一个案例演示一下存储过程的创建，首先在数据库chapter06中创建表student，创建student表的SQL语句如下所示：

CREATE TABLE student(

        id INT(3) PRIMARY KEY AUTO_INCREMENT ,

        name VARCHAR(20) NOT NULL , 

        grade FLOAT,

        gender CHAR(2)

);

        执行上述SQL语句创建student表，然后使用INSERT语句向student表中插入4条记录，INSERT语句如下所示：

INSERT INTO student (name , grade , gender) 

VALUES ('tom' , 60 , '男') , ('jack' , 70 , '男') ,('rose' , 90 , '女') ,('lucy' , 100 , '女') ;

---

例：创建一个查看student表的储存过程，其创建语句如下：

CREATE PROCEDURE Proc()

BEGIN

SELECT * FROM student;

END;

上述代码创建了一个存储过程Proc，每次调用这个存储过程的时候都会执行SELECT语句查看表的内容，代码的执行过程如下：

DELIMITER //

CREATE PROCEDURE Proc()

BEGIN

SELECT * FROM student;

END //

DELIMITER ;

在上述执行过程中，“DELIMITER //”语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号“；”，为了避免于存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕后再使用“DELIMITER ;”恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。需要格外注意的是，DELIMITER 与要设定的结束符之间一定要有一个空格，否则设定无效。

---

变量的使用

在编写存储过程时，有时会需要使用变量保存数据处理过程中的值。在MySQL中，变量可以在子程序中声明并使用，这些变量的作用范围在BEGIN...END程序中，接下来将针对变量的定义和赋值进行详细的讲解。

想要在存储过程中使用变量，首先需要定义变量。在存储过程中使用DECLARE语句定义变量，具体语法格式如下：

DECLARE var_name [,varname] ... data_type[DEFAULT value];

上述语法格式中，var_name为局部变量的名称。DAFAULT value子句给变量提供一个默认值。该值除了可以被声明为一个常数之外，还可以被指定一个表达式。如果没有DAFAULT子句，变量的初始值为NULL。

接下来定义一个名称为myvariable的变量，类型为INT类型，默认值为100，示例代码如下：

DACLARE myvariable INT DEFAULT 100;

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

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

在存储过程中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

在存储过程中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a = x , b = y , ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

接下来声明三个变量，分别为var1、var2、var3，数据类型为INT，使用SET为变量赋值，示例代码如下：

DECLARE var1,var2,var3 INT;

SET var1 = 10, var2 = 20;

SET var3 = var1 + var2;

除了可以使用SET语句为变量赋值外，MySQL中还可以通过SELECT...INTO为一个或多个变量赋值，该语句可以把选定的列直接存储到对应位置的变量。使用SELECT...INTO的具体语法格式如下：

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

在上述语法格式中，col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

---

例：声明变量s_grade和s_gender，通过SELECT...INTO语句查询指定记录并为变量赋值，具体代码如下：

DELIMITER //

CREATE PROCEDURE Proc1()

BEGIN

DECLARE s_grade FLOAT;

DECLARE s_gender CHAR(2);

SELECT grade , gender INTO s_grade , s_gender

FROM student WHERE name = 'rose';

END //

DELIMITER ;

上述语句将student表中name 为 rose 的同学的成绩和性别分别存入到了变量s_grade和s_gender中。至此有关变量的使用的内容便讲解完了。

---

定义条件和处理程序

在实际开发中，经常需要对特定的条件进行处理，这些条件可以联系到错误yi'j子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程在遇到警告或者错误时能继续执行。接下来将针对定义条件和处理程序进行详细的讲解。

定义条件

在编写存储过程时，定义条件使用DECLARE语句，语法格式如下：

DECLARE  condition_name CONDITION FOR [condition_type];

// 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为数值类型的错误代码。use mmm; 例如：ERROR1049（42000）中，sqlstate_value的值是42000，mysql_error_code的值是1049。

上述语法格式指定了需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

【例6-4】定义“ERROR1148（42000）”错误，名称为command_not_allowed。可以用两种不同的方法来定义，具体代码如下：

//方法一：使用sqlstate_value

DECLARE command_not_allowed CONDITION FOR SQLSTATE'42000';

//方法二：使用mysql_error_code

DECLARE command_not_allowed CONDITION FOR 1148;

定义处理程序

定义完条件后，还需要定义针对此条件的处理程序。MySQL中用DECLARE语句定义处理程序，具体语法格式如下：

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

handler_type:

    CONTINUE | EXIT | UNDO

condition_value:

    |condition_name

    |SQLWARNING

    |NOT FOUND

    |SQLEXCEPTION

    |mysql_error_code

上述语法格式中，handler_type为错误处理方式，参数取三个值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程；condition_value表示错误类型，可以有以下取值。

（1）SQLSTATE[VALUE]sqlstste_value包含5个字符的字符串错误值。

（2）condition_name表示DECLARE CONDITION定义的错误条件名称。

（3）SQLWARNING匹配所有以01开头的SQLSTATE错误代码。

（4）NOT FOUND匹配所有以02开头的SQLSTATE错误代码。

（5）SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。

（6）mysql_error_code匹配数值类型错误代码。

【例6-5】定义处理程序的几种方式，具体代码如下：

上述代码中演示了6种定义处理程序的方法。接下来将分别进行分析讲解，具体如下:

//方法一：捕获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 CONTINUE FOR 1146

DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';

//方法四：使用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';

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

第二种方法是捕获mysql_error_code值，如果遇到mysql_error_code值为1146，则执行CONTINUE操作，并且输出“NO_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”信息。

【例6-6】定义条件和处理程序，具体执行过程如下：

编辑过程 调用过程

@x是一个用户变量，执行@x等于3，这表明MySQL被执行到程序的末尾。如果没有“DECLARE CONTINUE HANDLER FOR SQLSTATE‘23000’SET @x2=1；”这句代码，第二个INSERT会因PRIMARY KEY强调而失败之后，MySQL可能已经采取默认(EXIT)路径，并且SELECT@x会返回2。

需要注意的是，“@var_name”表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户连接的所有变量将自动释放。上述案例中存储过程的调用和查询会在后面章节中进行详细的讲解，这里读者只需了解即可。

光标的使用

在编写存储过程中，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。接下来将针对光标的声明、使用和关闭进行详细的讲解。

光标的声明

想要使用光标处理结果集中的数据，需要先声明光标。光标必须声明在声明变量、条件之后，声明处理程序之前。MySQL中使用DECLARE关键字来声明光标，声明光标的具体语法格式如下：

DECLARE cursor_name CURSOR FOR select_statement

在上述语法格式中，cursor_name表示光标的名称；select_statement表示SELECT语句的内容，返回一个用于创建光标的结果集。

接下来声明一个名为cursor_student的光标，示例代码如下：

DECLARE cursor_student CURSOR FOR select s_name , s_gender FROM student;

通过上面的代码，已经成功声明了一个名为cursor_student的光标。

光标的使用

声明完光标后就可以使用光标了，使用光标之前首先要打开光标。MySQL中打开和使用光标的语法格式如下：

OPEN cursor_name

FETCH cursor_name INTO var_name [ ,var_name ]...

在上述语法格式中，cursor_name表示参数的名称；var_name表示将光标中的SELECT语句查询出来的信息存入该参数中，需要注意的是，var_name必须在声明光标之前就定义好。

使用名称为cursor_student的光标。将查询出来的信息存入s_name和s_gender中，示例代码如下：

FETCH cursor_student INTO s_name , s_gender;

光标的关闭

使用完光标后要将光标关闭，关闭光标的语法格式如下：

CLOSE cursor_name

值得一提的是，如果没有明确地关闭光标，它会在其声明的复合语句的末尾被关闭。

演示效果