环境说明

VirtualBox6.0、Vagrant2.2.3、CentOS7.6 、Dodkcer 18.09.7 、Oracle11G、

Docker Image：docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

PS：数据库运行VirtualBox虚拟机中的CentOS系统上的Docker容器中，使用Scott用户

游标简介

游标是Oracle执行SQL与的内存区域。在数据库编程中，游标是内部数据结构，能够处理SQL查询结果。

游标是相对于上下文区域的句柄或者指针。借助于游标PL/SQL程序可以控制上下文区域,以及当语句执行时所发生的事情。

游标有两种类型，分别为隐式游标和显式游标:

  • 隐式游标:每当SQL语句执行时，Oracle会自动声明一个隐式游标。用户不会感觉到这一点，并且不能控制或者处理隐式游标的信息。
  • 显式游标:对于返回多行数据的任何查询，都可以为之定义显式游标，定义好的游标可以处理返回的每行数据。

游标属性

在处理游标时，可能需要确定游标的状态。 以下是可以使用的游标属性列表。

属性 返回值 释义
%ISOPEN Boolean 游标处于打开状态返回TRUE
%FOUND Boolean 执行成功，则返回TRUE;、没有执行被返回，则返回FALSE
%NOTFOUND Boolean 执行了成功的FETCH，则返回FALSE、没有执行被返回，则返回TRUE
%ROWCOUNT Number 返回从游标中所检索的记录数

PS: 如果声明了游标，但不打开，则返回INVALID_CURSOR，或者游标已关闭。

隐式游标

隐式游标会自动的与每个DML语句(UPDATE、DELETE、INSERT)建立关联，所有的UPDATE、DELETE语句都有标识被当前操作所影响数据行集合的游标。
隐式游标被用户处理INSERT、UPDATE、DELETE和SELETE INTO语句，在处理隐式游标的过程中，Oracle会自动执行OPEN、FETCH和CLOSE的操作。

  • INSERT 使用游标:

    set serveroutput on;
begin
    INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (7986, 'Nathan', 'SYSDBA', 7698, 
            TO_DATE('2019-09-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
            18621.00, null, 20);
    DBMS_OUTPUT.PUT_LINE('INSERT:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行');
end;

--执行结果:
[2019-09-04 11:38:57] completed in 24 ms
[2019-09-04 11:38:57] INSERT:EMP=>受影响的行数是1行

  • SELECT INTO使用游标：

    set serveroutput on;
declare
    ename varchar2(20);
    sal number;
begin
    select emp.ename, emp.sal 
    into ename , sal  
    from emp where emp.empno = 7986;
    DBMS_OUTPUT.PUT_LINE('SELECT:EMP=>'||ename||'的薪水是'||sal);
exception
    when NO_DATA_FOUND then
    DBMS_OUTPUT.PUT_LINE('SELECT:EMP=>没有找到数据！');
end;

-- 执行结果:
[2019-09-04 11:39:54] completed in 6 ms
[2019-09-04 11:39:54] SELECT:EMP=>Nathan的薪水是18621

  • UPDATE使用游标：

    set serveroutput on;
begin
    update emp set emp.sal = 8000 where emp.empno = 7986;
    DBMS_OUTPUT.PUT_LINE('UPDATE:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行');
end;

-- 执行结果:
[2019-09-04 11:40:28] completed in 168 ms
[2019-09-04 11:40:28] UPDATE:EMP=>受影响的行数是1行

  • DELETE使用游标：

    set serveroutput on;
begin
    delete from emp where emp.empno = 7986;
    DBMS_OUTPUT.PUT_LINE('DELETE:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行');
end;

--执行结果:
[2019-09-04 11:41:48] completed in 10 ms
[2019-09-04 11:41:48] DELETE:EMP=>受影响的行数是1行

显式游标

相较于隐式游标，显式游标的好处是可以对程序进行更多的编程控制，隐式游标的效率没有显式游标的效率高，更难以捕获异常。显式游标的使用分为以下四步:

  1. 声明游标：在内存中建立游标的初始化环境
  2. 打开游标：打开已经声明好的游标，分配内存
  3. 检索游标：通过已经声明和打开的游标来检索数据
  4. 关闭游标：关闭，释放内存。
  • 使用游标查询emp表中的员工名称以及薪水:

-- 开启输出
set serveroutput on;

-- 游标的
declare
    -- 声明游标
    cursor cur is select emp.ENAME,emp.SAL from emp;
    ename emp.ename%type;
    sal emp.sal%type;
begin
    -- 打开游标
    open cur;
        loop
        	-- 检索数据
            fetch cur into ename,sal;
            -- 当游标中没有数据的时候退出
            exit when cur%notfound;
            DBMS_OUTPUT.PUT_LINE(ename|| '的薪水是'|| sal);
        end loop;
    -- 关闭游标
    close cur;
end;

-- 执行结果:
[2019-09-04 09:01:55] completed in 5 ms
[2019-09-04 09:01:55] SMITH的薪水是800
[2019-09-04 09:01:55] ALLEN的薪水是1600
[2019-09-04 09:01:55] WARD的薪水是1250
[2019-09-04 09:01:55] JONES的薪水是2975
[2019-09-04 09:01:55] MARTIN的薪水是1250
[2019-09-04 09:01:55] BLAKE的薪水是2850
[2019-09-04 09:01:55] CLARK的薪水是2450
[2019-09-04 09:01:55] SCOTT的薪水是3000
[2019-09-04 09:01:55] KING的薪水是5000
[2019-09-04 09:01:55] TURNER的薪水是1500
[2019-09-04 09:01:55] ADAMS的薪水是1100
[2019-09-04 09:01:55] JAMES的薪水是950
[2019-09-04 09:01:55] FORD的薪水是3000
[2019-09-04 09:01:55] MILLER的薪水是1300

Done.