plsql基础(我们似曾相识系列3)

在sql编程中，经常会查数据，游标的使用时不可少的，也是一个极其重要的概念吧。

概念：

在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针（ORACLE是默认就产生游标，我们自己定义游标出来时，自己更方便的使用。），或是命名一个工作区（Work Area），或是一种结构化数据类型。

游标的一个常见的用法是保存查询结果以备以后使用，一个游标结果集是通过执行SELECT 查询来建立的，
数据库游标允许你选择一组数据，通过翻阅这组数据记录（通常被称为数据集），检查每一个游标所在的特定的行。

在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

 

1,显式游标

四个步骤完成：

1 定义游标 :就是定义一个游标名，以及与其相对应的 SELECT 语句。

　　格式：
　　　　CURSOR cursor_name[(parameter[, parameter] … )] IS select_statement;
　　游标参数只能为输入参数，其格式为：
　　　　parameter_name [IN] datatype [{:= | DEFAULT} expression ]
　　在指定数据类型时，不能使用长度约束。如 NUMBER(4) 、 CHAR(10) 等都是错误的。

2 打开游标：就是执行游标所对应的 SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。

　　格式：
　　　　OPEN cursor_name[([parameter =>] value[, [parameter =>] value] … )];
　　在向游标传递参数时，可以使用与函数参数相同的传值方法。

　　注意： PL/SQL 程序不能用 OPEN 语句重复打开一个游标。如果游标查询语句中带有 FOR UPDATE 选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

3 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中.

　　格式：
　　　　FETCH cursor_name INTO {variable_list | record_variable };

4 关闭游标:释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用 FETCH 语句取其中数据。

　　格式：
　　　　CLOSE cursor_name;
　　注意：关闭后的游标可以使用 OPEN 语句重新打开。

 

 

2,游标属性
%FOUND 布尔型属性，当最近一次读记录时成功返回 , 则值为 TRUE ；
%NOTFOUND 布尔型属性，与 %FOUND 相反；
%ISOPEN 布尔型属性，当游标已打开时返回 TRUE ；

%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

 

例：LOOP循环游标

DECLARE
DeptRec dept%ROWTYPE;
Dept_name dept.dname%TYPE;
Dept_loc dept.loc%TYPE;
CURSOR c1 IS
SELECT dname, loc FROM dept WHERE deptno <= 30;
CURSOR c2(dept_no NUMBER DEFAULT 10) IS
SELECT dname, loc FROM dept WHERE deptno <= dept_no;
CURSOR c3(dept_no NUMBER DEFAULT 10) IS
SELECT * FROM dept WHERE deptno <=dept_no;
BEGIN
OPEN c1;
LOOP 
FETCH c1 INTO dept_name, dept_loc;
EXIT WHEN c1%NOTFOUND; ----LOOP循环用notfound
DBMS_OUTPUT.PUT_LINE(dept_name|| ’ --- ‘ ||dept_loc);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO dept_name, dept_loc;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name|| ’ --- ‘ ||dept_loc);
END LOOP;
CLOSE c2;
OPEN c3(dept_no =>20);
LOOP
FETCH c3 INTO deptrec;
EXIT WHEN c3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(deptrec.deptno|| ’ --- ‘ ||deptrec.dname
|| ’ --- ‘ ||deptrec.loc);
END LOOP;
CLOSE c3;
END;

 例：while循环游标

declare
  cursor c
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  open c;
  fetch c into vDept_row_record;
  while (c%found) loop --while循环用found
    dbms_output.put_line(vDept_row_record.dname);
    fetch c into vDept_row_record;
  end loop;
  close c;
end;

例：for循环游标

PL/SQL 语言提供了游标 FOR 循环语句，自动执行游标的 OPEN 、 FETCH 、 CLOSE语句和循环语句的功能；

 

declare
  cursor c
  is
  select * from dept;
  vDept_row_record c%rowtype;
begin
  for vDept_row_record in c loop
    dbms_output.put_line(vDept_row_record.dname);
  end loop;
end;

 

3，隐式游标

产生原因：

PL/SQL为所有SQL数据操纵语句（包括返回一行的查询）隐式声明游标，
(1)INSERT、UPDATE、DELETE语句
(2)SELECT查询结果为一条

不能对一个SQL游标(隐式游标)显式的执OPEN， CLOSE和FETCH语句,Oracle会自动的为隐式游标 --打开SQL游标、处理SQL游标、然后再关闭该游标

隐式游标的属性名虽然与显式游标的属性名一致，可含义不尽相同，请大家仔细区分：
%FOUND 如果DML语句影响一行或多行，则与该条语句配套的隐式游标的%FOUND属性返回TRUE，否则返回FALSE
%NOTFOUND 与%FOUND相反
%ROWCOUNT 统计DML语句返回的行数
%ISOPEN 隐式游标关联的SQL语句执行完后，ORACLE自动关闭该游标。因此，%ISOPEN属性的值始终为FALSE

例：

DECLARE

V_deptno emp.deptno%TYPE :=&p_deptno;

BEGIN

       DELETE FROM emp WHERE deptno=v_deptno;

       IF SQL%NOTFOUND THEN

              DELETE FROM dept WHERE deptno=v_deptno;

       END IF;

END;

 

4，动态游标（REF CURSOR）

ref游标是在运行的时候加载结果集
定义格式：TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]   cursor   ref_cursor_name 

分成两种：

强类型（限制）（Strong REF CURSOR），规定返回类型

弱类型（非限制）（Weak REF CURSOR），不规定返回类型，可以获取任何结果集。

 

例：强类型（指定了retrun 类型，CURSOR变量的类型必须和return 类型一致。）

 

declare 
 --声明记录类型 
 type emp_job_rec is record( 
  employee_id number, 
  employee_name varchar2(50), 
  job_title varchar2(30) 
 ); 
 --声明REF CURSOR，返回值为该记录类型 
 type emp_job_refcur_type  is  ref  cursor  return emp_job_rec; 
 --定义REF CURSOR游标的变量 
 emp_refcur  emp_job_refcur_tpe; 
 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_line(emp_job.employee_name || '''s job is '); 
  dbms_output.put_line(emp_job.job_title); 
  fetch emp_refcur into emp_job; 
 end loop; 
end; 

 

例：（网上找到的，复杂点的）

 

 

CREATE OR REPLACE PACKAGE emp_data AS

TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; 
--定义Strong REF CURSOR

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); 
--根据不同的choice选择不同的CURSOR

PROCEDURE retrieve_data(choice INT); 
--通过调用procedure open_emp_cv，返回指定的结果集。

END emp_data; 

 

CREATE OR REPLACE PACKAGE BODY emp_data AS

 

PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS 
--emp_cv作为传入/传出的CURSOR PARAMETER

BEGIN

IF choice = 1 THEN

OPEN emp_cv FOR  SELECT * FROM emp WHERE empno < 7800;

ELSIF choice = 2 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE SAL < 1000;

ELSIF choice = 3 THEN

OPEN emp_cv  FOR SELECT * FROM emp WHERE ename like 'J%';

END IF;

END;

 

PROCEDURE retrieve_data(choice INT) IS

return_cv empcurtyp; 
--定义传入open_emp_cv的CURSOR变量

return_row emp%ROWTYPE;

invalid_choice EXCEPTION;

BEGIN

--调用 procedure OPEN_EMP_CV 
open_emp_cv(return_cv, choice); 
dt>

 

IF choice = 1 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less t han7800');

ELSIF choice = 2 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');

ELSIF choice = 3 THEN

DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');

ELSE

RAISE invalid_choice;

END IF;

 

LOOP

FETCH return_cv   INTO return_row;

EXIT WHEN return_cv%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||

return_row.sal);

END LOOP;

 

EXCEPTION

WHEN invalid_choice THEN

DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');

END; 
 

END emp_data;

使用Weak REF CURSOR例子：

 

create or replace procedure open_cv(choice IN INT, return_cv OUT SYS_REFCURSOR) is

--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义
/*使用SYS_REFCURSOR可以使变量return_cv跟任何weak REF CURSOR类型匹配。  */

begin

if choice = 1 then

open return_cv for 'select * from emp';

elsif choice = 2 then

open return_cv for 'select * from dept';

end if;

end open_cv;

 

 

CREATE or replace procedure retrieve_data(choice IN INT) is

emp_rec emp%rowtype;

dept_rec dept%rowtype;

return_cv SYS_REFCURSOR;

invalid_choice exception; 
 

BEGIN

if choice=1 then

dbms_output.put_line('employee information');

open_cv(1,return_cv); --调用procedure open_cv;

loop

fetch return_cv into emp_rec;

exit when return_cv%notfound;

dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);

end loop;

elsif choice=2 then

dbm_output.put_line('department information');

open_cv(2,return_cv);
 

loop

fetch return_cv into dept_rec;

exit when return_cv%notfound;

dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);

end loop;

else

raise invalid_choice;
end if;

 

exception

when invalid_choice then

dbms_output.put_line('The CHOICE should be one of 1 and 2!');

when others then

dbms_output.put_line('Errors in procedure retrieve_data');

END retrieve_data;

 

附加：

用REF CURSOR实现BULK功能

1. 可以加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2. 加速SELECT，用BULK COLLECT INTO 来替代INTO。

 

create or replace procedure REF_BULK is

/*  定义复杂类型 */
type empcurtyp  is ref cursor; 

type idlist  is table of emp.empno%type;

type namelist  is table of emp.ename%type;

type sallist  is table of emp.sal%type;

  /* 定义变量  */

emp_cv  empcurtyp;

ids  idlist;

names namelist;

sals sallist;

row_cnt number;

begin

open emp_cv for select empno, ename, sal from emp;

fetch emp_cv  BULK COLLECT  INTO ids, names, sals; 
--将字段成批放入变量中，此时变量是一个集合

close emp_cv;

 

for i in ids.first .. ids.last loop

dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));

end loop;

 

FORALL  i  IN  ids.first .. ids.last 
insert into tab2 values (ids(i), names(i), sals(i));

commit;

select count(*) into row_cnt from tab2;

dbms_output.put_line('-----------------------------------');

dbms_output.put_line('The row number of tab2 is ' || row_cnt);

end REF_BULK;

 

最后：cursor 和 ref cursor的区别：

rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。

ref cursor可以返回给客户端，cursor则不行。

cursor可以是全局的global ，ref cursor则必须定义在过程或函数中。

ref cursor可以在子程序间传递，cursor则不行。

cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。

 

 

低头做事，抬头做人。