Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

DB2循环语句怎么写

Posted on 2010-03-18 22:31 Prayer 阅读(8917) 评论(0)  编辑 收藏 引用 所属分类: DB2
31.Db2的循环控制语句loop用法?
答:loop例子:
OPEN c1;
SET at_end = 0;
SET numrec = 0;
fetch_loop: 1
LOOP
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
LEAVE fetch_loop; 2
END IF;
END LOOP fetch_loop; 3
CLOSE c1;

32.Db2的循环控制语句while用法?
答:while 例子:
OPEN c1;
SET at_end = 0;
SET numrec = 0;
WHILE at_end = 0 DO
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
SET at_end = 1;
END IF;
END WHILE;
CLOSE c1;
  • myfriend2010 (2007-8-30 12:54:02)

    33.Db2的循环控制语句repeat用法?
    答:repeat例子
    SET numrec = 0;
    fetch_loop:
    REPEAT
    FETCH c1 INTO proc_cusnbr, proc_cuscrd;
    IF SQLCODE = 0 THEN
    SET proc_cuscrd = proc_cuscrd * 1.2;
    UPDATE ordapplib.customer
    SET cuscrd = proc_cuscrd
    WHERE CURRENT OF c1;
    SET numrec = numrec + 1;
    END IF;
    UNTIL SQLCODE <> 0
    END REPEAT fetch_loop;


    34.Db2的循环控制语句for用法?
    答:for 例子
    FOR each_record AS
    cursor1 CURSOR FOR
    SELECT cusnbr, cuscrd FROM ordapplib.customer
    DO
    UPDATE ordapplib.customer
    SET cuscrd = cuscrd * 1.1
    WHERE CURRENT OF cursor1;
    END FOR;
  • myfriend2010 (2007-8-30 12:54:41)

    34.循环控制Break,Continue在Db2用法?
    答:在db2中 Break 转换为leave lab, Continue 转换为ITERATE lab
    等同于GOTO语句
    举例如下:
    ============== leave 例子
    OPEN c1;
    SET at_end = 0;
    SET numrec = 0;
    fetch_loop: 1
    LOOP
    FETCH c1 INTO proc_cusnbr, proc_cuscrd;
    IF SQLCODE = 0 THEN
    SET proc_cuscrd = proc_cuscrd * 1.2;
    UPDATE ordapplib.customer
    SET cuscrd = proc_cuscrd
    WHERE CURRENT OF c1;
    SET numrec = numrec + 1;
    ELSE
    LEAVE fetch_loop; 2
    END IF;
    END LOOP fetch_loop;
    CLOSE c1;
    ============ iterate 例子
    BEGIN
    OPEN c1;
    ins_loop: 1
    LOOP
    FETCH c1 INTO v_dept, v_deptname, v_admdept;
    IF at_end = 1 THEN
    LEAVE ins_loop;
    ELSEIF v_dept = 'D11' THEN
    ITERATE ins_loop; 2
    END IF;
    INSERT INTO sampledb02.deptnew (deptno, deptname, admrdept)
    VALUES (v_dept, v_deptname, v_admdept);
    END LOOP;
    CLOSE c1;
    END;
    35.在Db2存储过程中使用滚动游标(scrollable cursor)?
    答:例子
    CREATE PROCEDURE MYMAX
    ( IN fld_name CHAR(30),
    IN file_name CHAR(128),
    INOUT max_value INTEGER)
    LANGUAGE SQL
    BEGIN atomic
    DECLARE sql_stmt CHAR(256);
    DECLARE not_found
    CONDITION FOR '02000';
    DECLARE c1 DYNAMIC SCROLL CURSOR FOR s1; -- 声明动态滚动游标
    DECLARE CONTINUE HANDLER FOR not_found
    SET max_value = NULL;
    SET sql_stmt = 'SELECT ' || fld_name || ' FROM ' || file_name ||
    ' ORDER BY 1'; --组合sql语句
    PREPARE s1 FROM sql_stmt;
    OPEN c1;
    FETCH LAST FROM c1 INTO max_value; --转到最后行
    CLOSE c1;
    END
    滚动游标的使用例子:(rpg)
    EXEC SQL BEGIN DECLARE SECTION;
    char fld_name[ 30 ];
    char file_name[ 128 ];
    integer max_value;
    short ind3;
    EXEC SQL END DECLARE SECTION;
    Then the indicator variable is used in the call statement:
    EXEC SQL
    CALL MYMAX( :fld_name, :file_name, :max_value :ind3);
  • myfriend2010 (2007-8-30 12:55:05)

    36.db2中存储过程中使用动态游标(dynamic cursor)?
    答:使用PREPARE , EXECUTE ,EXECUTE IMMEDIATE语句
    例子:
    CREATE PROCEDURE DYNSQLSAMPLE()
    LANGUAGE SQL
    BEGIN
    DECLARE stmt VARCHAR(256);
    SET stmt = 'UPDATE employee SET salary = salary * 1.1 WHERE empno = ?'; 1
    PREPARE s1 FROM stmt;
    ins_loop:
    FOR each_department AS
    c1 CURSOR FOR
    SELECT mgrno FROM department WHERE mgrno IS NOT NULL
    DO
    EXECUTE s1 USING mgrno;
    END FOR;
    END;
    EXECUTE IMMEDIATE statement 例子:
    PREPARE s1 FROM ‘UPDATE employee SET salary = salary * 1.1 WHERE
    empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL);
    EXECUTE s1;
    等同于
    EXECUTE IMMEDIATE ‘UPDATE employee SET salary = salary * 1.1 WHERE
    empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL);

    最基本动态游标语句
    ...
    DECLARE stmt VARCHAR[256];
    ...
    SET stmt = ‘SELECT COLUMN1, COLUMN2, COLUMN3 FROM TBL1’;
    PREPARE PreparedStatement FROM s1;
    DECLARE Cursor1 CURSOR FOR PreparedStatement;

  • 只有注册用户登录后才能发表评论。
    【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
    网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理