19.Oracle11g中的游标

oracle基础系统学习目录

01.CentOS7静默安装oracle11g
02.Oracle的启动过程
03.从简单的sql开始
04.Oracle的体系架构
05.Oracle数据库对象
06.Oracle数据备份与恢复
07.用户和权限管理
08.Oracle的表
09.Oracle表的分区
10.Oracle的同义词与序列
11.Oracle的视图
12.Oracle的索引
13.Oracle通过JDBC连接Java
14.Oracle中的事务
15.Oracle11g的归档方式和日志文件的相关操作
16.Oracle的数据字典和动态性能视图
17.Oracle11g的PL/SQL基础
18.Oracle的过程和函数
19.Oracle11g中的游标
20.Oracle11g中的触发器
21.Oracle的程序包(Package)
22.Oracle中的临时表空间
23.Oracle11g的UNDO表空间
24.Oracle11g的逻辑备份与恢复
25. Oracle的回收站
26.Oracle11g的数据装载
27.Oracle11g的闪回Flashback
28.Oracle11g物化视图

---

一、案例引入

假设我们有一个名为employee的表，其中存储了员工的信息，包括员工ID、姓名、部门和工资等。现在我们需要编写一个PL/SQL存储过程，用于计算每个部门的平均工资，并将结果输出到控制台。在这个业务案例中，我们可以使用游标来实现这个逻辑，因为需要对每个部门进行单独的计算并输出结果。

首先，让我们创建一个名为employee的示例表，并插入一些数据：

CREATE TABLE employee (
    employee_id NUMBER,
    employee_name VARCHAR2(100),
    department VARCHAR2(100),
    salary NUMBER
);

INSERT INTO employee (employee_id, employee_name, department, salary) VALUES (1, 'Alice', 'HR', 3000);
INSERT INTO employee (employee_id, employee_name, department, salary) VALUES (2, 'Bob', 'HR', 3500);
INSERT INTO employee (employee_id, employee_name, department, salary) VALUES (3, 'Charlie', 'IT', 4000);
INSERT INTO employee (employee_id, employee_name, department, salary) VALUES (4, 'David', 'IT', 3800);
INSERT INTO employee (employee_id, employee_name, department, salary) VALUES (5, 'Eve', 'IT', 4200);
1
2
3
4
5
6
7
8
9
10
11
12

接下来，我们可以编写一个存储过程，使用游标计算每个部门的平均工资，并将结果输出到控制台：

CREATE OR REPLACE PROCEDURE calculate_average_salary IS
  -- 声明游标
  CURSOR department_cursor IS
    SELECT department, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY department;
  -- 声明游标变量
  department_name employee.department%TYPE;
  avg_salary NUMBER;
BEGIN
  -- 打开游标
  OPEN department_cursor;
  -- 循环处理结果集
  LOOP
    FETCH department_cursor INTO department_name, avg_salary;
    EXIT WHEN department_cursor%NOTFOUND;
    -- 在这里可以处理每一行数据，例如打印到控制台
    DBMS_OUTPUT.PUT_LINE('Department: ' || department_name || ', Average Salary: ' || avg_salary);
  END LOOP;
  -- 关闭游标
  CLOSE department_cursor;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

在上面的示例中，我们首先声明了一个名为department_cursor的游标，它用于查询每个部门的平均工资。然后在存储过程中打开游标，使用循环逐行从游标中获取数据，并在循环中处理每一行数据，最后关闭游标。当我们调用calculate_average_salary存储过程时，它会计算每个部门的平均工资，并将结果输出到控制台。

这个示例演示了如何使用游标在Oracle中处理业务逻辑，通过游标，我们可以更灵活地处理查询结果集中的数据，实现更复杂的数据操作逻辑。

二、什么是游标

在Oracle 11g中，游标是一种用于在PL/SQL程序中处理查询结果集的数据结构。它允许程序员在程序中对查询结果集进行逐行处理，可以用来遍历和操作查询结果集中的数据。游标可以分为隐式游标、显式游标、 REF 游标。

• 隐式游标：

  1. 隐式游标是在PL/SQL块中执行SELECT语句时自动创建和管理的，程序员无需显式声明和使用。
  2. 隐式游标的查询结果集会被自动保存在系统生成的游标中，程序员可以直接在PL/SQL块中对结果集进行处理。
  3. 通过检查隐式游标的属性可以获得最近执行的DML 语句的信息。

  ---

• 显式游标则需要程序员显式地声明和使用，通常包括以下步骤：

  1. 声明游标：使用DECLARE语句声明游标，指定查询语句和游标的名称。
  2. 打开游标：使用OPEN语句打开游标，执行查询语句并将结果集保存在游标中。
  3. 循环处理结果集：使用FETCH语句从游标中逐行获取数据，并在循环中处理每一行数据。
  4. 关闭游标：使用CLOSE语句关闭游标，释放游标占用的资源。

  ---

• REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

通过游标，程序员可以更灵活地处理查询结果集中的数据，实现更复杂的数据操作逻辑。因此，游标在Oracle 11g中广泛应用于PL/SQL程序中对查询结果集进行处理。

三、隐式游标

1、隐式游标的属性

隐式游标是在PL/SQL块中执行SELECT语句时自动创建和管理的，程序员无需显式声明和使用。隐式游标的属性包括以下几个方面：

1. %ROWCOUNT：返回最近一次执行的SQL语句所影响的行数。
2. %FOUND：如果最近一次FETCH语句成功获取了一行数据，则返回TRUE；否则返回FALSE。
3. %NOTFOUND：如果最近一次FETCH语句未成功获取一行数据，则返回TRUE；否则返回FALSE。
4. %ISOPEN：如果隐式游标当前处于打开状态，则返回TRUE；否则返回FALSE。

这些属性可以在PL/SQL块中直接使用，用于判断查询结果集的状态和影响行数等信息。例如，可以使用%ROWCOUNT获取受影响的行数，使用%FOUND和%NOTFOUND判断是否成功获取了数据行，使用%ISOPEN判断游标是否处于打开状态。

2、创建语法

隐式游标的语法非常简单，它可以在PL/SQL的FOR循环语句中使用SELECT语句来隐式创建和使用游标。FOR循环语句的语法如下：

FOR loop_index IN select_statement LOOP
  -- 在这里处理每一行数据
END LOOP;
1
2
3

其中，loop_index是循环索引变量，它会在每次循环迭代中自动赋值为查询结果集中的一行数据。select_statement是SELECT语句，它用于查询数据并返回结果集。

3、示例

下面是一个简单的示例，演示了如何在PL/SQL中使用隐式游标：

CREATE OR REPLACE PROCEDURE calculate_order_total_amount IS
BEGIN
  -- 使用隐式游标，在FOR循环中迭代处理查询结果
  FOR order_rec IN (SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id) LOOP
    -- 在这里可以处理每一行数据，例如打印到控制台
    DBMS_OUTPUT.PUT_LINE('Customer ID: ' || order_rec.customer_id || ', Total Amount: ' || order_rec.total_amount);
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9

在上面的示例中，我们使用了一个隐式游标来处理查询结果集中的数据。在FOR循环中，我们使用了一个SELECT语句来查询所有订单的总金额，并按客户ID分组。在循环中，PL/SQL引擎会自动创建一个隐式游标来执行SELECT语句，并迭代处理查询结果集中的数据。在循环中，我们可以使用order_rec变量来访问每一行数据的字段值，并对数据进行处理。

需要注意的是，隐式游标只能用于一次性使用的简单查询，如果需要对结果集进行多次迭代或进行复杂的数据操作，建议使用显式游标来实现。

四、显示游标

当使用显式游标时，可以通过游标属性来访问有关游标的信息，包括游标的状态、当前行的数据等。下面是关于显式游标的属性、创建语法和应用场景的详细介绍，包含具体代码示例。

1、显示游标的属性

显式游标具有以下几个常用的属性：

• %FOUND：如果最近的FETCH语句成功获取了一行数据，则为TRUE；否则为FALSE。
• %NOTFOUND：如果最近的FETCH语句未能成功获取一行数据，则为TRUE；否则为FALSE。
• %ROWCOUNT：返回最近的OPEN、FETCH或CLOSE语句所涉及的行数。
• %ISOPEN：如果游标是打开状态，则为TRUE；否则为FALSE。

这些属性可以帮助开发人员在处理游标时了解游标的状态和当前行的数据。

2、创建语法

创建显式游标的语法如下：

DECLARE
  CURSOR cursor_name IS
    select_statement;
1
2
3

其中，cursor_name 是游标的名称，select_statement 是 SELECT 语句，用于定义游标要查询的数据集。

3、示例

显式游标适用于以下场景：

• 需要对查询结果集进行多次迭代处理。
• 需要在不同的地方打开和关闭游标，或者在不同的地方获取游标的状态信息。
• 需要在游标的生命周期中手动控制游标的状态。

1. 下面是一个具体的代码示例，演示了显式游标的属性、创建语法和应用场景：

   CREATE OR REPLACE PROCEDURE process_employee_salary IS
     -- 声明显式游标
     CURSOR emp_cursor IS
       SELECT employee_id, employee_name, salary FROM employees;
     emp_rec emp_cursor%ROWTYPE; -- 声明一个记录类型的变量，用于存储游标返回的数据
   
   BEGIN
     -- 打开游标
     OPEN emp_cursor;
   
     -- 获取游标的状态信息
     IF emp_cursor%ISOPEN THEN
       DBMS_OUTPUT.PUT_LINE('Cursor is open');
     END IF;
   
     -- 迭代处理查询结果集
     LOOP
       FETCH emp_cursor INTO emp_rec; -- 获取游标返回的数据
       EXIT WHEN emp_cursor%NOTFOUND; -- 当没有更多数据时退出循环
       -- 处理数据，例如打印到控制台
       DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.employee_name || ', Salary: ' || emp_rec.salary);
     END LOOP;
   
     -- 获取行数
     DBMS_OUTPUT.PUT_LINE('Total rows processed: ' || emp_cursor%ROWCOUNT);
   
     -- 关闭游标
     CLOSE emp_cursor;
   
     -- 获取游标的状态信息
     IF NOT emp_cursor%ISOPEN THEN
       DBMS_OUTPUT.PUT_LINE('Cursor is closed');
     END IF;
   
   END;
   /
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36

   在上面的示例中，我们创建了一个名为 emp_cursor 的显式游标，用于查询员工表中的数据。在过程中，我们打开了游标，使用 FETCH 语句迭代处理查询结果集，获取了游标的状态信息，获取了行数，并在最后关闭了游标。这个示例展示了显式游标的属性、创建语法和应用场景。

2. 带参数的显示游标案例
   以下是一个带参数的游标示例，其中我们使用参数来过滤查询结果集：

   -- 创建带参数的游标
   DECLARE
     CURSOR student_cursor (grade_param NUMBER) IS
       SELECT xh, xm
       FROM student
       WHERE grade = grade_param;
     xh_val student.xh%TYPE;
     xm_val student.xm%TYPE;
     grade_val NUMBER := 1; -- 设置参数的默认值
   BEGIN
     -- 打开游标
     OPEN student_cursor(grade_val);
     
     -- 迭代处理查询结果集
     LOOP
       FETCH student_cursor INTO xh_val, xm_val;
       EXIT WHEN student_cursor%NOTFOUND;
       
       -- 处理查询结果，例如打印到控制台
       DBMS_OUTPUT.PUT_LINE('Student ID: ' || xh_val || ', Name: ' || xm_val);
     END LOOP;
     
     -- 关闭游标
     CLOSE student_cursor;
   END;
   /
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26

   在上面的示例中，我们创建了一个带参数的游标 student_cursor，该游标接受一个名为 grade_param 的参数，并用于过滤查询结果集。我们在声明游标时指定了参数，并在打开游标时传入参数的值。这样就可以根据参数的值来过滤查询结果集，实现了带参数的游标功能。

五、REF游标

在Oracle数据库中，REF游标是一种特殊类型的游标，用于处理具有引用类型的数据。以下是关于REF游标的属性、创建语法和应用场景的详细介绍，包含具体代码示例。

1、REF游标的属性

REF游标具有以下常用属性：

• %FOUND：如果最近的FETCH语句成功获取了一行数据，则为TRUE；否则为FALSE。
• %NOTFOUND：如果最近的FETCH语句未能成功获取一行数据，则为TRUE；否则为FALSE。
• %ROWCOUNT：返回最近的OPEN、FETCH或CLOSE语句所涉及的行数。
• %ISOPEN：如果游标是打开状态，则为TRUE；否则为FALSE。

这些属性与普通显式游标的属性相似，用于获取游标的状态信息。

2、创建语法

创建REF游标的语法如下：

DECLARE
  CURSOR cursor_name IS
    SELECT REF(column_name) FROM table_name;
1
2
3

其中，cursor_name 是游标的名称，column_name 是包含引用类型数据的列名，table_name 是包含引用类型数据的表名。

3、示例

REF游标适用于以下场景：

• 处理包含引用类型数据的表，例如使用对象类型或嵌套表类型。
• 需要在游标中返回引用类型数据的引用。
• 需要在游标的生命周期中手动控制游标的状态。

下面是一个具体的代码示例，演示了REF游标的属性、创建语法和应用场景：

CREATE OR REPLACE PROCEDURE process_ref_cursor IS
  -- 声明REF游标
  CURSOR ref_cursor IS
    SELECT REF(e) FROM employees e;
  emp_ref employees%ROWTYPE; -- 声明一个记录类型的变量，用于存储引用类型数据的引用

BEGIN
  -- 打开游标
  OPEN ref_cursor;

  -- 获取游标的状态信息
  IF ref_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('REF Cursor is open');
  END IF;

  -- 迭代处理查询结果集
  LOOP
    FETCH ref_cursor INTO emp_ref; -- 获取游标返回的引用数据
    EXIT WHEN ref_cursor%NOTFOUND; -- 当没有更多数据时退出循环
    -- 处理引用数据，例如打印到控制台
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_ref.last_name);
  END LOOP;

  -- 获取行数
  DBMS_OUTPUT.PUT_LINE('Total rows processed: ' || ref_cursor%ROWCOUNT);

  -- 关闭游标
  CLOSE ref_cursor;

  -- 获取游标的状态信息
  IF NOT ref_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('REF Cursor is closed');
  END IF;

END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

在上面的示例中，我们创建了一个名为 ref_cursor 的REF游标，用于查询包含引用类型数据的员工表。在过程中，我们打开了REF游标，使用 FETCH 语句迭代处理查询结果集，获取了游标的状态信息，获取了行数，并在最后关闭了游标。这个示例展示了REF游标的属性、创建语法和应用场景。

六、循环游标

循环游标是一种在数据库中遍历结果集的方法。它可以用于迭代处理查询结果，对每一行数据执行特定的操作。以下是关于循环游标的详细介绍：

1、 循环游标的作用

循环游标的作用是遍历查询结果集中的数据，并对每一行数据执行特定的操作。这种遍历方式允许开发人员逐行处理结果集，执行复杂的业务逻辑，或者将结果集中的数据进行进一步处理。

• 数据处理：循环游标常用于对查询结果进行逐行处理，例如计算、数据转换等。
• 数据校验：可以使用循环游标来逐行校验查询结果，确保数据的完整性和准确性。
• 数据导出：通过循环游标，可以逐行将查询结果导出到其他数据源或文件中。

2、用for 与 loop 创建

使用循环游标，有两种常见的创建语法，一种是使用 FOR 循环，另一种是使用 LOOP 循环。

• 使用 FOR 循环创建循环游标的语法如下所示：

  DECLARE
    variable1 datatype;
    variable2 datatype;
  BEGIN
    FOR rec IN (SELECT column1, column2, ... FROM table_name WHERE condition) LOOP
      variable1 := rec.column1;
      variable2 := rec.column2;
      -- 在此处处理查询结果
    END LOOP;
  END;
  /
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• 使用 LOOP 循环创建循环游标的语法如下所示：

  DECLARE
    CURSOR cursor_name IS
      SELECT column1, column2, ...
      FROM table_name
      WHERE condition;
    variable1 datatype;
    variable2 datatype;
  BEGIN
    OPEN cursor_name;
    LOOP
      FETCH cursor_name INTO variable1, variable2, ...;
      EXIT WHEN cursor_name%NOTFOUND;
      -- 在此处处理查询结果
    END LOOP;
    CLOSE cursor_name;
  END;
  /
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

3、示例

以下是一个具体的示例代码，演示了使用 FOR 循环和 LOOP 循环创建循环游标，并遍历查询结果集：

• 使用 FOR 循环：

  DECLARE
    xh_val student.xh%TYPE;
    xm_val student.xm%TYPE;
  BEGIN
    FOR rec IN (SELECT xh, xm FROM student) LOOP
      xh_val := rec.xh;
      xm_val := rec.xm;
      DBMS_OUTPUT.PUT_LINE('Student ID: ' || xh_val || ', Name: ' || xm_val);
    END LOOP;
  END;
  /
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

  这段代码是一个 PL/SQL 块，用于遍历名为 student 的表中的数据，并输出学生的学号和姓名到控制台。具体解释如下：

  • DECLARE：声明部分，用于声明变量和游标。
  • xh_val student.xh%TYPE;：声明一个变量 xh_val，其类型与表 student 中的 xh 字段相同。
  • xm_val student.xm%TYPE;：声明一个变量 xm_val，其类型与表 student 中的 xm 字段相同。
  • BEGIN：开始部分，标志着 PL/SQL 块的开始。
  • FOR rec IN (SELECT xh, xm FROM student) LOOP：使用 FOR 循环创建一个游标 rec，并执行 SELECT 语句以从表 student 中获取学号和姓名。
  • xh_val := rec.xh;：将当前行的学号赋值给变量 xh_val。
  • xm_val := rec.xm;：将当前行的姓名赋值给变量 xm_val。
  • DBMS_OUTPUT.PUT_LINE('Student ID: ' || xh_val || ', Name: ' || xm_val);：输出当前行的学号和姓名到控制台。
  • END LOOP;：结束 FOR 循环。
  • END;：PL/SQL 块的结束。

• 使用 LOOP 循环：

  DECLARE
    CURSOR student_cursor IS
      SELECT xh, xm
      FROM student;
    xh_val student.xh%TYPE;
    xm_val student.xm%TYPE;
  BEGIN
    OPEN student_cursor;
    LOOP
      FETCH student_cursor INTO xh_val, xm_val;
      EXIT WHEN student_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Student ID: ' || xh_val || ', Name: ' || xm_val);
    END LOOP;
    CLOSE student_cursor;
  END;
  /
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

  这段代码是一个 PL/SQL 块，用于遍历名为 student 的表中的数据，并输出学生的学号和姓名到控制台。具体解释如下：

  • DECLARE：声明部分，用于声明变量和游标。
  • CURSOR student_cursor IS SELECT xh, xm FROM student;：声明一个名为 student_cursor 的游标，用于从表 student 中选择学号和姓名。
  • xh_val student.xh%TYPE;：声明一个变量 xh_val，其类型与表 student 中的 xh 字段相同。
  • xm_val student.xm%TYPE;：声明一个变量 xm_val，其类型与表 student 中的 xm 字段相同。
  • BEGIN：开始部分，标志着 PL/SQL 块的开始。
  • OPEN student_cursor;：打开游标 student_cursor。
  • LOOP：开始一个循环，用于逐行处理查询结果。
  • FETCH student_cursor INTO xh_val, xm_val;：从游标中获取下一行数据，并将学号和姓名赋值给对应的变量。
  • EXIT WHEN student_cursor%NOTFOUND;：如果游标中没有更多的数据，则退出循环。
  • DBMS_OUTPUT.PUT_LINE('Student ID: ' || xh_val || ', Name: ' || xm_val);：输出当前行的学号和姓名到控制台。
  • END LOOP;：结束循环。
  • CLOSE student_cursor;：关闭游标 student_cursor。
  • END;：PL/SQL 块的结束。

  ---

  在上面的示例中，我们演示了使用 FOR 循环和 LOOP 循环创循环游标，并遍历查询结果集，将学生的学号和姓名逐行输出到控制台。