mysql 存储过程，游标和错误提示

最新推荐文章于 2023-06-04 17:21:56 发布

fangzy0112

最新推荐文章于 2023-06-04 17:21:56 发布

阅读量4.2k

点赞数

分类专栏： mysql

mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

SQL语句中的错误提示

在存储过程中处理SQL语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条INSERT语句会导致一个出错消息，并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如，SQLSTATE 23000属于如下的出错代码：

Error 1022, "Can't write;duplicate（重复） key intable"

Error 1048, "Column cannot benull"

Error 1052, "Column is ambiguous（歧义）"

Error 1062, "Duplicate entry forkey"

MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。

为了防止MySQL在一条错误消息产生时就停止处理，需要使用到DECLAREhandler语句。该语句语句为错误代码声明了一个所谓的处理程序，它指明：对一条SQL语句的处理如果导致一条错误消息，将会发生什么。

DECLARE HANDLER语法格式为：

DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement

其中，handler_type为：

Continue

| EXIT

| UNDO

condition_value为：

SQLstate [VALUE] sqlstate_value

| condition_name

| SQLwarning

| NOT FOUND

| SQLexception

| mysql_error_code

说明：

● handler_type：处理程序的类型，主要有三种：CONTINUE、EXIT和UNDO。对CONTINUE处理程序，MySQL不中断存储过程的处理。对于EXIT处理程序，当前 BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句暂时还不被支持。

● condition_value：给出SQLSTATE的代码表示。

condition_name是处理条件的名称，接下来会讲到。

SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值，MySQL错误代码也被支持，表示的形式为：ERROR= 'xxxx'。

● sp_statement：处理程序激活时将要执行的动作。

例：创建一个存储过程，向XS表插入一行数据（'081101', '王民', '计算机', 1, '1990-02-10',50 , NULL, NULL），已知学号081101在XS表中已存在。如果出现错误，程序继续进行。

USE XSCJ;

DELIMITER $$

CREATE PROCEDURE MY_INSERT ()

BEGIN

DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

SET@x=2;

INSERTINTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

SET@x=3;

END$$

DELIMITER ;

说明：在调用存储过程后，未遇到错误消息时处理程序未被激活，当执行INSERT语句出现出错消息时，MySQL检查是否为这个错误代码定义了处理程序。如果有，则激活该处理程序，本例中，INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句（SET @x2=1）。此后，MySQL检查处理程序的类型，这里的类型为CONTINUE，因此存储过程继续处理，将用户变量x赋值为3。如果这里的INSERT语句能够执行，处理程序将不被激活，用户变量x2将不被赋值。

注意：不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

为了提高可读性，可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字。

DECLARE CONDITION语法格式为：

DECLARE condition_name CONDITION FORcondition_value

其中，condition_value：

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

说明：condition_name是处理条件的名称，condition_value为要定义别名的SQLSTATE或出错代码。

例：修改上例中的存储过程，将SQLSTATE '23000' 定义成NON_UNIQUE，并在处理程序中使用这个名称。程序片段为：

BEGIN

DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';

DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET @x2=1;

SET @x=2;

INSERT INTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

SET @x=3;

END;

游标

一条SELECT...INTO语句返回的是带有值的一行，这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据，如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中，游标一定要在存储过程或函数中使用，不能单独在查询中使用。

使用一个游标需要用到4条特殊的语句：DECLARE CURSOR（声明游标）、OPEN CURSOR（打开游标）、FETCH CURSOR（读取游标）和CLOSE CURSOR（关闭游标）。

如果使用了DECLARE CURSOR语句声明了一个游标，这样就把它连接到了一个由SELECT语句返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着，可以用FETCH CURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针，它指向当前的一行数据，使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时，使用CLOSECURSOR语句关闭这个游标。

（1）声明游标

语法格式：DECLAREcursor_name cursor for select_statement

说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行的数据。这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。

注意：这里的SELECT子句不能有INTO子句。

下面的定义符合一个游标声明：

DECLARE XS_CUR1 CURSOR FOR

SELECT 学号,姓名,性别,出生日期,总学分

FROM XS

WHERE 专业名 = '计算机';

注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。

（2）打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为：OPEN cursor_name