Mysql学习系列之二：Mysql存储过程及常见错误
时间：2008-09-19 来源：lincolnrainbow
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> </style>
Mysql 存储过程及常见错误：
1. Begin, end 模块
|
mysql> create procedure p11() -> BEGIN -> DECLARE x1 CHAR(5) DEFAULT 'outer'; -> BEGIN -> DECLARE x1 CHAR(5) DEFAULT 'inner'; -> SELECT x1; -> END //此处忘记加; -> SELECT x1; -> END;// ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT x1; END' at line 8
|
|
mysql> create procedure p11() -> BEGIN -> DECLARE x1 CHAR(5) DEFAULT 'outer'; -> BEGIN -> DECLARE x1 CHAR(5) DEFAULT 'inner'; -> SELECT x1; -> END; -> SELECT x1; -> END;// Query OK, 0 rows affected (0.00 sec) |
2. IF … THEN … ELSE IF … ELSE …
|
mysql> create procedure p12(IN parameter1 INT) -> BEGIN -> DECLARE variable1 INT; -> SET variable1 = parameter1 + 1; -> IF variable1 = 0 THEN -> INSERT INTO t VALUES(17); -> ENDIF; -> IF parameter1 = 0 THEN -> UPDATE t set s1 = s1 + 1; -> ELSE -> UPDATE t set s1 = s1 + 2; -> END IF; -> END;// ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; IF parameter1 = 0 THEN UPDATE t set s1 = s1 + 1; ELSE UPDATE t set ' at line 7
mysql> create procedure p12(IN parameter1 INT) -> BEGIN -> DECLARE variable1 INT; -> SET variable1 = parameter1 + 1; -> IF variable1 = 0 THEN -> INSERT INTO t VALUES(17); -> ENDIF; -> IF parameter1 = 0 THEN -> UPDATE t SET s1 = s1 + 1; -> ELSE -> UPDATE t SET s1 = s1 +2; -> END IF; -> END;// ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t S' at line 7 |
|
mysql> create procedure p12(in p1 int) -> begin -> declare v1 int; -> set v1 = p1 + 1; -> if v1 = 0 then -> insert into t values(17); -> end if; -> if p1 = 0 then update t set s1 = s1 +1; -> else update t set s1 = s1 + 2; -> end if; -> end;// Query OK, 0 rows affected (0.00 sec)
mysql> call p12(0)// Query OK, 4 rows affected (0.01 sec)
mysql> select * from t; -> // +------+ | s1 | +------+ | 6 | | 6 | | 6 | | 6 | +------+ 4 rows in set (0.00 sec)
mysql> call p12(-1)// Query OK, 5 rows affected (0.00 sec)
mysql> select * from t; -> // +------+ | s1 | +------+ | 8 | | 8 | | 8 | | 8 | | 19 | +------+ 5 rows in set (0.00 sec) |
3. case 语句
|
mysql> create procedure p13(in p1 int) -> begin -> declare v1 int; -> set v1 = p1 + 1; -> case v1 -> when 0 then insert into t values(8); -> when 1 then insert into t values(9); -> else insert into t values(10); -> end case; -> end;// Query OK, 0 rows affected (0.01 sec) |
|
mysql> call p13(-1)// Query OK, 1 row affected (0.00 sec)
mysql> select * from t; -> // +------+ | s1 | +------+ | 8 | | 8 | | 8 | | 8 | | 19 | | 8 | +------+ 6 rows in set (0.00 sec) |
|
|
|
mysql> call p13(NULL)// Query OK, 1 row affected (0.00 sec)
mysql> select * from t// +------+ | s1 | +------+ | 8 | | 8 | | 8 | | 8 | | 19 | | 8 | | 10 | +------+ 7 rows in set (0.00 sec) |
4. while语句
|
插入前： mysql> select * from t// +------+ | s1 | +------+ | 8 | | 8 | | 8 | | 8 | | 8 | +------+ 5 rows in set (0.00 sec)
|
|
mysql> create procedure p14() -> begin -> declare v int; -> set v = 0; -> while v < 5 do -> insert into t values(v); -> set v = v+1; -> end while; -> end;// Query OK, 0 rows affected (0.01 sec)
|
|
mysql> call p14()// Query OK, 1 row affected (0.01 sec)
|
|
mysql> select * from t// +------+ | s1 | +------+ | 8 | | 8 | | 8 | | 8 | | 1 | | 8 | | 0 | | 2 | | 3 | | 4 | +------+ 10 rows in set (0.01 sec)
|
5. repeat … until 语句
|
mysql> create procedure p15() -> begin -> declare v int; -> set v = 0; -> repeat -> insert into t values(v); -> set v = v+1; -> until v >= 5 -> end repeat; -> end;// Query OK, 0 rows affected (0.00 sec) |
|
mysql> call p15()// Query OK, 1 row affected (0.00 sec)
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 5 rows in set (0.00 sec) |
6. loop语句
|
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 5 rows in set (0.00 sec) |
|
mysql> create procedure p16() -> begin -> declare v int; -> set v = 0; -> loop_label: LOOP -> insert into t values(v); -> set v = v+1; -> if v >= 5 then -> leave loop_label; -> end if; -> end LOOP; -> end;// Query OK, 0 rows affected (0.01 sec) |
|
mysql> call p16()// Query OK, 1 row affected (0.00 sec) |
|
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 3 | | 4 | | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 10 rows in set (0.01 sec) |
7. 标签的使用
|
mysql> create procedure p17() -> label_1:begin -> label_2: while 0 = 1 do leave label_2; end while; -> label_3: repeat leave label_3; until 0=0 end repeat; -> label_4: loop leave label_4; end loop; -> end;// Query OK, 0 rows affected (0.01 sec) |
|
mysql> call p17()// Query OK, 0 rows affected (0.00 sec) |
8. iterate
|
mysql> create procedure p20() -> begin -> declare v int; -> set v = 0; -> loop_label: LOOP -> if v = 3 then -> set v = v + 1; -> iterate loop_label; -> end if; -> insert into t values(v); -> set v = v + 1; -> if v >= 5 then -> leave loop_label; -> end if; -> end loop; -> end;// Query OK, 0 rows affected (0.01 sec) |
|
mysql> call p20()// Query OK, 1 row affected (0.00 sec)
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 4 | +------+ 4 rows in set (0.00 sec) |
Error Handling异常处理
1. 问题样例： 故障记录
当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 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db5/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`)) （这里显示的是系统的出错信息） |
我开始要创建一个主键表，以及一个外键表。我们使用的是InnoDB，因此外键关联检查是打开的。然后当我向外键表中插入非主键表中的值时，动作将会失败。当然这种条件下可以很快找到错误号1452。
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 1452 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是用来处理异常的。意
思是如果错误1452发生了，这个程序将会在错误记录表中插入一行。EXIT意思是当动作成功提交后退出这个复合语句。
5. Sample Problem: Log Of Errors
|
mysql> call p22(5)// Query OK, 1 row affected (0.04 sec) |
调用这个存储过程会失败，这很正常，因为5值并没有在主键表中出现。但是没有错误信息
返回因为出错处理已经包含在过程中了。t3表中没有增加任何东西，但是error_log表中记录下了一些信息，这就告诉我们 INSERT into table t3 动作失败。
|
mysql> select * from error_log// +-------------------------------------------------------------+ | error_message | +-------------------------------------------------------------+ | Time:2008-08-10.Foreign Key Reference Failure For Value = 5 | +-------------------------------------------------------------+ 1 row in set (0.00 sec) |
|
mysql> select * from t3// Empty set (0.00 sec) |
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处理是如何工作的。
|
这个存储过程的第一个执行的语句是"SET @x = 1"。 运行后值1被插入到主键表中。 然后@x的值变为2。 然后程序尝试再次往主键表中插入数值，但失败了，因为主键有唯一性限制。 由于插入失败，错误处理程序被触发，开始进行错误处理。下一个执行的语句是错误处理的语 句，@x2被设为2。 到这里并没有结束，因为这是CONTINUE异常处理。所以执行返回到失败的插入语句之后， 继续执行将@x设定为3动作。 |
|
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) |
1. DECLARE CONDITION
|
CREATE PROCEDURE p24 () BEGIN DECLARE `Constraint Violation` //注意这里是反引号``, not ‘’ 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是约束错误。
|
mysql> select * from t2;// Empty set (0.01 sec)
mysql> call p24()// Query OK, 0 rows affected (0.03 sec)
mysql> select * from t2// Empty set (0.00 sec) |
我们调用这个存储过程看看结果是什么，从上面结果我们看到表t2没有插入任何记录。
全部事务都回滚了。这正是我们想要的。
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; //1 DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; //2 DECLARE CONTINUE HANDLER FOR NOT FOUND //3 SET b = 1; OPEN cur_1; //4 REPEAT FETCH cur_1 INTO a; //5 UNTIL b = 1 //6, 此处没分号 END REPEAT; CLOSE cur_1; //7 SET return_val = a; END;//
|
我们看一下包含游标的存储过程的新例子
1. 这个过程开始声明了三个变量。附带说一下，顺序是非常重要的。首先要进行变量声明，然后声明条件，随后声明游标，再后面才是声明错误处理器。如果你没有按顺序声明，系统会提示错误信息。
2. 程序第二步声明了游标cur_1，如果你使用过嵌入式SQL的话，就知道这和嵌入式SQL差不多。
3. 最后进行的是错误处理器的声明。这个CONTINUE处理没有引用SQL错误代码和SQLSTATE值。它使用的是NOT FOUND系统返回值，这和SQLSTATE 02000是一样的。
4. 过程第一个可执行的语句是OPEN cur_1，它与SELECT s1 FROM t语句是关联的， 过程将执行SELECT s1 FROM t，返回一个结果集。
5. 这里第一个FETCH语句会获得一行从SELECT产生的结果集中检索出来的值，然而表t中有多行，因此这个语句会被执行多次，当然这是因为语句在循环块内。
6. 最后当MySQL的FETCH没有获得行时，CONTINUE处理被触发，将变量b赋值为1。
7. 到了这一步 UNTIL b=1 条件就为真，循环结束。在这里我们可以自己编写代码关闭游标， 也可以由系统执行，系统会在复合语句结束时自动关闭游标，但是最好不要太依赖系统的 自动关闭行为（译注：这可能跟Java的Gc一样，不可信）
这个例程中我们为输出参数指派了一个局部变量，这样在过程结束后的结果仍能使用。
|
mysql> call p25(@return_val)// Query OK, 0 rows affected (0.01 sec) |
|
mysql> select @return_val// +-------------+ | @return_val | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
|
|
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 4 | +------+ 4 rows in set (0.00 sec) |
Functions 函数
Summary: 摘要
CREATE FUNCTION
Limitations of functions 函数的限制
我们已经很清楚可以在存储过程中使用的元素了。下面我要讲的是前面没有提到的函数。
CREATE FUNCTION 创建函数
|
CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // |
|
mysql> insert into t values(factorial(3.1415926))// Query OK, 1 row affected, 1 warning (0.00 sec) |
|
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 4 | | 6 | +------+ 5 rows in set (0.00 sec) |
|
mysql> insert into t values(factorial(6))// Query OK, 1 row affected (0.00 sec)
|
|
mysql> select * from t// +------+ | s1 | +------+ | 0 | | 1 | | 2 | | 4 | | 6 | | 720 | +------+ 6 rows in set (0.00 sec) |
|
|
相关阅读 更多 +
