【YashanDB知识库】共享从 MySQL异常处理CONTINUE HANDLER的改写方法

本文内容来自YashanDB官网,原文内容请见https://www.yashandb.com/newsinfo/7575253.html?templateId=1718516

MySQL的CONTINUE HANDLER
迁移MySQL业务时,遇到了MySQL的CONTINUE HANDLER FOR not found用法。详情如下:

-- 表DDL

create table t1(c1 int primary key, c2 int);

create table t2(c1 int primary key, c2 int);

-- 数据

insert into t1 values(1,1),(2,2);

insert into t2 values(1,11);

commit;

 

-- 使用CONTINUE HANDLER的存储过程

DELIMITER $$

CREATE PROCEDURE demo(IN p1 int)

BEGIN

    DECLARE s bigint DEFAULT 0;

    DECLARE v1 bigint DEFAULT 0;

    DECLARE v2 bigint DEFAULT 0;

    DECLARE cur1 CURSOR FOR select c2 from t1 where c1=p1;

    DECLARE CONTINUE HANDLER FOR not found SET s=1;

    OPEN cur1;

    r_loop: LOOP

        IF s = 1 THEN

            LEAVE r_loop;

        END IF;

        -- 如果游标fetch结果not found,则设置s=1

        FETCH cur1 INTO v1;

        IF s = 0 THEN

            -- 如果查询结果not found,则设置s=1

            select c2 into v2 from t2 where c1=v1;

            -- 调试:输出v2值

            select v2;

            -- 调试:输出s值

            select s;

        END IF;

    END LOOP r_loop;

    CLOSE cur1;

END$$

DELIMITER ;

执行效果如下:

mysql> CALL DEMO(1);

+------+

| v2 |

+------+

| 11 |

+------+

1 row in set (0.00 sec)

 

+------+

| s |

+------+

| 0 |

+------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL DEMO(2);

+------+

| v2 |

+------+

| 0 |

+------+

1 row in set (0.00 sec)

 

+------+

| s |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL DEMO(3);

Query OK, 0 rows affected (0.00 sec)

YashanDB的改写方法

MySQL的CONTINUE HANDLER需要在YashanDB进行改写。改写原则:1)对于游标的执行,需要使用游标属性%notfound来改写;2)对于select语句的执行,则需要通过NO_DATA_FOUND的异常处理来改写。例如,以上示例的改写详情如下:

CREATE OR REPLACE PROCEDURE demo(p1 IN int) IS

    s bigint DEFAULT 0;

    v1 bigint DEFAULT 0;

    v2 bigint DEFAULT 0;

    CURSOR cur1 is select c2 from t1 where c1=p1;

BEGIN

    OPEN cur1;

    <<r_loop>>

    LOOP

        IF s = 1 THEN

            EXIT r_loop;

        END IF;

        -- 如果游标fetch结果not found,则设置s=1

        FETCH cur1 INTO v1;

        IF cur1%notfound THEN

            s :=1 ;

        END IF;

        IF s = 0 THEN

            -- 如果查询结果not found,则设置s=1

            BEGIN

                select c2 into v2 from t2 where c1=v1;

            EXCEPTION

            WHEN NO_DATA_FOUND THEN

                s :=1 ;

            END;

            -- 调试:输出v2值

            DBMS_OUTPUT.PUT_LINE(v2);

            -- 调试:输出s值

            DBMS_OUTPUT.PUT_LINE(s);

        END IF;

    END LOOP r_loop;

    CLOSE cur1;

END;

/

YashanDB的执行效果
改写后的执行效果如下:

SQL> SET SERVEROUTPUT ON

SQL> CALL DEMO(1);

11

0

 

PL/SQL Succeed.

 

SQL> CALL DEMO(2);

0

1

 

PL/SQL Succeed.

 

SQL> CALL DEMO(3);

 

PL/SQL Succeed.
posted @   YashanDB  阅读(0)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 【全网最全教程】使用最强DeepSeekR1+联网的火山引擎,没有生成长度限制,DeepSeek本体
点击右上角即可分享
微信分享提示