游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。
例如，下面是一个简单的游标实例，其功能是检索数据库中所有学生的名和姓。
DECLARE
v_FirstName VARCHAR2(20);
v_LastName VARCHAR2(20);
--游标声明
--返回行
CURSOR c_Students IS
SELECT first_name, last_name
FROM students;
BEGIN
-- 启动游标进程
OPEN c_Students;
LOOP
-- 获取一行
FETCH c_Students INTO v_FirstName, v_LastName;
-- Exit the loop after all rows have been retrieved.
EXIT WHEN c_Students%NOTFOUND;
/* 数据过程 */
END LOOP;
-- 过程结束
CLOSE c_Students;
END;
6.3.1 游标创建
Oracle使用工作区执行SQL语句，存储处理信息，PL/SQL可以构造游标，命名工作区，访问存储的信息。PL/SQL用游标来管理SQL的SELECT语句。游标是为处理这些语句而分配的一大块内存。有时用户手工定义游标。游标定义类似于其他PL/SQL变量，并且必须遵守同样的命名规则。在本节中，我们将介绍显式游标（Explicit Cursor）和隐式游标（Implicit Cursor）。显式游标要声明（Declare），在使用前要打开（Open），使用完毕要关闭（Close）。使用隐式游标时，用户无须执行上述步骤，只要简单地编码SELECT语句并让PL/SQL根据需要处理游标即可。
与循环结构结合的显式游标处理返回多于一行的SELECT语句。与循环结合的游标将允许每次处理一行。当SELECT语句预计只返回一行时，隐式游标将做得更好。
显式游标是作为declare段中的一部分进行定义的。所定义的SQL语句必须只包含SELECT语句，并且不能用INSERT、UPDATE或DELETE关键字。当SELECT语句可能返回零或多于一行时，必须用显式游标。本节将介绍如下内容。
l 命名自己的显式游标。
l 准备（或打开）一个显式游标。
l 使用显式游标取数据。
l 用完后释放游标所占内存。
1．显式游标
在使用显式游标时，必须编写4部分代码。
l 在PL/SQL块的declare段中定义游标。
l 在PL/SQL块中初始begin后打开游标。
l 取游标到一个或多个变量中，在接收游标的fetch语句中，接收变量的数目必须与游标select列表中的表列数目一致。
l 使用游标后要关闭。
下面是一个游标定义实例，综合了以上所述的4个部分：
…
…
declare
fname varchar2(10);
lname varchar2(30);
ssec_num varchar2(8);
cursor region_cur is
select first_name,last_name,ssn
from person
where region_number = region_number_in;
begin
open region_cur;
fetch region_cur into fname,lname,ssec_num;
while region_cur%found
loop
if ssec_num is null then
insert into e_msg values(pin_in,'no ssnum');
else
insert into e_tab values(pin_in,sysdate);
end if;
fetch region_cur into fname,lname,ssec_num;
end loop;
close region_cur;
end;
…
…
/
关于显式游标要注意下述几点。
（1）用“％found”和“％notfound”检验游标（此处为“mycur”）成功与否。如果游标按照其选择条件从数据库中查询出一行数据，则返回成功，该测试必须在游标关闭前执行。
if mycur%found then
…
end if;
if mycur%notfound then
…
…
end if;
…
…
fetch mycur into temp_buffer;
close mycur;
- -因为游标已被关闭，下面的语句不被执行。
if mycur%found then
…
…
end if;
（2）循环执行游标取数操作时，检索出的总数据行数存放在系统变量“％rowcount”中。
while counter<100 loop
fetch mycur into temp_buffer;
if mycur%rowcount <=50 then
…
else
…
end if;
counter := counter+1;
end loop;
（3）所有游标必须被取至一个或多个变量（取决于游标select列表中的列数）。下面的语句是非法的：
open mycur;
fetch mycur;
if mycur%found then
…
（4）游标的目标变量必须与游标select表中表列的数据类型一致：
--以下用法正确
--
declare
cursor mycur is
select pin, / *pin是数字型* /
last_name /*Last_name是字符型* /
from person
where pin = pin_in;
field1 varchar2(10);
field2 number;
begin
open mycur;
fetch mycur into field2,field1 ;
…
--以下用法不正确，因为变量数据类型同游标Select列表中表列的类型不一致
--
declare
cursor mycur is
select pin, / *pin是数字型* /
last_name /*last_name是字符型数据*/
from person
where pin = pin_in;
field1 varchar2(10);
field2 number;
begin
open mycur;
fetch mycur into field1,field2;
…
（5）如果试图打开一个已打开的游标或关闭一个已关闭的游标，将会出现错误。因此用户在打开或关闭游标前，若不清楚其状态，应该用“%isopen”进行检查。根据其返回值为true或false，采取相应的动作。
…
…
if mycur%isopen then
null;
else
open mycur;
end if;
如果一个PL/SQL块中用了多个游标，那么每个游标的名称必须唯一。
2．隐式游标
下述代码段使用了隐式游标。如果把select语句直接安排在行中，那么PL/SQL会隐含地处理游标定义。在declare段中无隐式游标说明。
…
begin
if counter >= 20 then
select last_name
into lname
from person
where pin = pin_in;
else
…
end if;
…
end;
/
使用隐式游标要注意以下几点。
（1）每个隐式游标必须有一个into。
- -以下不正确
if this_value > 0 THEN
select count(*) from person;
end if;
- -以下正确
if this_value > 0 then
select count(*) into cnter from person;
end if;
（2）和显式游标一样，带有关键字“into”接收数据的变量时数据类型要与表列的一致。
（3）隐式游标一次仅返回一行，使用时必须检查表给出的异常。最常见的异常为“no_data_found”和“too_many_rows”。
…
if counter>=10 then
begin
select age
into v_age
from person
where pin =pin_value;
exception
when too_many_rows then
insert into taba values(pin_value,sysdate);
when no_data_found then
end;
end if;
…
…
/
推荐使用显式游标，因为它更有效，根据如下。
l 通过检查PL/SQL的系统变量“％found”或“％notfound”确认成功或失败。使用显式游标的代码段简单地检测这些系统变量以确定使用显示游标的select语句成功或失败。
l 显式游标是在declare段中由人工定义的，这样PL/SQL块的结构化程度更高，因为定义和使用分离开了。
l 游标的FOR循环减少了代码的数量，更容易按过程化进行处理。
6.3.2 使用游标FOR循环
这部分要求用户忽视刚刚了解的显式游标。游标FOR循环的优点是用户不需要打开游标、取数据及测试数据的存在（%found）和关闭游标或定义存放数据的变量。相同之处是在声明部分中的游标定义。当游标被调用时，用SELECT语句中的同样一些元素创建一条记录。对于由游标检索出的每一行继续执行循环内的全部代码，当没有数据发现时，游标自动关闭。该方法要求以最少的代码得到很少游标结构错误的结果。
前面的例子用游标的FOR循环重写如下：
…
…
declare
cursor region_cur is
select first_name,last_name,ssn
from person
where region_number = region_number_in;
begin
for region_rec in region_cur
loop
- -请注意：在select语句中所有列自动地产生record_name.column_name
- - (即:region_rec.last_name)
if region_rec.ssn is null then
insert into e_msg values(pin_in,'no ssnum');
else
insert into e_tab values(pin_in,sysdate);
end if;
end loop;
end;
…
…
/
考虑下面的PL/SQL块，用于计算结果，并将其存入到临时表中，FOR循环指示c1_rec是隐士声明的，作为一个记录，其域用来存储从游标c1提取出来的所有列值：
-- available online in file ‘examp7’
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;
当输入游标FOR循环的时候，游标名已经不输入一个使用OPEN贷款的游标。在每个FOR循环交互的时候，PL/SQL将数据提取到隐士声明的记录中，这个记录只能在循环内部定义，不能在循环外面参考它的域。
我们不需要声明一个游标，因为PL/SQL能够替换子查询，下面的游标FOR循环计算奖金后，将结果插入到表中：
DECLARE
bonus REAL;
BEGIN
FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP
bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);
INSERT INTO bonuses VALUES (emp_rec.empno, bonus);
END LOOP;
COMMIT;
END;
我们可以为游标FOR循环中的表达式值定义别名，使用隐士声明记录的域保存从最近提取的列值。这个域与对应的SELECT查询列中的列名一致，但是如果选择项是一个表达式，则会发生什么样的情况？考虑下面的示例：
CURSOR c1 IS
SELECT empno, sal+NVL(comm,0), job FROM ...
在这种情况下，必须为所选择的项目定义一个别名，在下面的示例中，wages就是所选项目sal+NVL(comm,0)的别名。
CURSOR c1 IS
SELECT empno, sal+NVL(comm,0) wages, job FROM…
为了引用相应的域，可以使用别名：
IF emp_rec.wages < 1000 THEN…
在游标FOR循环中，可以传递参数给游标。在下面的示例中，传递了部门号作为游标参数：
-- available online in file ‘examp8’
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
‘Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;
6.3.3 使用游标变量
与游标类似，游标变量指向多行查询结果集的当前行，但是游标与游标变量是不同的，类似常量和变量之间的关系一样。游标是静态的，游标变量是动态的，因为它并不与特定的查询绑定在一起。可以为任何兼容的查询打开游标变量，从而提供更好的灵活性。而且，可以将新的值赋予游标变量，将它作为参数传递给本地和存储子程序。
游标变量在每个PL/SQL客户都是可用的，可以在PL/SQL主环境中（如OCI和Pro*C程序中）声明游标变量，然后作为输入变量传递给PL/SQL。而且，应用开发工具，如Oracle Forms和Oracle Report，包含PL/SQL引擎，可以在客户端完全使用游标变量。
Oracle服务器同样包含PL/SQL引擎，因此可以将游标变量在应用和服务器之间进行传递。
1．游标变量
游标变量就像C和Pascal指针一样，保存某个项目的内存位置，而不是项目本身。因此，声明游标实质是创建一个指针，而不是项目。在PL/SQL中，指针具有数据类型REF X，REF是REFERENCE，X表示类对象。因此，游标变量具有数据类型REF CURSOR。
为了执行多行查询，Oracle打开为命名工作域，存储处理信息，为了访问这些信息，可以使用显示游标，它对工作区命名，或者使用游标变量，指向工作区。游标总是指向相同的查询工作区，游标变量能够指向不同的工作区，因此，游标和游标变量不能互操作。
2．使用游标变量
使用游标变量在PL/SQL存储子程序和大量的客户之间传递查询结果集。PL/SQL和任何客户都不拥有一个结果集；它们只是共享一个查询工作区，在这个工作区中存储了查询结果。例如，对于OCI客户、Oracle Form应用和Oracle服务器都能指向相同的工作区。
只要任何游标执行查询工作区的时候，它就可以被访问，因此可以从在一个范围到另一个范围之间传递游标变量的值。例如，如果将主游标变量嵌套在Pro*C程序的PL/SQL中，则游标指向的工作区将能够被访问。
3．定义REF CURSOR类型
创建游标变量包含两个步骤，第一个是定义REF CURSOR类型，然后声明这种类型的游标变量，我们可以在PL/SQL块、子程序和包中定义REF CURSOR类型，语法如下：
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
其中ref_type_name是在游标变量中使用的类型，return_type必须表示一个记录或者是数据库表的一行。在下面的示例中，我们定义了返回类型，表示数据库表dept的一行：
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR类型可以是strong，也可以是weak。在下面的示例中，一个强REF CURSOR类型定义了返回类型，但是弱定义没有：
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
4．声明游标变量
一旦定义了REF CURSOR类型，就可以在PL/SQL块或者子程序中声明游标变量，例如：
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable
在REF CURSOR类型定义的RETURN字句中，可以使用%ROWTYPE定义记录类型，表示强类型的游标变量返回的行，代码如下：
DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
tmp_cv TmpCurTyp; -- declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; -- declare cursor variable
同样，可以使用％TYPE提供记录变量的数据类型，格式如下：
DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; -- declare cursor variable
最后，在RETURN字句中定义了用户自定义的RECORD类型：
DECLARE
TYPE EmpRecTyp IS RECORD (
empno NUMBER(4),
ename VARCHAR2(1O),
sal NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable
我们还可以声明游标变量，作为函数和过程的参数，在下面的示例中，我们定义了REF CURSOR 类型EmpCurTyp，然后声明这种类型的游标变量，作为过程的参数：
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...
5．控制游标变量
可以使用3种语句控制游标变量：OPEN-FOR、FETCH和CLOSE。首先使用OPEN打开游标变量，然后使用FETCH从结果集中提取行，当所有的行都处理完毕后，使用CLOSE关闭游标变量。
OPEN-FOR语句与多行查询的游标相关联，执行查询并标识结果集，下面是具体的语法：
OPEN {cursor_variable | :host_cursor_variable} FOR
{ select_statement
| dynamic_string [USING bind_argument[, bind_argument]...] };
在下面的示例中，首先打开游标变量emp_cv。注意可以应用游标属性(%FOUND, %NOTFOUND, %ISOPEN, 和 %ROWCOUNT)到游标变量。
IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM emp;
END IF;
其他的OPEN-FOR语句可以为不同的查询打开相同的游标变量，因此在重新打开之前，不要关闭游标变量。可以打开游标，将它作为参数传给存储过程。例如，下面的示例表示一个打包存储过程打开游标变量emp_cv：
CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
CREATE PACKAGE BODY emp_data AS
...
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
END emp_data;
当声明一个游标变量，作为打开游标变量子程序的参数时，必须定义IN OUT模式。也就是说，子程序可以将一个打开的游标传给调用者。
可以使用单独的过程打开游标变量，在不同的包中定义REF CURSOR类型，然后在单独的过程中引用这种类型。例如，如果创建了下面的包体，就可以创建一个引用它所定义的类型的独立过程：
CREATE PACKAGE cv_types AS
TYPE GenericCurTyp IS REF CURSOR;
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
...
END cv_types;
在下面的示例中，可以创建一个独立的过程，引用REF CURSOR类型empCurTyp，其是在cv_types中定义的：
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
为了集中对数据的提取，可以将兼容的查询分组，在下面的示例中，包过程调用了一个选择因子作为参数。在调用的时候，过程打开游标变量emp_cv：
CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
END IF;
END;
END emp_data;
为了更灵活，可以将游标变量和选择因子作为参数传给存储过程，例如：
CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
CREATE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END admin_data;
可以在PL/SQL主环境中声明游标变量，为了使用游标变量，必须将其作为主变量传递给PL/SQL。下面的Pro*C示例，将主游标变量和选择因子传给PL/SQL块：
EXEC SQL BEGIN DECLARE SECTION;
...
/* Declare host cursor variable. */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
IF :choice = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :choice = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :choice = 3 THEN
OPEN :generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END-EXEC;
主游标变量域的任何查询返回类型都是兼容的，它们就像弱类型的PL/SQL游标变量一样。
使用FETCH语句从多行查询的结果集中提取行，语法格式如下：
FETCH {cursor_variable_name | :host_cursor_variable_name}
[BULK COLLECT]
INTO {variable_name[, variable_name]... | record_name};
在下面的示例中，一次提取一行：
LOOP
/* Fetch from cursor variable. */
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
END LOOP;
使用BULK COLLECT字句，可以从游标变量中提取多行，放入一个或者多个集合中，例如：
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
emp_cv EmpCurTyp;
names NameList;
sals SalList;
BEGIN
OPEN emp_cv FOR SELECT ename, sal FROM emp;
FETCH emp_cv BULK COLLECT INTO names, sals;
...
END;
在使用完游标变量以后，需要管理游标变量，语法格式如下：
CLOSE {cursor_variable_name | :host_cursor_variable_name};
在下面的示例中，当处理完最后一行以后，关闭游标变量：
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process data record
END LOOP;
/* Close cursor variable. */
CLOSE emp_cv;
6．将主游标变量传递给PL/SQL，减少网络通信量
当向PL/SQL传递游标变量的时候，可以通过将OPEN-FOR语句分组，来减少网络通信。例如，下面的PL/SQL块打开了5个游标变量：
/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
OPEN :dept_cv FOR SELECT * FROM dept;
OPEN :grade_cv FOR SELECT * FROM salgrade;
OPEN :pay_cv FOR SELECT * FROM payroll;
OPEN :ins_cv FOR SELECT * FROM insurance;
END;
在Oracle Form中是非常有用的，可以生成多块表单。
当传递主游标变量给PL/SQL块的时候，查询工作区可访问，例如：
BEGIN
OPEN :c1 FOR SELECT 1 FROM dual;
OPEN :c2 FOR SELECT 1 FROM dual;
OPEN :c3 FOR SELECT 1 FROM dual;
OPEN :c4 FOR SELECT 1 FROM dual;
OPEN :c5 FOR SELECT 1 FROM dual;
...
END;
赋予C1、C2、C3、C4和C5的游标能够正常工作，可以使用它们，使用完毕，将其释放即可：
BEGIN
CLOSE :c1;
CLOSE :c2;
CLOSE :c3;
CLOSE :c4;
CLOSE :c5;
...
END;
7．避免游标变量中的错误
如果游标变量包含一个强类型，它们必须具有相同的数据类型，在下面的示例中，即使游标变量具有相同的返回类型，赋值也会产生异常，因为它们具有不同的数据类型。
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (
emp_cv IN OUT EmpCurTyp,
tmp_cv IN OUT TmpCurTyp) IS
BEGIN
...
emp_cv := tmp_cv; -- causes 'wrong type' error
END;
如果我们尽力从不指向查询工作区的游标变量提取数据、关闭和应用游标属性，则PL/SQL将产生一个INVALID_CURSOR异常，可以使用两种方式指向查询工作区。
l 为查询打开一个游标变量；
l 将已经打开的主游标变量的值赋予游标变量。
下面的示例将显示这些方法是如何交互的：
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv1 EmpCurTyp;
emp_cv2 EmpCurTyp;
emp_rec emp%ROWTYPE;
BEGIN
/* The following assignment is useless because emp_cv1
does not point to a query work area yet. */
emp_cv2 := emp_cv1; -- useless
/* Make emp_cv1 point to a query work area. */
OPEN emp_cv1 FOR SELECT * FROM emp;
/* Use emp_cv1 to fetch first row from emp table. */
FETCH emp_cv1 INTO emp_rec;
/* The following fetch raises an exception because emp_cv2
does not point to a query work area yet. */
FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR
EXCEPTION
WHEN INVALID_CURSOR THEN
/* Make emp_cv1 and emp_cv2 point to same work area. */
emp_cv2 := emp_cv1;
/* Use emp_cv2 to fetch second row from emp table. */
FETCH emp_cv2 INTO emp_rec;
/* Reuse work area for another query. */
OPEN emp_cv2 FOR SELECT * FROM old_emp;
/* Use emp_cv1 to fetch first row from old_emp table.
The following fetch succeeds because emp_cv1 and
emp_cv2 point to the same query work area. */
FETCH emp_cv1 INTO emp_rec; -- succeeds
END;
在传递作为参数的游标变量时需要小心，如果实际的和正常的参数不兼容，则PL/SQL将会产生ROWTYPE_MISMATCH的异常。
在下面的Pro*C示例中，定义了REF CURSOR和返回类型emp%ROWTYPE，然后创建了一个引用新类型的独立过程，在PL/SQL块中，打开了主游标变量，用于查询dept表，最后将打开的游标变量作为参数传给了存储过程，由于实际返回类型与参数类型不兼容，因此PL/SQL将产生ROWTYPE_MISMATCH。
CREATE PACKAGE cv_types AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
...
END cv_types;
/
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
/
-- anonymous PL/SQL block in Pro*C program
EXEC SQL EXECUTE
BEGIN
OPEN :cv FOR SELECT * FROM dept;
...
open_emp_cv(:cv); -- raises ROWTYPE_MISMATCH
END;
END-EXEC;
6.3.4 游标变量实例
游标和游标变量的使用十分灵活，其功能也是十分强大的，下面就来介绍几个游标和游标变量的应用实例。
1．主表和细节表
考虑下面的存储过程，搜索主图书馆的数据库，查询数、日期和磁带。主表存储了title和category代码。3个细节表存储了与类别相关的信息。当调有的时候，过程搜索主表，使用相关的类别代码，提取OPEN-FOR语句，然后打开游标变量：
CREATE PACKAGE cv_types AS
TYPE LibCurTyp IS REF CURSOR;
...
END cv_types;
CREATE PROCEDURE find_item (
title VARCHAR2(100),
lib_cv IN OUT
cv_types.LibCurTyp)
AS
code BINARY_INTEGER;
BEGIN
SELECT item_code FROM titles INTO code
WHERE item_title = title;
IF code = 1 THEN
OPEN lib_cv FOR SELECT * FROM books
WHERE book_title = title;
ELSIF code = 2 THEN
OPEN lib_cv FOR SELECT * FROM periodicals
WHERE periodical_title = title;
ELSIF code = 3 THEN
OPEN lib_cv FOR SELECT * FROM tapes
WHERE tape_title = title;
END IF;
END find_item;
2．客户端PL/SQL块
在分支图书馆的客户端应用可以使用下面的PL/SQL块显示提取的信息。
DECLARE
lib_cv cv_types.LibCurTyp;
book_rec books%ROWTYPE;
periodical_rec periodicals%ROWTYPE;
tape_rec tapes%ROWTYPE;
BEGIN
get_title(:title); -- title is a host variable
find_item(:title, lib_cv);
FETCH lib_cv INTO book_rec;
display_book(book_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
BEGIN
FETCH lib_cv INTO periodical_rec;
display_periodical(periodical_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
FETCH lib_cv INTO tape_rec;
display_tape(tape_rec);
END;
END;
3．Pro*C程序
下面的Pro*C程序提示用户选择数据库表，打开游标变量，提取查询返回的行。
#include <stdio.h>
#include <sqlca.h>
void sql_error();
main()
{
char temp[32];
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger";
SQL_CURSOR generic_cv;
int table_num;
struct
{
int emp_num;
char emp_name[11];
char job_title[10];
int manager;
char hire_date[10];
float salary;
float commission;
int dept_num;
} emp_rec;
struct
{
int dept_num;
char dept_name[15];
char location[14];
} dept_rec;
struct
{
char emp_name[11];
char job_title[10];
float salary;
} bonus_rec;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :uid;
EXEC SQL ALLOCATE :generic_cv;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("/n1 = EMP, 2 = DEPT, 3 = BONUS");
printf("/nEnter table number (0 to quit): ");
gets(temp);
table_num = atoi(temp);
if (table_num <= 0) break;
EXEC SQL EXECUTE
BEGIN
IF :table_num = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :table_num = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :table_num = 3 THEN
OPEN :generic_cv FOR SELECT * FROM bonus;
END IF;
END;
END-EXEC;
for (;;)
{
switch (table_num)
{
case 1:
EXEC SQL FETCH :generic_cv INTO :emp_rec;
break;
case 2:
EXEC SQL FETCH :generic_cv INTO :dept_rec;
break;
case 3:
EXEC SQL FETCH :generic_cv INTO :bonus_rec;
break;
}
}
EXEC SQL CLOSE :generic_cv;
}
exit(0);
}
void sql_error()
{
在这里控制SQL错误
}
4．在SQL*Plus中操纵主变量
主变量是在主环境中声明的变量，可以传给一个或者多个PL/SQL程序，与其他任何变量一样使用。在SQL*Plus中，声明主变量使用VARIABLE命令，例如：
VARIABLE return_code NUMBER
SQL*Plus和PL/SQL都能引用主变量，SQL*Plus能够显示它的值。然而，在PL/SQL中引用主变量，必须带有冒号前缀，例如：
DECLARE
...
BEGIN
:return_code := 0;
IF credit_check_ok(acct_no) THEN
:return_code := 1;
END IF;
...
END;
为了在SQL*Plus中显示主变量的值，使用PRINT命令，格式如下：
SQL> PRINT return_code
RETURN_CODE
-----------
1
SQL*Plus数据类型REFCURSOR用来声明游标变量，能够从存储的子程序中返回查询结果，在下面的脚本中，声明了一个主变量REFCURSOR，使用SQL*Plus命令SET AUTOPRINT ON 自动显示查询结果。
CREATE PACKAGE emp_data AS
TYPE EmpRecTyp IS RECORD (
emp_id NUMBER(4),
emp_name VARCHAR2(10),
job_title VARCHAR2(9),
dept_name VARCHAR2(14),
dept_loc VARCHAR2(13));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc FROM emp, dept
WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno
ORDER BY empno;
END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv)
6.3.5 使用游标表达式
一个游标表达式返回一个嵌套游标，结果集中的每行都包含值加上子查询生成的游标。然而，单个查询能够从多个表中提取相关的值。可以使用嵌套循环处理这个结果集，首先提取结果集的行，然后是这些行中的嵌套游标。
PL/SQL支持使用游标表达式的查询，作为游标声明的一个部分，可以在动态SQL查询中使用游标表达式，语法格式如下：
CURSOR ( subquery )
嵌套游标在包含的行从父游标中提取的时候被打开。只有在下面的情形下，嵌套游标才被关闭。
l 嵌套游标被用户显示关闭；
l 父游标被重新执行；
l 父游标被关闭；
l 父游标被取消；
l 在提取父游标一行的时候出现错误。
下面是一个嵌套游标表达式的示例，在这个例子中，定义了位置ID，和一个游标，能够从中提取该位置的所有部门。在提取部门名称的时候，还可以获得另一个游标，使我们能够从另一个表中提取它们的雇员信息。
CREATE OR REPLACE procedure emp_report(p_locid number) is
TYPE refcursor is ref cursor;
-- The query returns only 2 columns, but the second column is
-- a cursor that lets us traverse a set of related information.
CURSOR c1 is
SELECT l.city,
CURSOR(SELECT d.department_name,
CURSOR(SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id) as ename
FROM departments d where l.location_id = d.location_id) dname
FROM locations l
WHERE l.location_id = p_locid;
loccur refcursor;
deptcur refcursor;
empcur refcursor;
V_city locations.city%type;
V_dname departments.department_name%type;
V_ename employees.last_name%type;
BEGIN
OPEN c1;
LOOP
FETCH C1 INTO v_city, loccur;
EXIT WHEN c1%notfound;
-- We can access the column C1.city, then process the results of
-- the nested cursor.
LOOP
FETCH loccur INTO v_dname, deptcur; -- No need to open
EXIT WHEN loccur%notfound;
LOOP
FETCH deptcur into v_ename; -- No need to open
EXIT WHEN deptcur%notfound;
DBMS_OUTPUT.PUT_LINE(v_city ||' '||v_dname||' '||v_ename);
END LOOP;
END LOOP;
END LOOP;
close c1;
END;
/
1219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
