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);