oracle 游标使用
?
forall 语句 forall index in m..n批量更新declare type id_table is table of number(6) index by binary_integer; type name_table is table of varchar2(10) index by binary_integer; vid id table; vname name_tablebegin for i in 1..100 loop vid(i):=i; vname(i):=to_char(i)||'aa'; end loop; forall i in 1..vid.count update t2 set sname=vname(i) where id = vid(i);end;批量删除bulk collect... bulk collect into collection...游标 cursorcontext area 上下文区显示游标隐式游标定义游标cursor cursonname is select_statement打开游标open cursorname提取游标（数据）fetch cursonname into关闭游标close cursorname属性cursorname %ISOPEN... %FOUND... %NOTFOUND... %ROWCOUNTdeclare v_emp emp%rowtype;--声明游标 cursor cur_emp is select * from emp where deptno=10;begin --open cursor open cur_emp; loop --fetch fetch cur_emp into v_emp; exit when cur_emp%notfound; dbms_output.put_line(v_emp.ename); end loop; --close cursor close cur_emp; end;declare type r1 is record( ename emp.ename%type, sal emp.sal%type ); type a1 is table of r1 index by binary_integer; a a1;begin select ename,sal bulk collect into a from emp; for i in a.first..a.last loop dbms_output.put_line(a(i).ename||' '||a(i).sal); end loop;end;
plsql 第五六课带参数的游标declarev_emp emp%rowtype;cursor cur_emp(v_deptno emp.deptno%type,v_empno emop.empno%type) is select * from where deptno=v_deptno and empno = v_empno;begin open cur_emp(&deptno,$empno); loop fetch cur_emp into v_emp; exit when cur_emp%notfound; dbms_output.put_line(v_emp.ename); dbms_output.put_line(v_emp.sal); end loop; close cur_emp;end;游标变量declare v_emp emp%rowtype; type cur_type is ref cursor; cur_emp cur_type;begin open cur_emp for 'select * from emp where deptno=:a' using &deptno; loop fetch cur_emp into v_emp; exit when cur_emp%notfound; dbms_output.put_line(v_emp.ename); dbms_output.put_line(v_emp.sal); end loop; close cur_emp; end;游标变量for循环declare cursor cur_emp is select * from emp where deptno=20;begin for v_emp in cur loop dbms_output.put_line(v_emp.ename); dbms_output.put_line(v_emp.sal); end loop;end;隐式游标pls/sql控制declare v_rows number(9);begin update myemp set sal=sal+100 where sal<2000; v_rows:=sql%rowcount; dbms_output.put_line(v_rows);end;使用游标更新或删除数据declare v_emp myemp%rowtype; cur c1 is select * from myemp for update nowait;begin open c1; loop fetch c1 into v_emp; exit when c1%notfound; if v_emp.sal<5000 then update myemp set sal = sal+500 where current of c1; end if; end loop;commit;close c1;end;手动锁表lock table myemp in exclusive mode;释放：commit，rollback；exitdeclare v_emp myemp%rowtype; cursor c1 is select * from emp for update;begin open c1; loop fetch c1 into v_emp; exit when c1%notfound; if v_emp.deptno=30 then delete from myemp where current of c1; end if; end loop; commit;end;
