MySQL存储过程

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。

存储过程通常有以下优点：

（1）存储过程在服务器端运行，执行速度快。

（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

(4) 存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

1．格式

在MySQL 5.1中创建存储过程，必须具有CREATE routine权限。

CREATE PROCEDURE的语法格式：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

其中，proc_parameter的参数如下：

[ IN | OUT | INOUT ] param_name type

characteristic特征如下：

language SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

MySQL存储过程创建的简要描述：

CREATE PROCEDURE存储过程名 (参数列表)

   BEGIN

         SQL语句代码块

END

1．由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

2．过程体的开始与结束使用BEGIN与END进行标识, 里面包含了在过程调用的时候必须执行的语句。当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

3．在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。

当使用delimiter命令时，应该避免使用反斜杠(‘"’)字符，因为那是MySQL的转义字符。

例：

mysql> delimiter //

mysql> create procedure proc1(out s int)

-> begin

-> select count(*) into s from tb;

-> end

-> //

Query OK, 0 rows affected (0.70 sec)

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN、OUT、INOUT。

IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数：该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数：调用时指定，并且可被改变和返回

小结：如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。

characteristic：存储过程的某些特征设定，下面一一介绍：

language sql：表明编写这个存储过程的语言为SQL语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其扩展，最有可能第一个被支持的语言是PHP。

deterministic：设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果，设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

contains SQL：表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句，但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。

SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。默认值是DEFINER。

COMMENT 'string'：对存储过程的描述，string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

1） IN参数例子

mysql> delimiter //

mysql> create procedure proc_in_param(in pin int)

-> begin

-> select pin;

-> set pin = 2;

-> select pin;

-> end

-> //

Query OK, 0 rows affected (0.61 sec)

mysql> delimiter ;

执行结果:

mysql> set @pin=1;

Query OK, 0 rows affected (0.00 sec)

mysql> call proc_in_param(@pin);

+------+

| pin |

+------+

| 1 |

+------+

1 row in set (0.61 sec)

+------+

| pin |

+------+

| 2 |

+------+

1 row in set (0.63 sec)

Query OK, 0 rows affected (0.63 sec)
以上可以看出，pin虽然在存储过程中被修改，但并不影响@pin的值

2）OUT参数例子

mysql> delimiter //

mysql> create procedure proc_out_param(out pout int)

-> begin

-> select pout;

-> set pout = 2;

-> select pout;

-> end

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

执行结果:

mysql> set @pout=1;

Query OK, 0 rows affected (0.00 sec)

mysql> call proc_out_param(@pout);

+------+

| pout |

+------+

| NULL |

+------+

1 row in set (0.61 sec)

+------+

| pout |

+------+

| 2 |

+------+

1 row in set (0.61 sec)

Query OK, 0 rows affected (0.61 sec)

3）INOUT参数例子

mysql> delimiter //

mysql> create procedure proc_inout_param(inout pinout int)

-> begin

-> select pinout;

-> set pinout = 2;

-> select pinout;

-> end

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

执行结果:

mysql> set @pinout = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> call proc_inout_param(@pinout);

+--------+

| pinout |

+--------+

| 1 |

+--------+

1 row in set (0.00 sec)

+--------+

| pinout |

+--------+

| 2 |

+--------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

2．变量

Ⅰ. 变量定义

DECLARE variable_name [,variable_name...] type [DEFAULT value];

其中，type为MySQL的数据类型，如:int、float、date、varchar(length)

DECLARE用来声明局部变量，且DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

在存储过程中声明局部变量，它们可以用来存储临时结果。局部变量和用户变量的区别在于：局部变量前面没有使用@符号，局部变量在其所在的BEGIN…END语句块处理完后就消失了，而用户变量存在于整个会话当中。

例如：

DECLARE counter INT DEFAULT 0;

DECLARE v_int int unsigned default 4000000;

DECLARE v_numeric number(8,2) DEFAULT 9.95;

DECLARE v_date date DEFAULT '2012-12-31';

DECLARE v_datetime datetime DEFAULT '2012-01-01 00:00:00';

DECLARE v_varchar varchar(255) DEFAULT 'This will not be padded';

Ⅱ. 变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

参考变量可能是子程序内声明的变量，或者是全局服务器变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。

其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。

这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

Ⅲ. 用户变量

用户变量与数据库连接有关，在这个连接中声明的变量，在连接断开的时候，就会消失。

在此连接中声明的变量无法在另一连接中使用。用户变量的变量名的形式为@varname的形式。名字必须以@开头。

变量可以使用使用set语句来赋值，也可以使用可以使用select 语句为变量赋值。

对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符.

mysql里面的变量是不严格限制数据类型的，它的数据类型根据你赋给它的值而随时变化。

Mysql中的变量分为：用户变量与系统变量。

系统变量：系统变量又分为全局变量与会话变量。

全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）

全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话。

ⅰ. 在MySQL客户端使用用户变量

mysql > SELECT 'Hello World' into @x;

mysql > SELECT @x;

+-------------+

| @x |

+-------------+

| Hello World |

+-------------+

说明：SELECT col_name[,...] INTO var_name[,...] table_expr

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

例：SELECT id,data INTO x,y FROM tb.t1 LIMIT 1;

SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

mysql > SET @y='Goodbye’;

mysql > SELECT @y;

+---------------------+

| @y |

+---------------------+

| Goodbye |

+---------------------+

mysql > SET @z=1+2+3;

mysql > SELECT @z;

+------+

| @z |

+------+

| 6 |

+------+

mysql>set@name = '';

mysql>select @name:=password from user limit 0,1;

ⅱ. 在存储过程中使用用户变量

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');

mysql > SET @greeting='Hello';

mysql > CALL GreetWorld( );

+----------------------------+

| CONCAT(@greeting,' World') |

+----------------------------+

| Hello World |

+----------------------------+

ⅲ. 在存储过程间传递全局范围的用户变量

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';

mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);

mysql> CALL p1( );

mysql> CALL p2( );

+-----------------------------------------------+

| CONCAT('Last procedure was ',@last_proc |

+-----------------------------------------------+

| Last procedure was p1 |

+-----------------------------------------------+

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

关于变量说明：http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html

(5). 注释

MySQL存储过程可使用两种风格的注释

双模杠：-- 该风格一般用于单行注释

c风格：一般用于多行注释

例如：

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc1 --name存储过程名

-> (IN parameter1 INTEGER)

-> BEGIN

-> DECLARE variable1 CHAR(10);

-> IF parameter1 = 17 THEN

-> SET variable1 = 'birds';

-> ELSE

-> SET variable1 = 'beasts';

-> END IF;

-> INSERT INTO table1 VALUES (variable1);

-> END

-> //

mysql > DELIMITER ;

4. MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

语法格式：CALL sp_name([parameter[,...]])

说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。

Java代码调用存储过程：

java.sql.CallableStatement接口用于调用存储过程，通过Connection实例的prepareCall()方法返回CallableStatement对象，在prepareCall()内部为一固定写法{call 存储过程名(参数列表1,参数列表2)}，其中，参数可用?占位，如：

connection.prepareCall("{call proc(?)}");

存储过程的输入参数：通过java.sql.CallableStatement实例的setXXX()方法赋值，用法类似java.sql.PreparedStatement。

存储过程的输出参数：通过java.sql.CallableStatement实例的registerOutParameter(参数位置, 参数类型)方法赋值，参数类型使用java.sql.Types中定义的类型。

示例如下：

调用带输入参数的存储过程：

public void exeProc () {

try {

callableStatement=connection.prepareCall("{call proc_in_param(?)}");

callableStatement.setInt(1, 1); //设置输入参数

resultSet=callableStatement.executeQuery();//执行存储过程

if(resultSet.next()) {

System.out.println(resultSet.getInt(1)+""t"+resultSet.getString(2));

}

} catch (SQLException e) {

e.printStackTrace();

}

代码调用带输出参数的存储过程 (返回数据库中的记录数)

public void exeProc2(){

try {

callableStatement=connection.prepareCall("{call proc_in_param(?)}");

//设置输出参数

callableStatement.registerOutParameter(1, Types.INTEGER);

//执行存储过程

resultSet=callableStatement.executeQuery();

if(resultSet.next()) {

System.out.println(resultSet.getInt(1));

}

} catch (SQLException e) {

e.printStackTrace();

}

5. MySQL存储过程的查询

查看某个数据库下面的存储过程：

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema='数据库名';

或者

show procedure status where db='数据库名';

进行查询。

查看存储过程的详细信息：

SHOW CREATE PROCEDURE 数据库.存储过程名;

6. MySQL存储过程的修改

ALTER PROCEDURE sp_name [characteristic ...]

其中，characteristic为：

{ CONTAINS SQL | NO SQL | READS SQLDATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

说明：characteristic是存储过程创建时的特征

注意：该语句只能修改存储过程的特性，不能修改代码。

例：使用alter procedure p2() select concat('Last procedure was',@last_procedure); 该语句与创建时语句相似，但执行会抛出错误。

如果要修改存储过程的内容(即过程体)，可以使用先删除再重新定义存储过程的方法。

7. MySQL存储过程的删除

DROP PROCEDURE [if exists] sp_name

在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

8. MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end变量时，内部变量消失，此时已经在其作用域外，内部变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc3()

-> begin

-> declare x1 varchar(5) default 'outer';

-> begin

-> declare x1 varchar(5) default 'inner';

-> select x1;

-> end;

-> select x1;

-> end;

-> //

mysql > DELIMITER ;

mysql> call proc3();

+-------+

| x1 |

+-------+

| inner |

+-------+

1 row in set (0.05 sec)

+-------+

| x1 |

+-------+

| outer |

+-------+

1 row in set (0.05 sec)

(2). 条件语句

Ⅰ. if-then -else语句

可根据不同的条件执行不同的操作。

语法格式为：

IF 判断的条件THEN 一个或多个SQL语句

[ELSEIF判断的条件THEN一个或多个SQL语句] ...

[ELSE一个或多个SQL语句]

END IF

说明：当判断条件为真时，就执行相应的SQL语句。

IF语句不同于系统的内置函数IF()函数，IF()函数只能判断两种情况。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc2(IN parameter int)

-> begin

-> declare var int;

-> declare t int;

-> set var=parameter+1;

-> if var=0 then

-> insert into var values(17);

-> end if;

-> if parameter=0 then

-> update t set s1=s1+1;

-> else

-> update t set s1=s1+2;

-> end if;

-> end;

-> //

mysql > DELIMITER ;

例：

mysql> delimiter //

mysql> CREATE PROCEDURE testProc(IN K1 int, IN K2 int, OUT K3 char(6))

-> BEGIN

-> IF K1>K2 THEN

-> SET K3= '大于';

-> elseif k1=k2 then

-> set k3='等于';

-> ELSE

-> SET K3= '小于';

-> END IF;

-> END;

-> //

mysql> delimiter ;

Ⅱ. case语句：

语法格式为：

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

或者：

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

说明：一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。

第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，search_condition指定了一个比较表达式，表达式为真时执行THEN后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc3 (in parameter int)

-> begin

-> declare var int;

-> set var=parameter+1;

-> case var

-> when 0 then

-> insert into t values(17);

-> when 1 then

-> insert into t values(18);

-> else

-> insert into t values(19);

-> end case;

-> end;

-> //

或：

mysql > CREATE PROCEDURE proc3 (in parameter int)

-> begin

-> declare var int;

-> set var=parameter+1;

-> case

-> when var=0 then

-> insert into t values(17);

-> when var=1 then

-> insert into t values(18);

-> else

-> insert into t values(19);

-> end case;

-> end;

-> //

mysql > DELIMITER ;

(3). 循环语句

MySQL支持3条用来创建循环的语句：while、repeat和loop语句。在存储过程中可以定义0个、1个或多个循环语句。

Ⅰ. while ···· end while：

WHILE语句语法格式为：

WHILE search_condition DO

statement_list

END WHILE

说明：语句首先判断search_condition是否为真，不为真则执行statement_list中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc4()

-> begin

-> declare var int;

-> set var=0;

-> while var<6 do

-> insert into t values(var);

-> set var=var+1;

-> end while;

-> end;

-> //

mysql > DELIMITER ;

Ⅱ. repeat···· end repeat：

repeat语句格式如下：

REPEAT

statement_list

UNTIL search_condition

END REPEAT

说明：REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。REPEAT也可以被标注。

它在执行操作后检查结果，而while则是执行前进行检查。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc5 ()

-> begin

-> declare v int;

-> set v=0;

-> repeat

-> insert into t values(v);

-> set v=v+1;

-> until v>=5

-> end repeat;

-> end;

-> //

mysql > DELIMITER ;

说明：REPEAT语句和WHILE语句的区别在于：REPEAT语句先执行语句，后进行判断；而WHILE语句是先判断，条件为真时才执行语句。

Ⅲ. loop ·····end loop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

LOOP语句语法格式如下：

LOOP

statement_list

END LOOP

说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE 语句。

LEAVE语句经常和BEGIN...END或循环一起使用。结构如下：

LEAVE label ; label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc6 ()

-> begin

-> declare v int;

-> set v=0;

-> LOOP_LABLE:loop

-> insert into t values(v);

-> set v=v+1;

-> if v >=5 then

-> leave LOOP_LABLE;

-> end if;

-> end loop;

-> end;

-> //

mysql > DELIMITER ;

Ⅳ. LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

(4). ITERATE迭代

Ⅰ. ITERATE:

iterate语句，它只可以出现在LOOP、REPEAT和WHILE语句内，意为“再次循环”，通过引用复合语句的标号，来从新开始复合语句。

它的格式为：ITERATE label

说明：该语句格式与LEAVE差不多，区别在于：LEAVE语句是离开一个循环，而ITERATE语句是重新开始一个循环。

mysql > DELIMITER //

mysql > CREATE PROCEDURE proc10 ()

-> begin

-> declare v int;

-> set v=0;

-> LOOP_LABLE:loop

-> if v=3 then

-> set v=v+1;

-> ITERATE LOOP_LABLE;

-> end if;

-> insert into t values(v);

-> set v=v+1;

-> if v>=5 then

-> leave LOOP_LABLE;

-> end if;

-> end loop;

-> end;

-> //

mysql > DELIMITER ;

9．数据库交互

(1) INTO用于存储单行记录的查询结果，语法：

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

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回

(2) CURSOR(游标)用于处理多行记录的查询结果

在MySQL中，游标一定要在存储过程或函数中使用，不能单独在查询中使用。

使用一个游标需要用到4条特殊的语句：DECLARE CURSOR（声明游标）、OPEN CURSOR（打开游标）、FETCH CURSOR（读取游标）和CLOSE CURSOR（关闭游标）。

如果使用了DECLARE CURSOR语句声明了一个游标，这样就把它连接到了一个由SELECT语句返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着，可以用FETCH CURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针，它指向当前的一行数据，使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时，使用CLOSE CURSOR语句关闭这个游标。

i. 声明游标

语法格式：DECLARE cursor_name cursor for select_statement

说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行的数据。这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。

注意：这里的SELECT子句不能有INTO子句。

下面的定义符合一个游标声明：

DECLARE XS_CUR1 CURSOR FOR

SELECT 学号, 姓名, 性别, 出生日期, 总学分

FROM XS

WHERE 专业名 = '计算机';

注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。

ii. 打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为：OPEN cursor_name

在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。

iii. 读取数据

游标打开后，就可以使用fetch…into语句从中读取数据。

语法格式：FETCH cursor_name INTO var_name [, var_name] ...

说明：FETCH ...INTO语句与SELECT...INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变量名。

iv. 关闭游标

游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：

CLOSE cursor_name语句参数的含义与OPEN语句中相同。

例：创建一个测试表：create table curtest(name varchar(20));

增加一些测试数据：

mysql> insert into curtest values('t1');

mysql> insert into curtest values('t2');

mysql> insert into curtest values('t3');

mysql> insert into curtest values('t4');

mysql> insert into curtest values('t5');

创建存储过程：

mysql> delimiter //

mysql> /*建立存储过程 create */

mysql> CREATE PROCEDURE useCursor()

-> BEGIN

-> /*局部变量的定义 declare*/

-> declare tmpName varchar(20) default '' ;

-> declare allName varchar(255) default '' ;

-> declare cur1 CURSOR FOR SELECT name FROM curtest ;

-> declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;

-> /*开游标*/

-> OPEN cur1;

-> /*游标向下走一步*/

-> FETCH cur1 INTO tmpName;

-> /* 循环体把游标查询出的 name 都加起并用 ; 号隔开 */

-> WHILE ( tmpname is not null) DO

-> set tmpName = CONCAT(tmpName ,";") ;

-> set allName = CONCAT(allName ,tmpName) ;

-> /*游标向下走一步*/

-> FETCH cur1 INTO tmpName;

-> END WHILE;

-> CLOSE cur1;

-> select allName ;

-> END

-> //

mysql> delimiter ;

执行存储过程：

mysql> call useCursor();

+-----------------+

| allName |

+-----------------+

| t1;t2;t3;t4;t5; |

+-----------------+

1 row in set (0.08 sec)

例：

CURSOR用于处理多行记录的查询结果

DELIMITER //

DROP PROCEDURE IF EXITS cursor_example //

CREATE PROCEDURE cursor_example()

READS SQL DATA

BEGIN

DECLARE l_employee_id INT;

DECLARE l_salary NUMERIC(8,2);

DECLARE l_department_id INT;

DECLARE done INT DEFAULT 0;

DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur1;

emp_loop: LOOP

FETCH cur1 INTO l_employee_id, l_salary, l_department_id;

IF done=1 THEN

LEAVE emp_loop;

END IF;

END LOOP emp_loop;

CLOSE cur1;

END

DELIMITER ;

unbounded SELECT语句用于存储过程返回结果集，例：

DELIMITER //

DROP PROCEDURE IF EXISTS sp_emps_in_dept //

CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT)

BEGIN

SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id;

END

DELIMITER ;

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里，例：

DELIMITER //

DROP PROCEDURE IF EXITS sp_update_salary //

CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2))

BEGIN

IF in_new_salary < 5000 OR in_new_salary > 500000 THEN

SELECT "Illegal salary: salary must be between $5000 and $500, 000";

ELSE

UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id;

END IF;

END

DELIMITER ;

使用CALL调用存储程序，例：

DELIMITER //

DROP PROCEDURE IF EXISTS call_example //

CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))

NO SQL

BEGIN

DECLARE l_bonus_amount NUMERIC(8,2);

IF employee_type='MANAGER' THEN

CALL calc_manager_bonus(employee_id, l_bonus_amount);

ELSE

CALL calc_minion_bonus(employee_id, l_bonus_amount);

END IF;

CALL grant_bonus(employee_id, l_bonus_amount);

END

DELIMITER ;

10. MySQL存储过程的基本函数

(1).字符串类

    CHARSET(str)                                        //返回字串字符集
    CONCAT (string2 [,... ])                         //连接字串
    INSTR (string ,substring )                       //返回substring首次在string中出现的位置,不存在返回0
    LCASE (string2 )                                    //转换成小写
    LEFT (string2 ,length )                           //从string2中的左边起取length个字符
    LENGTH (string )                                   //string长度
    LOAD_FILE (file_name )                      //从文件读取内容
    LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
    LPAD (string2 ,length ,pad )                   //重复用pad加在string开头,直到字串长度为length
    LTRIM (string2 )                                     //去除前端空格
    REPEAT (string2 ,count )                       //重复count次
    REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
    RPAD (string2 ,length ,pad)                    //在str后用pad补充,直到长度为length
    RTRIM (string2 )                                     //去除后端空格
    STRCMP (string1 ,string2 )                     //逐字符比较两字串大小,
    SUBSTRING (str , position [,length ])     //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

    TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
    UCASE (string2 )                                            //转换成大写
    RIGHT(string2,length)                                    //取string2最后length个字符
    SPACE(count)                                                 //生成count个空格

mysql> select substring('abcd',0,2);

+-----------------------+

| substring('abcd',0,2) |

+-----------------------+

| |

+-----------------------+

1 row in set (0.00 sec)

mysql> select substring('abcd',1,2);

+-----------------------+

| substring('abcd',1,2) |

+-----------------------+

| ab |

+-----------------------+

1 row in set (0.02 sec)

(2).数学类

    ABS (number2 )                                          //绝对值
    BIN (decimal_number )                              //十进制转二进制
    CEILING (number2 )                                  //向上取整
    CONV(number2,from_base,to_base)          //进制转换
    FLOOR (number2 )                                     //向下取整
    FORMAT (number,decimal_places )          //保留小数位数
    HEX (DecimalNumber )                              //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
    LEAST (number , number2 [,..])                 //求最小值
    MOD (numerator ,denominator )                 //求余
    POWER (number ,power )                           //求指数
    RAND([seed])                                              //随机数
    ROUND (number [,decimals ])                    //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：
(1)默认变为整形值

mysql> select round(1.23);

+-------------+

| round(1.23) |

+-------------+

| 1 |

+-------------+

1 row in set (0.00 sec)

mysql> select round(1.56);

+-------------+

| round(1.56) |

+-------------+

| 2 |

+-------------+

1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据

mysql> select round(1.567,2);

+----------------+

| round(1.567,2) |

+----------------+

| 1.57 |

+----------------+

1 row in set (0.00 sec)

SIGN (number2 ) //

(3).日期时间类

    ADDTIME (date2 ,time_interval )                                //将time_interval加到date2
    CONVERT_TZ (datetime2 ,fromTZ ,toTZ )                //转换时区
    CURRENT_DATE ( )                                                   //当前日期
    CURRENT_TIME ( )                                                    //当前时间
    CURRENT_TIMESTAMP ( )                                       //当前时间戳
    DATE (datetime )                                                          //返回datetime的日期部分
    DATE_ADD (date2 , INTERVAL d_value d_type )    //在date2中加上日期或时间
    DATE_FORMAT (datetime ,FormatCodes )                //使用formatcodes格式显示datetime
    DATE_SUB (date2 , INTERVAL d_value d_type )     //在date2上减去一个时间
    DATEDIFF (date1 ,date2 )                                            //两个日期差
    DAY (date )                                                                   //返回日期的天
    DAYNAME (date )                                                       //英文星期
    DAYOFWEEK (date )                                                  //星期(1-7) ,1为星期天
    DAYOFYEAR (date )                                                   //一年中的第几天
    EXTRACT (interval_name FROM date )                     //从date中提取日期的指定部分
    MAKEDATE (year ,day )                                             //给出年及年中的第几天,生成日期串
    MAKETIME (hour ,minute ,second )                           //生成时间串
    MONTHNAME (date )                                                 //英文月份名
    NOW ( )                                                                         //当前时间
    SEC_TO_TIME (seconds )                                           //秒数转成时间
    STR_TO_DATE (string ,format )                                 //字串转成时间,以format格式显示
    TIMEDIFF (datetime1 ,datetime2 )                              //两个时间差
    TIME_TO_SEC (time )                                                 //时间转秒数]
    WEEK (date_time [,start_of_week ])                            //第几周
    YEAR (datetime )                                                          //年份
    DAYOFMONTH(datetime)                                          //月的第几天
    HOUR(datetime)                                                           //小时
    LAST_DAY(date)                                                         //date的月的最后日期
    MICROSECOND(datetime)                                          //微秒
    MONTH(datetime)                                                         //月
    MINUTE(datetime)                                                        //分返回符号,正负或0
    SQRT(number2)                                                             //开平方

SQL语句中的错误提示

在存储过程中处理SQL语句可能导致一条错误消息，并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如：

Error 1022, "Can't write;duplicate（重复） key intable"

Error 1048, "Column cannot benull"

Error 1052, "Column is ambiguous（歧义）"

Error 1062, "Duplicate entry forkey"

MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。http://dev.mysql.com/doc/refman/5.1/zh/error-handling.html

为了防止MySQL在一条错误消息产生时就停止处理，需要使用 DECLARE handler语句。该语句语句为错误代码声明了一个处理程序，它指明：对一条SQL语句的处理如果导致一条错误消息，将会发生什么。

DECLARE HANDLER语法格式为：

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

说明：

● handler_type：处理程序的类型，主要有三种：CONTINUE、EXIT和UNDO。对CONTINUE处理程序，MySQL不中断存储过程的处理。对于EXIT处理程序，当前 BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句暂时还不被支持。

● condition_value：给出SQLSTATE的代码表示。

condition_name是处理条件的名称。

SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值，MySQL错误代码也被支持，表示的形式为：ERROR= 'xxxx'。

● sp_statement：处理程序激活时将要执行的动作。

例：首先在表中插入一条数据：insert into tb values('1001','dd',20);

然后，创建一个存储过程：

mysql> create procedure exam()

-> begin

-> insert into tb values('1001','dd',20);

-> end

-> //

mysql> call exam();

ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'

再创建另一个存储过程，如果出现错误，让程序继续进行。

mysql> DELIMITER //

mysql> CREATE PROCEDURE exam_handler()

-> BEGIN

-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

-> SET @x=2;

-> INSERT INTO tb VALUES('1001', '张三', 20);

-> SET@x=3;

-> END

-> //

mysql> DELIMITER ;

再看执行结果：

mysql> call exam_handler();

Query OK, 0 rows affected (0.01 sec)

说明：在调用存储过程后，未遇到错误消息时处理程序未被激活，当执行INSERT语句出现出错消息时，MySQL检查是否为这个错误代码定义了处理程序。如果有，则激活该处理程序，本例中，INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条，接下来执行处理程序的附加语句（SET @x2=1）。此后，MySQL检查处理程序的类型，这里的类型为CONTINUE，因此存储过程继续处理，将用户变量x赋值为3。如果这里的INSERT语句能够执行，处理程序将不被激活，用户变量x2将不被赋值。

注意：不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

为了提高可读性，可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字。

DECLARE CONDITION语法格式为：

DECLARE condition_name CONDITION FOR condition_value

其中，condition_value：

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

说明：condition_name是处理条件的名称，condition_value为要定义别名的SQLSTATE或出错代码。

例：修改上例中的存储过程，将SQLSTATE '23000' 定义成NON_UNIQUE，并在处理程序中使用这个名称。如：

mysql> DELIMITER //

mysql> CREATE PROCEDURE exam_handler()

-> BEGIN

-> DECLARE NON_UNIQUE CONTINUE FOR SQLSTATE '23000';

-> DECLARE HANDLER FOR NON_UNIQUE SET @x2=1;

-> SET @x=2;

-> INSERT INTO tb VALUES('1001', '张三', 20);

-> SET@x=3;

-> END

-> //

mysql>DELIMITER ;

关于存储过程的官方文档：http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html

posted @ 2013-08-20 21:25 Jevo 阅读(4956) 评论(1) 编辑收藏举报

刷新页面返回顶部

Jevo的博客

MySQL存储过程

公告