Oracle笔记九PLSQL游标的使用.docVIP

Oracle笔记 九、PL/SQL 游标的使用 --演示隐式游标,系统自动声明，自动打开，自动使用并且自动关闭 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