oracle 游标总结

 游标
环境区域是用来处理SQL语句的一个oracle存储区域。游标是指向它的指针或句柄。通过游标，PL/SQL程序可以控制这个环境区域中被处理的语句。
Oracle中的游标有两种：显式游标、隐式游标。
显示游标是用cursor...is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理，而隐式游标是在执行插入(insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。

1、显式游标操作
显式游标在块定义部分、包或子程序中声明。当声明了显式游标后，可以通过以下三条命令控制显式游标的操作：打开游标、推进游标、关闭游标。
(1)声明显式游标
--例1

Java代码 大的大的
declare   
  v_auths auths%rowtype;   
  v_code auths.author_code%type;   
  cursor c_auths is    
    select * from auths where author_code=v_code;  

declare 
  v_auths auths%rowtype; 
  v_code auths.author_code%type; 
  cursor c_auths is 
    select * from auths where author_code=v_code;

 

上例是将PL/SQL变量绑定在WHERE子句中，下面将游标参数绑定在游标的WHERE子句中：

 

(2)打开显式游标
游标操作的第一步是打开游标。
例1，下面的语句是打开上节例1中声明的显式游标c_auths; 
  --在打开游标前为绑定变量赋值。   

 

begin
  --在打开游标前为绑定变量赋值。
  v_code:='A00001';
  --打开游标。
  open c_auths;

 

打开一个已打开的游标也是合法的。当第二次打开游标时，PL/SQL先自动关闭游标，然后再打开。一次打开多个游标也是PL/SQL所允许的

 

(3)推进显式游标
      当打开显式游标后，就可以使用FETCH语句来推进游标，返回查询结果集中的一行。每执行完一条FETCH语句后，显式游标会自动指向查询结果集的下一行。

 3.1 fetch方法

begin   
  --打开游标，并初始化结果集   
  open c_salary;   
  loop   
    --推进游标，将游标的查询结果集中的一行存到变量v_salary中。   
    fetch c_salary into v_salary,v_code;   
    --当结果集中没有行时退出循环。   
    exit when c_salary%notfound;   
     end loop;   
  --关闭游标，释放游标占用资源。   
  close c_salary;   

 3.2 for方法 ，可以自动控制游标的打开、推进和关闭

      

delcare   
  cursor c_salary is   
    select salary form auths where author_code<='A00006';   
begin   

   -- IF  c_salary %ISOPEN = FALSE THEN
     --    OPEN c_salary ; end if;   
    ---ORA-06511: PL/SQL: 游标已经打开
      用FOR时不能添加，否则报错显示游标已经打开,open,close 都不能添加

  --开始游标FOR循环，隐含地打开c_salary游标。   
  for v_salary in c_salary loop   
    --一个隐含的fetch语句在这里被执行。   
　  --在循环继续前，一个隐含的c_auths%notfound被检测。   
　end loop;   
  --现在循环已经结束，c_auths游标的一个隐含的close操作被执行。   
  commit;   
end;  

 

(4)关闭显式游标
当整个结果集都检索完以后，应当关闭游标。关闭游标用来通知PL/SQL游标操作已经结束，并且释放游标所占用的资源(结果集所使用的资源空间)。

 

2.显示游标属性

      游标有四个属性：%found、%notfound、%isopen和%rowcount。要注意这些属性只能使用在过程性语句中，而不能使用在SQL语句中。

 

3、隐式游标处理
PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它。SQL游标用来处理insert、update、delete以及返回一行的select...into语句。
       一个SQL游标不管是打开还是关闭，open、fetch和close命令都不能操作它。SQL游标与显式游标类似，也有四个一样的属性。当打开SQL游标之前，SQL游标的属性都为NULL。
     隐式游标只使用 SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性.SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。

        

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：

　　. TRUE :INSERT

　　. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.

　　. TRUE :SELECT INTO至少返回一行

　　当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。

　　SQL%ROWCOUNT

　　在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO_DATA_FOUND.

　　SQL%ISOPEN

　　SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

 

 5、游标变量

到目前为止前面所有显式游标的例子都是静态游标－即游标与一个SQL语句关联，并且该SQL语句在编译时已经确定。
而游标变量是一个引用类型(REF)的变量。

(1)游标变量的声明
Java代码
declare  
  --使用%rowtype定义一个游标变量类型。  
  type t_authsref is ref cursor return auths%rowtype;  
  --定义一个记录类型。  
  type t_coderecord is record(  
    author_code article.author_code%type,  
    article_code article.article_code%type);  
  --声明一个记录类型的变量。  
  v_code t_coderecord;  
  --使用t_coderecord作为一个游标变量类型的结果集类型。  
  type t_coderef is ref cursor return t_codeRecord;  
  --使用v_code作为一个游标变量类型的结果集类型。  
  type t_coderef2 is ref cursor return v_code%type;  
  --使用上面的类型声明的两个游标变量。  
  v_authcv t_authsref;  
  v_codecv t_coderef; 

declare
  --使用%rowtype定义一个游标变量类型。
  type t_authsref is ref cursor return auths%rowtype;
  --定义一个记录类型。
  type t_coderecord is record(
    author_code article.author_code%type,
    article_code article.article_code%type);
  --声明一个记录类型的变量。
  v_code t_coderecord;
  --使用t_coderecord作为一个游标变量类型的结果集类型。
  type t_coderef is ref cursor return t_codeRecord;
  --使用v_code作为一个游标变量类型的结果集类型。
  type t_coderef2 is ref cursor return v_code%type;
  --使用上面的类型声明的两个游标变量。
  v_authcv t_authsref;
  v_codecv t_coderef;PL/SQL2.8以上版本中，可以使用一个没有指定结果集类型的游标变量来指定多个不同类型的查询。
type t_authsref if ref cursor;
v_cursorvar t_authsref;--声明一个该类型的变量。
(2)打开游标变量
为了将一个游标变更与一个具体的select语句联系起来，open的语法中增加了一个select语句。
Java代码
open cursor_variable for select_statement;  
declare  
  type t_authorsref is ref cursor return auths%rowtype;  
  v_authscv t_authorsref;  
  --然后打开  
  open v_authscv for select * from auths; 

open cursor_variable for select_statement;
declare
  type t_authorsref is ref cursor return auths%rowtype;
  v_authscv t_authorsref;
  --然后打开
  open v_authscv for select * from auths;(3)推进游标变更
(4)关闭游标变更
该操作用来释放查询所占用的资源。但没有释放游标变量占用的存储空间。当变量超出作用域时，它所占用的空间才被释放掉。
下面的块中定义了一个没有指定结果集的游标变量，这样我们就可以使用这个游标变量指向不同的查询，并能够返回不同的记录类型：
Java代码
set serveroutput on size 100000 --设置存储缓冲区大小。  
declare  
  /*定义游标变更类型t_curref，该游标变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PL/SQL记录类型。*/ 
  type t_curref is ref cursor;  
  --声明一个游标变量类型的变量  
  c_cursorref t_curref;  
  --定义PL/SQL记录类型t_authorrec，该类型的变量用来接收游标变量的返回值。  
  type t_authorrec is record(  
    authorcode auths.author_code%type,  
    name auths.name%type);  
  --定义PL/SQL记录类型t_articlerec,该类型的变量也用来接收游标变量的返回值。  
  type t_articlerec is record(  
    authorcode article.author_code%type,  
    title artitle.title%type);  
  --声明两个记录类型变量。  
  v_author t_authorrec;  
  v_article t_articlerec;  
begin  
  --打开游标变量c_cursorref，返回t_authorrec类型的记录。  
  open c_cursorref for   
    select author_code,name from auths where author_code in('A00001','A00002','A00003','A00004','A00005');  
  --推进游标变量  
  fetch c_cursorref into v_author;  
  --游标变量的推进循环。  
  while c_cursorref%found loop  
    --将作家代码和相应的作家名字输出到屏幕上。  
    dbms_output.put(v_author.authorcode||':'||v_author.name||' ');  
    fetch c_cursorref into v_author;  
  end loop;  
  dbms_output.new_line;--向屏幕上输出一个回车行。  
  --关闭游标变量，仅仅将游标变量指定的资源释放掉，游标变量本身的存储空间没有释放掉。  
  close c_cursorref;  
  --再次打开游标变量，返回t_articlerec类型的记录。  
  open c_cursorref for   
    select author_code,title from article  
    where author_code in('A00001','A00002','A00003','A00004','A00005');  
  fetch c_cursorref into v_article;  
  while c_cursorref%found loop  
    ...  
  end loop;  
  close c_cursorref;  
end; 

set serveroutput on size 100000 --设置存储缓冲区大小。
declare
  /*定义游标变更类型t_curref，该游标变量类型没有指定结果集类型，所以该游标变量类型的变量可以返回不同的PL/SQL记录类型。*/
  type t_curref is ref cursor;
  --声明一个游标变量类型的变量
  c_cursorref t_curref;
  --定义PL/SQL记录类型t_authorrec，该类型的变量用来接收游标变量的返回值。
  type t_authorrec is record(
    authorcode auths.author_code%type,
    name auths.name%type);
  --定义PL/SQL记录类型t_articlerec,该类型的变量也用来接收游标变量的返回值。
  type t_articlerec is record(
    authorcode article.author_code%type,
    title artitle.title%type);
  --声明两个记录类型变量。
  v_author t_authorrec;
  v_article t_articlerec;
begin
  --打开游标变量c_cursorref，返回t_authorrec类型的记录。
  open c_cursorref for
    select author_code,name from auths where author_code in('A00001','A00002','A00003','A00004','A00005');
  --推进游标变量
  fetch c_cursorref into v_author;
  --游标变量的推进循环。
  while c_cursorref%found loop
    --将作家代码和相应的作家名字输出到屏幕上。
    dbms_output.put(v_author.authorcode||':'||v_author.name||' ');
    fetch c_cursorref into v_author;
  end loop;
  dbms_output.new_line;--向屏幕上输出一个回车行。
  --关闭游标变量，仅仅将游标变量指定的资源释放掉，游标变量本身的存储空间没有释放掉。
  close c_cursorref;
  --再次打开游标变量，返回t_articlerec类型的记录。
  open c_cursorref for
    select author_code,title from article
    where author_code in('A00001','A00002','A00003','A00004','A00005');
  fetch c_cursorref into v_article;
  while c_cursorref%found loop
    ...
  end loop;
  close c_cursorref;
end;注意，在上例中，第一次关闭游标变量是可省略的，因为在第二次打开游标变量时，就将第一次的查询丢失掉了。而且游标变量也有游标属性，通常在推进游标变量时使用这些游标属性，例如上例使用了%found属性。

 

6.显示游标参数

--打开游标时将参数传入。
  open c_auths('A00001');

set serveroutput on; 
declare 
  var_name varchar(2); 
  cursor mysor(myid number) is select username from userinfo where userid=myid; 
  begin 
    loop 
       open mysor(6);//给游标传参数 
       fatch mysor into var_name; 
       exit when mysor%notfound; 
       dbms_output.put.line(var_name); 
    end loop; 
    close mysor; 
  end; 

 7.多层游标嵌套

Oracle 多层游标嵌套：一般的多层嵌套可以用几个表联合起来查询就能替代,但有时却不能代替,比如,第二个要查询的值是第一个查出值后再进行like运算



declare
  v_0 number;
  v_1 number;

  cursor c1 is
    select productordernumber
      from his_productorder@pro_crm
     where productid in (9000045516);

  cursor c2 is
    select cust_order_id
      from suf_cust_order_q_his
     where cust_order_num like v_0 || '%';

  cursor c3 is
    select * from suf_work_order_q_his where cust_order_id = v_1;

begin

  for i in c1 loop
    v_0 := i.productordernumber;
  
    for j in c2 loop
      v_1 := j.cust_order_id;
      for k in c3 loop
        dbms_output.put_line(k.work_order_id || ' ' || k.status);
      end loop;
    end loop;
  end loop;
  ;