Oracle Cursor详解与实例

2021-10-23 02:59:02

Oracle Cursor详解与实例

摘要：详细介绍oracle数据库中关于游标的定义和使用。通过实例操作来深入了解cursor的用法和用处。

一：相关概念

1、concept

When Oracle Database executes aSQL statement , it stores the result set and processing information in anunnamed private SQL area . A pointer to this unnamed area , called a cursor ,let you retrieve the rows of the result set one at a time . Cursor attributesreturn information about the state of the cursor .

2、概念：

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。Cursor类型：静态游标——分为显式（explicit）游标和隐式（implicit）游标、REF游标——动态游标、是一种引用类型、类似于指针。

二：具体类型及使用

1、implicit cursor

1） explanation：Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. You can get information about this cursor from itsattributes, but you cannot control it. After the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.

2） implicit cursor（隐式游标）由系统自动打开和关闭、当我们执行一个SQL DML时、系统会自动打开一个cursor、当执行完毕之后系统会关闭cursor、我们不能直接控制cursor、但是却可以通过implicit Cursor的属性来了解操作的状态和结果、从而达到流程的控制——Cursor的属性包括：

i、SQL%ROWCOUNT整形——代表DML语句成功执行的行数

ii、SQL%FOUND布尔型——值为true时代表插入、删除、更新或查询操作成功

iii、SQL%NOTFOUND布尔型——与上面相反

v、SQL%ISOPEN布尔型——DML执行过程中为真、否则为假

3）示例：

begin
  update student set sname=‘chy‘ WHERE sno=‘1‘;
  if sql%isopen then
     dbms_output.put_line(‘cursor is opening !‘);
  else
     dbms_output.put_line(‘cursor is closed !‘);
  end if;
  if sql%found then
     dbms_output.put_line(‘DML is successed !‘);
  else
     dbms_output.put_line(‘DML is failed !‘);
  end if;
  if sql%notfound then
     dbms_output.put_line(‘DML is failed !‘);
  else
     dbms_output.put_line(‘DML is successed !‘);
  end if;
      dbms_output.put_line(sql%rowcount||‘ is the number of result !‘);
  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;

2、explicit cursor

1）explanation：PL/SQLalso lets you declare explicit cursors. An explicit cursor has a name and isassociated with a query (SQLSELECT statement)—usually one that returns multiplerows. After declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement), and close the cursor (with the CLOSE statement). After closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.

很直白的说明了显示游标的用处、以及用法。

2）explicit cursor的属性包含：

游标的属性返回值类型意义

%ROWCOUNT 整型获得FETCH语句返回的数据行数

%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND 布尔型与%FOUND属性返回值相反

%ISOPEN 布尔型游标已经打开时值为真，否则为假

3）对于explicit Cursor使用分四个步骤：

a 定义游标——Cursor [ Cursor Name[param_name, param_type]] IS select xxx from xxxwhere xxx;

b 打开游标——Open [ Cursor Name[varialbe_value] ] ;

c 操作游标——Fetch [ Cursor Name ];

d 关闭游标——Close [ Cursor Name ] ;

4）具体使用显示游标,遍历循环游标步骤：

a）使用显示游标

i、声明游标：划分存储区域，注意此时并没有执行Select语句。CURSOR游标名(参数列表) [返回值类型] IS Select 语句；

ii、打开游标：执行Select语句，获得结果集存储到游标中，此时游标指向结果集头，而不是第一条记录。open游标名(参数列表);

iii、获取记录：移动游标取一条记录 fetch 游标名 into 临时记录或属性类型变量；

v、关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。

close 游标名；

b）遍历循环游标

i、for循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

……

for 变量名 In 游标名

loop

数据处理语句；

end loop；

ii、loop循环游标

……

loop

fetch 游标名 into 临时记录或属性类型变量；

exit when 游标名%notfound；

end loop；

iii、while循环

……

open 游标名

fetch 游标名into临时记录或属性类型变量；

while 游标名%foundloop

-- do something

fetch 游标名into临时记录或属性类型变量；

end loop;

……

close 游标名

5）常见显式Cursor用法：

i、使用for循环来使用cursor：

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  for userinfo in cur loop
    exit when cur%notfound;
    dbms_output.put_line(‘user id : ‘ || userinfo.id || ‘-‘ || ‘user name : ‘ || userinfo.username);
  end loop;
  exception 
    when others then
      dbms_output.put_line(sqlerrm);
end;

ii、使用fetch来使用cursor： exp2

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  open cur;
  loop
     exit when cur%notfound;
     fetch cur into userinfo;
     dbms_output.put_line(‘user id : ‘ || userinfo.id || ‘-‘ || ‘user name : ‘ || userinfo.username);
  end loop;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

iii、使用fetch结合while使用cursor：exp3

declare
  cursor cur is select * from t_user where age = 23;
  userinfo t_user%rowtype;
begin
  open cur;
  fetch cur into userinfo;
  if cur%isopen then
    while cur%found loop
          dbms_output.put_line(‘user id : ‘ || userinfo.id || ‘-‘ || ‘user name : ‘ || userinfo.username);
          fetch cur into userinfo;
    end loop;
    dbms_output.put_line(‘totle result : ‘ || cur%rowcount);
  else
    dbms_output.put_line(‘cursor is closed!‘);
  end if;  
  close cur;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

v、使用cursor实现数据的修改（带参数的cursor）、下面三种作用是一样的、只是内部实现有点区别

-- 给工作为CLERK的员工加薪

--one
declare
   cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
   ef emp1%rowtype;
   c_sal emp1.sal%type;
begin
  for ef in cur(‘CLERK‘) LOOP
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    UPDATE EMP1 SET EMP1.SAL=C_SAL WHERE CURRENT OF CUR;
  END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
     dbms_output.put_line(sqlerrm);
END;
--two
declare
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  EF emp1%rowtype;
  C_SAL emp1.sal%type;
begin
  open cur(‘CLERK‘);
  fetch cur into EF;
  while cur%found loop 
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    update emp1 set emp1.sal=C_SAL where current of cur;
    fetch cur into EF;
  end loop;
  close cur;
end;  
--three
declare
  --define the cursor Note: the select sql is not excuted!
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  ef emp1%rowtype;
  c_sal emp1.sal%type;
begin
  open cur(‘CLERK‘);
  fetch cur into ef;
  
  while cur%found loop
    exit when cur%notfound;
    case
      when ef.sal < 1000 
      then c_sal := ef.sal*1.2;
      when ef.sal < 2000 
      then c_sal := ef.sal*1.4;
      when ef.sal < 3000 
      then c_sal := ef.sal*1.6;
    end case;
    update emp1 set emp1.sal = c_sal where current of cur;
    fetch cur into ef;
  end loop;
  close cur;
end;

vi、使用cursor实现数据的删除：

--use cursor to delect date
create table emp3 as select * from emp;

--delete the date of emp3 where the job is ‘CLERK‘;
declare
  cursor cur(c_job varchar2) is select * from emp3 where emp3.job=c_job for update;
  ef emp3%rowtype;
begin
  for ef in cur(‘CLERK‘) loop
    exit when cur%notfound;
    delete from emp3 where current of cur;
  end loop;
end;

补充：

ref cursor会在下一个笔记中出现、这里补充一个循环时使用的判断条件if的东西。

注意看下面两段代码：

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSE IF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSE IF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

当我们使用多个if条件的时候写成后则就会出错、必须要写成前面的elsif来结合if多条件的情况！

Oracle Cursor详解与实例,布布扣,bubuko.com

Oracle Cursor详解与实例

码农公寓

Oracle Cursor详解与实例

一：相关概念

1、concept

2、概念：

二：具体类型及使用

1、implicit cursor

2、explicit cursor

补充：

相关文章