几种常用游标的例子

---------------sql%rowcount-----------------------

begin
delete from emp;
dbms_output.put_line('删除行数:'||sql%rowcount);
update demo set dname='kkkkk' ;
dbms_output.put_line('更新行数:'||sql%rowcount);
end;



----------------sql%bulk_rowcount------------------

declare
type demo_dname_type is table of demo.dname%type index by binary_integer;
dnames demo_dname_type;
begin
   select dname bulk collect into  dnames from demo;
   forall i in 1..dnames.count
       insert into temp values(dnames(i));
   for j in 1..dnames.count
   loop
       dbms_output.put_line(j||'次插入:'||sql%bulk_rowcount(j));
    end loop;   
   commit;
end;


------------------显示游标--------------------------

declare
cursor mycur is select * from jobs;
rowdata jobs%rowtype;
begin
open mycur;

loop
  fetch mycur into rowdata;
  exit when mycur%notfound;
end loop;

close mycur;
end;

-------------------参数游标-------------------------------------------

select * from jobs
declare     --注意：参数类型不需要指定精度
cursor mycur(salary number) is select * from jobs where max_salary<salary;
rowdata jobs%rowtype;
begin
open mycur(20000);--打开游标时给游标参数赋值

loop
  fetch mycur into rowdata;
  exit when mycur%notfound;
  dbms_output.put_line('did:'||rowdata.job_id||'   dname:'||rowdata.job_title);
end loop;

close mycur;
end;


---------------------for 循环游标------------------

declare
cursor mycur(salary number) is select * from jobs where max_salary<salary;
begin

for rowdata in mycur(20000)
loop
  dbms_output.put_line('did:'||rowdata.job_id||'   dname:'||rowdata.job_title);
end loop;
end;


----------------------游标更新------------------

select * from employees emp where emp.salary<20000
declare
cursor mycur(nid number) is select * from employees emp where emp.salary<nid for update;
begin

for r in mycur(20000)
loop  --修改游标一定一定要注意where current of的应用
  update employees set salary=salary*1.1  where current of mycur;
  dbms_output.put_line('did:'||r.employee_id ||'  '||'dname:'||r.salary);
end loop;

end;


declare
cursor mycur(nid number) is select * from demo where did>nid for update;
rowdata demo%rowtype;
begin
open mycur(1);

loop
  fetch mycur into rowdata;
  exit when mycur%notfound;
  update demo set dname='ddddddd' where current of mycur;
end loop;
commit;

close mycur;
end;


------------------动态游标 ref----------------------

declare
type mycur_type1 is ref cursor return employees%rowtype;---强类型(不能用在动态SQL语句当中)
cur1 mycur_type1;
type mycur_type2 is ref cursor;---弱类型
cur2 mycur_type2;
rowdata jobs%rowtype;
begin         --打开游标时，指定游标的内容
open cur2 for select * from jobs;
loop
  fetch cur2 into rowdata;
  exit when cur2%notfound;
  dbms_output.put_line('did:'||rowdata.job_id);
end loop;

close cur2;
end;


---------------------存储过程---------------------

create table emp1
(
id number(10),
name varchar2(10),
salary number(10)
);
create or replace procedure insertpro(id in number, name in varchar2,salary in number) is
begin
  insert into emp1 values(id,name,salary);
end insertpro;

call insertpro(10,'yanther',10000);
select * from emp1;
--程序包规范中声明一个游标类型
create or replace package types
   as
     type cursorType is ref cursor;
   end;
--利用存储过程返回结果集
create or replace procedure getemps(id number,p_cursor in out types.cursorType )
  as
  begin
  open p_cursor for 'select * from emp1 where id='||id;--表的名字
  end;  
 
--游标作为参数使用
declare
type mycr is ref cursor;
cr1 mycr;
rowdata emp1%rowtype;
begin
getemps(10,cr1);
loop
  fetch cr1 into rowdata;
  exit when cr1%notfound;
  dbms_output.put_line('did:'||rowdata.id||'   dname:'||rowdata.name);
end loop;
close cr1;
end;


已有 0 人发表留言，猛击->> 这里<<-参与讨论


ITeye推荐

• —软件人才免语言低担保 赴美带薪读研！—