PL/SQL用循环进行迭代处理

PL/SQL
PL/SQL基础
608 0
简单循环的语法结构式所有循环结构中最基础的，它包括了一个LOOP和END LOOP关键字。语法如下： LOOP . . . 必须存在一条可执行语句 END LOOP;

1、简单循环

    简单循环的语法结构式所有循环结构中最基础的，它包括了一个LOOP和END LOOP关键字。语法如下：

LOOP
    . . . 必须存在一条可执行语句
END LOOP;

特性：

a）循环如何结束

    简单循环是依赖循环体内部的EXIT语句的执行，如果没有执行EXIT语句，则成为无穷循环。

b）什么时候测试是否该结束循环

    这个测试是再循环体内部发生的，是再EXIT或者EXIT WHEN语句执行时发生的。

c）使用这种循环的原因

    不能确定循环会执行多少次、要求循环至少执行一次

简单循环只有当执行EXIT、EXIT WHEN或者程序出现异常才停止执行。

那么什么时候使用EXIT、什么时候使用EXIT WHEN呢？

a）如果只有一个条件表达式决定循环是否应该结束，这种情况使用EXIT WHEN

b）如果有多个退出条件、或者要根据不同的退出条件设置“返回值”时，使用IF或者CASE语句更适合，这时应该使用EXIT语句。

【如】输出从satrtIndex开始到endIndex的数字

PROCEDURE display_number(
    startIndex    IN    INTEGER,
    endIndex     IN    INTEGER
)AS
    currentIndex     INTEGER    := startIndex;
BEGIN
    LOOP
        EXIT WHEN currentIndex > endIndex; --循环退出条件
        DBMS_OUTPUT.PUT_LINE(currentIndex );
        currentIndex := currentIndex + 1;
        -- 将上面的三句代码重写
        IF currentIndex > endIndex THEN
            EXIT;
        ELSE
            DBMS_OUTPUT.PUT_LINE(currentIndex );
            currentIndex := currentIndex + 1;
        END IF;
    END LOOP;
END;

我们可以使用简单循环来模仿repeat until循环，repeat until循环要求在循环体中执行完毕后才测试，这种循环体部分至少会执行一次。如：

LOOP
    . . . 待执行的语句
    EXIT WHEN condition -- 判断退出条件
END LOOP;

有时为了监控系统，那么我们就不得不让简单循环无限的循环下去。但是系统子资源消耗非常高，因此可以在执行后暂停一段时间再继续执行。如：

LOOP
    . . . 待执行的语句
    DBMS_LOCK.sleep(10); -- 10秒钟内不做任何事情
END LOOP;

怎样来终止这个人为的无穷循环呢？最好的办法是，在循环体中插入一个“命令解释器”，然后通过数据库内置的进程间通讯机制来发送命令，这种机制也叫做数据库管道。如：

DECLARE
    popename    CONSTANT    VARCHAR2(12) := 'signaler';
    result             INTEGER;
    pipebuf         VARCHAR2(64);
BEGIN
    /* 创建一个指定名字的私有管道*/
    result    := DBMS_PIPE.create_pipe(pipename);
    LOOP
        . . . 待执行的语句
        DBMS_LOCK.sleep(10);
        
        /* 检查管道里是否有消息 */
        IF DBMS_PIPE.receive_message(pipename, 0) = 0 THEN
        /* 解释消息表采取动作*/
        DBMS_PIPE.unpack_message(pipebuf);
        EXIT WHEN pipebuf = 'stop';
        END IF;
    END LOOP;
END;

现在可以构造一个简单的伙伴程序，这个程序只需要同在一个管道中发送一个“stop”消息就可以终止这个循环的运行。

DECLARE
    pipename    VARCHAR2(12) := 'signaler';
    result            INTEGER := DBMS_PIPE.create_pipe(pipename);
BEGIN
    DBMS_PIPE.pack_message('stop');
END;

同样也可以通过管道发送其他命令——比如：增加或减少休眠间隔。

2、For循环

    PL/SQL中有两种类型的FOR循环：数字型FOR循环和游标型FOR循环。数值型FOR循环式传统的大家熟悉的“计数”循环，这种FOR循环的迭代次数在循环开始的时候就知道了。如果在FOR循环的范围处没有设置循环索引的递进方向从低到高或者从高到低（reverse将范围条件置反）。语法如下：

FOR loopIndex    IN [REVERSE] lowest_number..heighest_number 
LOOP
    . . . 可以执行的语句
END LOOP;

使用这种循环的原因：

    如果只想有限次的执行一段代码，又不想过早的结束循环，就可以使用数值型的FOR循环。

【如】输出从satrtIndex开始到endIndex的数字

PROCEDURE display_number(
    startIndex    IN    INTEGER,
    endIndex     IN    INTEGER
)AS
BEGIN
    FOR currentIndex IN startIndex.. endIndex
    LOOP
        DBMS_OUTPUT.PUT_LINE(currentIndex );
    END LOOP;
END;
FOR info IN (SELECT * FROM student)
LOOP
    . . . info.C_NAME
END LOOP;

使用数字型FOR循环的规则：

    a、不要声明循环索引，PL/SQL会自动、隐式的用一个INTEGER类型的局部变量作为循环索引。作用范围就是该循环本省，不能在循环之外使用

    b、在范围部分使用的表达式会在循环开始时被求值一次。范围部分不会随着循环的反复执行而被重复求值，如果在循环体内部改变范围表达式使用的变量，这种改变对范围边界没有任何作用

    c、在循环体内部不要改变索引值或者范围边界值。

    d、使用reverse关键字可以迫使循环从上边界到下边界递减进行，但是你必须保证上边界大于下边界

处理非平滑增长：

    PL/SQL并没有提供可以指定特殊的累进步幅得“进步”语法。在PL/SQL中数值型的FOR循环的各种变体中，递进步幅总是为1为单位进步。如果让循环只有当遇到1到100之间的偶数时才执行？你可以使用MOD函数。如：

FOR loop_index IN 1..100 LOOP
    IF MOD (loop_index, 2) = 0 THEN
       calc_values(loop_index);
    END IF;
END LOOP;

或者

FOR loop_index IN 1..50 LOOP
    calc_values(loop_index*2);
END LOOP;

游标型FOR循环：

    游标型FOR循环是和一个现实游标或者直接放在循环边界中的SELECT语句关联在一起的循环。如果你需要取出游标的每条记录依次处理，就可以使用游标FOR循环，而这种也是游标的主要用法。语法如下：

FOR record IN {cursor_name | select statement}
LOOP
    executable statements
END LOOP;

其中record是一个记录，这个记录是PL/SQL根据cursor_name这个游标%ROWTYPE属性隐式声明出来的。注意：不要现实的声明一个与循环索引（record）同名的记录，会导致逻辑错误。直接在循环中嵌套SELECT语句。

FOR student IN （SELECT * FROM students）
LOOP
    DBMS_OUTPUT.PUT_LINE( student.N_USERNAME );
END LOOP;

然而，应该避免使用这种方式，因为SELECT出现在了不该出现的地方，维护难以理解。

如：我们将更新所有员工的薪水。

方式一：

DECLARE
    CURSOR student_cursor IS
        SELECT id, name, salaray FROM employers;
    student    student_cursor%ROWTYPE；
BEGIN
    OPEN student_cursor;
    LOOP
        FETCH student_cursor INTO student；
        EXIT WHEN student_cursor%NOTFOUND;
        UPDATE students SET salary= student .salary*0.3 WHERE id=student.id;    
    END LOOP;
    CLOSE student_cursor;
END;

方式二：

DECLARE
    CURSOR student_cursor IS
        SELECT id, name, salaray FROM employers;
BEGIN
    LOOP student IN student_cursor 
        UPDATE students SET salary= student .salary*0.3 WHERE id=student.id;    
    END LOOP;
END;

3、While循环

    while循环是一个条件循环，只要循环边界处定义的布尔条件的求值结果为true，循环就会执行。如果无法提前知道循环执行的次数，就可以使用while循环。语法如下：

WHILE condition -- 循环条件（true执行、false或null不执行）
LOOP
    . . . 待执行的语句
END LOOP;

使用这种循环的原因：a、你事先无法确定循环多少次 b、你想通过条件来终止循环 c、循环体不是必须要执行

【如】输出从satrtIndex开始到endIndex的数字

PROCEDURE display_number(
    startIndex    IN    INTEGER,
    endIndex     IN    INTEGER
)AS
    currentIndex     INTEGER    := startIndex;
BEGIN
    WHILE (currentIndex < endIndex)
    LOOP
        DBMS_OUTPUT.PUT_LINE(currentIndex );
        currentIndex := currentIndex + 1;
    END LOOP;
END;

4、循环的标签

    可以使用一个标签给一个循环命名。PL/SQL中循环的标签是下面这种给事：

    <<label_name>>其中的label_name就是标签的名称，并且循环的标签要紧靠在LOOP语句前面：

<<label_name>>
FOR emp_rec IN emp_cur 
LOOP
    . . . 
END LOOP;

标签也可以跟在END LOOP保留词的后面，这是可选的，如下：

<<label_name>>
FOR emp_rec IN emp_cur 
LOOP
    . . . 
END LOOP label_name;

标签的用处：

    a、如果循环体包含很多语句且包含很多的循环，那么使用标签可以很好的区分每个END LOOP属于哪个FOR。

    b、你可以使用标签让循环的索引变量命名更规范化。如：

<<year_label>>
FOR year_number IN 1800..1995
LOOP
    <<month_loop>>
    FOR month_number IN 1..12
    LOOP
        IF year_label.year_number = 1900 THEN
            . . . 可以执行的语句
        END IF;
    END LOOP month_loop;
END LOOP year_loop；

c、使用嵌套循环式，通过标签可以控制外层循环。如下：退出外层循环

EXIT out_loop_label;
EXIT out_loop_label WHEN condition;

尽量不要这么做，会增加代码的调试难度。

5、CONTINUE语句

    Oracle数据库11g为循环提供了一个新特性：CONTINUE语句。使用这个语句可以从循环体的本次迭代退出，紧接着立即进入下一次迭代。该语句的语法：

    CONTINUE; -- 无条件

    CONTINUE WHEN condition； -- 有条件

如：

LOOP
    EXIT WHEN exit_condition_met;
    CONTINUE WHEN condition1;
    CONTINUE WHEN condition2;
    setup_steps_here;
    
    IF condition4 THEN
        action4_executed;
        CONTINUE;
    END IF;
    IF condition5 THEN
        action5_executed;
        CONTINUE; -- 严格说可以不要
    END IF;
END LOOP;

不用CONTINUE语句的结构如下：

LOOP
    EXIT WHEN exit_condition_met;
    IF condition1 THEN
        NULL;
    ELSIF condition1 THEN
        NULL;
    ELSE
        setup_steps_here;
        IF condition4 THEN
            action4_executed;
        ELSEIF condition5  THEN
            action5_executed;
        END IF;
    ELSE
END LOOP;

6、迭代处理技巧

    a、循环索引使用容易理解的名称

    b、好的退出方式。在for循环和while循环中不要使用exit和exit when语句、循环中不要使用return和goto语句。

    c、获取FOR循环执行信息。因为FOR循环的索引游标是隐式的创建和关闭。所以在FOR循环外边不能够获取FOR循环的索引游标。你可以在声明FOR循环的块中声明一个变量，然后再循环内部给变量赋值。如：

DECLAE
    count    INTEGER := 0;
BEGIN
    FOR my_cursor IN ... LOOP
        count := my_cursor%ROWCOUNT；    
    END LOOP；
    IF count > 10 THEN
        ...
    END IF;
END;

    d、循环式的SQL语句。如果将一个表中某个标志为指定值的列移动到另一张表中且删除当前表中的记录。首先想到的是：

FOR cursor_info INTO cursor_ LOOP
    DECLARE
    BEGIN    
        INSERT . . .
        UPDATE . . .  
    EXCEPTION
        INSERT logger
    END;
END LOOP;

能保证某一条数据移除失败，不会将所有已插入的回滚。

另一种方式，开销要小一些：

INSERT(col1, col2)SELECT col1, col2 FROM ...
我们愈是学习，愈觉得自己的贫乏。 —— 雪莱
0 不喜欢
说说我的看法 -
全部评论（）
没有评论
目录
开发工具 更多
准确、全面、详细的标准C函数参考手册，为你保驾护航
提供 Windows 最全的 DOS 命令参考手册
提供最好用的 Linux 命令大全，便捷、快速
提供常用 MySQL 内置函数，及函数的用法示例
提供 Oracle 内置常用函数参考手册
热门标签
热门文章
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098