DECLARE
CURSOR C1 is
select empno, ename from emp;
cursor c2 is
select * from dept where deptno = 10;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno dept.deptno%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
BEGIN
if not c1%isopen then
open c1;
end if;
loop
fetch c1
into v_empno, v_ename;
exit when c1%notfound;
dbms_output.put_line('Employee id is:' || v_empno);
dbms_output.put_line('Employee Name is:' || v_ename);
end loop;
close c1;
open c2;
loop
fetch c2
into v_deptno, v_dname, v_loc;
exit when c2%notfound;
dbms_output.put_line('Dept No is:' || v_deptno);
dbms_output.put_line('Dept Name is:' || v_dname);
dbms_output.put_line('Dept Loc is:' || v_loc);
end loop;
close c2;
END;
/
DECLARE
CURSOR C1 is
select empno, ename from emp;
cursor c2 is
select * from dept where deptno = 10;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_dept dept%rowtype;
BEGIN
if not c1%isopen then
open c1;
end if;
loop
fetch c1
into v_empno, v_ename;
exit when c1%notfound;
if v_empno = 7369 then
update emp_t set sal = sal * 1.5 where empno = 7369;
elsif v_ename = 'SCOTT' then
delete from emp_t where ename = 'SCOTT';
end if;
dbms_output.put_line('Employee id is:' || v_empno);
dbms_output.put_line('Employee Name is:' || v_ename);
end loop;
close c1;
open c2;
loop
fetch c2--select * 语句定义的游标可以使用整个变量
into v_dept;
exit when c2%notfound;
dbms_output.put_line('Dept No is:' || v_dept.deptno);
dbms_output.put_line('Dept Name is:' || v_dept.dname);
dbms_output.put_line('Dept Loc is:' || v_dept.loc);
end loop;
close c2;
for v_dept in c2 loop --for循环会默认打开、关闭游标,也会自动fetch
--fetch c2
--into v_dept;
dbms_output.put_line('Dept No is:' || v_dept.deptno);
dbms_output.put_line('Dept Name is:' || v_dept.dname);
dbms_output.put_line('Dept Loc is:' || v_dept.loc);
end loop;
if c2%isopen then--发现for循环后游标已经关闭
dbms_output.put_line('Cursor is open.');
else
dbms_output.put_line('Cursor is close');
end if;
END;
/
DECLARE
CURSOR C1 (v_deptno Number, v_job varchar2) is
select empno, ename
from emp
where deptno = v_deptno
and job = v_job;
v_empno number;
v_ename varchar2(20);
BEGIN
open c1(10, 'CLERK');
loop
fetch c1
into v_empno, v_ename;
exit when c1%notfound;
dbms_output.put_line('The employy No is:' || v_empno);
dbms_output.put_line('The employy Name is:' || v_ename);
end loop;
close c1;
for v_emp in c1(20,'CLERK') loop
dbms_output.put_line('The employy No is:' || v_emp.empno);
dbms_output.put_line('The employy Name is:' || v_emp.ename);
end loop;
END;
/