Oracle扩展PL/SQL简介(四)

本来接下来要讲一下数组类型，但内容很多，因此放到后面

 

5.    pl/sql游标
游标：
用来查询数据库，获取记录集合（结果集)的指针，当在PL/SQL块中执行查询语句SELECT和数据操纵语句DML时,ORACLE会为其分配上下文区(CONTEXT AREA),游标指上下文区指针,对于数据操纵语句和单行SELECT INTO语句来说,ORACLE会为他们分配隐式游标。使用显示游标处理多行数据,也可使用SELECT..BULK COLLECT INTO 语句处理多行数据.

分类：
静态游标：
分为显式游标和隐式游标(SQL游标)。
REF游标：
是一种引用类型，类似于指针。

显式游标：
定义：CURSOR 游标名 ( 参数 ) [返回值类型] IS SELECT 语句
生命周期：
1打开游标(OPEN)：
解析，绑定。。。不会从数据库检索数据
2从游标中获取记录(FETCH INTO)：
执行查询，返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。
3关闭游标(CLOSE)
完成游标处理，用户不能从游标中获取行。还可以重新打开。
选项：参数和返回类型
举例:
在hr模式下：
set serveroutput on
declare
 cursor emp_cur (p_deptid in number ) is
 select * from employees where department_id=p_deptid; --定义游标
 v_emp employees%rowtype;
begin
--属于30部门的员工
 dbms_output.put_line('Getting employees from department 30');
 open emp_cur(30); --打开游标
 loop
   fetch emp_cur into v_emp; --获取数据
   exit when emp_cur%notfound;
   dbms_output.put('Employee id '|| v_emp.employee_id || ' is ');
   dbms_output.put_line(v_emp.first_name|| ' '||v_emp.last_name);
 end loop;
 close emp_cur; --关闭游标
--属于90部门的员工
 dbms_output.put_line('Getting employees from department 90');
 open emp_cur(90);
 loop
   fetch emp_cur into v_emp;
   exit when emp_cur%notfound;
   dbms_output.put('Employee id '|| v_emp.employee_id || ' is ');
   dbms_output.put_line(v_emp.first_name || ' ' || v_emp.last_name);
  end loop;
  close emp_cur;
end;
/

隐式游标(SQL游标)：
不用明确建立游标变量，分两种：
1．在PL/SQL中使用DML语言，使用ORACLE提供的名为SQL的隐式游标
2．CURSOR FOR LOOP，用于for loop语句。
举例：
hr模式下
declare
begin
for my_dept_rec in ( select department_name, department_id from departments)
loop
   dbms_output.put_line(my_dept_rec.department_id || ‘ : ’ || my_dept_rec.department_name);
end loop;
end;
/

SQL游标有四种属性
%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。
%ISOPEN：是否打开。
举例:
sql%isopen:执行时,会隐含的打开和关闭游标.因此该属性的值永远都是FALSE
sql%found:用于确定SQL语句执行是否成功.当SQL有作用行时,为TRUE,否则为FALSE
declare
 v_deptno emp.deptno%type:=10;
begin
  update emp set sal=sal+1 where deptno=v_deptno;
  if sql%found then
    dbms_output.put_line('success!');
  else
    dbms_output.put_line('fail!');
  end if;
end;
/
sql%rowcount:返回SQL语句所作用的总计行数(hr模式下)
declare
begin
update departments set department_name=department_name;
--where 1=2;
dbms_output.put_line(‘update ‘|| sql%rowcount ||’ records’);
end;
/

显式游标和隐式游标的区别：
尽量使用隐式游标，避免编写附加的游标控制代码(声明，打开，获取，关闭)，也不需要声明变量来保存从游标中获取的数据。

REF CURSOR游标:
动态游标，在运行的时候才能确定游标使用的查询。分类：
强类型(限制)REF CURSOR，规定返回类型
弱类型(非限制)REF CURSOR，不规定返回类型，可以获取任何结果集。
TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

DECLARE
TYPE refcur_t is ref cursor;

TYPE emp_refcur_t is ref cursor return employee%rowtype;
BEGIN
NULL;
END;
/
强类型举例：
declare
--声明记录类型
 type emp_job_rec is record(
   employee_id number,
   employee_name varchar2(50),
   job_title varchar2(35)
 );
--声明REF CURSOR，返回值为该记录类型
 type emp_job_refcur_type is ref cursor return emp_job_rec;
--定义REF CURSOR游标的变量
 emp_refcur emp_job_refcur_type;
 emp_job emp_job_rec;
 begin
  open emp_refcur for
    select
     e.employee_id,e.first_name||' '||e.last_name "employee_name",
     j.job_title
    from employees e,jobs j
    where e.job_id =j.job_id and rownum <11 order by 1;
  fetch emp_refcur into emp_job;
  while emp_refcur%found loop
    dbms_output.put(emp_job.employee_name || '''s job is ');
    dbms_output.put_line(emp_job.job_title);
    fetch emp_refcur into emp_job;
 end loop;
 close emp_refcur;
end;
/

单独select
declare
 v_empno emp.empno%type;
 v_ename emp.ename%type;
begin
select empno,ename into v_empno,v_ename from emp where rownum =1;
dbms_output.put_line(v_empno||' '||v_ename);
end;
/
使用INTO获取值，只能返回一行。