oracle 游标遍历%rowtype中的记录

本是古典何须时尚 2022-04-17 04:17 228阅读 0赞

那么我们使用Oracle游标  
  
游标分为：静态游标和引用游标（动态游标）  
  
静态游标：由用户定义（隐式游标、显示游标）结果集不变  
  
引用游标游标：结果集变化  
  
隐式游标：用DML操作时，自动使用隐式游标。我们可以使用隐式游标判断SQL语句执行结果  
  
自动声明和处理的。在Session会话区，开启游标。处理后自动关闭。可以返回单行查询。  
  
隐式游标使用：  
  
declare  
  
%NOTFOUND －－ 执行行没有找到。  
  
%FOUND －－执行行找到  
  
%ROWCOUNT －－游标影响行数  
  
%ISOPEN －－ 当前游标是否打开  
  
我们现在通过游标来看看上篇文章的例子  
通过循环来遍历数据：  
1、loop when循环  
declare  
cursor myCur is select \* from hr.jobs;  
oneRow hr.jobs%rowtype;  
begin  
open myCur;  
loop  
fetch myCur into oneRow;  
dbms\_output.put\_line(oneRow.job\_id ||' ' ||onerow.job\_title);  
exit when myCur%notFound;  
end loop;  
close myCur;  
end;  
  
2、while 循环  
declare  
cursor myCur is select \* from hr.jobs;  
oneRow hr.jobs%rowtype;  
begin  
open myCur;  
fetch myCur into oneRow;  
while (myCur%found)  
loop  
dbms\_output.put\_line(oneRow.job\_id ||' ' ||onerow.job\_title);  
fetch myCur into oneRow;  
end loop;  
close myCur;  
end;  
  
3、for 循环  
declare  
cursor myCur is select \* from hr.jobs;  
oneRow hr.jobs%rowtype;  
begin  
for oneRow in myCur loop  
dbms\_output.put\_line(oneRow.job\_id ||' ' ||onerow.job\_title);  
end loop;  
end;  
  
结果如下：  
AD\_PRES President  
AD\_VP Administration Vice President  
AD\_ASST Administration Assistant  
FI\_MGR Finance Manager  
FI\_ACCOUNT Accountant  
AC\_MGR Accounting Manager  
AC\_ACCOUNT Public Accountant  
SA\_MAN Sales Manager  
SA\_REP Sales Representative  
PU\_MAN Purchasing Manager  
PU\_CLERK Purchasing Clerk  
ST\_MAN Stock Manager  
ST\_CLERK Stock Clerk  
SH\_CLERK Shipping Clerk  
IT\_PROG Programmer  
MK\_MAN Marketing Manager  
MK\_REP Marketing Representative  
HR\_REP Human Resources Representative  
PR\_REP Public Relations Representative