declare
v_count NUMBER;
begin
select count(*) into v_count from emp;
dbms_output.put_line('sql%rowcount =' || sql%rowcount);
end;
/
declare
begin
INSERT into DEPT(DEPTNO,DNAME,LOC) VALUES(80,'firefox','shanghai');
dbms_output.put_line('sql%rowcount =' || sql%rowcount);
end;
/
declare
begin
update DEPT SET DNAME = 'firefox.COMMIT';
dbms_output.put_line('sql%rowcount' || sql%rowcount);
end;
/
--通过以上几个简单的程序可以发现隐式游标一直存在，但是隐式游标有很多种;
declare
v_empRow emp%ROWTYPE ; --保存emp一行数据;
begin
select * into v_empRow from emp where EMPNO = 7369;
if SQL%FOUND THEN
dbms_output.put_line('name is :' || v_empRow.ENAME || ',JOB is :' ||
v_empRow.JOB);
end if;
end;
/
--单行隐士游标
declare
begin
update emp SET SAL=SAL*1.2 where 1=2;
if sql%FOUND THEN
dbms_output.put_line('update rowcount:' || sql%rowcount);
else
dbms_output.put_line('NOT FOUND data update');
end if;
end;
/
--多行隐士游标;
--范例:定义显示游标;
declare
cursor cur_emp is select * from emp;
v_empRow emp%ROWTYPE;
begin
--游标如果要操作一定要保证其已经打开
if cur_emp%isopen THEN
NULL; --什么都不做
else
open cur_emp; --打开游标
end if;
--默认情况下游标在第一行记录上
fetch cur_emp into v_empRow; --取得当前行数据
while cur_emp%FOUND loop
dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal );
fetch cur_emp into v_empRow; --把游标指向下一行;
end loop;
close cur_emp;
end;
/
--使用loop循环
declare
cursor cur_emp is select * from emp;
v_empRow emp%ROWTYPE;
begin
--游标如果要操作一定要保证其已经打开
if cur_emp%isopen THEN
NULL; --什么都不做
else
open cur_emp; --打开游标
end if;
--默认情况下游标在第一行记录上
loop
fetch cur_emp into v_empRow;
exit when cur_emp%NOTFOUND; --没有数据推出循环
dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal );
end loop;
close cur_emp;
end;
/
--使用for循环进行游标操作：
declare
cursor cur_emp return emp%ROWTYPE is select * from emp;
v_empRow emp%ROWTYPE;
begin
for v_empRow in cur_emp loop
exit when cur_emp%NOTFOUND; --没有数据推出循环
dbms_output.put_line('rowmeber is :' || cur_emp%rowcount ||',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal );
end loop;
end;
/
--偷懒for循环进行游标操作;
declare
begin
for v_empRow in (select * from emp)loop
dbms_output.put_line(',name is : ' || v_empRow.ename ||
', JOB is :'|| v_empRow.JOB || ',sal is : ' || v_empRow.sal );
end loop;
end;
/
--在动态select中使用游标;
declare
v_lowsal emp.sal%TYPE := &inputlowsal;
v_highsal emp.sal%TYPE := &inputhighsal;
cursor cur_emp is select * from emp where sal between v_lowsal and
v_highsal;
begin
for emp_row in cur_emp loop
dbms_output.put_line(cur_emp%rowcount || ',name is : ' ||
emp_row.ename || ', JOB is : ' || emp_row.JOB || ', sal : '
|| emp_row.sal);
end loop;
end;
/
--
declare
cursor cur_emp(p_dno emp.DEPTNO%TYPE) is select * from
emp where DEPTNO=p_dno;
begin
for emp_row in cur_emp(&inputDeptno) loop
dbms_output.put_line(cur_emp%rowcount || ', name is : ' ||
emp_row.ename || ', JOB is : ' || emp_row.JOB ||
'sal is :' || emp_row.sal);
end loop;
end;
/
范例：使用嵌套表接收游标数据
declare
TYPE dept_nested is table of dept%ROWTYPE;
v_dept dept_nested;
cursor cur_dept is select * from DEPT;
begin
if cur_dept%isopen THEN
null;
else
open cur_dept;
end if;
fetch cur_dept bulk collect into v_dept;
for x in v_dept.first .. v_dept.last loop
dbms_output.put_line('member is :' || v_dept(x).DEPTNO || ', name is : ' ||
v_dept(x).DNAME || ',localtion is : '|| v_dept(x).loc);
end loop;
close cur_dept;
end;
/
范例：将游标数据保存在可变数组之中;
declare
TYPE dept_varray is varray(4) of dept%ROWTYPE; --定义可变数组
v_dept dept_varray;
cursor cur_dept is select * from DEPT;
v_rows NUMBER := 4; --每次提取2行记录;
v_count NUMBER := 1; --每次少显示1行记录;
begin
if cur_dept%isopen THEN
null;
else
open cur_dept;
end if;
fetch cur_dept bulk collect into v_dept limit v_rows;
for x in v_dept.first .. (v_dept.last - v_count) loop
dbms_output.put_line('member is :' || v_dept(x).DEPTNO || ', name is : ' ||
v_dept(x).DNAME || ',localtion is : '|| v_dept(x).loc);
end loop;
close cur_dept;
end;
/
--游标更行操作
declare
cursor cur_emp is select * from emp;
begin
for emp_row in cur_emp loop
if emp_row.deptno = 10 then
if emp_row.sal*1.5 < 5000 then
update emp set sal = sal*1.5
where empno=emp_row.empno;
else
update emp set sal=5000
where empno=emp_row.empno;
end if;
elsif emp_row.deptno = 20 then
if emp_row.sal*1.22 < 5000 then
update emp set sal = sal*1.22
where empno=emp_row.empno;
else
update emp set sal=5000
where empno=emp_row.empno;
end if;
elsif emp_row.deptno = 30 then
if emp_row.sal*1.39 < 5000 then
update emp set sal = sal*1.39
where empno=emp_row.empno;
else
update emp set sal=5000
where empno=emp_row.empno;
end if;
else
null;
end if;
end loop;
exception
when others then
dbms_output.put_line('SQLCODE =' || SQLCODE);
dbms_output.put_line('SQLERRM =' || SQLERRM);
ROLLBACK;
end;
/
范例:创建一个不等待的游标;
declare
cursor cur_emp is select * from emp where deptno=10 for update nowait;
begin
for emp_row in cur_emp loop
update emp set sal=9999
where empno=emp_row.empno;
end loop;
end;
/
范例:使用where current of 子句:
declare
cursor cur_emp is select * from emp where deptno=10 for update;
begin
for emp_row in cur_emp loop
update emp set sal=9999
where current of cur_emp; --表示更新当前行的数据;
end loop;
end;
/
范例:使用游标删除表中数据：
declare
cursor cur_emp is select * from emp where deptno=10 for update
of sal,comm;
begin
for emp_row in cur_emp loop
delete from
emp
where current of cur_emp; --表示更新当前行的数据;
end loop;
end;
/
范例:创建一个多表查询的操作; --for update
declare
cursor cur_emp is
select e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e , dept d
where e.deptno=d.deptno
and e.deptno=10 for update;
begin
for emp_row in cur_emp loop
update emp set sal=9999 where current of cur_emp;
end loop;
end;
/
declare
cursor cur_emp is
select e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e , dept d
where e.deptno=d.deptno
and e.deptno=10 for update of sal;
begin
for emp_row in cur_emp loop
update emp set sal=9999 where current of cur_emp;
end loop;
end;
/
declare
type dept_ref is ref cursor return dept%ROWTYPE; --定义强类型游标
cur_dept dept_ref; --定义游标变量
v_deptRow dept%ROWTYPE; --定义行类型
begin
open cur_dept for select * from dept; --打开游标并决定游标类型
loop
fetch cur_dept into v_deptRow; --取得游标类型
exit when cur_dept%NOTFOUND; --如果没有数据就退出
dbms_output.put_line('name is : ' || v_deptRow.dname ||
',localtion : ' || v_deptRow.loc);
end loop;
close cur_dept;
end;
/
declare
type cursor_ref is ref cursor; --定义弱类型游标
cur_var cursor_ref; --定义游标变量
v_deptRow dept%ROWTYPE; --定义行类型
v_empRow emp%ROWTYPE; --定义行类型;
begin
open cur_var for select * from dept; --打开游标并决定游标类型
loop
fetch cur_var into v_deptRow; --取得游标类型
exit when cur_var%NOTFOUND; --如果没有数据就退出
dbms_output.put_line('1. name is : ' || v_deptRow.dname ||
',localtion : ' || v_deptRow.loc);
end loop;
close cur_var;
open cur_var for select * from emp where deptno = 10; -- 打开游标;
loop
fetch cur_var into v_empRow;
exit when cur_var%NOTFOUND;
dbms_output.put_line('2. name is :' || v_empRow.ename ||
'job is : ' || v_empRow.job);
end loop;
close cur_var;
exception
when ROWTYPE_MISMATCH then
dbms_output.put_line('游标数据类型不匹配异常. SQLCODE' || SQLCODE ||
'SQLERRM' || SQLERRM);
end;
/
