MySQL 5.0 新特性教程存储进程:第四讲

泉源：网海拾贝

作者：mysql AB;翻译:陈朋奕

Error Handling 特殊很是处置处罚

　　好了，我们目前要讲的是特殊很是处置处罚

1. Sample Problem: Log Of Failures 标题问题样例：障碍记录

　　当INSERT失败时，我希望能将其记取实日志文件中我们用来闪现出错处置处罚的标题问题样例是很
平凡的。我希望失失落错误的记录。当INSERT失败时，我想在另一个文件中记下这些错误的
信息，例如出错时分，出错原因等。我对拔出奇异感兴味的原因是它将违背外键联系关连的束厄狭隘

2. Sample Problem: Log Of Failures (2)

mysql> CREATE TABLE t2
s1 INT, PRIMARY KEY (s1))
engine=innodb;//
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
FOREIGN KEY (s1) REFERENCES t2 (s1))
engine=innodb;//
mysql> INSERT INTO t3 VALUES (5);//
...
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails（这里体现的是系统的出错信息）

　　我入部着手要树立一个主键表，以及一个外键表。我们运用的是InnoDB，因其它键联系关连反省是打
开的。然后当我向外键表中拔出非主键表中的值时，举措将会失败。当然这种条件下可以很
快找到错误号1216。

3. Sample Problem: Log Of Failures

CREATE TABLE error_log (error_message
CHAR(80))//

　　下一步便是树立一个在做拔出举措出错时存储错误的表。

4. Sample Problem: Log Of Errors

CREATE PROCEDURE p22 (parameter1 INT)
BEGIN

DECLARE EXIT HANDLER FOR 1216
INSERT INTO error_log VALUES
(CONCAT('Time: ',current_date,
'. Foreign Key Reference Failure For
Value = ',parameter1));
INSERT INTO t3 VALUES (parameter1);
END;//

　　上面便是我们的步调。这里的第一个语句DECLARE EXIT HANDLER是用来处置处罚特殊很是的。意思是倘若错误1215发作了，这个步调将会在错误记录表中拔出一行。EXIT意思是当举措告成提交后加入这个复合语句。

5. Sample Problem: Log Of Errors

CALL p22 (5) //

　　挪用这个存储进程会失败，这很正常，因为5值并没有在主键表中出现。但是没有错误信息
前往因为出错处置处罚曾经包孕在进程中了。t3表中没有添加任何器材，但是error_log表中记录
下了一些信息，这就关照我们INSERT into table t3举措失败。

DECLARE HANDLER syntax 声明特殊很是处置处罚的语法

DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement

　　上面便是错误处置处罚的用法，也便是一段当步调出错后主动触发的代码。MySQL允许两种处置处罚器，一种是EXIT处置处罚，我们刚才所用的便是这种。另一种便是我们将要演示的，CONTINUE处置处罚，它跟EXIT处置处罚相似，分歧在于它实验后，原主步调依然继续运转，那么这个复合语句就没有出口了。

1. DECLARE CONTINUE HANDLER example CONTINUE处置处罚例子

CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//

　　这是MySQL参考手册上的CONTINUE处置处罚的例子，这个例子非常好，以是我把它拷贝到这里。
　　经由这个例子我们可以看出CONTINUE处置处罚是如何事情的。

2. DECLARE CONTINUE HANDLER声明CONTINUE特殊很是处置处罚

CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//

　　这次我将为SQLSTATE值定义一个处置处罚步调。还记得反面我们运用的MySQL错误代码1216吗？
　　实际上这里的23000SQLSTATE是更常用的，当外键束厄狭隘出错或主键束厄狭隘出错就被挪用了。

3. DECLARE CONTINUE HANDLER

CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1; <--
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//

　　这个存储进程的第一个实验的语句是"SET @x = 1"。

4. DECLARE CONTINUE HANDLER example

　　运转后值1被拔出到主键表中。

5. DECLARE CONTINUE HANDLER

　　然后@x的值变为2。

6. DECLARE CONTINUE HANDLER example

　　然后步调检验检验再次往主键表中拔出数值，但失败了，因为主键有唯一性限制。

7. DECLARE CONTINUE HANDLER example

　　因为拔出失败，错误处置处罚步调被触发，入部着手制止错误处置处罚。下一个实验的语句是错误处置处罚的语句，@x2被设为2。

8. DECLARE CONTINUE HANDLER example

　　到这里并没有完毕，因为这是CONTINUE特殊很是处置处罚。以是实验前往到失败的拔出语句之后，继续实验将@x设定为3举措。

9. DECLARE CONTINUE HANDLER example

mysql> CALL p23()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, @x2//

------ ------
| @x | @x2 |
------ ------
| 3 | 1 |
------ ------
1 row in set (0.00 sec)

　　运转进程后我们观测@x的值，很确定的可以知道是3，观测@x2的值，为1。从这里可以判别步调运转无误，完全凭据我们的思路制止。大大家可以花点时分去补救错误处置处罚器，让反省放在语句段的首部，而不是放在能够出现错误的地方，当然那样看起来步调很杂乱，跳来跳去的感触传染。但是何等的代码很平安也很清楚。

1. DECLARE CONDITION

CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //

　　这是其它一个错误处置处罚的例子，在反面的根基上批改的。实际上你可给SQLSTATE约莫错误代码其他的名字，你就可以在处置处罚中运用自己定义的名字了。上面看它是如何完成的：我把表t2定义为InnoDB表，以是对这个表的拔出操作城市ROLLBACK（回滚），ROLLBACK（回滚事变）也是恰好会发作的。因为对主键拔出两个异常的值会招致SQLSTATE 23000错误发作，这里SQLSTATE 23000是束厄狭隘错误。

2. DECLARE CONDITION声明条件

　　这个束厄狭隘错曲解招致ROLLBACK（回滚事变）和SQLSTATE 23000错误发作。

3. DECLARE CONDITION

mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)

mysql> SELECT * FROM t2//
Empty set (0.00 sec)

　　我们挪用这个存储进程看结果是什么，从上面结果我们看到表t2没有拔出任何记录。全部事变都回滚了。这恰是我们想要的。

4. DECLARE CONDITION

mysql> CREATE PROCEDURE p9 ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
-> END;//
Query OK, 0 rows affected (0.00 sec)

　　这里是三个预声明的条件：NOT FOUND (找不到行), SQLEXCEPTION (错误),SQLWARNING (警告或表明)。因为它们是预声明的，是以不需求声明条件就可以运用。不外倘若你去做何等的声明："DECLARE SQLEXCEPTION CONDITION ..."，你将会失失落错误信息提醒。

Cursors 游标

　　游标完成结果择要：

DECLARE cursor-name CURSOR FOR SELECT ...;
OPEN cursor-name;
FETCH cursor-name INTO variable [, variable];
CLOSE cursor-name;

　　目前我们入部着手着眼游标了。当然我们的存储进程中的游口号法还并没有残缺的完成，但是曾经可以完成根蒂根基的事变如声明游标，掀开游标，从游标里读取，关闭游标。

1. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　我们看一下包孕游标的存储进程的新例子。

2. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT; <--
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　这个进程入部着手声清楚懂得三个变量。附带说一下，次序诟谇常严重的。首先要制止变量声明，然后声明条件，随后声明游标，再反面才是声明错误处置处罚器。倘若你没有按次序声明，系统会提醒错误信息。

3. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <--
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　步调第二步声清楚懂得游标cur_1，倘若你运用过嵌入式SQL的话，就知道这和嵌入式SQL差不多。

4. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND <--
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　最后制止的是错误处置处罚器的声明。这个CONTINUE处置处罚没有引用SQL错误代码和SQLSTATE值。它运用的是NOT FOUND系统前往值，这和SQLSTATE 02000是一样的。

5. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1; <--
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　进程第一个可实验的语句是OPEN cur_1，它与SELECT s1 FROM t语句是联系关连的，进程将实验SELECT s1 FROM t，前往一个结果集。

6. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a; <--
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　这里第一个FETCH语句会得到一行从SELECT发作的结果会合检索出来的值，但是表t中有多行，是以这个语句会被实验屡次，当然这是因为语句在循环块内。

7. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　最后当MySQL的FETCH没有得到行时，CONTINUE处置处罚被触发，将变量b赋值为1。

8. Cursor Example

　　到了这一步UNTIL b=1条件就为真，循环完毕。在这里我们可以自己编写代码关闭游标，也可以由系统实验，系统会在复合语句完毕时主动关闭游标，但是最好不要太依赖系统的主动关闭举动（译注：这能够跟Java的Gc一样，不可信）。

9. Cursor Example

　　这个例程中我们为输出参数指派了一个部分变量，何等在进程竣预先的结果仍能运用。

10. Cursor Example

mysql> CALL p25(@return_val)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @return_val//
-------------
| @return_val |
-------------
| 5 |
-------------
1 row in set (0.00 sec)

　　上面是进程挪用后的结果。可以看到return_val参数得到了数值5，因为这是表t的最后一行。
　　由此可以知道游标事情正常，出错处置处罚也事情正常。

Cursor Characteristics 游标的特性

　　择要：
　　READ ONLY只读属性
　　NOT SCROLLABLE次序读取
　　ASENSITIVE敏感

　　在5.0版的MySQL中，你只可以从游标中取值，不能对其制止更新。因为游标是（READONLY）只读的。你可以何等做：

FETCH cursor1 INTO variable1;
UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;

　　游标也是不可以迁移改变的，只允许逐个读取下一行，不能在结果会合行进或后退。上面代码便是错误的：

FETCH PRIOR cursor1 INTO variable1;
FETCH ABSOLUTE 55 cursor1 INTO variable1;

　　同时也不允许在已掀开游标制止操作的表上实验updates事变，因为游标是（ASENSITIVE）敏感的。因为倘若你不制止update事变，那就不知道结果会变成什么。倘若你运用的是InnoDB而不是MyISAM存储引擎的话，结果也会纷例如样。

Security 平安步调

　　择要
　　Privileges (1) CREATE ROUTINE
　　Privileges (2) EXECUTE
　　Privileges (3) GRANT SHOW ROUTINE?
　　Privileges (4) INVOKERS AND DEFINERS

　　这里我们要联系一些关于特权和平安相干的标题问题。但因为在MySQL平安步调的结果并没有完全，以是我们不会对其制止过多联系。

1. Privileges CREATE ROUTINE

GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];

　　目前用root就可以了

　　在这里要引见的特权是CREATE ROUTINE，它不单同其他特权一样可以树立存储进程和函数，还可以树立视图和表。Root用户拥有这种特权，同时还有ALTER ROUTINE特权。

2. Privileges EXECUTE

GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];

　　上面的特权是决议你可否可以运用或实验存储进程的特权，进程树立者默许拥有这个特权。

3. Privileges SHOW ROUTINE?

GRANT SHOW ROUTINE ON db6.* TO joey
[WITH GRANT OPTION];

　　因为我们曾经有控制视图的特权了：GRANT SHOW VIEW。以是在这个根基上，为了保证兼容，日后能够会添加GRANT SHOW ROUTINE特权。何等做是不太相符范例的，在写本书的时候，MySQL还没完成这个结果。

4. Privileges Invokers and Definers 特权挪用者和定义者

CREATE PROCEDURE p26 ()
SQL SECURITY INVOKER
SELECT COUNT(*) FROM t //
CREATE PROCEDURE p27 ()
SQL SECURITY DEFINER
SELECT COUNT(*) FROM t //
GRANT INSERT ON db5.* TO peter; //

　　目前我们测试一下SQL SECURITY子句吧。Security是我们反面提到的步调特性的一部门。你root用户，将拔出权赋给了peter。然后运用peter上岸制止新的事情，我们看peter可以如何运用存储进程，垂青：peter没有对表t的select权力，只要root用户有。

5. Privileges Invokers and Definers

/* Logged on with current_user = peter */运用帐户peter上岸

mysql> CALL p26();
ERROR 1142 (42000): select command denied to user
'peter'@'localhost' for table 't'
mysql> CALL p27();
----------
| COUNT(*) |
----------
| 1 |
----------
1 row in set (0.00 sec)

　　当peter检验检验挪用含有挪用失密步调的进程p26时会失败。那是因为peter没有对表的select的权力。

　　但是当petre挪用含有定义失密步调的进程时就能告成。原因是root有select权力，Peter有root的权力，是以进程可以实验。

版权声明： 原创作品，允许转载，转载时请务必以超链接方式标明文章原始来由、作者信息和本声明。不然将追究法令责任。

MySQL 5.0 新特性教程 存储进程:第四讲

MySQL 5.0 新特性教程存储进程:第四讲