Oracle中的游标

灰太狼 2024-03-22 15:18 62阅读 0赞

**目录**

一、游标定义

二、游标分类

2.1静态游标

2.1.1. 隐式游标：

2.1.2. 显式游标：

2.1.3. 游标变量：

2.1.4. 游标参数：

2.2动态游标

2.2.1强类型游标

2.2.2弱类型游标

2.2.3普通动态游标

三、游标的优缺点

优点：

缺点：

四、游标使用注意事项

五、游标的作用

--------------------

## 一、游标定义 ##

游标即**cursor**，是一种用于遍历结果集的数据类型。它是一个指向结果集的指针，可以用于遍历结果集中的每一行数据，但是一次只能指向一行。游标通常**用于存储过程和函数**中，以便在处理结果集时能够逐行处理数据。

游标提供了**在逐行的基础上操作表中数据**的方法，游标的结果集是由select语句产生的。  
拿个表来打比方，游标打开时会在第一行数据的上面每fetch一次，下去一行。

## 二、游标分类 ##

游标有两种类型：***静态游标和动态游标***。接下来分别描述各个类型的游标：

### 2.1静态游标 ###

静态游标是一种在编译时就确定结果集的游标，其结果集*不会随着时间或用户的不同而发生变化*。在Oracle中，静态游标包括以下几种类型：

#### 2.1.1. 隐式游标： ####

由SELECT语句自动创建的游标，其*结果集在编译时就已经确定*，不会随时间或用户的不同而发生变化。而且隐式游标是Oracle数据库默认使用的游标类型。它是一种*只能在SQL语句中使用*的游标，*不能在PL/SQL代码中使用*。隐式游标会在执行一条SELECT语句时自动创建，并且*只能返回一行数据*。例如：

SELECT column1, column2
    FROM table1
    WHERE column3 = 'value';
    
    SELECT * 
    FROM employees 
    WHERE department_id = 10;

上面的SELECT语句会自动创建一个隐式游标，其结果集是所有部门ID为10的员工。由于结果集在编译时就已经确定，因此这是一种静态游标。

> **总结：**
> 
> *隐式游标不用声明，不用打开，不用赋值，不用关闭，这些都是oracle自动完成的*。  
> 常见的方式有 delete 、update、 insert select into 单行赋值（主要用来统计dml操作了多少行）格式：
> 
>     INSERT UPDATE DELETE SELECT..int
> 
> ***隐式游标的四个属性***
> 
>  *  sql%isopen 判断游标是否打开 ，永远返回假
>  *  sql%found 判断指针里面是否有值 ，如果指针里曾经有值 ，返回的是真
>  *  sql%notfound 和found相反
>  *  sql%rowcount 返回指针指过的行数

举例说明：

要求给emp1表的10部门员工工资增加500，并打印有多少人增加了 工资：

begin
      update emp1 set sal=sal+500 where deptno=10;
      dbms_output.put_line(sql%rowcount);
      if sql%found then dbms_output.put_line('有人涨工资了');
      end if;
    end;

#### 2.1.2. 显式游标： ####

由*DECLARE、OPEN、FETCH和CLOSE等语句手动创建的游标*，其结果集在定义游标时就已经确定，不会随时间或用户的不同而发生变化。并且*显式游标是由程序员在PL/SQL代码中显式声明和使用的游标类型*。它可以在程序中读取和处理多行数据。例如：

DECLARE
      cursor_name CURSOR FOR
        SELECT column1, column2
        FROM table1
        WHERE column3 = 'value';
        
      var1 table1.column1%TYPE;
      var2 table1.column2%TYPE;
    BEGIN
      OPEN cursor_name;
      
      LOOP
        FETCH cursor_name INTO var1, var2;
        EXIT WHEN cursor_name%NOTFOUND;
        
        -- 处理数据
      END LOOP;
      
      CLOSE cursor_name;
    END;
    -------------------------------------------------------------------------------
    DECLARE
      CURSOR c_emp IS SELECT * FROM employees WHERE department_id = 10;
      v_emp employees%ROWTYPE;
    BEGIN
      OPEN c_emp;
      LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
        -- 对结果集进行处理
      END LOOP;
      CLOSE c_emp;
    END;

上面的代码定义了一个名为c\_emp的显式游标，其结果集是所有部门ID为10的员工。由于结果集在定义游标时就已经确定，因此这是一种静态游标。

***其实显示游标又分为带参数的显示游标和不带参数的显示游标。***

***Ⅰ、不带参数的显式游标***  
声明方法：

cursor 游标名 is select 语句

例如：

打印部门dept表中的所有部门名称（dept表一共有4个部门）：

declare
    cursor c1 is select dname from dept;
    v_name varchar2(20);
    begin
      open c1;
      fetch c1 into v_name;
      dbms_output.put_line(v_name);
      fetch c1 into v_name;
      dbms_output.put_line(v_name);
      fetch c1 into v_name;
      dbms_output.put_line(v_name);
      fetch c1 into v_name;
      dbms_output.put_line(v_name);  
      close c1;
    end;

循环打印dept表中的所有部门名称：

declare
    cursor c1 is select dname from dept;
    v_name varchar2(20);
    begin
      open c1;
     for i in 1..4 loop
       fetch c1 into v_name;
      dbms_output.put_line(v_name);
      end loop;
      close c1;
    end;

打印员工emp表所有的员工编号 （表内一共有14个员工信息）：

declare
    cursor c1 is select empno from emp;
    v_name varchar2(20);
    begin
      open c1;
     for i in 1..14 loop
       fetch c1 into v_name;
      dbms_output.put_line(v_name);
      end loop;
      close c1;
    end;

***但是如果我们不知道一张表内某个列中值有多少行时，就得用到游标的四个属性 ：***

>  *  游标名%isopen 判断游标是否打开，是的话返回真 ，否则返回假
>  *  游标名%found 判断游标的指针里是否有值，有值返回真 ，没值返回假
>  *  游标名%notfound 判断游标的指针里是否没值，没值返回真 ，有值返回假
>  *  游标名%rowcount 游标的指针指了多少行，返回的是行数

***普通循环搭配不带参数的显式游标的步骤顺序如下：***

*  1.打开游标
 *  2.loop
 *  3.fetch
 *  4.exit when
 *  5.打印
 *  6.end loop
 *  7.关闭游标

比如：

打印员工emp表中的所有员工编号 ：

declare 
    cursor c1 is select empno from emp;
    v_empno number;
    begin
      open c1;
      loop
        fetch c1 into v_empno;
        exit when c1%notfound;
        dbms_output.put_line(v_empno);
      end loop;
       close c1;
    end;

打印emp表经理是KING的员工姓名:

declare 
    cursor c1 is select ename 
                 from emp 
                 where mgr=(select empno 
                            from emp 
                            where ename='KING');
    v_name varchar2(20);
    begin
      open c1;
      loop
        fetch c1 into v_name;
        exit when c1%notfound;
        dbms_output.put_line(v_name);
      end loop;
       close c1;
    end;

打印 emp表经理是KING的姓名以及人数:

***while 循环和游标 -WHILE循环+%found的步骤 ：***

*  1.open 打开游标
 *  2.fetch 先提取一次 以便进入循环
 *  3.while
 *  4.打印 进入循环先打印一次 避免漏掉第一条数据
 *  5.fetch
 *  6.end loop
 *  7.close

为什么这里要多打印一次呢，因为在open后fetch前，我们的游标%found是为假的，所以就无法进入while循环,必须先fetch一遍！！！

举例说明：

打印dept表中的所有部门名称：

declare
    cursor c1 is select dname from dept;
    v_name varchar2(20);
    begin
      open c1;
      fetch c1 into v_name;      --先提取一次
      while c1%found loop
        dbms_output.put_line(v_name);
        fetch c1 into v_name;
      end loop;
      close c1;
    end;

要求 打印emp表职位是销售的员工信息，使用while循环和游标方法，则语句如下：

declare
    cursor c is select *
                from emp 
                where job='SALESMAN';
    v emp%rowtype;
    begin
      open c;
      fetch c into v;
      while c%found loop
        dbms_output.put_line(lpad(v.empno,9,' ')||''||
    lpad(v.ename,9,' ')||''||
    lpad(v.job,9,' ')||''||
    lpad(v.mgr,9,' ')||''||
    lpad(v.hiredate,9,' ')||''||
    lpad(v.sal,9,' ')||''||
    lpad(v.comm,9,' ')||''||
    lpad(v.deptno,9,' ')
    );
        fetch c into v;
      end loop;
      close c;
    end;

***Ⅱ、带参数的显式游标***  
声明参数时，只用写类型，不用写长度，参数可以在where条件后调用。使用时需要在open游标后加括号里面填上参数（多个参数用逗号隔开）。格式为：

CURSOR cur_name(参 类型,..) IS SELECT 语句(包含参);

比如：

要求打印emp表中部门编号是10的员工姓名，然后再打印emp表中部门编号是20的员工编号：

declare
    cursor c1(v number) is 
              select ename,empno from emp where deptno=v;
    vname varchar2(20);
    vempno number;
    begin
      open c1(10);
      loop
        fetch c1 into vname,vempno;
        exit when c1%notfound;
        dbms_output.put_line(vname);
      end loop;
      close c1;
      open c1(20);
        loop
        fetch c1 into vname,vempno;
        exit when c1%notfound;
        dbms_output.put_line(vempno);
      end loop;
      close c1;
    end;

请打印emp表中职位是CLERK的姓名，职位是MANAGER的工资 ：

declare
    cursor c1 (v varchar2) is 
           select ename,sal from emp where job=v;
    v_name varchar2(20);
    v_sal number;
    begin
      open c1('CLERK'); 
      loop
      fetch c1 into v_name,v_sal;
      exit when c1%notfound;
      dbms_output.put_line(v_name);
      end loop;
      close c1;
      open c1('MANAGER');
      LOOP
      fetch c1 into v_name,v_sal;
      exit when c1%notfound;
      dbms_output.put_line(v_sal);
      end loop;
      close c1;
     end;

#### 2.1.3. 游标变量： ####

可以将查询结果集存储在游标变量中，以便在后续的处理中使用。游标变量在*定义时需要指定结果集*，其结果集也是在编译时就已经确定的。游标变量是*一种PL/SQL变量*，它可以存储一个游标对象。游标变量可以在程序中*传递和重复使用*，可以在不同的程序块中声明和使用。例如：

DECLARE
      cursor_name SYS_REFCURSOR;
      var1 table1.column1%TYPE;
      var2 table1.column2%TYPE;
    BEGIN
      OPEN cursor_name FOR
        SELECT column1, column2
        FROM table1
        WHERE column3 = 'value';
        
      LOOP
        FETCH cursor_name INTO var1, var2;
        EXIT WHEN cursor_name%NOTFOUND;
        
        -- 处理数据
      END LOOP;
      
      CLOSE cursor;
    ------------------------------------------------------------
    DECLARE
      TYPE t_emp IS RECORD (
        employee_id    employees.employee_id%TYPE,
        first_name     employees.first_name%TYPE,
        last_name      employees.last_name%TYPE,
        email          employees.email%TYPE,
        phone_number   employees.phone_number%TYPE
      );
      TYPE t_emp_list IS TABLE OF t_emp;
      v_emp_list t_emp_list;
    BEGIN
      SELECT employee_id, first_name, last_name, email, phone_number
      BULK COLLECT INTO v_emp_list
      FROM employees
      WHERE department_id = 10;
      -- 对结果集进行处理
    END;

上面的代码将所有部门ID为10的员工存储在名为v\_emp\_list的游标变量中。由于结果集在定义游标变量时就已经确定，因此这是一种静态游标。

#### 2.1.4. 游标参数： ####

可以将查询结果集作为参数传递给存储过程或函数，以便在后续的处理中使用。游标参数在定义时需要指定结果集，其结果集也是在编译时就已经确定的。例如：

CREATE OR REPLACE PROCEDURE p_emp_list (p_dept_id IN NUMBER, 
                                            p_emp_list OUT SYS_REFCURSOR)
    IS
    BEGIN
      OPEN p_emp_list FOR
        SELECT * FROM employees WHERE department_id = p_dept_id;
    END;

上面的代码定义了一个名为p\_emp\_list的存储过程，其参数p\_dept\_id是部门ID，p\_emp\_list是查询结果集。当调用p\_emp\_list存储过程时，会根据传入的部门ID返回相应的员工列表。该存储过程可以被其他程序调用，并将查询结果作为输出参数返回。

> **总结：**
> 
> 静态游标是一种在编译时就确定结果集的游标，其结果集不会随着时间或用户的不同而发生变化。在实际应用中，可以根据需要选择不同类型的静态游标来处理数据。

### 2.2动态游标 ###

动态游标是一种在 *PL/SQL 中使用游标*的方式，它允许在运行时动态地构建 SQL 查询语句，以便适应不同的查询需求。相比于静态游标，*动态游标更加灵活*，可以根据不同的条件和参数生成不同的查询语句。

列举使用动态游标的示例：

CREATE OR REPLACE PROCEDURE p_emp_list (p_dept_id IN NUMBER, 
                                            p_emp_list OUT SYS_REFCURSOR)
    IS
      v_query VARCHAR2(200);
    BEGIN
      v_query := 'SELECT * FROM employees WHERE department_id = ' || p_dept_id;
      OPEN p_emp_list FOR v_query;
    END;

在该存储过程中，使用一个 VARCHAR2 类型的变量 v\_query 来构建动态查询语句。首先将一个固定的字符串 'SELECT \* FROM employees WHERE department\_id = ' 和输入参数 p\_dept\_id 拼接起来，生成一个完整的查询语句。然后使用 OPEN 语句打开游标 p\_emp\_list，并将动态查询语句作为参数传入。

通过使用动态游标，该存储过程可以根据不同的部门 ID 生成不同的查询语句，并返回不同的查询结果。这种灵活性使得动态游标在复杂的查询场景中非常有用。

***但是动态游标也有强弱之分***，下面分别描述：

#### 2.2.1强类型游标 ####

强游标（strong cursor）是指*在游标打开时就将所有数据都读入到游标中*，因此游标*可以在任何时候访问数据*，但是这种方式会*占用大量的内存资源*。而且强类型游标是**可以更换结果集，但是不能换表结构**。

比如：

要求打印emp表的10部门的员工姓名，并打印emp1职位是CLERK的员工编号，则语句如下：

declare 
    type ty1 is ref cursor return emp%rowtype;
    c1 ty1;
    vemp emp%rowtype;
    begin
      open c1 for select * from emp where deptno=10;
      loop
        fetch c1 into vemp;
        exit when c1%notfound;
        dbms_output.put_line(vemp.ename);
      end loop;
      close c1;
      open c1 for select * from emp1 where job='CLERK';
      loop
       fetch c1 into vemp;
        exit when c1%notfound;
        dbms_output.put_line(vemp.empno);
      end loop;
      close c1;
    end;

创建三个表分别为 empa empb empc ，内容均和emp表一样，打印empa中10部门的员工姓名和部门编号，empb中工资大于2000的员工姓名和工资，empc中职位是CLERK的员工姓名和职位 ，则具体语句如下：

create table empa as select * from emp;
    create table empb as select * from emp;
    create table empc as select * from emp;
    
    declare
    type ty1 is ref cursor return emp%rowtype;
    c1 ty1;
    v_emp emp%rowtype;
    begin
      open c1 for select * from empa where deptno=10;
      loop
        fetch c1 into  v_emp;
        exit when c1%notfound;
        dbms_output.put_line(v_emp.ename||' '||v_emp.deptno);
      end loop;
      close c1;
      open c1 for select * from empb where sal>2000;
      loop
         fetch c1 into  v_emp;
        exit when c1%notfound;
        dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
      end loop;
      close c1;
      open c1 for select * from empc where job='CLERK';
      loop
        fetch c1 into v_emp;
        exit when c1%notfound;
        dbms_output.put_line(v_emp.ename||' '||v_emp.job);
      end loop;
      close c1;
    end;

#### 2.2.2弱类型游标 ####

弱游标（weak cursor）是指游标*只在需要访问数据时才会读取数据*，因此*占用内存资源较少*，但是需要在访问数据时进行频繁的I/O操作，因此*效率较低*。而且弱类型游标也可以更换结果集，而且可以换表结构。

比如：

要求打印emp表的员工姓名和dept表的部门所在地，并打印emp表每个部门的员工人数，则语句如下：

declare
    type t1 is ref cursor;
    c1 t1;
    v1 varchar2(20);
    v2 number;
    begin
      open c1  for select ename from emp;
      loop
        fetch c1 into v1;
        exit when c1%notfound;
        dbms_output.put_line(v1);
      end loop;
      close c1;
     open c1 for select loc from dept;
     loop
       fetch c1 into v1;
        exit when c1%notfound;
        dbms_output.put_line(v1);
      end loop;
      close c1;
     open c1 for select count(1) from emp group by deptno;
     loop
       fetch c1 into v2;
       exit when c1%notfound;
        dbms_output.put_line(v2);
      end loop;
      close c1;
    end;

> **总结：**
> 
> 在使用动态游标时，需要根据具体情况选择合适的游标类型，权衡内存资源和访问效率。
> 
> **强类型游标和弱类型游标的区别：**
> 
>  *  强类型有return open时for的结果集
>  *  必须和return表结构一致
>  *  弱类型没有return open时for的结果集比较自由

#### 2.2.3普通动态游标 ####

是最常用的动态游标，语句格式：

declare
    c1 sys_refcursor; --声明一个动态游标

比如：

打印emp表的员工姓名和dept表的部门所在地，并打印emp表每个部门的员工人数，则语句如下：

declare
    c1 sys_refcursor;
    v1 varchar2(20);
    v2 number;
    begin
      open c1  for select ename from emp;
      loop
        fetch c1 into v1;
        exit when c1%notfound;
        dbms_output.put_line(v1);
      end loop;
      close c1;
     open c1 for select loc from dept;
     loop
       fetch c1 into v1;
        exit when c1%notfound;
        dbms_output.put_line(v1);
      end loop;
      close c1;
     open c1 for select count(1) from emp group by deptno;
     loop
       fetch c1 into v2;
       exit when c1%notfound;
        dbms_output.put_line(v2);
      end loop;
      close c1;
    end;

要求打印emp表名字包含A的员工姓名和人数，dept表包含A的部门名称和部门数，则语句如下：

declare
    c1 sys_refcursor;
    vname varchar2(20);
    vcount number;
    begin
      open c1 for select ename from emp where ename like'%A%';
      loop
        fetch c1 into vname;
        exit when c1%notfound;
        dbms_output.put_line(vname);
        end loop;
        close c1;
      open c1 for  select count(1) from emp where ename like'%A%';
        loop
        fetch c1 into vcount;
        exit when c1%notfound;
        dbms_output.put_line(vcount);
        end loop;
        close c1;
     open c1 for select dname from dept where dname like'%A%';
      loop
        fetch c1 into vname;
        exit when c1%notfound;
        dbms_output.put_line(vname);
        end loop;
        close c1;
      open c1 for  select count(1) from dept where dname like'%A%';
        loop
        fetch c1 into vcount;
        exit when c1%notfound;
        dbms_output.put_line(vcount);
        end loop;
        close c1;
    end;

***for循环+游标***

***Ⅰ、有游标名的***  
声明一个显式游标，不用打开 ，不用赋值 ，不用关闭，因为有游标名 所以可以用游标的属性；

比如：

打印dept表的所有信息：

declare
    cursor c1 is select * from dept;
    begin
      for i in c1 loop
        dbms_output.put_line(i.deptno||' '||i.dname
                            ||' '||i.loc);
      end loop;
    end;

***Ⅱ、没有游标名***  
不用声明 ，不用打开 ，不用赋值 ，不用关闭；不能和动态游标和带参数的显式游标一起使用 （直接for i in结果集） ；

比如：

打印dept表的所有信息：

begin
      for i in (select * from dept) loop
        dbms_output.put_line(i.deptno||' '||i.dname||
                             ' '||i.loc);
      end loop;
    end;

## 三、游标的优缺点 ##

### 优点： ###

*  可以对大量数据进行有效的遍历和操作，尤其是在数据量较大时；
 *  可以根据需要随时调整游标位置，灵活性较高；
 *  可以在一些特定的场景下提高程序的效率；
 *  使用游标可以执行多个不相关的操作；
 *  使用游标可以提高脚本的可读性；
 *  使用游标可以建立命令字符串，可以传送表名，或者把变量传送到参数中，以便建立可以执行的命令字符串；
 *  提供基于游标位置而对表中数据进行删除或更新；

### 缺点： ###

*  处理大数据量时可能会占用大量的系统资源，如内存和CPU，效率底下；
 *  可能会对数据库的性能产生一定的影响；
 *  可能会导致数据不一致的问题，如在游标遍历过程中修改了数据，但游标并不会立即反映出来；
 *  游标使用时会对行加锁，可能会影响其他业务的正常进行。

## 四、游标使用注意事项 ##

在使用游标时，需要注意以下几点：

*  1. 游标必须在使用前声明，并且声明时需要指定游标的类型和返回结果集的SELECT语句。
 *  2. 游标必须在使用前打开，并在使用完毕后关闭。
 *  3. 游标可以使用FETCH语句获取结果集中的每一行数据。
 *  4. 游标可以使用%ROWTYPE属性来定义一个与结果集结构相同的变量，以便在遍历结果集时方便地存储每一行数据。
 *  5. 游标可以使用FOR循环结构来遍历结果集，避免手动进行游标的打开、获取和关闭操作。

## 五、游标的作用 ##

游标是一种数据库技术，通过游标可以在一个结果集中逐个地访问每一条记录，然后对记录进行操作。游标可以用于以下几个方面：

5.1. 遍历数据：

游标可以逐条地遍历查询结果集，对每一条数据进行处理，比如筛选、修改、删除等操作。

假设有一个包含学生姓名、年龄、成绩等信息的数据表，需要实现一个查询功能，查询所有成绩低于60分的学生姓名和年龄。通过游标可以实现：

*  定义一个游标，用于查询数据表中的所有记录。
 *  使用FETCH语句逐条读取记录，然后对每条记录进行判断，如果成绩低于60分，则将学生姓名和年龄存储到一个临时表中。
 *  等待游标读取完毕后，从临时表中获取所有符合条件的学生姓名和年龄，然后返回给用户。

5.2. 分页查询：

通过游标可以实现分页查询，每次查询指定数量的记录，以便于提高查询效率和减少网络传输的数据量。

假设有一个包含1000条记录的数据表，需要实现一个每页显示10条记录的分页查询功能。通过游标可以实现以下步骤：

*  1. 定义一个游标，用于查询数据表的所有记录。
 *  2. 使用FETCH语句逐条读取记录，然后将记录存储到一个缓存区中。
 *  3. 在缓存区中逐条读取记录，直到读取到10条记录，然后将这10条记录返回给用户。
 *  4. 等待用户请求下一页数据，然后再次从缓存区中读取下10条记录，直到所有记录都被读取完毕。

> 通过游标实现分页查询功能可以大大提高查询效率和减少网络传输的数据量。而且，由于游标可以灵活地控制查询进度，因此可以避免一些不必要的数据库操作，从而提高系统的性能和稳定性。

5.3. 数据库事务：

游标可以用于管理数据库事务，它可以让开发人员对每一条记录进行细粒度的控制，从而确保事务的安全性和一致性。

> **注意：**
> 
> 游标可以在事务中使用，但它并不是实现事务的关键。

5.4. 数据库备份和恢复：

游标可以用于数据库备份和恢复，通过游标可以逐条读取数据库中的数据，然后将数据写入备份文件中，或者将备份文件中的数据逐条恢复到数据库中。