Oracle PL/SQL 关于游标的使用

环境说明

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 使用游标:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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使用游标:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    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使用游标:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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使用游标:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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表中的员工名称以及薪水:
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
37
38
39
40

-- 开启输出
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.