该线程包含一些有关游标高级概念的有用提示/示例。
更多示例
===================
declare
er emp%rowtype;
cursor c1 is select * from emp;
begin
open c1;
loop
fetch c1 into er;
exit when c1%notfound;
if er.job='SALESMAN' then
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||er.sal*1.10);
elsif er.job='CLERK' then
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||er.sal*1.08);
else
dbms_output.put_line(er.empno||' '||er.ename||' '||er.sal||' '||'No ince req.');
end if;
end loop;
close c1;
end;
注意:-请在SCOTT SCHEMA中尝试以上代码
示例#1
--------------------
begin
--no need to open and close.
for dr in (select * from dept) loop
dbms_output.put_line(dr.deptno||' '||dr.dname||' '||dr.loc);
end loop;
end;
例子#2
----------------------
declare
--cursor is declared
cursor c1 is select * from dept;
-- a cur type variable is declared
dr c1%rowtype;
begin
-- open the cursor
open c1;
--fetch into the target variable
fetch c1 into dr;
-- check for existance of more dat in the cursor
while (c1%found=TRUE) loop
fetch c1 into dr;
dbms_output.put_line(dr.deptno||' '||dr.dname||' '||dr.loc);
--end the loop
end loop;
--close the cursor.
close c1;
end;
在光标中显示记录类型的示例程序
-------------------------------------------------- -------------------------------------------------- -------
declare
TYPE MYTYPE IS RECORD
(
MENAME VARCHAR2(10),
MMGR NUMBER(4)
);
mm mytype;
CURSOR C1 IS
select ename,mgr from emp where deptno=20 and job='CLERK';
begin
open C1;
LOOP
FETCH C1 INTO mm.mename,mm.mmgr;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mm.mename || ' '|| mm.mmgr);
END LOOP;
CLOSE C1;
END;
同时检查
PL / SQL游标-5From: https://bytes.com/topic/oracle/insights/739014-pl-sql-cursor-4-a