如何打破db2数据库中的循环 [英] how to break the loop in db2 database

问题描述

我创建了1个过程并使用了1个游标，使用这个游标我已经循环了但是从游标中获取的循环将进入无限循环.在我的程序代码下面.

i have created 1 procedure and used 1 cursor, using this cursor i have used to loop but that loop fetching from cursor is going to infinite loop. below my code for procedure.

    db2 "CREATE OR REPLACE PROCEDURE NEWEMPSELECT7 RESULT SETS 1 LANGUAGE SQL 
BEGIN 
DECLARE OUTRATE VARCHAR(50);
DECLARE C1 CURSOR for 
SELECT Emp_name FROM NEWEMP WHERE Emp_id=100;
OPEN C1;
LOOP
FETCH FROM C1 INTO OUTRATE;
CALL DBMS_OUTPUT.PUT_LINE(OUTRATE);
END LOOP;
CLOSE C1;
END" 

结果将进入无限循环.甚至我无法使用C1％NOTFOUND或任何关键字，因为db2不支持任何关键字来违反规则.

The result is going to infinite loop. even i am not able to use C1%NOTFOUND or any keyword, because db2 doesnt support any keyword to break the rule.

请提出如何打破db2中的循环的想法.

please suggest how to break this loop in db2.

推荐答案

如果您正在学习SQL PL，那么如果您花时间学习IBM

If you are learning SQL PL, it will help you if you spend time studying the IBM example programs and making them work for your environment.

这是一个基于您的代码的简单示例，可与Db2-LUW上的SAMPLE数据库一起使用:

Here is a trivial example based on your code, that works with the SAMPLE database on Db2-LUW:

请注意，可以使用更优雅的方式来编写此类循环，包括使用WHILE语句，使用SQLSCODE/SQLSTATE变量以及这些变量的比较等.

Note that there are more elegant ways to code such loops, including using the WHILE statement, using SQLSCODE/SQLSTATE variables and comparisons of these etc.

--#SET TERMINATOR @

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE NEWEMPSELECT7 
RESULT SETS 1 LANGUAGE SQL 
BEGIN 
    DECLARE OUTRATE VARCHAR(50);
    DECLARE v_at_end INTEGER default 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE C1 CURSOR for 
        SELECT LASTNAME FROM EMPLOYEE WHERE Empno=100;
    DECLARE CONTINUE HANDLER FOR not_found  SET v_at_end = 1 ;

    OPEN C1;
    fetch_loop:
    LOOP
        FETCH FROM C1 INTO OUTRATE;
        IF v_at_end <>0 THEN LEAVE fetch_loop; END IF;
        CALL DBMS_OUTPUT.PUT_LINE(OUTRATE);
    END LOOP;
    CLOSE C1;
END
@

这篇关于如何打破db2数据库中的循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！