深入理解Oracle中的游标实现方式及案例分析（oracle中的游标案例）

深入理解Oracle中的游标实现方式及案例分析

游标是Oracle中一种非常有用的数据访问机制，能够以一种逐行的方式遍历查询结果。在很多业务场景下，我们需要对查询结果进行一些特殊的处理，这时候游标就可以派上用场。在本文中，我们将深入探讨Oracle中游标的实现方式，以及通过具体案例分析说明游标的使用方法和注意事项。

一、游标的实现方式

在Oracle中，游标有两种实现方式：显式游标和隐式游标。

1. 显式游标

显式游标是通过PL/SQL代码显式定义的游标。它需要开发者自行手动声明、打开、获取数据，并最终释放游标资源。显式游标的基本流程如下：

-- 声明游标
CURSOR cur_test IS
SELECT col1, col2, col3 FROM table1 WHERE condition;
-- 打开游标
OPEN cur_test;
-- 获取数据（循环遍历）
LOOP
FETCH cur_test INTO v_col1, v_col2, v_col3;
EXIT WHEN cur_test%NOTFOUND;
-- 对数据进行处理
END LOOP;
-- 释放游标资源
CLOSE cur_test;

需要注意的是，显式游标使用后需要释放游标资源，否则会占用Oracle中的一些重要资源，导致系统性能下降。

2. 隐式游标

隐式游标是Oracle数据库内部定义的游标。隐式游标不需要手动打开和关闭，Oracle会在需要时自动打开和关闭。隐式游标的基本语句如下：

FOR rec IN (SELECT col1, col2, col3 FROM table1 WHERE condition) LOOP
-- 对数据进行处理
END LOOP;

隐式游标比显式游标更为简便，但是需要注意，隐式游标的使用会增加系统的开销。

二、游标案例分析

1. 显式游标案例

下面我们通过一个具体案例，展示显式游标的使用方法。

需求：查询表student的所有学生，根据总分倒序排列，输出学生的姓名、语文分数、数学分数、总分，并统计全班分数分布情况。分数分布情况统计方法为：分100分以下、100分（含）~200分、200分（含）~300分、300分（含）~400分、400分以上五个区间，输出每个区间的人数和比例。

PL/SQL 代码如下：

DECLARE
-- 声明变量和游标
  v_name VARCHAR(20);
  v_chinese NUMBER;
  v_math NUMBER;
  v_total NUMBER;
  
  c_less_100 NUMBER:=0;
  c_100_200 NUMBER:=0;
  c_200_300 NUMBER:=0;
  c_300_400 NUMBER:=0;
  c_more_400 NUMBER:=0;

CURSOR cur_students IS
SELECT name, chinese, math, chinese+math AS total
FROM student
ORDER BY total DESC;
BEGIN
-- 打开游标
OPEN cur_students;

-- 循环遍历数据
LOOP
  FETCH cur_students INTO v_name, v_chinese, v_math, v_total;
  EXIT WHEN cur_students%NOTFOUND;
  -- 输出学生信息
  DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_chinese || ' ' || v_math || ' ' || v_total);
  -- 统计分数分布情况
  IF v_total 
    c_less_100 := c_less_100 + 1;
  ELSIF v_total >= 100 AND v_total 
    c_100_200 := c_100_200 + 1;
  ELSIF v_total >= 200 AND v_total 
    c_200_300 := c_200_300 + 1;
  ELSIF v_total >= 300 AND v_total 
    c_300_400 := c_300_400 + 1;
  ELSE
    c_more_400 := c_more_400 + 1;
  END IF;
END LOOP;

-- 输出分数分布情况
DBMS_OUTPUT.PUT_LINE('Less than 100: ' || c_less_100 ||'('|| TO_CHAR(c_less_100/(c_less_100+c_100_200+c_200_300+c_300_400+c_more_400)*100,'999.99')||'%)');
DBMS_OUTPUT.PUT_LINE('100-200: ' || c_100_200 ||'('|| TO_CHAR(c_100_200/(c_less_100+c_100_200+c_200_300+c_300_400+c_more_400)*100,'999.99')||'%)');
DBMS_OUTPUT.PUT_LINE('200-300: ' || c_200_300 ||'('|| TO_CHAR(c_200_300/(c_less_100+c_100_200+c_200_300+c_300_400+c_more_400)*100,'999.99')||'%)');
DBMS_OUTPUT.PUT_LINE('300-400: ' || c_300_400 ||'('|| TO_CHAR(c_300_400/(c_less_100+c_100_200+c_200_300+c_300_400+c_more_400)*100,'999.99')||'%)');
DBMS_OUTPUT.PUT_LINE('More than 400: ' || c_more_400 ||'('|| TO_CHAR(c_more_400/(c_less_100+c_100_200+c_200_300+c_300_400+c_more_400)*100,'999.99')||'%)');
-- 释放游标
CLOSE cur_students;
END;

通过上述代码，我们可以实现对查询结果的逐行遍历，并使用游标实时计算分数分布情况。

2. 隐式游标案例

下面我们通过另一个具体案例，展示隐式游标的使用方法。

需求：查询表employee中所有在职员工的姓名、部门和工资，并计算全公司员工的平均工资、最高工资、最低工资，并输出不同部门员工的平均工资。

PL/SQL 代码如下：

DECLARE
-- 声明变量
  v_total_salary NUMBER:=0;
  v_max_salary NUMBER:=0;
  v_min_salary NUMBER:=0;
  
  TYPE dep_salary_rec IS RECORD
    ( department VARCHAR2(50),
      avg_salary NUMBER );
  TYPE dep_salary_tab IS TABLE OF dep_salary_rec INDEX BY BINARY_INTEGER;
  v_dep_salary dep_salary_tab;
  
BEGIN
-- 隐式游标遍历员工信息
  FOR rec IN (SELECT name, department, salary FROM employee WHERE status = 'on-job') LOOP
    -- 输出姓名、部门和工资
    DBMS_OUTPUT.PUT_LINE(rec.name || ' ' || rec.department || ' ' || rec.salary);
    -- 累计员工工资信息
    v_total_salary := v_total_salary + rec.salary;
    IF v_max_salary 
      v_max_salary := rec.salary;
    END IF;
    IF v_min_salary = 0 OR v_min_salary > rec.salary THEN
      v_min_salary := rec.salary;
    END IF;
    -- 统计部门工资信息
    IF v_dep_salary.EXISTS(rec.department) THEN
      v_dep_salary(rec.department).avg_salary := (v_dep_salary(rec.department).avg_salary * v_dep_salary.COUNT(rec.department) + rec.salary) / (v_dep_salary.COUNT(rec.department) + 1);
    ELSE
      v_dep_salary(rec.department).department := rec.department;
      v_dep_salary(rec.department).avg_salary := rec.salary;
    END IF;
  END LOOP;
-- 输出平均工资、最高工资和最低工资
  DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_total_salary / SQL%ROWCOUNT);
  DBMS_OUTPUT.PUT_LINE('Max Salary: ' || v_max_salary);
  DBMS_OUTPUT.PUT_LINE('Min Salary: ' || v_min_salary);
-- 输出不同部门员工的平均工资
  FOR i IN v_dep_salary.FIRST .. v_dep_salary.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(v_dep_salary(i).department || ': ' || v_dep_salary(i).avg_salary);
  END LOOP;
END;

通过上述代码，我们可以实现对员工信息的逐行遍历，并在统计过程中使用隐式游标，最终输出全公司和每个部门的工资情况。

三、总结

通过本