Oracle中游标的使用

游标可以看成是指向一个查询结果集的指针，通过游标的移动逐行提取每一行的记录可以让开发者一次访问一行结果集，在每条结果集上作操作。游标的作用如下：

（1）指定结果集中特定行的位置；

（2）基于当前的结果集位置检索一行或连续的几行；

（3）在结果集的当前位置修改行中的数据；

（4）以编程的方式访问数据库。

由于程序语言是面向记录的，一组变量一次只能存放一个变量或者一条记录，无法直接接收数据库中的查询结果集，而引入游标就解决了这个问题。

一、游标的分类

游标分为两种类型：隐式游标和显式游标。

1、隐式游标

DML操作（INSERT、UPDATE、DELETE）和单行查询操作（SELECT…INTO…）会使用隐式游标。隐式游标不需要用户自己声明，它由系统自动定义，名称为sql。

可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。但要注意，通过SQL游标名只能访问前一个DML操作或单行SELECT操作的隐式游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种：标的属性有四种，如下所示。

（1）SQL%ROWCOUNT：代表DML语句成功执行的数据行数（整型）；

（2）SQL%FOUND：值为TRUE代表插入、删除、更新或单行查询操作成功，否则表示操作失败；

（3）SQL%NOTFOUND：与SQL%FOUND属性返回值相反；

（4）SQL%ISOPEN：DML执行过程中为真，结束后为假。

更新失败的例子（没有部门号为21的雇员）：

SQL> 
begin
    update emp set comm=100 
    where deptno=21;
    if sql%found then
        dbms_output.put_line('更新成功！');
    else
        dbms_output.put_line('更新失败！');
    end if;
    if sql%isopen then
    dbms_output.put_line('成功更新了'||sql%rowcount||'条记录');
end;
 16  /
更新失败！
成功更新了0条记录

PL/SQL procedure successfully completed.

更新成功的例子：

SQL> 
begin
    update emp set comm=100 
    where deptno=20;
    if sql%found then
        dbms_output.put_line('更新成功！');
    else
        dbms_output.put_line('更新失败！');
    end if;
    dbms_output.put_line('成功更新了'||sql%rowcount||'条记录');
end;
 16  /
更新成功！
成功更新了5条记录

PL/SQL procedure successfully completed.

2、显式游标

如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。下面介绍显示游标的使用方法。

二、显示游标的使用

显示游标的使用分为4个步骤：（1）声明游标；（2）打开游标；（3）提取数据；（4）关闭游标。

1、声明游标

声明游标的语法如下：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
IS SELECT语句;

说明：

（1）必须在代码块的DECLEAR部分声明游标；

（2）参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

（3）SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

（4）声明游标时并没有执行Select 语句。

2、打开游标

打开游标的语法如下：

说明：

（1）必须在代码块的可执行部分打开游标；

（2）打开游标时，执行Select 语句，SELECT语句的查询结果就被传送到了游标工作区。

（3）打开游标后，游标指向结果集头，而不是第一条记录。

3、提取数据

打开游标之后，就可以在代码块的可执行部分，将游标工作区中的数据取到变量中。格式如下：

FETCH 游标名 INTO 变量名1[，变量名2...];
或 
FETCH 游标名 INTO 记录变量;

说明：

（1）游标打开后有一个指针指向数据区，FETCH语句执行一次返回指针所指的一行数据，要返回多行数据可以使用循环语句来实现。可以通过判断游标的%found或%notfound属性的值来控制循环。

（2）第一种格式中变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

（3）第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量。

4、关闭游标

说明：显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

三、遍历循环游标

1、使用Loop循环

使用Loop循环遍历游标之前，需要先声明并打开游标，使用完毕需要手动关闭游标。

Loop
    Fatch 游标名 InTo ....;
    Exit When 游标名%NotFound;
End Loop;

2、使用for循环

循环执行时隐式打开游标，自动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。格式如下：

For 变量名 In 游标名 Loop
      数据处理语句；
End Loop;

四、游标使用举例

1、利用游标显示10号部门所有雇员的姓名、雇佣日期、工资和奖金

（1）使用Loop循环

SQL> 
declare
    row_emp emp%rowtype;
    cursor cur_emp 
    is select * from emp
    where deptno=10;   --定义游标变量
begin
    open cur_emp;           --打开游标
    loop
        fetch cur_emp into row_emp;
        exit when cur_emp%notfound;
        dbms_output.put_line('雇员姓名：'||rpad((row_emp.ename),7,' ')||
             '，雇佣日期：'||row_emp.hiredate||'，工资：'||row_emp.sal||
             '，奖金：'||nvl(row_emp.comm,0));
    end loop;
    close cur_emp;          --关闭游标
end;
/
雇员姓名：MILLER ，雇佣日期：1982-01-23 00:00:00，工资：1300，奖金：100
雇员姓名：CLARK  ，雇佣日期：1981-06-09 00:00:00，工资：2450，奖金：0
雇员姓名：KING   ，雇佣日期：1981-11-17 00:00:00，工资：5000，奖金：0

PL/SQL procedure successfully completed.

（2）使用for循环

SQL> 
begin
    for cur_emp in (select * from emp where deptno=10) loop
        dbms_output.put_line('雇员姓名：'||rpad((cur_emp.ename),7,' ')||
             '，雇佣日期：'||cur_emp.hiredate||'，工资：'||cur_emp.sal||
             '，奖金：'||nvl(cur_emp.comm,0));
    end loop;
end;
  8  /
雇员姓名：MILLER ，雇佣日期：1982-01-23 00:00:00，工资：1300，奖金：100
雇员姓名：CLARK  ，雇佣日期：1981-06-09 00:00:00，工资：2450，奖金：0
雇员姓名：KING   ，雇佣日期：1981-11-17 00:00:00，工资：5000，奖金：0

PL/SQL procedure successfully completed.

2、显示每个部门中工资最高的前两名员工的姓名、雇佣日期、工资、奖金

SQL> 
declare 
    emp_count number default 1;
begin
    for cur_dept in (select * from dept order by deptno) loop
        emp_count:=1;
        dbms_output.put_line('部门编号：'||cur_dept.deptno||
             '，部门名称：'||cur_dept.dname);
        for cur_emp in (select ename,hiredate,sal,comm 
            from emp where deptno=cur_dept.deptno 
            order by sal desc) loop
                dbms_output.put_line('===>雇员姓名：'||
                     rpad((cur_emp.ename),7,' ')||
                     '，雇佣日期：'||cur_emp.hiredate||
                     '，工资：'||cur_emp.sal||
                     '，奖金：'||nvl(cur_emp.comm,0));
        exit when emp_count>=2;
        emp_count:=emp_count+1;
        end loop;
    end loop;
end;
 21  /
部门编号：10，部门名称：ACCOUNTING
===>雇员姓名：KING   ，雇佣日期：1981-11-17 00:00:00，工资：5000，奖金：0
===>雇员姓名：CLARK  ，雇佣日期：1981-06-09 00:00:00，工资：2450，奖金：0
部门编号：20，部门名称：RESEARCH
===>雇员姓名：SCOTT  ，雇佣日期：1987-04-19 00:00:00，工资：3000，奖金：100
===>雇员姓名：FORD   ，雇佣日期：1981-12-03 00:00:00，工资：3000，奖金：100
部门编号：30，部门名称：SALES
===>雇员姓名：BLAKE  ，雇佣日期：1981-05-01 00:00:00，工资：2850，奖金：0
===>雇员姓名：ALLEN  ，雇佣日期：1981-02-20 00:00:00，工资：1600，奖金：300
部门编号：40，部门名称：OPERATIONS
===>雇员姓名：TOMMY  ，雇佣日期：2020-02-03 10:59:11，工资：8000，奖金：1200
===>雇员姓名：MARK DO，雇佣日期：2020-02-13 10:59:11，工资：3000，奖金：0

PL/SQL procedure successfully completed.

五、带参数的游标

如果游标带有参数，则打开游标时需要指定参数，带参数的游标格式如下：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 
IS SELECT 语句;

例如：

SQL> 
declare
    row_emp emp%rowtype;
    cursor cur_emp(dept_no number) is
    select * from emp where deptno=dept_no;
begin
    open cur_emp(10);
    loop
        fetch cur_emp into row_emp;
        exit when cur_emp%notfound;
        dbms_output.put_line('雇员编号:'||row_emp.empno||
            ',  姓名：'||row_emp.ename);
    end loop;
    close cur_emp;
end;
 15  /
雇员编号:7934,  姓名：MILLER
雇员编号:7782,  姓名：CLARK
雇员编号:7839,  姓名：KING

PL/SQL procedure successfully completed.

使用for循环：

SQL> 
declare
    cursor cur_emp(dept_no number) is
    select * from emp where deptno=dept_no;
begin
    for r in cur_emp(20) loop
        dbms_output.put_line('雇员编号:'||r.empno||
            ',  姓名：'||r.ename);
    end loop;
end;
 10  /
雇员编号:7788,  姓名：SCOTT
雇员编号:7876,  姓名：ADAMS
雇员编号:7566,  姓名：JONES
雇员编号:7902,  姓名：FORD
雇员编号:7369,  姓名：SMITH

PL/SQL procedure successfully completed.

改变游标的参数：

SQL> 
declare
    cursor cur_emp(dept_no number) is
    select * from emp where deptno=dept_no;
begin
    for r in cur_emp(10) loop
        dbms_output.put_line('雇员编号:'||r.empno||
            ',  姓名：'||r.ename);
    end loop;
end;
 10  /
雇员编号:7934,  姓名：MILLER
雇员编号:7782,  姓名：CLARK
雇员编号:7839,  姓名：KING

PL/SQL procedure successfully completed.

六、动态游标的用法

对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行。

语法如下：

例如：

SQL> 
declare
    str varchar2(200);
    v_ename varchar2(10);
begin
    str:='select ename from emp where empno=7788';
    execute immediate str into v_ename;
    dbms_output.put_line('name:'||v_ename);
end;
/
name:SCOTT

PL/SQL procedure successfully completed.

使用动态游标可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。

1、定义动态游标类型

语法如下：

2、声明游标变量

语法如下：

3、打开动态游标

在代码块的执行部分打开一个动态游标。语法如下：

4、动态游标应用举例

查询姓名以某个字母开头的雇员，并把雇员编号和姓名显示出来：

SQL> 
declare    
    type cur_type is ref cursor;  --声明一个动态游标
    cur_emp cur_type;
    row_emp emp%rowtype;
    str varchar2(50);
    letter char:='M';
begin  
    str:= 'select * from emp where ename like '''||letter||'%''';  --字符串中如果包含单引号（'），用两个单引号代替
    open cur_emp for str;   --打开动态游标
    loop
        fetch cur_emp into row_emp;
            exit when cur_emp%notfound;
        dbms_output.put_line('雇员编号：'||row_emp.empno||
             '，姓名：'||row_emp.ename);
    end loop;
end;  
 17  /
雇员编号：7934，姓名：MILLER
雇员编号：7654，姓名：MARTIN
雇员编号：8101，姓名：MARK DOWN

PL/SQL procedure successfully completed.

Oracle中游标的使用Oracle中游标的使用

Oracle中游标的使用

一、游标的分类

1、隐式游标

2、显式游标

二、显示游标的使用

1、声明游标

2、打开游标

3、提取数据

4、关闭游标

三、遍历循环游标

1、使用Loop循环

2、使用for循环

四、游标使用举例

1、利用游标显示10号部门所有雇员的姓名、雇佣日期、工资和奖金

（1）使用Loop循环

（2）使用for循环

2、显示每个部门中工资最高的前两名员工的姓名、雇佣日期、工资、奖金

五、带参数的游标

六、动态游标的用法

1、定义动态游标类型

2、声明游标变量

3、打开动态游标

4、动态游标应用举例

继续阅读