mysql 隐式游标 使用隐式游标查询

隐式游标简单说明

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

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

隐式游标的四种属性如下：

属性	值	SELECT	INSERT	UPDATE	DELETE
SQL%ISOPEN		FALSE	FALSE	FALSE	FALSE
SQL%FOUND	TRUE	有结果		成功	成功
	FALSE	没结果		失败	失败
SQL%NOTFOUND	TRUE	没结果		失败	失败
	FALSE	有结果		成功	成功
SQL%ROWCOUNT		返回行数	插入的行数	修改的行数	删除的行数

 

例子演示

准备数据

新建一个部门表，只有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已存在，已修改部门名称。”，而表内容如下。