Oralce Loop循环实例：FOR,WHILE,GOTO[11gR2]

Oralce Loop循环实例：

CREATE OR REPLACE PACKAGE BODY acc_loop_pkg IS

  PROCEDURE loop_example_p IS
    v_idx NUMBER := 1;

    --LOOP循环实例6：CURSOR结果循环
    v_salary emp.sal%TYPE;
    v_emp    emp%ROWTYPE;
    CURSOR v_cursor IS
      SELECT * FROM emp WHERE sal > v_salary;

    --LOOP循环实例8：输出数组循环
    TYPE array_type IS VARRAY(4) OF VARCHAR2(10);
    v_arr array_type := array_type('A', 'B', 'C', 'D');
  BEGIN

    dbms_output.put_line('Loop循环实例1：正序循环');
    --reverse 正序循环
    FOR i IN 1 .. 5 LOOP
      dbms_output.put_line('i=' || i);
    END LOOP;

    dbms_output.put_line('Loop循环实例2：倒序循环');
    --reverse 可控制倒序循环
    FOR i IN REVERSE 1 .. 5 LOOP
      dbms_output.put_line('i=' || i);
    END LOOP;

    dbms_output.put_line('Loop循环实例3：WHILE循环');
    --使用WHILE进行循环
    WHILE v_idx != 5 LOOP
      dbms_output.put_line('v_idx=' || v_idx);
      v_idx := v_idx + 1;
    END LOOP;

    dbms_output.put_line('Loop循环实例4：EXIT WHEN循环');
    --使用EXIT WHEN 退出循环
    v_idx := 1;
    LOOP
      EXIT WHEN v_idx = 5;
      dbms_output.put_line('v_idx=' || v_idx);
      v_idx := v_idx + 1;
    END LOOP;

    dbms_output.put_line('Loop循环实例5：SELECT结果循环');
    --对SELECT结果进行循环输出
    v_idx := 1;
    FOR i IN (SELECT job, COUNT(1) cnt FROM emp GROUP BY job) LOOP
      dbms_output.put_line(v_idx || ' > ' || i.job || ':' || i.cnt);
      v_idx := v_idx + 1;
    END LOOP;

    dbms_output.put_line('LOOP循环实例6：CURSOR结果循环');
    --对游标CURSOR结果进行循环输出
    v_salary := 2000;
    IF v_cursor%ISOPEN = FALSE THEN
      OPEN v_cursor;
    END IF;
    LOOP
      FETCH v_cursor
        INTO v_emp;
      EXIT WHEN v_cursor%NOTFOUND;
      dbms_output.put_line('EmpNO：' || v_emp.empno || ',' || 'Name:' || v_emp.ename || ',' || 'Salary:' || v_emp.sal);
    END LOOP;

    dbms_output.put_line('LOOP循环实例7：GOTO跳转循环');
    --使用GOTO跳转循环位置
    v_idx := 5;
    <<loop_7>>
    v_idx := v_idx - 1;
    dbms_output.put_line('v_idx=' || v_idx);
    IF v_idx > 0 THEN
      GOTO loop_7;
    END IF;

    dbms_output.put_line('LOOP循环实例8：输出数组循环');
    FOR i IN 1 .. v_arr.count LOOP
      dbms_output.put_line(i || ':' || v_arr(i));
    END LOOP;

  END loop_example_p;

END acc_loop_pkg;

运行结果：

Loop循环实例1：正序循环
i=1
i=2
i=3
i=4
i=5
Loop循环实例2：倒序循环
i=5
i=4
i=3
i=2
i=1
Loop循环实例3：WHILE循环
v_idx=1
v_idx=2
v_idx=3
v_idx=4
Loop循环实例4：EXIT WHEN循环
v_idx=1
v_idx=2
v_idx=3
v_idx=4
Loop循环实例5：SELECT结果循环
1 > CLERK:4
2 > SALESMAN:4
3 > PRESIDENT:1
4 > MANAGER:3
5 > ANALYST:2
LOOP循环实例6：CURSOR结果循环
EmpNO：7566,Name:JONES,Salary:2975
EmpNO：7698,Name:BLAKE,Salary:2850
EmpNO：7782,Name:CLARK,Salary:2450
EmpNO：7788,Name:SCOTT,Salary:3000
EmpNO：7839,Name:KING,Salary:5000
EmpNO：7902,Name:FORD,Salary:3000
LOOP循环实例7：GOTO跳转循环
v_idx=4
v_idx=3
v_idx=2
v_idx=1
v_idx=0
LOOP循环实例8：输出数组循环
1:A
2:B
3:C
4:D