现在我们开始着眼游标了。虽然我们的存储过程中的游标语法还并没有完整的实现,但是已经可以完成基本的事务如声明游标,打开游标,从游标里读取,关闭游标。
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的权力,因此过程可以执行。