Oracle 遍历游标的四种方式汇总（for、fetch、while、BULK COLLECT）

Oracle 遍历游标的四种方式汇总

在Oracle数据库中，常常需要使用游标来处理查询结果集。通过遍历游标可以获取到查询结果集中的所有数据。在Oracle数据库中，遍历游标的方式有多种，其中常用的方式有 for、fetch、while、BULK COLLECT。

1. for 方式

使用 for 遍历游标时，Oracle自动取出游标中的一行数据并赋给目标变量，然后在循环体中处理汇总。格式如下：

FOR 目标变量 IN 游标名称 LOOP
     -- 处理游标查询结果中的数据 
END LOOP;

示例：

定义一个游标，查询员工表中所有员工的姓名和部门名称，并计算部门员工个数。

DECLARE 
     -- 定义变量
    v_emp_name   emp.ename%TYPE;
    v_dept_name  dept.dname%TYPE;
    v_count      NUMBER := 0; -- 员工人数统计
BEGIN 
     -- 定义游标
    FOR emp_cursor IN (SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno)
    LOOP 
         -- 取出游标中的查询结果并赋值，然后处理
        v_emp_name := emp_cursor.ename;
        v_dept_name := emp_cursor.dname;
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('姓名:' || v_emp_name || ',部门名称:' || v_dept_name);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('员工总数为：' || v_count);
END;

2. fetch 方式

使用 fetch 遍历游标需要先打开游标，然后使用 loop 循环获取游标中的数据，并在每一次循环结束后判断游标是否已经读取了所有的数据。格式如下：

-- 打开游标
OPEN 游标名称;
LOOP 
     -- 取出游标中的查询结果并赋值，然后处理
    FETCH 游标名称 INTO 目标变量;

    -- 处理游标查询结果中的数据 
    EXIT WHEN 游标名称%NOTFOUND;
END LOOP;

-- 关闭游标
CLOSE 游标名称;

示例：

使用 fetch 方式遍历游标，查询员工表中所有员工的姓名和部门名称，并计算部门员工个数。

DECLARE 
    -- 定义变量
    v_emp_name   emp.ename%TYPE;
    v_dept_name  dept.dname%TYPE;
    v_count      NUMBER := 0; 
    -- 定义游标
    CURSOR emp_cursor IS
        SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
BEGIN
    -- 打开游标
    OPEN emp_cursor;
    LOOP 
        -- 取出游标中的查询结果并赋值，然后处理
        FETCH emp_cursor INTO v_emp_name, v_dept_name;
        -- 处理游标查询结果中的数据 
        EXIT WHEN emp_cursor%NOTFOUND;
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('姓名:' || v_emp_name || ',部门名称:' || v_dept_name);
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('员工总数为：' || v_count);
END;

3. while 方式

使用 while 循环遍历游标时需要先打开游标，并在 while 循环中获取游标中的数据，如果游标已经读取完所有数据则退出循环，然后关闭游标。格式如下：

-- 打开游标
OPEN 游标名称;
WHILE 游标名称%FOUND LOOP 
     -- 取出游标中的查询结果并赋值，然后处理
    FETCH 游标名称 INTO 目标变量;

    -- 处理游标查询结果中的数据 
END LOOP;
-- 关闭游标
CLOSE 游标名称;

示例：

使用 while 方式遍历游标，查询员工表中所有员工的姓名和部门名称，并计算部门员工个数。

DECLARE 
    -- 定义变量
    v_emp_name   emp.ename%TYPE;
    v_dept_name  dept.dname%TYPE;
    v_count      NUMBER := 0; 
    -- 定义游标
    CURSOR emp_cursor IS
        SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
BEGIN
    -- 打开游标
    OPEN emp_cursor;
    WHILE emp_cursor%FOUND LOOP 
        -- 取出游标中的查询结果并赋值，然后处理
        FETCH emp_cursor INTO v_emp_name, v_dept_name;
        -- 处理游标查询结果中的数据 
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('姓名:' || v_emp_name || ',部门名称:' || v_dept_name);
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('员工总数为：' || v_count);
END;

4. BULK COLLECT 方式

使用 BULK COLLECT 遍历游标时，Oracle会同时取出游标中的多条数据，然后赋给 PL/SQL 表，从而提高了查询数据的效率。格式如下：

-- 定义 PL/SQL 表
TYPE 目标变量列表 IS TABLE OF 表列类型;

-- 取出游标中的多条数据赋给 PL/SQL 表
BULK COLLECT INTO 目标变量列表 FROM 游标名称;

-- 处理 PL/SQL 表中的所有数据 
FOR i IN 目标变量列表.First..目标变量列表.Last LOOP
     -- 处理 PL/SQL 表中的数据 
     目标变量 := 目标变量列表(i);
END LOOP;

示例：

使用 BULK COLLECT 遍历游标，查询员工表中所有员工的姓名和部门名称，并将这些信息存入 PL/SQL 表中。

DECLARE 
     -- 定义 PL/SQL 表
    TYPE emp_dept_tbl IS TABLE OF emp.ename%TYPE;
    TYPE dept_tbl IS TABLE OF dept.dname%TYPE;

    -- 声明变量
    v_emp_names   emp_dept_tbl;
    v_dept_names  dept_tbl;

BEGIN 
     -- 定义游标并使用 BULK COLLECT 将游标中的数据取出到 PL/SQL 表中 
    CURSOR emp_cursor IS
        SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
    BEGIN
        BULK COLLECT INTO v_emp_names, v_dept_names FROM emp_cursor;

        -- 遍历 PL/SQL 表中的数据
        FOR i IN v_emp_names.First..v_dept_names.Last LOOP
            DBMS_OUTPUT.PUT_LINE('姓名:' || v_emp_names(i) || ',部门名称:' || v_dept_names(i));
        END LOOP;
    END;
END;