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;