深入理解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;

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

三、总结

通过本

版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《深入理解Oracle中的游标实现方式及案例分析(oracle中的游标案例)》
文章链接:https://zhuji.vsping.com/190934.html
本站资源仅供个人学习交流,请于下载后24小时内删除,不允许用于商业用途,否则法律问题自行承担。