?

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_table
begin
  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...

游标 cursor
context area 上下文区
显示游标
隐式游标


定义游标
cursor cursonname is select_statement
打开游标
open cursorname
提取游标（数据）
fetch cursonname into
关闭游标
close cursorname

属性
cursorname %ISOPEN
... %FOUND
... %NOTFOUND
... %ROWCOUNT

declare
 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 第五六课
带参数的游标
declare
v_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；exit


declare 
  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;