--案例1.使用游标查询部门编号为10的所有人姓名和薪水
create or replace procedure sp_04 is
begin
declare type c is ref cursor;
emp_sor c;
ename emp.ename%type;
sal emp.sal%type;
begin
open emp_sor for select ename,sal from emp where deptno=10;
loop
fetch emp_sor into ename,sal;--取值标的值给变量
dbms_output.put_line('ename:'||ename||'sal:'||sal);
exit when emp_sor%notfound;
end loop;
close emp_sor;
end;
end sp_04;
--案例2.直接定义游标
create or replace procedure sp_05 is
begin
declare
cursor emp_sor is select ename,sal from emp where deptno=10;
ename emp.ename%type;
sal emp.sal%type;
begin
open emp_sor;
loop
fetch emp_sor into ename,sal;
dbms_output.put_line('ename:'||ename||'sal:'||sal);
exit when emp_sor%notfound;
end loop;
close emp_sor;
end;
end sp_05;
--案例3.使用记录变量来接受游标指定的表的数据
create or replace procedure sp_06 is
begin
declare
cursor emp_sor is select ename,sal from emp where deptno=10;
--使用记录变量来接受游标指定的表的数据
type emp_type is record(
v_ename emp.ename%type,
v_sal emp.sal%type
);
--emp_type声明一个与emp_type 类似的记录变量，该记录有两列，与emp表的ename,sal同类型的列.
emp_type1 emp_type;
begin
open emp_sor;
loop
fetch emp_sor into emp_type1;--取游标的值给变量
dbms_output.put_line(emp_type1.v_ename||','||emp_type1.v_sal);
exit when emp_sor%notfound;
end loop;
close emp_sor;
end;
end sp_06;
--案例4.用for游标取值
create or replace procedure sp_07 is
begin
declare
cursor emp_sor is select a.ename from emp a;
type ename_table_type is table of varchar2(20);
ename_table ename_table_type;
begin
--用for游标取值
open emp_sor;
--通过bulk cellect 减少loop处理的开销，使用bulk collect 提高oralce查询效率
--oracle 8i中首次引入bulk collect 特性，该特性可以让我们在PL/sql中能使用批查询，批查询在某些情况下能显著提高查询效率
--采用bulk collect 可以将查询结果一次性地加载到collertions 中。
--而不是通过cursor一条一条地处理
--可以在select into ,fetch into ,returning into 语句中使用bulk collect.
--注意在使用bulk collect 时，所有的into 变量都必须是collections
fetch emp_sor bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_sor;
end;
end sp_07;
--案例5.用for取值，带隐形游标会自动打开和关闭
create or replace procedure sp_08 is
begin
declare
cursor emp_sor is select a.ename from emp a;
type emp_table is table of varchar2(20);
begin
for emp_record in emp_sor
loop
dbms_output.put_line('第'||emp_sor%rowcount||'雇员名：'||emp_record.ename);
end loop;
end;
end sp_08;
--案例6.判断游标是否打开
create or replace procedure sp_09 is
begin
declare
cursor emp_sor is select a.ename from emp a;
type emp_table_type is table of varchar2(20);
emp_table emp_table_type;
begin
--用for取值，判断游标是否打开
if not emp_sor%isopen then
open emp_sor;
end if;
fetch emp_sor bulk collect into emp_table;
dbms_output.put_line(emp_sor%rowcount);
close emp_sor;
end;
end sp_09;
--案例7.使用游标变量取值
create or replace procedure sp_10 is
begin
--使用游标变量取值
declare
cursor emp_sor is select a.ename,a.sal from emp a;
emp_record emp_sor%rowtype;
begin
open emp_sor;
loop
fetch emp_sor into emp_record;
exit when emp_sor%notfound;
--exit when emp_sor%notfound 放的位置不一样得到的结果也不一样，如果放到dbms_...后;
--结果会多显示一行数据，即查询结果的最后一行显示了两次
dbms_output.put_line('序号'||emp_sor%rowcount||'名称：'||emp_record.ename||'薪水：'||emp_record.sal);
end loop;
close emp_sor;
end;
end sp_10;
--案例8.带参数的游标，在打开游标的时候传入参数
create or replace procedure sp_11 is
begin
--带参数的游标，在打开游标的时候传入参数
declare
cursor emp_sor(no number) is select a.ename from emp a where a.deptno=no;
emp_record emp_sor%rowtype;
begin
open emp_sor(10);
loop
fetch emp_sor into emp_record;
exit when emp_sor%notfound;
dbms_output.put_line('序号'||emp_sor%rowcount||'名称：'||emp_record.ename);
end loop;
close emp_sor;
end;
end sp_11;
--案例9.使用游标做更新操作
create or replace procedure sp_12 is
begin
--使用游标做更新，删除操作，必须在定义游标的时候加上for update
--当然也可以用for update nowait;
declare
cursor emp_sor is select a.ename,a.sal from emp a for update;
cname emp.ename%type;
csal emp.sal%type;
begin
open emp_sor;
loop
fetch emp_sor into cname,csal;
exit when emp_sor%notfound;
dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
if csal < 2000 then
update emp set sal=sal+200 where current of emp_sor;
end if;
end loop;
close emp_sor;
--要查看更新后的数据，必须得重新打开游标去查询
open emp_sor;
loop
fetch emp_sor into cname,csal;
exit when emp_sor%notfound;
dbms_output.put_line('名称:'||cname||','||'薪水:'||csal);
end loop;
close emp_sor;
end;
end sp_12;
--案例10.使用游标做删除操作
create or replace procedure sp_13 is
begin
--使用游标做更新，删除操作，必须在定义游标的时候加上for update
declare
cursor emp_sor is select a.empno from emp a for update;
pempno emp.empno%type;
begin
open emp_sor;
loop
fetch emp_sor into pempno;
exit when emp_sor%notfound;
dbms_output.put_line('旧的empno:'||pempno);
if pempno = 2009 then
delete emp where current of emp_sor;
end if;
end loop;
close emp_sor;
--要查看删除后的数据，必须得重新打开游标去查询
open emp_sor;
loop
fetch emp_sor into pempno;
exit when emp_sor%notfound;
dbms_output.put_line('新的empno:'||pempno);
end loop;
close emp_sor;
end;
end sp_13;
--案例11.直接使用游标而不用去定义
create or replace procedure sp_14 is
begin
for emp_record in(select empno,sal,deptno from emp )
loop
dbms_output.put_line('员工编号:'||emp_record.empno||',薪水:'||emp_record.sal||',部门编号'||emp_record.deptno);
end loop;
end sp_14;
--案例12.带sql的统计查询
create or replace procedure sp_15 is
begin
declare
type test_cursor_type is ref cursor;
test_cursor test_cursor_type;
v_ename emp.ename%type;
v_count number;
str_sql varchar2(100);
begin
open test_cursor for select ename from emp;
loop
fetch test_cursor into v_ename;
if v_ename is not null then
str_sql:='select count(*) from emp';
execute immediate str_sql into v_count;
end if;
exit when test_cursor%notfound;
dbms_output.put_line(v_ename||','||v_count);
end loop;
close test_cursor;
end;
end sp_15;
个人整理出来的oracle存储过程中游标的使用，供大家参考和学习。
