PL/SQL(三)

定义数组（装的是一行类型）declaretype ma is table of varchar2(20) index by binary_integer;may ma;beginmay(1):='嘿嘿';may(2):='哈哈';dbms_output.put_line(may(1)||chr(9)||may(2));end;/

 

For循环 直接执行 open  fetch close  直接输出declarecursor rs is select * from emp;beginfor r in rs loopdbms_output.put_line(r.ename);end loop;end;

 

把emp表放入数组中...declaretype list is table of emp%rowtype index by binary_integer;l list;cursor rs is select * from emp;v_count int:=0;beginfor i in rs loopv_count:=v_count+1;l(v_count):=i;end loop;for i in 1..v_count loopdbms_output.put_line(l(i).empno||chr(9)||l(i).ename);end loop;end;

 

输入部分编号显示部门名称 所在地 部员名declarecursor 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;beginfor i in rs(&empno) loopdbms_output.put_line(i.ename||chr(9)||i.loc||chr(9)||i.dname);end loop;end;

 

输入部门编号 查询 所在地 和 员工名declarecursor rs(v_deptno emp.deptno%type) is select dname,loc,ename from emp natural join dept where deptno=v_deptnobeginfor r in rs(&deptno) loopdbms_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 thenupdate score set grade=95;elsif r.grade=95 thenupdate 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 内容declarers sys_refcursor;v_dept dept%rowtype;v_ename emp.ename%type;v_dname dept.dname%type;v_loc dept.loc%type;beginopen rs for select * from dept;loopfetch 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;loopfetch 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;