[oracle]静态游标之隐式游标

隐式游标简单说明

我们在执行DML操作(增删改)和单行的select into语句时会自动使用隐式游标。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用关键字sql来进行访问，不过这个sql只能访问上一次的DML操作或单行select into操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

隐式游标的四种属性如下

sql%found

boolean类型，默认值为null。
当执行DML或select into 语句后则不再为null。如果增删改成功至少1行时或者select into能取到数据时，返回true；反之false。

sql%notfound

boolean类型，默认值为null。基本情况和found属性相同，只不过在不为null时，它的值刚好和found的值相反。

sql%rowcount

数值类型，默认值为null。rowcount表示的是受影响行数。

sql%isopen

boolean类型，默认值为false。它用来表示游标是否打开。
但是对于隐式游标而言这是一个没有什么卵用的属性，因为隐式游标在DML语句执行时打开，结束时就立即关闭，所以至始至终这个属性都是返回false。

例子演示

准备数据

新建一个部门表，只有2个字段：编号、部门名。

create table dept(
       id number(5) primary key,
       name varchar2(50) not null
);
insert into dept values(10,'市场部');
insert into dept values(20,'人事部');
insert into dept values(30,'生产技术部');
commit;

例子1.执行DML语句

以update为例，根据id修改某个部门的名称，然后看隐式游标4个属性的值。

create or replace procedure procTest1(deptId number,deptName varchar2)
as
begin
  update dept set name=deptName where id=deptId;
  if sql%rowcount is null then
    dbms_output.put_line('rowcount is null');
  else
    dbms_output.put_line('rowcount='||sql%rowcount);
  end if;
  if sql%notfound is null then
    dbms_output.put_line('notfound is null');
  else
    if sql%notfound then
      dbms_output.put_line('notfound=true');
    else
      dbms_output.put_line('notfound=false');   
    end if;
  end if;
  if sql%found is null then
    dbms_output.put_line('found is null');
  else
    if sql%found then
      dbms_output.put_line('found=true');
    else
      dbms_output.put_line('found=false');   
    end if;
  end if;
  if sql%isopen is null then
    dbms_output.put_line('isopen is null');
  else
    if sql%isopen then
      dbms_output.put_line('isopen=true');
    else
      dbms_output.put_line('isopen=false');
    end if;
  end if;
  commit;
end;

call procTest1(30,'安全部门');--修改的是原表存在的数据时

output结果如下

rowcount=1
notfound=false
found=true
isopen=false

然后update一个表里不存在的数据

call procTest1(50,'研发部');--修改的是原表不存在的数据时

output结果如下

rowcount=0
notfound=true
found=false
isopen=false

例子2.执行select into语句

使用select into语句时，如果未查找到数据，会引发no_data_found异常，所以要加上异常处理。

create or replace procedure procTest2(deptId number)
as
  deptName varchar2(50);
begin
  begin
    select name into deptName from dept where id=deptId;
    exception when no_data_found THEN
      dbms_output.put_line('no_data_found异常');
  end;
  dbms_output.put_line('deptName='||deptName);
  if sql%rowcount is null then
    dbms_output.put_line('rowcount is null');
  else
    dbms_output.put_line('rowcount='||sql%rowcount);
  end if;
  if sql%notfound is null then
    dbms_output.put_line('notfound is null');
  else
    if sql%notfound then
      dbms_output.put_line('notfound=true');
    else
      dbms_output.put_line('notfound=false');   
    end if;
  end if;
  if sql%found is null then
    dbms_output.put_line('found is null');
  else
    if sql%found then
      dbms_output.put_line('found=true');
    else
      dbms_output.put_line('found=false');   
    end if;
  end if;
  if sql%isopen is null then
    dbms_output.put_line('isopen is null');
  else
    if sql%isopen then
      dbms_output.put_line('isopen=true');
    else
      dbms_output.put_line('isopen=false');
    end if;
  end if;
end;
call procTest2(10);--查找的是表里存在的数据

output结果如下

deptName=市场部
rowcount=1
notfound=false
found=true
isopen=false

然后查找一个表里不存在的数据

call procTest2(50);--表里不存在的数据

output结果如下

select into未找到数据异常
deptName=
rowcount=0
notfound=true
found=false
isopen=false

例子3.没有执行DML和select into语句

在没有执行DML和select into语句的情况下直接查看隐式游标的4个属性的值。

begin
  if sql%rowcount is null then
    dbms_output.put_line('rowcount is null');
  else
    dbms_output.put_line('rowcount='||sql%rowcount);
  end if;
  if sql%notfound is null then
    dbms_output.put_line('notfound is null');
  else
    if sql%notfound then
      dbms_output.put_line('notfound=true');
    else
      dbms_output.put_line('notfound=false');   
    end if;
  end if;
  if sql%found is null then
    dbms_output.put_line('found is null');
  else
    if sql%found then
      dbms_output.put_line('found=true');
    else
      dbms_output.put_line('found=false');   
    end if;
  end if;
  if sql%isopen is null then
    dbms_output.put_line('isopen is null');
  else
    if sql%isopen then
      dbms_output.put_line('isopen=true');
    else
      dbms_output.put_line('isopen=false');
    end if;
  end if;
end;

output结果如下

rowcount is null
notfound is null
found is null
isopen=false

可以看到，无论是否执行DML或select into语句，sql%isopen的结果始终是false。

例子4.制定实际需求

根据id修改某个部门名称，如果该id不存在，则新增一个部门。

create or replace procedure procTest3(deptId number,deptName varchar2)
as
begin
  update dept set name=deptName where id=deptId;
  if sql%found then
    dbms_output.put_line('该ID已存在，已修改部门名称。');
  else
    insert into dept values(deptId,deptName);
    dbms_output.put_line('插入成功。');
  end if;
  commit;
end;

call procTest3(40,'研发部');
select * from dept;

output结果为“插入成功。”，而表内容如下。

再执行一次

call procTest3(40,'研发部abc');
select * from dept;

output结果为“该ID已存在，已修改部门名称。”，而表内容如下。