Oracle游标

原创已于 2025-07-07 08:36:53 修改 · 878 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #游标 #sql

于 2025-06-05 09:27:24 首次发布

Oracle 专栏收录该内容

30 篇文章

订阅专栏

(1)声明游标（Declaring a Cursor）

(2)打开游标（Opening a Cursor）

(3)提取数据（Fetching Data）

(4)关闭游标（Closing a Cursor）

3.2.2显式游标语法示例

4.快速游标

Oracle 游标的 SQL% 四个系统变量属性

5.游标练习

6.带参数的游标

二、游标知识点总结

一、游标相关概念介绍

1.推荐文章

《Oracle 游标详解（cursor）》

《Oracle游标》

2.什么是游标

游标：是指向结果集的一个指针。

3.隐式游标与显式游标

3.1隐式游标

不需要手动创建游标

SELECT ... INTO ...;

DML 语句也会产生隐式游标

DML: INSERT INTO UPDATE DELETE

3.2显式游标

显式游标：分为声明游标、打开游标、提取数据和关闭游标四个步骤

1.返回的结果可以是0行，一行或者多行。

2.需要手动创建/手动声明

3.2.1显式游标四个步骤

(1)声明游标（Declaring a Cursor）

在这一步，你定义游标的名字和查询语句。游标是数据库查询结果的指针，声明游标时，你并不立即执行查询，而是定义了将要执行的查询。

DECLARE cursor_name CURSOR FOR

SELECT column1, column2 FROM table_name WHERE condition;

(2)打开游标（Opening a Cursor）

声明游标后，你需要打开它以准备执行查询。打开游标会实际执行声明中的SQL语句，并准备结果集供后续提取。

OPEN cursor_name;

(3)提取数据（Fetching Data）

打开游标后，你可以使用提取命令从结果集中逐行读取数据。这通常在一个循环中完成，直到检索完所有的数据

FETCH NEXT FROM cursor_name INTO variable1, variable2;

(4)关闭游标（Closing a Cursor）

一旦完成数据提取，你应该关闭游标来释放数据库资源。这一步很重要，因为它可以避免资源泄露。

CLOSE cursor_name

3.2.2显式游标语法示例

SET SERVEROUTPUT ON; -- 是一个SQLPlus命令，用于启用服务器端输出，确保可以输出结果

DECLARE
    -- 步骤1: 声明游标
    v_empno    emp.empno%TYPE;
    v_ename    emp.ename%TYPE;
    v_job      emp.job%TYPE;
    v_deptno   emp.deptno%TYPE;
    -- 声明一个游标，选择emp表中的所有记录
    CURSOR emp_cursor IS
        SELECT empno, ename, job, deptno
        FROM emp;

BEGIN
    -- 步骤2: 打开游标
    OPEN emp_cursor;

    -- 步骤3: 提取数据
    LOOP
        -- 尝试提取数据
        FETCH emp_cursor INTO v_empno, v_ename, v_job, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND; -- 如果没有更多数据，则退出循环

        -- 输出提取的数据
        DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_empno);
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
        DBMS_OUTPUT.PUT_LINE('Job: ' || v_job);
        DBMS_OUTPUT.PUT_LINE('Department Number: ' || v_deptno);
        DBMS_OUTPUT.PUT('-----------------------'); -- 输出分隔线
    END LOOP;

    -- 步骤4: 关闭游标
    CLOSE emp_cursor;
EXCEPTION
    WHEN OTHERS THEN
        -- 如果发生异常，输出错误信息
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        -- 如果游标是打开的，则关闭它
        IF emp_cursor%ISOPEN THEN
            CLOSE emp_cursor;
        END IF;
        -- 重新抛出异常以进行进一步的错误处理
        RAISE;
END;

4.快速游标

Oracle提供了一种简化的游标使用方式，称为"快速游标"（Fast Cursor），在某些情况下可以省略打开和关闭游标步骤。

Oracle 游标的 SQL% 四个系统变量属性

属性	返回值类型	作用
SQL%ISOPEN	布尔型	判断游标是否处于 "开启" 状态（即是否已执行 OPEN 但未执行 CLOSE）
SQL%FOUND	布尔型	判断游标是否 "获取" 到值（即最近一次 FETCH 是否成功返回数据）
SQL%NOTFOUND	布尔型	判断游标是否 "未获取" 到值（与 % FOUND 相反，常用于退出循环条件）
SQL%ROWCOUNT	整型	返回 "当前" 已成功执行的 SQL 语句影响的行数（非总记录数，常用于 DML）

关键说明：

SQL%ISOPEN：

仅对显式游标有效，隐式游标（如 SELECT INTO）始终返回 FALSE。

SQL%FOUND / %NOTFOUND：

在执行 FETCH 语句后才能确定其值。
首次执行 FETCH 前，值为 NULL。

SQL%ROWCOUNT：

对 SELECT INTO 语句：若成功返回 1 行，否则抛出异常。
对 DML 语句（INSERT/UPDATE/DELETE）：返回受影响的行数。
对游标：返回当前已 FETCH 的行数。

5.游标练习

使用FOR IN LOOP结构，将 EMP 表的员工工号、姓名一起打印出来

DECLARE
    CURSOR c1 IS --c1是游标的名称
        SELECT empno, ename
        FROM emp;
BEGIN
    FOR i IN c1
        LOOP
            --i在c1的范围内遍历，故i能代表c1的结果集
            dbms_output.put_line(i.empno || '-' || i.ename); 
            --i 代表游标 c1 当前检索到的记录，可以像访问表中的列一样访问它的字段。
        END LOOP;
END;

示例：通过游标的形式逐行将EMP表的数据，写入到 EMP_CP

drop table EMP_CP;
create table EMP_CP as select * from EMP where 1 = 2;

DECLARE
    CURSOR c1 IS
        SELECT *
        FROM emp;
BEGIN
    FOR i IN c1
        LOOP
            insert into EMP_CP
            values (i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal, i.comm, i.deptno);
            commit;
        END LOOP;
END;

select * from emp_cp;

练习：DBMS_OUTPUT.PUT_LINE 打印出 EMP 每个员工的名字，岗位、入职日期

DECLARE
    CURSOR C1 IS
        SELECT ENAME, JOB, HIREDATE
        FROM EMP;
BEGIN
    FOR I IN C1
        LOOP
            dbms_output.put_line(i.ENAME || '-' || i.JOB || '-' || TO_CHAR(i.HIREDATE,'YYYY-MM-DD'));
        END LOOP;
END;

6.带参数的游标

带参数的游标：游标名(参数1 数据类型 [, 参数2 数据类型 .. .. ])

需要注意：

1、参数的数据类型不要带长度，只需要定义类型即可

2、定义了参数，在打开游标取数的时候就必须要传参数进去

3、定义的参数是什么数据类型，传参的时候就必须传该数据类型的值

参数和变量区别：参数声明的时候不能加数据类型的长度，变量需要指定长度。

示例：传入一个部门编号，打印出这个部门的所有员工的名字和工资

-- 不带参数的游标
DECLARE
    V_DEPTNO NUMBER := :INPUT;
    CURSOR C1 IS --声明游标
        SELECT ENAME, SAL
        FROM EMP
        WHERE DEPTNO = V_DEPTNO;
BEGIN
    FOR I IN C1
        LOOP
            dbms_output.put_line(i.ENAME || '-' || i.SAL);
        END LOOP;
END;

-- 带有参数的游标
DECLARE
    V_DEPTNO NUMBER := :INPUT;
    CURSOR C1(P_DEPTNO NUMBER) IS --声明游标
        SELECT ENAME, SAL
        FROM EMP
        WHERE DEPTNO = P_DEPTNO;
BEGIN
    FOR I IN C1(V_DEPTNO)
        LOOP
            dbms_output.put_line(i.ENAME || '-' || i.SAL);
        END LOOP;
END;

-- 10=>V_DEPTNO>c1(V_DEPTNO)=>WHERE DEPTNO=P_DEPTNO

示例：接收用户输入的员工编号 X，然后将跟X同部门的数据，利用游标逐行写入到 EMP_01

drop table EMP_01;
CREATE TABLE EMP_01 AS SELECT * FROM EMP WHERE 1 = 2;

DECLARE
    X NUMBER := :INPUT;
    CURSOR C1(P_EMPNO NUMBER) IS --声明游标
        SELECT *
        FROM EMP
        WHERE DEPTNO = (SELECT DEPTNO
                        FROM EMP
                        WHERE EMPNO = P_EMPNO);
BEGIN
    FOR I IN C1(X)
        LOOP
            INSERT INTO EMP_01
            VALUES (i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal, i.comm, i.deptno);
            commit;
        END LOOP;
END;

SELECT * FROM EMP_01;

练习：接收用户输入的员工编号, 打印该员工相同工作的所有员工姓名不显示输入的编号本身

DECLARE
    V_EMPNO NUMBER := :INPUT;
    CURSOR C1(P_EMPNO NUMBER) IS --声明游标
        SELECT ENAME
        FROM EMP
        WHERE JOB = (SELECT JOB
                     FROM EMP
                     WHERE EMPNO = P_EMPNO)
          AND EMPNO != P_EMPNO;
BEGIN
    FOR I IN C1(V_EMPNO)
        LOOP
            DBMS_OUTPUT.PUT_LINE(I.ENAME);
        END LOOP;
END;

游标简便写法：

declare
  v_empno number := &input;
begin
  for i in (select ename
              from emp
             where job = (select job from emp where empno = v_empno)
               and empno != v_empno) loop
    dbms_output.put_line(i.ename);
  end loop;
end;

游标比较复杂的写法：

DECLARE
    CURSOR C_JOB IS--声明游标
        SELECT EMPNO, ENAME, JOB, SAL
        FROM EMP
        WHERE JOB = 'CLERK';
    --定义一个游标变量
    C_ROW C_JOB%ROWTYPE; --引用所有的字段类型

BEGIN
    OPEN C_JOB; --- 等效于： SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE JOB = 'MANAGER';
    LOOP
        FETCH C_JOB--从C_JOB中提取
            INTO C_ROW;--插入到C_ROW
        EXIT WHEN C_JOB%NOTFOUND;-- 如果没有更多行，退出循环，此时布尔类型true：理解为 not found 为真
        DBMS_OUTPUT.PUT_LINE(C_ROW.EMPNO || '-' || C_ROW.ENAME || '-' ||
                             C_ROW.JOB || '-' || C_ROW.SAL);
    END LOOP;
    CLOSE C_JOB; --关闭游标
END;

示例：传入一个部门编号，打印出这个部门的所有员工的名字，工资打印出来，编写存储过程使用游标

create or replace procedure p_9(p_deptno number) 
is cursor c1 is -- 这里可以定义游标
  select ename, sal from emp where deptno = p_deptno;
begin
  FOR i in c1 loop
    dbms_output.put_line(i.ename || '-' || i.sal);
  end loop;
end;

-- 调用存储过程 
begin
  p_9(p_deptno => 10);
end;

练习1. 编辑存储过程，使用游标、变量，通过入参 P_EMPNO，将该员工对应的所有相同工种的数据，同步到EMP_0317;

drop table EMP_0317;
CREATE TABLE EMP_0317 AS
SELECT *
FROM emp
WHERE 1 = 2;

create or replace procedure p_10(P_EMPNO number)
as
    V_JOB varchar2(10);-- 定义变量
    cursor c1 is -- 定义游标
        select *
        from EMP
        where JOB = V_JOB;
begin
    select JOB
    into V_JOB
    from EMP
    where EMPNO = P_EMPNO;
    FOR I IN c1
        LOOP
            INSERT INTO EMP_0317
            VALUES (i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal, i.comm, i.deptno);
            COMMIT;
        end loop;
end;

-- 调用存储过程
begin
    p_10(P_EMPNO=>7788);
end;

select *
from EMP_0317;

二、游标知识点总结

1.游标关键字是 CURSOR；

2.需要在 BEGIN 之前定义好；

3.游标就是将可变的 SELECT 查询结果集封装成一个游标，可以有入参 (参数)，语法是：
CURSOR 游标名 (参数名称  参数类型) IS
SELECT 字段 FROM 表 WHERE 条件;
4.在 FOR循环打开游标，语法是：
FOR I IN 游标名(参数值) LOOP
做的操作(I.ENAME,I.SAL)
END LOOP;
5.游标是指向结果集每一行的指针。