第六章 MySQL存储过程

存储程序的CASE语句实现一个复杂的条件构造。如果 search_condition 求值为真，相应的SQL被执行。如果没 有搜索条件匹配，在ELSE子句里的语句被执行。 举例： CREATE PROCEDURE p2 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 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//
循环语句 LOOP … END LOOP 举例： CREATE PROCEDURE p5 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END //
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
流程控制
IF语句 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF IF实现了一个基本的条件构造。如果search_condition求 值为真，相应的SQL语句列表被执行。如果没有 search_condition匹配，在ELSE子句里的语句列表被执行。 statement_list可以包括一个或多个语句。
循环语句 WHILE … END WHILE 举例： CREATE PROCEDURE p4 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END //
举例： DELIMITER // CREATE PROCEDURE p1(IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END // DELIMITER ;
存储过程的优点
1. 存储过程只在创造时进行编译，以后每次执行存储过程都不 需再重新编译，而一般SQL语句每执行一次就编译一次,所以
使用存储过程可提高数据库执行速度。
2. 当对数据库进行复杂操作时（如对多个表进行Update、 Insert、Query、Delete时），可将此复杂操作用存储过程封 装起来与数据库提供的事务处理结合一起使用。 3. 存储过程可以重复使用，可减少数据库开发人员的工作量。 4. 安全性高，可设定只有某此用户才具有对指定存储过程的使 用权。
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type: CONTINUE | EXIT condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION 这个语句指定每个可以处理一个或多个条件的处理程序。如 果产生一个或多个条件，指定的语句被执行。 对一个CONTINUE处理程序，当前子程序的执行在执行处理 程序语句之后继续。对于EXIT处理程序，当前 BEGIN...END复合语句的执行被终止。
in 把数据从外部传递给存储过程 out 从存储过程内部返回值给外部使用者 inout 把数据传递给存储过程和将存储过程的返 回值传递给外部使用者
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name 这个语句被用来移除一个存储程序或函数。即， 从服务器移除一个制定的子程序。在MySQL 5.1中， 你必须有ALTER ROUTINE权限才可用此子程序。这 个权限被自动授予子程序的创建者。 IF EXISTS 子句是一个MySQL的扩展。如果程序 或函数不存在，它防止发生错误
变量赋值
变量赋值，SET语句： SET var_name = expr [, var_name = expr] ... 也可以用语句代替SET来为用户变量分配一个值。在这种 情况下，分配符必须为:=而不能用=，因为在非SET语句中= 被视为一个比较 操作符， 如下所示： mysql> SET @t1=0, @t2=0, @t3=0; mysql> SELECT @t1:=0,@t2:=0,@t3:=0; 对于使用select语句为变量赋值的情况，若返回结果为空 ，即没有记录，此时变量的值为上一次变量赋值时的值，如 果没有对变量赋过值，则为NULL。
存储过程的变量
声明变量： DECLARE var_name[,...] type [DEFAULT value] 这个语句被用来声明局部变量。要给变量提供一个默认值 ，需要包含一个DEFAULT子句。值可以被指定为一个表达式 ，不需要为一个常数。如果没有DEFAULT子句，初始值为 NULL。 局部变量的作用范围在它被声明的BEGIN ... END块内。它 可以被用在嵌套的块中，除了那些用相同名字声明变量的块。
变量赋值，SELECT ... INTO语句 SELECT col_name[,...] INTO var_name[,...] table_expr这个SELECT语法把选定的列直 接存储到变量。因此，只有单一的行可以被 取回。 SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
BEGIN...END复合语句
[begin_label:] BEGIN [statement_list] END [end_label] 存储子程序可以使用BEGIN ... END复合语句来包含多个语句。 statement_list 代表一个或多个语句的列表。statement_list之内 每个语句都必须用分号（；）来结尾。 复合语句可以被标记。除非begin_label存在，否则end_label不 能被给出，并且如果二者都存在，他们必须是同样的。 使用多重语句需要客户端能发送包含语句定界符;的查询字符串 。这个符号在命令行客户端被用delimiter命令来处理。改变查询 结尾定界符;（比如改变为//）使得; 可被用在子程序体中。
执行的本质都一样。只是函数有如只能返 回一个变量的限制。而存储过程可以返回 多个。而函数是可以嵌入在sql中使用的， 可以在select中调用，而存储过程不行。
函数限制比较多，比如不能用临时表，只 能用表变量。还有一些函数不可用等等。 而存储过程的限制相对就比较少。
一般来说，存储过程实现的功能要复杂一 点，而函数的实现的功能针对性比较强。 对于存储过程来说可以返回参数，而函数 只能返回值或者表对象。 存储过程一般是作为一个独立的部分来执 行，而函数可以作为查询语句的一个部分来 调用，由于函数可以返回一个表对象，因此 它可以在查询语句中位于FROM关键字的后 面。
[begin_label:] LOOP statement_list END LOOP [end_label] LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。 在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE 语句。
补充：迭代（ITERATE）语句
CREATE PROCEDURE p7 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END //
show procedure status; 查看存储过程状态
delimiter // DROP PROCEDURE IF EXISTS sp_test // CREATE PROCEDURE sp_test /* 存储过程名 */ (IN inparms INT,OUT outparams varchar(32)) /* 输入参数 */ BEGIN /* 语句块头 */ DECLARE var CHAR(10); /* 变量声明 */ DECLARE num int; IF inparms = 1 THEN /* IF条件开始*/ SET var = 'hello'; /* 赋值 */ ELSE SET var = 'world'; END IF; /* IF结束 */ INSERT INTO t1 VALUES (var); /* SQL语句 */ select count(*) from t1 into num; SELECT name FROM t1 LIMIT num, 1 INTO outparams; END // delimiter ; call sp_test(1, @out); Select @out; 事先创建表 create table t1(id int not null auto_increment,name varchar(45),primary key pk_id (可使用两种风格的注释