mysql从头学一 1.1存储过程基础（1）

变量的使用 存储过程和函数中可以使用变量，而且在 MySQL 5.1 版本中，变量是不区分大小写的。

1．变量的定义 通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中，可以用 在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一 次声明多个相同类型的变量。如果需要，可以使用 DEFAULT 赋默认值。 定义一个变量的语法如下： DECLARE var_name[,...] type [DEFAULT value] 例如，定义一个 DATE 类型的变量，名称是 last_month_start： DECLARE last_month_start DATE;

2．变量的赋值 变量可以直接赋值，或者通过查询赋值。 直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下： SET var_name = expr [, var_name = expr] ... 给刚才定义的变量 last_month_start 赋值，具体语法如下： 156 SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH); 也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下

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

通过查询将结果赋值给变量 v_payments：

CREATE FUNCTION get_customer_balance(p_customer_id INT,
p_effective_date DATETIME)
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
 …
 DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
 …
 SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
 FROM payment
 WHERE payment.payment_date <= p_effective_date
 AND payment.customer_id = p_customer_id;
 …
 RETURN v_rentfees + v_overfees - v_payments;
END $$
12.2.5 定义条件和处理
条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。
1．条件的定义
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
2．条件的处理
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
157
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code
下面将通过两个例子来说明：在向 actor 表中插入记录时，如果没有进行条件的处理，那么
在主键重的时候会抛出异常并退出，如果对条件进行了处理，那么就不会再抛出异常。
（1）当没有进行条件处理时，执行结果如下：
mysql> SELECT MAX(actor_id) FROM actor;
+---------------+
| MAX(actor_id) |
+---------------+
| 200 |
+---------------+
1 ROW IN SET (0.00 sec)
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE actor_insert ()
 -> BEGIN
 -> SET @x = 1;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');
 -> SET @x = 2;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');
 -> SET @x = 3;
 -> END;
 -> $$
QUERY OK, 0 ROWS affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL actor_insert();
ERROR 1062 (23000): DUPLICATE entry '1' FOR KEY 'PRIMARY'
mysql> SELECT @x;
+------+
| @x |
+------+
| 2 |
+------+
1 ROW IN SET (0.00 sec)
从上面的例子可以看出，执行到插入 actor_id=1 的记录时，会主键重并退出，没有执行到下
面其他的语句。
（2）当对主键重的异常进行处理时，执行结果如下：
158
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE actor_insert ()
 -> BEGIN
 -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
 -> SET @x = 1;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');
 -> SET @x = 2;
 -> INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');
 -> SET @x = 3;
 -> END;
 -> $$
QUERY OK, 0 ROWS affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL actor_insert();
QUERY OK, 0 ROWS affected (0.06 sec)
mysql> SELECT @x,@x2;
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 ROW IN SET (0.00 sec)
调用条件处理的过程，再遇到主键重的错误时，会按照定义的处理方式进行处理，由于例子
中定义的是 CONTINUE，所以会继续执行下面的语句。
handler_type 现在还只支持 CONTINUE 和 EXIT 两种，CONTINUE 表示继续执行下面的语句，
EXIT 则表示执行终止，UNDO 现在还不支持。
condition_value 的值可以是通过 DECLARE 定义的 condition_name，可以是 SQLSTATE 的值或
者 mysql-error-CODE 的值或者 SQLWARNING、NOT FOUND、SQLEXCEPTION，这 3 个值是 3 种
定义好的错误类别，分别代表不同的含义。
·SQLWARNING 是对所有以 01 开头的 SQLSTATE 代码的速记。
·NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记。
·SQLEXCEPTION 是对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记。
因此，上面的例子还可以写成以下几种方式：
--捕获 mysql-error-code：
DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
--事先定义 condition_name：
DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
--捕获 SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;
159
12.2.6 光标的使用
在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、
OPEN、FETCH 和 CLOSE，其语法分别如下。
 声明光标：
DECLARE cursor_name CURSOR FOR select_statement
 OPEN 光标：
OPEN cursor_name
 FETCH 光标：
FETCH cursor_name INTO var_name [, var_name] ...
 CLOSE 光标：
CLOSE cursor_name
以下例子是一个简单的使用光标的过程，对 payment 表按照行进行循环的处理，按照 staff_id
值的不同累加 amount 的值，判断循环结束的条件是捕获 NOT FOUND 的条件，当 FETCH 光
标找不到下一条记录的时候，就会关闭光标然后退出过程。
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE payment_stat ()
 -> BEGIN
 -> DECLARE i_staff_id INT;
 -> DECLARE d_amount DECIMAL(5,2);
 -> DECLARE cur_payment CURSOR FOR SELECT staff_id,amount FROM payment;
 -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
 ->
 -> SET @x1 = 0;
 -> SET @x2 = 0;
 ->
 -> OPEN cur_payment;
 ->
 -> REPEAT
 -> FETCH cur_payment INTO i_staff_id, d_amount;
 -> IF i_staff_id = 2 THEN
 -> SET @x1 = @x1 + d_amount;
 -> ELSE
 -> SET @x2 = @x2 + d_amount;
 -> END IF;
 -> UNTIL 0 END REPEAT;
 ->
 -> CLOSE cur_payment;
 ->
 -> END;
 -> $$
QUERY OK, 0 ROWS affected (0.00 sec)
mysql> DELIMITER ;
160
mysql>
mysql> CALL payment_stat();
QUERY OK, 0 ROWS affected (0.11 sec)
mysql> SELECT @x1,@x2;
+----------+----------+
| @x1 | @x2 |
+----------+----------+
| 33927.04 | 33489.47 |
+----------+----------+
1 ROW IN SET (0.00 sec)
注意：变量、条件、处理程序、光标都是通过 DECLARE 定义的，它们之间是有先后顺序的要
求的。变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序