好学IT学院:IT信息技术分享交流平台
来源:互联网  作者:作者:mysql-AB;翻译:陈朋奕  发布时间:2006-12-24  ★★★加入收藏〗〖手机版
摘要:Error Handling 异常处理好了,我们现在要讲的是异常处理1. Sample Problem: Log Of Failures 问题样例:故障记录当INSERT失败时,我希望能将其记录在日志文件中我们用来展示出错处理的问题样例是很……

现在我们开始着眼游标了。虽然我们的存储过程中的游标语法还并没有完整的实现,但是已经可以完成基本的事务如声明游标,打开游标,从游标里读取,关闭游标。

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


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一样,不可信)。


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的权力,因此过程可以执行。

  • 好学考试H5触屏版开放内测
  • 好学触屏公众号虎力全开、杨帆起航!