Oracle——游标的创建和使用

游标

• SQL语言是面向集合的，是对指定列的操作。如果要对列中的指定行进行操作，就必须使用游标。
• 当在PL/SQL块中执行查询语句(SELECT) 和数据操纵语句(DML) 时，Oracle会为其分配一个上下文区(Context Area)
• 游标是指向上下文区的指针，它为应用提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法

显式游标

游标分为显式游标和隐含游标两种

• 隐含游标用于处理SELECT INTO和DML语句
• 显式游标则用于处理S ELECT语句返回的多行数据

1. 使用显式游标

定义游标

CURSOR cursor_ name IS select statement;

打开游标

OPEN cursor name;

提取数据

FETCH cursor_name INTO variable1,variable...;

FETCH cursor_name bulk collect into collect1..;

关闭游标
CLOSE cursor name;

代码：

--创建游标
declare
cursor 游标名称 is select * from 表名;
变量名 表名%rowtype;
begin
  open 游标名;--打开游标
  loop
    fetch 游标名 into 变量名;--提取游标
    exit when 游标名%notfound;
    dbms_output.put_line(变量名.列名);
    end loop;
   close 游标名;--关闭游标
end;

显示游标属性

 

显式游标属性用于返回显式游标的执行信息

• 游标属性使用格式为: 游标名+ 属性名
• %ISOPEN

　　　　用于确定游标是否已经打开。如果游标已经打开，则返回值为TRUE; 否则返回FALSE

• %FOUND

　　　　检查是否从结果集中提取到数据。如果提取到数据，则返回值为TRUE; 否则返回FALSE

• %NOTFOUND

　　　　与%FOUND属性恰好相反，如果提取到数据，则返回值为FALSE; 否则返回TRUE

• %ROWCOUNT

　　　　返回到当前行为止已经提取到的实际行数

参数游标

参数游标是指带有参数的游标。在定义了参数游标之后，使用不同参数值多次打开游标可以生成不同的结果集。

代码：

--参数游标
declare
cursor cursor_wxn(x_empno number)--定义参数（形参）
 is 
 select * from emp where empno=x_empno;
v_name emp%rowtype;
begin
  open cursor_wxn(7369);--（实参）
  loop
    fetch cursor_wxn into v_name;
    exit when cursor_wxn%notfound;
    dbms_output.put_line(v_name.ename);
    end loop;
   close cursor_wxn;
end;

游标for循环

游标for循环是在pl/sql块中使用游标的最简单方式，它可以简化对游标的处理。当使用游标for循环时，oracle会隐含的打开游标，提取游标数据并关闭游标。

代码：

--游标for循环
declare
v_name emp%rowtype;
cursor cursor_wxn
is 
select * from emp;
begin
  for v_name in cursor_wxn loop
    dbms_output.put_line(v_name.ename);
    end loop;
end;

上面代码中无须进行取值和关闭的操作，游标for循环可以自己进行。

下面还有一个更为简单的游标for循环，参考上面的代码进行读阅：

--简单for
begin
  for v_name in (select * from emp) loop
    dbms_output.put_line(v_name.ename);
    end loop;
end;

使用游标变量

代码实例：

declare
type youbiao_bianliang is ref cursor;--变量类型
cursor_varisble youbiao_bianliang;--定义游标类型的变量
v_name emp%rowtype;--变量
begin
open cursor_varisble
for select * from emp where empno=7788;
  loop
    fetch cursor_varisble into v_name;
    exit when cursor_varisble%notfound;
    dbms_output.put_line(v_name.ename);
    end loop;
   close cursor_varisble;
end;

隐含游标

作用：用属性进行一些判断（一种判断方式）

含义：当执行一条DML语句或者SELECT...INTO语句时，都会创建一个隐含游标。

隐含游标的名称是SQL，不能对SQL游标显示执行OPEN、FETCH和CLOSE语句。

Oracle隐式地打开、提取，并总是自动地关闭SQL游标。

属性：

1. SQL%FOUND:只有DML语句影响一行或多行时，SQL%FOUND属性才返回true。
   
2. SQL%NOTDOUND：如果DNL语句没有影响行数，此属性将返回false。
3. SQL%ROWCOUNT：返回DML影响的行数，如果DML语句没有影响行则返回0.
4. SQL%ISOPEN：此属性用于判断SQL游标是否已经打开。在执行SQL语句之后，Oracle自动关闭SQL游标，所以隐含游标的SQL%ISOPEN属性始终为false。

简单示例1：

-- 隐含游标
begin
  delete from emp where empno=1;
  if sql%notfound then 
    dbms_output.put_line('找不到记录');
  else
    dbms_output.put_line('记录');
  end if;
end;

示例2:

declare
a number:=1;
begin
  delete from emp where empno=a;
  if sql%notfound then
    dbms_output.put_line('没有改变');
  else
    dbms_output.put_line('改变');  
  end if;
end;