oracle游标——相关知识整理

时间:2015-04-22 19:33 来源:linux.it.net.cn 作者:IT

在写存储过程或者是函数的时候，基本上都会用到游标，最近在写买票佣金计算的存储过程的时候，就用到了游标。趁这个机会整理下oracle游标的知识，以备以后使用。
游标的定义

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。
隐式游标
DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT ... INTO ...。
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
相关属性
◦SQL%ROWCOUNT    整型代表DML语句成功执行的数据行数
◦SQL%FOUND   布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
◦SQL%NOTFOUND    布尔型与SQL%FOUND属性返回值相反
◦SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
例子：
Source code

begin
         UPDATE emp SET ENAME='ALEARK' WHERE EMPNO=7469;
         IF sql%isopen then
           dbms_output.put_line('Openging');
           else
             dbms_output.put_line('closing');
             end IF;
          IF sql%found then
            dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行
            else
              dbms_output.put_line('Sorry');
              end IF;
              IF sql%notfound then
                dbms_output.put_line('Also Sorry');
                else
                  dbms_output.put_line('Haha');
                  end IF;
                   dbms_output.put_line(sql%rowcount);
                   exception
                     when no_data_found then
                       dbms_output.put_line('Sorry No data');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;显示游标
属性
◦%ROWCOUNT   整型获得FETCH语句返回的数据行数
◦%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假
◦%NOTFOUND   布尔型与%FOUND属性返回值相反
◦%ISOPEN 布尔型游标已经打开时值为真，否则为假
例子
Source code

declare
       --游标声明
       cursor csr_TestWhile
       IS
       --select语句
       SELECT LOC
       FROM Depth;
       --指定行指针
       row_loc csr_TestWhile%rowtype;
begin
--打开游标
       open csr_TestWhile;
       --给第一行喂数据
       fetch csr_TestWhile INTO row_loc;
       --测试是否有数据，并执行循环
         while csr_TestWhile%found loop
           dbms_output.put_line('部门地点：'||row_loc.LOC);
           --给下一行喂数据
           fetch csr_TestWhile INTO row_loc;
         end loop;
       close csr_TestWhile;
end;
SELECT * FROM emp动态游标
例子：
Source code

declare
type cur_type IS ref cursor;
cur cur_type;
rec scott.emp%rowtype;
str varchar2(50);
letter char:= 'A';
begin
        loop
         str:= 'select ename from emp where ename like ''%'||letter||'%''';
         open cur FOR str;
         dbms_output.put_line('包含字母'||letter||'的名字：');
          loop
         fetch cur INTO rec.ename;
         exit when cur%notfound;
        dbms_output.put_line(rec.ename);
end loop;
exit when letter='Z';
letter:=chr(ascii(letter)+1);
end loop;
end;动态select查询
例子：
Source code

SET SERVEROUTPUT ON
        DECLARE
        str varchar2(100);
        v_ename varchar2(10);
        begin
        str:='select ename from scott.emp where empno=7788';
        execute immediate str INTO v_ename;
        dbms_output.put_line(v_ename);
        END; (责任编辑：IT)