代码之家 › 专栏 › 技术社区 › Ashutosh

如何打破db2数据库中的循环

db2

-1

Ashutosh · 技术社区 · 7 年前

我创建了一个过程并使用了一个游标,我使用这个游标来循环但从游标获取的循环将是无限循环。下面是我的程序代码。

    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不支持任何关键字来打破规则。

1 回复 | 直到 7 年前

mao 7 年前

如果您正在学习SQLPL,那么花时间学习IBM将对您有所帮助 example

下面是一个基于您的代码的简单示例,用于Db2 LUW上的示例数据库:

--#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
@

spinjector 6 年前

因为这个概念在任何语言中都是有效的,所以我将用伪代码表示它,如下所示:

Begin Procedure

Declare Variable safety_counter As Integer

Begin loop
    ...
    <body of code>
    If some_criteria = True Then          <-- this is your normal exit
        Exit Loop
    End If
    ...
    safety_counter = safety_counter + 1
    If safety_counter >= 1000             <-- set to any value you wish
        Exit Loop                         <-- this is the emergency exit
    End If
End Loop

如果循环很简单,那么两个退出条件可以用相同的格式编写,如果:

    If some_criteria = True Then          <-- normal exit
    Or safety_counter >= 1000             <-- emergency exit
        Exit Loop
    End If
    safety_counter = safety_counter + 1

返回的错误值可能如下所示:

Begin Procedure

Declare Variable result_value   As Integer
Declare Variable safety_counter As Integer

Begin loop
    ...
    <body of code>
    If some_criteria = True Then          <-- this is your normal exit
        Set result_value = abc * xyz      <-- the value you seek          
        Exit Loop
    End If
    ...
    safety_counter = safety_counter + 1
    If safety_counter >= 1000             <-- set to any sufficient value
        Set result_value = (-123)         <-- indicate "infinite loop error"
        Exit Loop                         <-- this is the emergency exit
    End If
End Loop
Return result_value