oracle 游标

1.       游标: 容器，存储SQL语句影响行数。

 

2.       游标类型: 隐式游标，显示游标，REF游标。其中，隐式游标和显示游标属于静态游标（运行前将游标与SQL语句关联）,REF游标属于动态游标(运行时将游标与SQL语句关联)。

 

3.       隐式游标: DML语句对应的游标，由Oracle自动管理，也称SQL游标。（所有的DML操作都被Oracle内部解析为一个cursor名为SQL的隐式游标）

 

q      隐式游标的属性有：

q      %FOUND – SQL 语句影响了一行或多行时为 TRUE

q      %NOTFOUND – SQL 语句没有影响任何行时为TRUE

q      %ROWCOUNT – SQL 语句影响的行数

q      %ISOPEN - 游标是否打开，始终为FALSE

 

 

举例说明：

 

declare

v_empno emp.empno %type:=7000;

begin

   update emp set ename='fxe' where empno=v_empno;

   if SQl%found then

      dbms_output.put_line(SQL%rowcount||'Delete Ok!');

   end if;

   if SQL%notfound then -- 一条SQL就表示是一个隐式游标

      dbms_output.put_line('雇员编号'||v_empno||'不存在');

   end if;

end;

 

4. 显示游标操作:

                  (1)声明游标（关联SQL语句）    cursor+游标名 is/as sql语句

                  (2)打开游标（执行SQL语句，填充游标）    open+游标名

                  (3)提取游标的行    fetch 游标名 into 行类型变量

                  (4)关闭游标       close+游标名

 

举例说明：

declare

cursor emp_cur is select * from emp; --声明无参显示游标 关联SQL

empRecord emp%rowtype;  --表示类型为一条记录

begin

open emp_cur; --打开游标

loop --如果不只有一条的话，可以不加loop ---endloop

    fetch emp_cur into empRecord; --取出emp_cur 这个游标所对应的记录放入empRecord变量里

    exit when emp_cur%notfound; --当没记录时，退出循环

    dbms_output.put_line(empRecord.ename); --打印ename

end loop;

close emp_cur;

end;

 

 

 

5带参数的显示游标

例:

 

 

 

declare

destination varchar2(20);

cursor emp_cur(dest varchar2) --声明带参显示游标

   is select * from emp where empno=dest; --关联到SQL

empRecord emp%rowtype;  -- 行类弄的变量empRecord
  
begin

destination:=&empno;  --弹出用户输入框

open emp_cur(destination); --打开游标

loop

fetch emp_cur into empRecord; -- 循环每一游标所对应的记录放入empRecord

exit when emp_cur%notfound;

dbms_output.put_line(empRecord.ename);

end loop;

close emp_cur;

end;

 

----输入参数：7369

6.使用显示游标更新行

q      允许使用游标删除或更新活动集中的行

q      声明游标时必须使用 SELECT … FOR UPDATE语句

 

例：

declare

   old_sal number(4);

   emp_name varchar2(20);

   cursor emp_cur is select ename,sal from emp --声明显示游标，关联SQL

                     where sal<1000

                     for update of sal;   -- 定义的游标会让数据库对涉及的行（对应的列）加锁，别的会话如果要访问该游标中的行便会进入等待状态。
                                  --如果别的会话一直不解锁，那么你的select就会一直等待下去，如果你不想等,只需在for   update后面加上nowait就可以解决这个问题了,这样你的选择会立即返回。
begin

   open emp_cur; 

   loop

     fetch emp_cur into emp_name,old_sal;

     exit when emp_cur%notfound;

     update emp set sal=1.1*old_sal  --更新数据

     where current of emp_cur;     --作用范围就只在你循环的当前行的范围中了

     dbms_output.put_line('更新成功！');

   end loop;

end;

 

7.循环游标

q     循环游标用于简化游标处理代码

q     当用户需要从游标中提取所有记录时使用

q     循环游标的语法如下：

 

    

FOR <record_index> IN <cursor_name>

LOOP

       <executable statements>

END LOOP;

 

 

例:

declare

cursor emp_cur is select empno,ename,sal from emp; --显示游标

begin


for empRecord in emp_cur  --empRecord 表示每行，emp_cur  所有行

loop

    dbms_output.put_line(empRecord.empno

    ||empRecord.ename||empRecord.sal);

end loop;

end;

 

 

8.REF游标和游标变量

q      REF 游标和游标变量用于处理运行时动态执行的 SQL 查询

q      创建游标变量需要两个步骤：

q      声明 REF 游标类型

q      声明 REF 游标类型的变量

q      用于声明 REF 游标类型的语法为：

 

       TYPE <ref_cursor_name> IS REF CURSOR

       [RETURN <return_type>];

q      打开游标变量的语法如下：

        OPEN cursor_name FOR select_statement;

 

声明强类型的REF游标

type emp_cur is ref cursor return emp%rowtype;

empRecord emp_cur;

 

声明弱类型的REF游标

type emp_cur is ref cursor;

empRecord emp_cur;

 

 

例:

DECLARE

    TYPE emp_cur IS REF CURSOR 

          RETURN emp%ROWTYPE; -- 声明强类型的REF游标（只能返回emp结构类弄的游标）

     empObj emp_cur;    --声明 REF 游标类型的变量

    empRecord emp%ROWTYPE; --行数据类开的变量

     BEGIN

    OPEN empObj FOR  --打开ref类型的游标

      SELECT * FROM emp; 

    loop

    FETCH empObj INTO empRecord; --一行一行取出

    exit when empObj%notfound;

    dbms_output.put_line(empRecord.ename);

    end loop;

    CLOSE empObj;

     END;

 

 

9.游标变量的优点和限制

 

q      游标变量的功能强大，可以简化数据处理。

q      游标变量的优点有：

q      可从不同的 SELECT 语句中提取结果集

q      可以作为过程的参数进行传递

q      可以进行赋值运算

q      使用游标变量的限制：

q      不能在程序包中声明游标变量（这儿指的不是声明游标）

q      FOR UPDATE子句不能与游标变量一起使用

q      不能使用比较运算符