当前位置:数据库 > Oracle >>

ORACLE游标遍历

ORACLE游标遍历
 
-- Created on 2011-10-9 by HUCHANGKUN 
declare 
  -- Local variables here 
  CURSOR C_EVENT is  select t.eventname from T_Event t; 
  temp T_Event.EVENTNAME%TYPE; 
begin 
  -- for 遍历游标 
  for temp in C_EVENT 
  Loop 
      dbms_output.put_line('事件名称:'|| temp.EVENTNAME); 
  End Loop; 
  
  dbms_output.put_line('------------------分割线--------------'); 
  
  --while 遍历游标 
  OPEN C_EVENT;--打开游标 
  FETCH C_EVENT into temp; --取值 
  while C_EVENT%found  
  Loop 
    dbms_output.put_line('事件名称'||temp); --打印 
    FETCH C_EVENT into temp; --取值 
  End Loop; 
  CLOSE C_EVENT; 
  
  dbms_output.put_line('------------------分割线--------------'); 
  --loog 遍历游标 
  OPEN C_EVENT;--打开游标 
  Loop 
      FETCH C_EVENT into temp; --取值 
      Exit when C_EVENT%NOTFOUND; 
      dbms_output.put_line('事件名称'||temp); --打印 
  End Loop; 
  CLOSE C_EVENT; 
end; 

注意:必须先fetch,再用游标的notfound来判断 

---带入参的游标,注意只指定入参类型,不指定入参长度,且只有入参,无出参 

-- Created on 2011-10-9 by HUCHANGKUN 
declare 
  -- Local variables here 
  CURSOR C_EVENT(ROW_NUM NUMBER DEFAULT 3) is  (select t.eventname from T_Event t where rownum <ROW_NUM); 
  temp T_Event.EVENTNAME%TYPE; 
begin 
  -- for 遍历游标 
  for temp in C_EVENT(ROW_NUM=>2) 
  Loop 
      dbms_output.put_line('事件名称:'|| temp.EVENTNAME); 
      dbms_output.put_line('index: '||C_EVENT%ROWCOUNT); 
  End Loop; 
  
  dbms_output.put_line('------------------分割线--------------'); 
  
  --while 遍历游标 
  OPEN C_EVENT(ROW_NUM=>3);--打开游标 
  FETCH C_EVENT into temp; --取值 
  while C_EVENT%found  
  Loop 
    dbms_output.put_line('事件名称:'||temp); --打印 
    dbms_output.put_line('index: '||C_EVENT%ROWCOUNT); 
    FETCH C_EVENT into temp; --取值 
  End Loop; 
  CLOSE C_EVENT; 
  
  dbms_output.put_line('------------------分割线--------------'); 
  --loog 遍历游标 
  OPEN C_EVENT(ROW_NUM=>4);--打开游标 
  Loop 
      FETCH C_EVENT into temp; --取值 
      Exit when C_EVENT%NOTFOUND; 
      dbms_output.put_line('事件名称:'||temp); --打印 
      dbms_output.put_line('index: '||C_EVENT%ROWCOUNT); 
  End Loop; 
  CLOSE C_EVENT; 
end; 


注意捕获异常: 
dbms_output.put_line(sqlcode||' '||sqlerrm); 
sqlcode是错误代码,sqlerrm是错误信息,不是sql语句本身,我要的是发生错误的sql语句 


exception 
  when others then 
  dbms_output.put_line(sqlcode||' '||sqlerrm); 

 


CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,