2. 首页 > Oracle教程 > 正文

Oracle教程FG071-PL/SQL游标

教程整理：风哥教程  |  更新时间：2026-01-05  |  教程分类：Oracle教程  |  文档学习：7

Part02-生产环境规划与建议

2.1 游标设计原则

建议：在设计PL/SQL游标时，应遵循以下原则：

  • 游标应该只处理必要的数据，避免查询过多的行
  • 游标应该有明确的命名规范，便于识别和管理
  • 游标应该有适当的注释，说明其功能和使用方法
  • 避免在游标中执行大量的操作，影响性能
  • 使用 FOR
    循环游标来简化游标的管理

2.2 游标性能考虑

建议：在使用PL/SQL游标时，应考虑以下性能因素：

  • 使用适当的WHERE子句来限制结果集的大小
  • 使用 FOR
    循环游标来自动管理游标的生命周期
  • 避免在游标循环中执行大量的SQL语句
  • 使用BULK COLLECT来批量获取数据，减少上下文切换
  • 定期监控和优化游标的性能

2.3 游标安全性考虑

建议：在使用PL/SQL游标时，应考虑以下安全性因素：

  • 避免在游标中使用动态SQL，防止SQL注入攻击
  • 对输入参数进行验证，确保参数的有效性
  • 使用适当的权限控制，限制对游标的访问权限
  • 避免在游标中处理敏感数据，保护数据的安全性
  • 定期审计游标的使用情况，发现和解决潜在的安全问题
from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中使用PL/SQL游标时，应遵循以下步骤：

  1. 需求分析：明确游标的功能和要求
  2. 设计：设计游标的结构和逻辑
  3. 开发：编写游标的代码
  4. 测试：测试游标的功能和性能
  5. 部署：将游标部署到生产环境
  6. 监控：监控游标的使用情况和性能
  7. 维护：定期维护和优化游标
更多视频教程www.fgedu.net.cn

Part04-生产案例与实战讲解

4.1 隐式游标的使用

隐式游标是由Oracle自动创建和管理的游标，用于处理SQL语句的执行结果，下面是一个使用隐式游标的例子：

— 使用隐式游标
SET SERVEROUTPUT ON;
DECLARE
v_employee_id NUMBER :=
100;
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
BEGIN
— 隐式游标
SELECT
first_name, last_name IN
TO v_first_name, v_last_name
FROM employees
WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE(‘员工姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);

— 使用隐式游标的属性
IF
SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘找到员工记录’);
END IF
;

DBMS_OUTPUT.PUT_LINE(‘影响的行数: ‘ || SQL%ROWCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘未找到员工记录’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘找到多条员工记录’);
END
;
/

执行：

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_employee_id NUMBER :=
100;
3 v_first_name VARCHAR2(50);
4 v_last_name VARCHAR2(50);
5 BEGIN
6 — 隐式游标
7 SELECT
first_name, last_name IN
TO v_first_name, v_last_name
8 FROM employees
9 WHERE employee_id = v_employee_id;
10
11 DBMS_OUTPUT.PUT_LINE(‘员工姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);
12
13 — 使用隐式游标的属性
14 IF
SQL%FOUND THEN
15 DBMS_OUTPUT.PUT_LINE(‘找到员工记录’);
16 END IF
;
17
18 DBMS_OUTPUT.PUT_LINE(‘影响的行数: ‘ || SQL%ROWCOUNT);
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 DBMS_OUTPUT.PUT_LINE(‘未找到员工记录’);
22 WHEN TOO_MANY_ROWS THEN
23 DBMS_OUTPUT.PUT_LINE(‘找到多条员工记录’);
24 END
;
25 /
员工姓名: Steven King
找到员工记录
影响的行数: 1

PL/SQL procedure successfully completed.

4.2 显式游标的使用

显式游标是由用户显式声明、打开、获取和关闭的游标，下面是一个使用显式游标的例子：

— 使用显式游标
SET SERVEROUTPUT ON;
DECLARE
— 声明游标
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 90
ORDER BY salary DESC;

— 声明变量
v_employee_id employees.employee_id%TYPE
;
v_first_name employees.first_name%TYPE
;
v_last_name employees.last_name%TYPE
;
v_salary employees.salary%TYPE
;
BEGIN
— 打开游标
OPEN c_employees;

— 获取数据
LOOP
FETCH c_employees IN
TO v_employee_id, v_first_name, v_last_name, v_salary;
EXIT WHEN c_employees%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘, 薪水: ‘
|| v_salary);
END LOOP
;

— 关闭游标
CLOSE c_employees;

DBMS_OUTPUT.PUT_LINE(‘游标处理完成’);
END
;
/

执行：

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 — 声明游标
3 CURSOR c_employees IS
4 SELECT
employee_id, first_name, last_name, salary
5 FROM employees
6 WHERE department_id = 90
7 ORDER BY salary DESC;
8
9 — 声明变量
10 v_employee_id employees.employee_id%TYPE
;
11 v_first_name employees.first_name%TYPE
;
12 v_last_name employees.last_name%TYPE
;
13 v_salary employees.salary%TYPE
;
14 BEGIN
15 — 打开游标
16 OPEN c_employees;
17
18 — 获取数据
19 LOOP
20 FETCH c_employees IN
TO v_employee_id, v_first_name, v_last_name, v_salary;
21 EXIT WHEN c_employees%NOTFOUND;
22
23 DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘,
薪水: ‘ || v_salary);
24 END LOOP
;
25
26 — 关闭游标
27 CLOSE c_employees;
28
29 DBMS_OUTPUT.PUT_LINE(‘游标处理完成’);
30 END
;
31 /
员工ID: 100, 姓名: Steven King, 薪水: 24000
员工ID: 101, 姓名: Neena Kochhar, 薪水: 17000
员工ID: 102, 姓名: Lex De Haan, 薪水: 17000
游标处理完成

PL/SQL procedure successfully completed.

4.3 游标变量的使用

游标变量是可以存储不同查询结果集的变量，下面是一个使用游标变量的例子：

— 使用游标变量
SET SERVEROUTPUT ON;
DECLARE
— 声明游标变量类型
TYPE
employee_cursor IS
REF CURSOR;

— 声明游标变量
c_emp employee_cursor;

— 声明变量
v_employee_id employees.employee_id%TYPE
;
v_first_name employees.first_name%TYPE
;
v_last_name employees.last_name%TYPE
;
v_department_id NUMBER :=
90;
BEGIN
— 打开游标变量
OPEN c_emp FOR
SELECT
employee_id, first_name, last_name
FROM employees
WHERE department_id = v_department_id;

— 获取数据
LOOP
FETCH c_emp IN
TO v_employee_id, v_first_name, v_last_name;
EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);
END LOOP
;

— 关闭游标变量
CLOSE c_emp;

— 重新打开游标变量，使用不同的查询
OPEN c_emp FOR
SELECT
employee_id, first_name, last_name
FROM employees
WHERE salary > 15000;

— 获取数据
LOOP
FETCH c_emp IN
TO v_employee_id, v_first_name, v_last_name;
EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘高薪员工 – 员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);
END LOOP
;

— 关闭游标变量
CLOSE c_emp;

DBMS_OUTPUT.PUT_LINE(‘游标变量处理完成’);
END
;
/

执行：

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 — 声明游标变量类型
3 TYPE
employee_cursor IS
REF CURSOR;
4
5 — 声明游标变量
6 c_emp employee_cursor;
7
8 — 声明变量
9 v_employee_id employees.employee_id%TYPE
;
10 v_first_name employees.first_name%TYPE
;
11 v_last_name employees.last_name%TYPE
;
12 v_department_id NUMBER :=
90;
13 BEGIN
14 — 打开游标变量
15 OPEN c_emp FOR
16 SELECT
employee_id, first_name, last_name
17 FROM employees
18 WHERE department_id = v_department_id;
19
20 — 获取数据
21 LOOP
22 FETCH c_emp IN
TO v_employee_id, v_first_name, v_last_name;
23 EXIT WHEN c_emp%NOTFOUND;
24
25 DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);
26 END LOOP
;
27
28 — 关闭游标变量
29 CLOSE c_emp;
30
31 — 重新打开游标变量，使用不同的查询
32 OPEN c_emp FOR
33 SELECT
employee_id, first_name, last_name
34 FROM employees
35 WHERE salary > 15000;
36
37 — 获取数据
38 LOOP
39 FETCH c_emp IN
TO v_employee_id, v_first_name, v_last_name;
40 EXIT WHEN c_emp%NOTFOUND;
41
42 DBMS_OUTPUT.PUT_LINE(‘高薪员工 – 员工ID: ‘ || v_employee_id || ‘, 姓名: ‘ || v_first_name || ‘ ‘ || v_last_name);
43 END LOOP
;
44
45 — 关闭游标变量
46 CLOSE c_emp;
47
48 DBMS_OUTPUT.PUT_LINE(‘游标变量处理完成’);
49 END
;
50 /
员工ID: 100, 姓名: Steven King
员工ID: 101, 姓名: Neena Kochhar
员工ID: 102, 姓名: Lex De Haan
高薪员工 – 员工ID: 100, 姓名: Steven King
高薪员工 – 员工ID: 101, 姓名: Neena Kochhar
高薪员工 – 员工ID: 102, 姓名: Lex De Haan
游标变量处理完成

PL/SQL procedure successfully completed.

学习交流加群风哥微信: itpux-com

4.4 游标循环的使用

FOR
循环游标是使用 FOR
循环自动管理游标的声明、打开、获取和关闭，下面是一个使用 FOR
循环游标的例子：

— 使用 FOR
循环游标
SET SERVEROUTPUT ON;
DECLARE
— 声明游标
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary DESC;
BEGIN
— 使用 FOR
循环游标
FOR
emp IN
c_employees LOOP
DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || emp.employee_id || ‘, 姓名: ‘ || emp.first_name || ‘ ‘ || emp.last_name || ‘,
薪水: ‘ || emp.salary);
END LOOP
;

DBMS_OUTPUT.PUT_LINE(‘ FOR
循环游标处理完成’);
END
;
/

— 使用 FOR
循环游标直接使用查询
BEGIN
— 使用 FOR
循环游标直接使用查询
FOR
emp IN
(
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || emp.employee_id || ‘, 姓名: ‘ || emp.first_name || ‘ ‘ || emp.last_name || ‘,
薪水: ‘ || emp.salary);
END LOOP
;

DBMS_OUTPUT.PUT_LINE(‘ FOR
循环游标（直接查询）处理完成’);
END
;
/

执行：

SQL> SET SERVEROUTPUT ON;
SQL> — 使用 FOR
循环游标
SQL> DECLARE
2 — 声明游标
3 CURSOR c_employees IS
4 SELECT
employee_id, first_name, last_name, salary
5 FROM employees
6 WHERE department_id = 60
7 ORDER BY salary DESC;
8 BEGIN
9 — 使用 FOR
循环游标
10 FOR
emp IN
c_employees LOOP
11 DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || emp.employee_id || ‘, 姓名: ‘ || emp.first_name || ‘ ‘ || emp.last_name ||
‘, 薪水: ‘ || emp.salary);
12 END LOOP
;
13
14 DBMS_OUTPUT.PUT_LINE(‘ FOR
循环游标处理完成’);
15 END
;
16 /
员工ID: 103, 姓名: Alexander Hunold, 薪水: 9000
员工ID: 104, 姓名: Bruce Ernst, 薪水: 6000
员工ID: 105, 姓名: David Austin, 薪水: 4800
员工ID: 106, 姓名: Valli Pataballa, 薪水: 4800
员工ID: 107, 姓名: Diana Lorentz, 薪水: 4200
FOR
循环游标处理完成

PL/SQL procedure successfully completed.

SQL> — 使用 FOR
循环游标直接使用查询
SQL> BEGIN
2 — 使用 FOR
循环游标直接使用查询
3 FOR
emp IN
(
4 SELECT
employee_id, first_name, last_name, salary
5 FROM employees
6 WHERE department_id = 60
7 ORDER BY salary DESC
8 ) LOOP
9 DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || emp.employee_id || ‘, 姓名: ‘ || emp.first_name || ‘ ‘ || emp.last_name ||
‘, 薪水: ‘ || emp.salary);
10 END LOOP
;
11
12 DBMS_OUTPUT.PUT_LINE(‘ FOR
循环游标（直接查询）处理完成’);
13 END
;
14 /
员工ID: 103, 姓名: Alexander Hunold, 薪水: 9000
员工ID: 104, 姓名: Bruce Ernst, 薪水: 6000
员工ID: 105, 姓名: David Austin, 薪水: 4800
员工ID: 106, 姓名: Valli Pataballa, 薪水: 4800
员工ID: 107, 姓名: Diana Lorentz, 薪水: 4200
FOR
循环游标（直接查询）处理完成

PL/SQL procedure successfully completed.

4.5 批量获取数据

使用BULK COLLECT来批量获取数据，减少上下文切换，提高性能：

— 使用BULK COLLECT批量获取数据
SET SERVEROUTPUT ON;
DECLARE
— 声明游标
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 60;

— 声明集合类型
TYPE
employee_id_table IS
TABLE
OF employees.employee_id%TYPE
;
TYPE
first_name_table IS
TABLE
OF employees.first_name%TYPE
;
TYPE
last_name_table IS
TABLE
OF employees.last_name%TYPE
;
TYPE
salary_table IS
TABLE
OF employees.salary%TYPE
;

— 声明集合变量
v_employee_ids employee_id_table;
v_first_names first_name_table;
v_last_names last_name_table;
v_salaries salary_table;
BEGIN
— 批量获取数据
OPEN c_employees;
FETCH c_employees BULK COLLECT IN
TO v_employee_ids, v_first_names, v_last_names, v_salaries;
CLOSE c_employees;

— 处理数据
FOR
i IN
1..v_employee_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_ids(i) || ‘, 姓名: ‘ || v_first_names(i) || ‘ ‘ || v_last_names(i)
|| ‘, 薪水: ‘ || v_salaries(i));
END LOOP
;

DBMS_OUTPUT.PUT_LINE(‘批量获取数据处理完成，共处理 ‘ || v_employee_ids.COUNT || ‘ 条记录’);
END
;
/

执行：

SQL> SET SERVEROUTPUT ON;
SQL> — 使用BULK COLLECT批量获取数据
SQL> DECLARE
2 — 声明游标
3 CURSOR c_employees IS
4 SELECT
employee_id, first_name, last_name, salary
5 FROM employees
6 WHERE department_id = 60;
7
8 — 声明集合类型
9 TYPE
employee_id_table IS
TABLE
OF employees.employee_id%TYPE
;
10 TYPE
first_name_table IS
TABLE
OF employees.first_name%TYPE
;
11 TYPE
last_name_table IS
TABLE
OF employees.last_name%TYPE
;
12 TYPE
salary_table IS
TABLE
OF employees.salary%TYPE
;
13
14 — 声明集合变量
15 v_employee_ids employee_id_table;
16 v_first_names first_name_table;
17 v_last_names last_name_table;
18 v_salaries salary_table;
19 BEGIN
20 — 批量获取数据
21 OPEN c_employees;
22 FETCH c_employees BULK COLLECT IN
TO v_employee_ids, v_first_names, v_last_names, v_salaries;
23 CLOSE c_employees;
24
25 — 处理数据
26 FOR
i IN
1..v_employee_ids.COUNT LOOP
27 DBMS_OUTPUT.PUT_LINE(‘员工ID: ‘ || v_employee_ids(i) || ‘, 姓名: ‘ || v_first_names(i) || ‘ ‘ ||
v_last_names(i) || ‘, 薪水: ‘ || v_salaries(i));
28 END LOOP
;
29
30 DBMS_OUTPUT.PUT_LINE(‘批量获取数据处理完成，共处理 ‘ || v_employee_ids.COUNT || ‘ 条记录’);
31 END
;
32 /
员工ID: 103, 姓名: Alexander Hunold, 薪水: 9000
员工ID: 104, 姓名: Bruce Ernst, 薪水: 6000
员工ID: 105, 姓名: David Austin, 薪水: 4800
员工ID: 106, 姓名: Valli Pataballa, 薪水: 4800
员工ID: 107, 姓名: Diana Lorentz, 薪水: 4200
批量获取数据处理完成，共处理 5 条记录

PL/SQL procedure successfully completed.

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

5.1 游标使用最佳实践

  • 优先使用 FOR
    循环游标，它会自动管理游标的生命周期，减少出错的可能性
  • 使用BULK COLLECT来批量获取数据，提高性能
  • 游标应该只处理必要的数据，避免查询过多的行
  • 游标应该有明确的命名规范，便于识别和管理
  • 在游标中添加适当的注释，说明其功能和使用方法

5.2 游标性能优化技巧

  • 使用适当的WHERE子句来限制结果集的大小
  • 使用 FOR
    循环游标来自动管理游标的生命周期
  • 使用BULK COLLECT来批量获取数据，减少上下文切换
  • 避免在游标循环中执行大量的SQL语句
  • 使用合适的索引来提高查询性能

5.3 游标安全性建议

  • 避免在游标中使用动态SQL，防止SQL注入攻击
  • 对输入参数进行验证，确保参数的有效性
  • 使用适当的权限控制，限制对游标的访问权限
  • 避免在游标中处理敏感数据，保护数据的安全性
  • 定期审计游标的使用情况，发现和解决潜在的安全问题
更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

风哥提示：建议：在设计PL/SQL游标时，应遵循以下原则：

  • 游标应该只处理必要的数据，避免查询过多的行
  • 游标应该有明确的命名规范，便于识别和管理
  • 游标应该有适当的注释，说明其功能和使用方法
  • 避免在游标中执行大量的操作，影响性能
  • 使用 FOR
    循环游标来简化游标的管理

学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息