--演示隐式游标,系统自动声明,自动打开,自动使用并且自动关闭 begin update emp set sal = 1000; dbms_output.put_line('影响的行数:' || sql%rowcount); end; rollback; /*游标的使用方法: 第一步:声明游标 第二步:打开游标 第三步:使用游标进行循环操作 第四步:关闭游标*/ --普通游标,游标本身就是一个变量 declare --下面的这行代码声明了一个游标 cursor mycur is select * from emp where deptno = 20; emprow emp%rowtype; begin open mycur; --打开游标 loop fetch mycur into emprow; --把游标所指的纪录放到变量中 exit when (mycur%notfound); --当游标没有指向行时退出循环 dbms_output.put_line('名字:' || emprow.ename || '薪水:' || emprow.sal); end loop; close mycur; --关闭游标 end; --简单游标,列操作 declare empname emp.ename%type; empsal emp.sal%type; cursor mycur is select ename,sal from emp where deptno = 30; begin open mycur; loop fetch mycur into empname,empsal; exit when mycur%notfound; dbms_output.put_line('姓名:' || empname || '工资' || empsal); end loop; end; --简单游标,列操作 declare cursor c is select * from dept; vDept_row_record c%rowtype; begin open c; fetch c into vDept_row_record; dbms_output.put_line(vDept_row_record.dname); close c; end; --when循环游标 declare cursor c is select * from dept; vDept_row_record c%rowtype; begin open c; loop fetch c into vDept_row_record; exit when(c%notfound); dbms_output.put_line(vDept_row_record.dname); end loop; close c; end; --while循环游标 declare cursor c is select * from dept; vDept_row_record c%rowtype; begin open c; fetch c into vDept_row_record; while (c%found) loop dbms_output.put_line(vDept_row_record.dname); fetch c into vDept_row_record; end loop; close c; end; --for循环游标 declare cursor c is select * from dept; vDept_row_record c%rowtype; begin for vDept_row_record in c loop dbms_output.put_line(vDept_row_record.dname); end loop; end; --带参游标 declare cursor c(sSal emp.sal%type, sEmpno emp.empno%type) is select * from emp where sal >= sSal and empno > sEmpno; begin for record_data in c(2500, 6666) loop dbms_output.put_line(record_data.ename); end loop; end; --update游标 declare cursor c(sSal emp2.sal%type) is select * from emp2 where sal >= sSal for update; begin for record_data in c(2500) loop if (record_data.sal < 3000) then update emp2 set sal = sal 3 where current of c; dbms_output.put_line(record_data.ename); elsif (record_data.sal = 5000) then update emp2 set sal = sal - 3 where current of c; dbms_output.put_line(record_data.ename); end if; end loop; end; --引用游标不能使用循环游标的语法 --引用游标不能进行删除和修改 --引用游标是一个数据类型,使用该类型必须声明变量 --弱类型引用游标,就是不指定游标将要提取的数据行的类型 declare type my_cur_type is ref cursor; mycur my_cur_type;--声明变量 which varchar2(10); deptrow dept%rowtype; emprow emp%rowtype; begin which := '&请选择dept还是emp'; if (which = 'dept') then open mycur for select * from dept; loop fetch mycur into deptrow; exit when (mycur%notfound); dbms_output.put_line(deptrow.deptno || ' ' || deptrow.dname); end loop; elsif (which = 'emp') then open mycur for select * from emp; loop fetch mycur into emprow; exit when (mycur%notfound); dbms_output.put_line(emprow.empno || ' ' || emprow.ename); end loop; end if; close mycur; end; --强类型引用游标,就是指定游标将要提取的数据行的类型 ,只能是record或%rowtype类型 --比如:return number是错的,return emp.ename%type也是错的 declare type mycurtype is ref cursor return emp%rowtype; mycur mycurtype;--声明变量 emprow emp%rowtype; begin open mycur for select * from emp; loop fetch mycur into emprow; exit when mycur%notfound; dbms_output.put_line(emprow.empno || ' ' || emprow.ename); end loop; close mycur; 出处:http://www.cnblogs.com/hoojo/archive/2011/05/03/2035357.html
|