MySQL 5.0 新特点教程 存储历程:第四讲[MySQL防范]

Error Handling 非常处理

　　 好了,我们目前要讲的是非常处理

1. Sample Problem: Log Of Failures 问题样例:弊端记录

　　 当INSERT失利时,我但愿能将其记录在日记文件中我们用来展示出错处理的问题样例是很
普通的.我但愿得到错误的记录.当INSERT失利时,我想在另一个文件中记下这些错误的信息,比方出错时间,出错缘由等.我对插入分外感爱好的缘由是它将违反外键关联的约束

2. Sample Problem: Log Of Failures (2)

　　下一步就是成立一个在做插入行动出错时存储错误的表.

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
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;//
　　运行后值1被插入到主键表中.


5. 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;//
　　然后@x的值变成2.

6. DECLARE CONTINUE HANDLER example
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;//
　　然后程序尝试再次往主键表中插入数值,但失利了,因为主键有唯一性限制.

7. DECLARE CONTINUE HANDLER example
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;//
　　由于插入失利,错误处理程序被触发,开始举行错误处理.下一个履行的语句是错误处理的语句,@x2被设为2.


8. DECLARE CONTINUE HANDLER example
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;//
　　到这里并没有完毕,因为这是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声明条件
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; //
　　这个约束错曲解招致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 ...",你将会得到错误信息提醒.

[1] [2] [3] 下一页  

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;//

8. 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;//
　　到了这一步UNTIL b=1条件就为真,循环完毕.在这里我们可以自己编写代码关闭游标,也可以由系统履行,系统会在复合语句完毕时自动关闭游标,但是最好不要太依靠系统的自动关闭行为（译注:这大概跟Java的Gc一样,不可托）.

上一页  [1] [2] [3] 下一页  

9. 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;//
　　这个例程中我们为输出参数指派了一个部分变量,这样在历程完毕后的后果仍能利用.


10. 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> 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的权利,因此历程可以履行.

上一页  [1] [2] [3]