PL/SQL(三)

定义数组（装的是一行类型）

declare
type ma is table of varchar2(20) index by binary_integer;
may ma;
begin
may(1):='嘿嘿';
may(2):='哈哈';
dbms_output.put_line(may(1)||chr(9)||may(2));
end;
/

 

For循环 直接执行 open  fetch close  直接输出
declare
cursor rs is select * from emp;
begin
for r in rs loop
dbms_output.put_line(r.ename);
end loop;
end;

 

把emp表放入数组中...

declare
type list is table of emp%rowtype index by binary_integer;
l list;
cursor rs is select * from emp;
v_count int:=0;
begin
for i in rs loop
v_count:=v_count+1;
l(v_count):=i;
end loop;
for i in 1..v_count loop
dbms_output.put_line(l(i).empno||chr(9)||l(i).ename);
end loop;
end;

 

输入部分编号显示部门名称 所在地 部员名

declare
cursor rs(v_empno emp.empno%type) is select dname,loc,ename,empno from emp,dept where emp.deptno=dept.deptno and empno=v_empno;
v_empno emp.empno%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
v_empn emp.empno%type;
v_emp emp%rowtype;
begin
for i in rs(&empno) loop
dbms_output.put_line(i.ename||chr(9)||i.loc||chr(9)||i.dname);
end loop;
end;

 

输入部门编号 查询 所在地 和 员工名

declare
cursor rs(v_deptno emp.deptno%type) is select dname,loc,ename from emp natural join dept where deptno=v_deptno
begin
for r in rs(&deptno) loop
dbms_output.put_line(r.dname||chr(9)||r.loc||chr(9)||r.ename);
end loop;
end;
/

 

锁定当前行 current of cur_score;
没家where 条件的话修改所有 值

declare
 cursor cur_score is select rowid,grade from score for update;
begin 
for r in cur_score loop
 if r.grade=72 then
  update score set grade=83 where current of cur_score;
 elsif r.grade=83 then
update score set grade=95;
elsif r.grade=95 then
update score set grade=100 where current of cur_score;
end if;
end loop;
end;
结果：95  95 100
这里 先执行 缓存中的内容.. current of 就是和 缓存中的行 像对应  然后从上到下执行sql语句
执行完缓存里所有 数据之后 才保存到 数据库中..
游标变量
1、声明
游标名 sys_refcursor;
2、打开
 open 游标名 for 查询语句;
3、提取
fetch 游标名 into 变量；
4、关闭
 close 游标名；

查询dept表内容

查询 dname ename loc 内容

declare
rs sys_refcursor;
v_dept dept%rowtype;
v_ename emp.ename%type;
v_dname dept.dname%type;
v_loc dept.loc%type;
begin
open rs for select * from dept;
loop
fetch rs into v_dept;
exit when rs%notfound;
dbms_output.put_line(v_dept.deptno||chr(9)||v_dept.dname||chr(9)||v_dept.loc);
end loop;
close rs;
dbms_output.put_line('-----------------------------------------------');
open rs for select ename,dname,loc from emp,dept where emp.deptno=dept.deptno;
loop
fetch rs into v_ename,v_dname,v_loc;
exit when rs%notfound;
dbms_output.put_line(v_ename||chr(9)||v_dname||chr(9)||v_loc);
end loop;
close rs;
end;