44.8. 游标
Prev UpChapter 44. pl/sql(兼容Oracle PL/SQL) — SQL过程化语言Home Next

44.8. 游标

44.8.1. 隐式游标
44.8.2. 显式游标
44.8.3. 处理查询结果集
44.8.4. 游标变量

可以设置一个封装查询的游标，然后一次读取几行查询结果，而不是立即执行整个查询。这样做的一个原因是避免在结果包含大量行时出现内存溢出。(然而，pl/sql用户通常不需要担心这个问题，因为FOR循环在内部自动使用游标以避免内存问题。)更有趣的用法是返回一个函数创建的游标的引用，允许调用者读取行。这提供了一种从函数返回大型行集的有效方式。

游标是指向存储有关处理特定SELECT或DML语句的信息的私有SQL区域的指针。

由pl/sql构建和管理的游标是隐式游标。您构建和管理的游标是显式游标。您可以从其属性获取有关任何会话游标的信息(您可以在过程语句中引用这些属性，但不能在SQL语句中引用)。

44.8.1. 隐式游标

隐式游标是由pl/sql构建和管理的会话游标。每次运行SELECT或DML语句时，pl/sql都会打开一个隐式游标。您无法控制隐式游标，但可以从其属性获取信息。

隐式游标将SELECT查询的结果集给一个RECORD变量， 这个变量名除了支持identifier标识符以外，新增支持两个关键字INNER和OUTER。

举例，隐式游标获取结果集的RECORD变量名为INNER： 

create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');

DECLARE
  CURSOR c_a IS
     select * from record_filed;
  zqdm_p  record_filed.id%TYPE;
BEGIN
FOR inner IN c_a LOOP
  zqdm_p := inner.id;
end loop;
end;
/

举例，隐式游标获取结果集的RECORD变量名为OUTER： 

create table record_filed(id int, zqdm varchar(100));
insert into record_filed values(12,'hello world!');

DECLARE
  CURSOR c_a IS
     select * from record_filed;
  zqdm_p  record_filed.id%TYPE;
BEGIN
FOR outer IN c_a LOOP
  zqdm_p := outer.id;
end loop;
end;
/

值得注意的是：

  • OUTER为非保留关键字，可以作为表名、列名、函数名、类型名等， INNER为函数名类型名关键字，只能作为函数名;

  • 类型名(具体指联合数组、可变数组和嵌套表）和FOR循环变量的名字相同时，报错;

  • 已创建 OUTER同名的对象后， 不允许再创建OUTER类型。

与之关联的语句运行后，隐式游标会关闭;但是，其属性值仍然可用，直到运行另一个SELECT或DML语句。

最近运行的SELECT或DML语句可能处于不同的作用域中。为了保存属性值以供以后使用，请立即将其分配给本地变量。否则，其他操作，例如子程序调用，可能会在您测试之前更改属性的值。

44.8.1.1. SQL%ISOPEN属性：游标是否打开？

SQL%ISOPEN总是返回FALSE，因为一个隐式游标在关联的语句执行完后总是会关闭。

44.8.1.2. SQL%FOUND属性：是否有任何行受到影响？

SQL%FOUND返回：

  • 如果没有SELECT或DML语句运行，则返回NULL，

  • 如果SELECT语句返回了一个或多个行或DML语句影响了一个或多个行，则返回TRUE，

  • 否则返回FALSE。

使用 SQL%FOUND 来确定 DELETE 语句是否影响了任何行。例如： 

create table employees(salary int, job_id varchar(100), employee_id int, last_name varchar(100), first_name varchar(100), department_id int, hire_date date);
create table departments(department_id int, department_name varchar(100), location_id int, staff int);
create table locations(country_id varchar(100),city varchar(100),location_id int);
create table jobs(job_title varchar(100), job_id varchar(100));

insert into employees values (12308, 'SA_REP', 100, 'huaxiang', 'zhao', 2345, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'));
insert into employees values (12301, 'AD_FG', 12301, 'huahai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'SA_REP', 12302, 'huaduo', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'S[HT]_CLERK', 12302, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12304, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12305, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12306, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12307, 'AD_FG', 100, 'huashuai', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12308, 'AD_MAN', 100, 'huaxiang', 'zhao', 2345, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));

insert into departments values (110, 'asd', 1700, 2345);
insert into departments values (2345, 'asd', 1800, 2345);
insert into departments values (110, 'asd', 1900, 2345);
insert into departments values (110, 'AD_FG', 12302, 2345);

insert into locations values ('JP', 'Japan', 1700);
insert into locations values ('Toronto', 'Canada', 1800);
insert into locations values ('WH', 'Whitehorse', 1900);

insert into jobs values ('sdadf', 'AD_FG');

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
  SELECT * FROM departments;
 
DECLARE
  dept_no NUMBER := 12;
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
 
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Delete succeeded for department number ' || dept_no);
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/

44.8.1.3. SQL%NOTFOUND Attribute: Were Any Rows Affected?

SQL%NOTFOUND （逻辑上与 SQL%FOUND 相反）返回：

  • 如果没有运行 SELECT 或 DML 语句，则返回 NULL。

  • 如果 SELECT 语句返回了一行或多行，或者 DML 语句影响了一行或多行，则返回 FALSE。

44.8.1.4. SQL%ROWCOUNT 属性：有多少行受到影响？

SQL%ROWCOUNT 返回：

  • 如果没有运行 SELECT 或 DML 语句，则返回 NULL。

  • 否则，返回 SELECT 语句返回的行数或被 DML 语句影响的行数（一个 INTEGER 类型）。

使用 SQL%ROWCOUNT 来确定被删除的行数。例如： 

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
  SELECT * FROM employees;

DECLARE
  mgr_no int := 123005;
  rowcount int := 0;
BEGIN
  DELETE FROM employees_temp WHERE employee_id = mgr_no;
  rowcount := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE ('Number of employees deleted: ' || rowcount);
END;
/

44.8.2. 显式游标

显式游标是一种会话游标，由您构造和管理。 您必须声明和定义一个显式游标，给它起一个名称并将其与一个查询关联（通常，查询会返回多行）。 然后，您可以通过以下两种方式之一处理查询结果集：

  • Open 显式游标，从结果集中 fetch 行，并 close 显式游标。

  • 在游标 FOR LOOP 语句中使用显式游标。

与 隐式游标 不同，您可以通过名称引用 显式游标 或游标变量。因此，显式游标或游标变量被称为命名游标。

44.8.2.1. 声明和定义 显式游标

您可以先声明显式游标，然后在同一块、子程序或包中稍后定义它，也可以同时声明和定义它。

显式游标声明，仅声明游标，其语法如下： 

CURSOR cursor_name parameter_list RETURN return_type;

显式游标定义 的语法如下： 

CURSOR cursor_name parameter_list RETURN return_type IS select_statement;

要实际使用显式游标，可以直接定义它而不声明它。

在下面的示例中，声明并定义了三个显式游标： 

DECLARE
  CURSOR c1 RETURN departments%ROWTYPE;    -- 声明 c1
 
  CURSOR c2 IS                             -- 声明并定义 c2
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > 2000; 
 
  CURSOR c1 RETURN departments%ROWTYPE IS  -- 定义 c1，
    SELECT * FROM departments              -- 重复返回类型
    WHERE department_id = 110;
 
  CURSOR c3 RETURN locations%ROWTYPE;      -- 声明 c3

  CURSOR c3 IS                             -- 定义 c3，
    SELECT * FROM locations                -- 省略返回类型
    WHERE country_id = 'JP';
BEGIN
  NULL;
END;
/

44.8.2.2. 打开和关闭显式游标

声明并定义了显式游标后，可以使用OPEN语句打开它。游标名忽略大小写。 定义的游标不带参数，打开时也可以使用游标名加()的形式。

下面是打开游标的几种方式: 

open C_tmp;
open c_tmp;
open c_tmp();

使用CLOSE语句关闭打开的显式游标，从而允许重用其资源。关闭游标后，无法从其结果集中获取记录或引用其属性。

44.8.2.3. 使用显式游标获取数据

打开显式游标后，可以使用FETCH语句获取查询结果集的行。返回一行的FETCH语句的基本语法如下： 

FETCH cursor_name INTO into_clause;

into_clause 是变量列表或单个记录变量。 对于查询返回的每一列，变量列表或记录必须有一个相应的类型兼容变量或字段。 在声明用于FETCH语句的变量和记录时，%TYPE和%ROWTYPE属性非常有用。

FETCH语句检索结果集的当前行，将该行的列值存储到变量或记录中，并将游标移动到下一行。

通常，您在LOOP语句中使用FETCH语句，当 FETCH语句运行完所有行时退出循环。 要检测此退出条件，请使用游标属性%NOTFOUND。当FETCH语句返回没有行时，PL/oraSQL不会引发异常。

使用FETCH和%NOTFOUND在LOOP语句中一次一行地检索两个显式游标的结果集。 第一个FETCH语句将列值检索到变量中，第二个FETCH语句将列值检索到记录中。 变量和记录分别使用%TYPE和%ROWTYPE声明。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
    ORDER BY last_name;

  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  v_jobid     employees.job_id%TYPE;     -- variable for job_id

  CURSOR c2 IS
    SELECT * FROM employees
    WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
    ORDER BY job_id;

  v_employees employees%ROWTYPE;  -- record variable for row of table

BEGIN
  OPEN c1;
  LOOP  -- Fetches 2 columns into variables
    FETCH c1 INTO v_lastname, v_jobid;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (RPAD(v_lastname, 25, ' ') || v_jobid);
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE ('-------------------------------------');

  OPEN c2;
  LOOP  -- Fetches entire row into the v_employees record
    FETCH c2 INTO v_employees;
    EXIT WHEN c2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id);
  END LOOP;
  CLOSE c2;
END;
/

这个操作通过使用五个FETCH语句， 将结果集的前五行数据分别装载到五个记录变量中。 这些记录变量使用%ROWTYPE进行声明。例如： 

DECLARE
  CURSOR c IS
    SELECT e.job_id, j.job_title
    FROM employees e, jobs j
    WHERE e.job_id = j.job_id AND e.employee_id = 100
    ORDER BY last_name;
 
  -- Record variables for rows of cursor result set:
 
  job1 c%ROWTYPE;
  job2 c%ROWTYPE;
  job3 c%ROWTYPE;
  job4 c%ROWTYPE;
  job5 c%ROWTYPE;
 
BEGIN
  OPEN c;
  FETCH c INTO job1;  -- fetches first row
  FETCH c INTO job2;  -- fetches second row
  FETCH c INTO job3;  -- fetches third row
  FETCH c INTO job4;  -- fetches fourth row
  FETCH c INTO job5;  -- fetches fifth row
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE (job1.job_title || ' (' || job1.job_id || ')');
  DBMS_OUTPUT.PUT_LINE (job2.job_title || ' (' || job2.job_id || ')');
  DBMS_OUTPUT.PUT_LINE (job3.job_title || ' (' || job3.job_id || ')');
  DBMS_OUTPUT.PUT_LINE (job4.job_title || ' (' || job4.job_id || ')');
  DBMS_OUTPUT.PUT_LINE (job5.job_title || ' (' || job5.job_id || ')');
END;
/

44.8.2.4. 显式游标查询中的变量

显式游标查询可以引用其作用域内的任何变量。 当您打开显式游标时，PL/oraSQL会评估查询中的任何变量，并在识别结果集时使用这些值。 后来更改变量的值不会改变结果集。

显式游标查询引用变量factor。 当游标打开时，factor的值为2。因此， sal_multiple始终为sal的两倍， 尽管在每次检索后都会增加factor的值。例如： 

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  CURSOR c1 IS
    SELECT salary, salary*factor FROM employees
    WHERE job_id LIKE 'AD_%';
 
BEGIN
  OPEN c1;  -- PL/SQL evaluates factor
 
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE ('sal          =  ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
    factor := factor + 1;  -- Does not affect sal_multiple
  END LOOP;
 
  CLOSE c1;
END;
/

显式游标查询中的变量-结果集更改。例如： 

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  CURSOR c1 IS
    SELECT salary, salary*factor FROM employees
    WHERE job_id LIKE 'AD_%';
 
BEGIN
  DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('sal          =  ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
  END LOOP;
  CLOSE c1;
 
  factor := factor + 1;
 
  DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
  OPEN c1;  -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('sal          =  ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
  END LOOP;
  CLOSE c1;
END;
/

44.8.2.5. 显式游标查询需要列别名时

当显式游标查询包括虚拟列（表达式）时， 如果符合以下任一条件，则该列必须有别名：

使用CLOSE语句可以关闭一个开启的显式游标， 从而允许其资源被重用。关闭游标之后，您不能再从其结果集中获取记录或引用其属性。 如果尝试这样做，PL/oraSQL会引发预定义的异常INVALID_CURSOR。

  • 如果您使用的游标是用%ROWTYPE声明的记录变量，则可以使用该游标将数据装载到该记录变量中。

  • 如果您想在程序中引用虚拟列，则可以使用该游标。

显式游标中的虚拟列需要为上述两个原因都提供别名。例如： 

DECLARE
  CURSOR c1 IS
    SELECT employee_id,
           (salary * .05) raise
    FROM employees
    WHERE job_id LIKE '%_MAN'
    ORDER BY employee_id;
  emp_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Raise for employee #' ||  emp_rec.employee_id || ' is $' || emp_rec.raise); 
  END LOOP;
  CLOSE c1;
END;
/

44.8.2.6. 接受参数的显式游标

您可以创建具有形式参数的显式游标， 然后每次打开游标时传递不同的实际参数。 在游标查询中，您可以在可以使用常量的任何位置使用形式游标参数。 在游标查询之外，您无法引用形式游标参数。

创建一个显式游标，其两个形式参数表示一个工作和其最高工资。 当使用指定的工作和最高工资打开游标时， 游标查询会选择那些薪资过高的职员（对于每个这样的职员，查询会选择其名字、姓氏和超支金额）。 接下来，该示例创建一个打印游标查询结果集的过程（关于过程的信息，请参阅PL/SQL子程序）。 最后，该示例使用一组实际参数打开游标，打印结果集，关闭游标，然后使用不同的实际参数打开游标，打印结果集并关闭游标。例如： 

DECLARE
  CURSOR c (job VARCHAR2, max_sal NUMBER) IS
    SELECT last_name, first_name, (salary - max_sal) overpayment
    FROM employees
    WHERE job_id = job
    AND salary > max_sal
    ORDER BY salary;
 
    last_name_   employees.last_name%TYPE;
    first_name_  employees.first_name%TYPE;
    overpayment_      employees.salary%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('----------------------');
  DBMS_OUTPUT.PUT_LINE ('Overpaid Stock Clerks:');
  DBMS_OUTPUT.PUT_LINE ('----------------------');
  OPEN c('ST_CLERK', 5000);
  LOOP
    FETCH c INTO last_name_, first_name_, overpayment_;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (last_name_ || ',' || first_name_ || ', (by' || overpayment_ || ')');
  END LOOP; 
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE ('----------------------');
  DBMS_OUTPUT.PUT_LINE ('Overpaid Sales Representatives:');
  DBMS_OUTPUT.PUT_LINE ('----------------------');
  OPEN c('SA_REP', 10000);
  LOOP
    FETCH c INTO last_name_, first_name_, overpayment_;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (last_name_ || ',' || first_name_ || ', (by' || overpayment_ || ')');
  END LOOP;
  CLOSE c;
END;
/

44.8.2.7. 具有默认值的形式游标参数

当您创建一个具有形式参数的显式游标时， 您可以为它们指定默认值。当形式参数具有默认值时， 相应的实际参数是可选的。 如果您在不指定实际参数的情况下打开游标， 那么形式参数具有默认值。 

parameter_name [IN] data_type [:= | default expression]

应用场景包括存储过程、函数、匿名块和包。 与Oracle相比，有一些差异。例如，在 data_type 中， Oracle 支持在游标参数字符串中声明 char， 但是 lightdb 的 char 长度默认值为 1， 因此对于长度超过 1 的字符串，您需要指定 char 长度，char(100)。 但对于可变长度字符类型，如 varchar、varchar2 等，使用方式与 Oracle 相同。 并且不支持 Oracle 类型：string、long、raw、long raw、rowid、timestamp with local time 等。 在包中不支持由 constant 定义的常量类型。

与 Oracle 相比，Expressions 也有一些差异。

  • 布尔表达式

    • 包支持返回布尔类型、布尔表达式和布尔字面量的函数（支持 NULL，Oracle 不支持），

    • 函数、存储过程和匿名块支持返回布尔类型、布尔常量、布尔变量、布尔表达式和布尔字面量的函数（支持 NULL，Oracle 不支持）。

  • 字符表达式

    • 包支持返回 characer 类型和 characer 字面量的函数，

    • 函数、存储过程和匿名块支持返回 characer 类型、characer 常量、characer变量、characer 表达式和 characer 字面量的函数。

  • 日期表达式

    • 包支持返回 date 类型和 date 字面量的函数，

    • 函数、存储过程和匿名块支持返回 date 类型、date 常量、date 变量、date 表达式和 date 字面量的函数。

  • 数值表达式

    • 包中不支持：collection、named_cursor、numberic_constant、numberic_variable、placeholder、SQL% cursor attribute、**（不支持的运算符），

    • 函数、存储过程和匿名块中不支持：collection、named_cursor、placeholder、SQL% cursor attribute、**（不支持的运算符）。

  • Case 表达式

    • 包、函数、存储过程和匿名块中支持 search_case_expression 和 simple_case_expression。

创建一个显式游标，其形式参数代表位置ID。参数的默认值是公司总部的位置ID。例如： 

DECLARE
  CURSOR c (location NUMBER DEFAULT 1700) IS
    SELECT d.department_name,
           e.last_name manager,
           l.city
    FROM departments d, employees e, locations l
    WHERE l.location_id = location
      AND l.location_id = d.location_id
      AND d.department_id = e.department_id
    ORDER BY d.department_id;
    dept_name  departments.department_name%TYPE;
    mgr_name   employees.last_name%TYPE;
    city_name  locations.city%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS AT HEADQUARTERS:');
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  OPEN c;
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE ('DEPARTMENTS IN CANADA:');
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  OPEN c(1800); -- Toronto
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')');
  END LOOP; 
  CLOSE c;
  OPEN c(1900); -- Whitehorse
  LOOP
    FETCH c INTO dept_name, mgr_name, city_name;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (dept_name || ' (Manager: ' || mgr_name || ')');
  END LOOP;
  CLOSE c;
END;
/

如果你向游标添加形式参数，并为这些参数指定了默认值，那么您无需更改对游标的现有引用。例如： 

DECLARE
  CURSOR c (job VARCHAR2, max_sal NUMBER,
            hired DATE DEFAULT TO_DATE('31-DEC-1999', 'DD-MON-YYYY')) IS
    SELECT last_name, first_name, (salary - max_sal) overpayment
    FROM employees
    WHERE job_id = job
    AND salary > max_sal
    AND hire_date > hired
    ORDER BY salary;
    last_name_   employees.last_name%TYPE;
    first_name_  employees.first_name%TYPE;
    overpayment_      employees.salary%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  DBMS_OUTPUT.PUT_LINE ('Overpaid Sales Representatives Hired After 2004:');
  DBMS_OUTPUT.PUT_LINE ('--------------------------------');
  OPEN c('SA_REP', 10000);
  LOOP
    FETCH c INTO last_name_, first_name_, overpayment_;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (last_name_ || ', (by ' || overpayment_ || ')');
  END LOOP;  
  CLOSE c;
END;
/

44.8.2.8. 显式游标属性

显式游标属性的语法是cursor_name，紧跟着属性（例如，c1%ISOPEN）。

使用CLOSE语句关闭一个打开的显式游标，从而允许其资源被重用。 关闭游标之后，您就无法从其结果集中提取记录或引用其属性。 如果尝试这样做，PL/oraSQL会引发预定义的异常INVALID_CURSOR。

44.8.2.8.1. %ISOPEN属性：游标是否打开？

如果显式游标处于打开状态，%ISOPEN会返回true，否则返回false。

仅当显式游标c1未打开时，才打开它；仅当它已打开时，才关闭它。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11;

  the_name employees.last_name%TYPE;
  the_salary employees.salary%TYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;

  FETCH c1 INTO the_name, the_salary;

  IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
END;
/

要使用ROWNUM，lightdb_dblevel_syntax_compatible_type的值必须为oracle。

44.8.2.8.2. %FOUND属性：是否有行受影响？

%FOUND 返回：

  • 在显式游标打开但在第一次提取之前返回NULL，

  • 如果最近一次从显式游标提取了一行，则返回TRUE，

  • 否则返回FALSE。

循环遍历结果集，打印每个提取的行，并在没有更多行可提取时退出。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;

  my_ename   employees.last_name%TYPE;
  my_salary  employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%FOUND THEN  -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE ('Name = ' || my_ename || ' salary = ' || my_salary);
    ELSE  -- fetch failed
      EXIT;
    END IF;
  END LOOP;
END;
/

44.8.2.8.3. %NOTFOUND属性：是否有行受影响？

%NOTFOUND (the logical opposite of %FOUND) returns:

  • 在显式游标打开但在第一次提取之前返回NULL，

  • 如果最近一次从显式游标提取了一行，则返回FALSE，

  • 否则返回TRUE。

当FETCH未返回行时，%NOTFOUND用于退出循环。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;
  rowcount int;
  name_1  employees.last_name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name_1;
    EXIT WHEN c1%NOTFOUND;
    rowcount := c1%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE (rowcount ||'. ' || name_1);
    IF c1%ROWCOUNT = 5 THEN
       DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

44.8.2.8.4. %ROWCOUNT属性：有多少行受到影响？

%ROWCOUNT returns:

  • 在显式游标打开但在第一次提取之前返回零，

  • 否则，返回提取的行数（一个INTEGER）。

对提取的行进行编号并打印，并在提取第五行后打印一条消息。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name FROM employees
    WHERE ROWNUM < 11
    ORDER BY last_name;

  name_1  employees.last_name%TYPE;
  rowcount int;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name_1;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
    rowcount := c1%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE (rowcount || '. ' || name_1);
    IF c1%ROWCOUNT = 5 THEN
       DBMS_OUTPUT.PUT_LINE ('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

44.8.3. 处理查询结果集

在PL/oraSQL中，与传统的数据库编程一样，您使用游标来处理查询结果集。 然而，在PL/oraSQL中，您可以使用隐式游标或显式游标。

前者需要的代码较少，但后者更加灵活。 例如，显式游标可以接受参数。

以下 pl/sql 语句使用 pl/sql 为您定义和管理的隐式游标：

  • SELECT INTO。

  • 隐式游标 FOR LOOP。

以下 pl/sql 语句使用显式游标：

  • 显式游标 FOR LOOP。

  • OPEN、FETCH 和 CLOSE。

44.8.3.1. 使用SELECT INTO语句处理查询结果集

使用隐式游标，SELECT INTO语句从一个或多个数据库表中检索值（就像SQL的SELECT语句一样）并将它们存储在变量中（这是SQL的SELECT语句所不具备的）。

44.8.3.1.1. 处理单行结果集

如果您希望查询仅返回一行，则使用SELECT INTO语句将该行的值存储在一个或多个标量变量，或一个记录变量中。

如果查询可能返回多行，但您只关心第n行，则可以使用WHERE ROWNUM=n子句将结果集限制为该行。

44.8.3.1.2. 处理大型多行结果集

如果您必须将大量的表数据分配给变量，LightDB建议使用带有BULK COLLECT子句的SELECT INTO语句。

44.8.3.2. 使用游标FOR LOOP语句处理查询结果集

游标FOR LOOP语句允许您运行SELECT语句，然后立即循环遍历结果集中的行。

这个语句可以使用隐式或显式游标（但不能使用游标变量）。

如果您只在游标的 FOR LOOP 语句中使用SELECT语句， 则应该在游标的 FOR LOOP 语句内部指定SELECT语句。 这称为隐式游标的FOR LOOP语句。 例如： 

BEGIN
  FOR item IN (
    select last_name, job_id
    FROM employees
    WHERE job_id LIKE '%CLERK%'
    AND employee_id > 120
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

为了兼容Oracle数据库，LightDB支持在隐式游标FOR LOOP语句中使用WITH子句。 例如： 

CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50));
INSERT INTO employees VALUES (1, 'John');
INSERT INTO employees VALUES (2, 'Jane');
INSERT INTO employees VALUES (3, 'Bob');

BEGIN
  FOR emp_rec IN (WITH employeetemp AS
                    ( SELECT employee_id AS eid, first_name AS fname FROM employees
                    ) SELECT eid,fname FROM employeetemp
                ) loop
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.eid || ', Name: ' || emp_rec.fname);
  END LOOP;
END;
/

显式游标FOR LOOP语句。例如： 

DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE job_id LIKE '%CLERK%' AND employee_id > 120
    ORDER BY last_name;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

传递参数给显式游标FOR LOOP语句。例如： 

DECLARE
  CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
    SELECT * FROM employees
    WHERE job_id = job
    AND salary > max_wage;
BEGIN
  FOR person IN c1('ST_CLERK', 3000)
  LOOP
     -- 处理数据记录
    DBMS_OUTPUT.PUT_LINE ('Name = ' || person.last_name || ', salary = ' || person.salary || ', Job id= ' || person.job_id);
  END LOOP;
END;
/

游标FOR LOOP引用虚拟列。例如： 

BEGIN
  FOR item IN (
    SELECT first_name || ' ' || last_name AS full_name,
           salary * 10                    AS dream_salary 
    FROM employees
    WHERE ROWNUM < 6
    ORDER BY dream_salary DESC, last_name ASC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE (item.full_name || ' dreams of making ' || item.dream_salary);
  END LOOP;
END;
/

44.8.3.3. 使用子查询处理查询结果集

如果你通过循环遍历查询结果集并为每行运行另一个查询来处理结果集， 那么你可以通过将第二个查询从循环内部删除并将其作为第一个查询的子查询来提高性能。

虽然普通的子查询对每个表进行评估， 但相关子查询对每一行进行评估。

父查询中FROM子句中的子查询。例如： 

DECLARE
  CURSOR c1 IS
    SELECT t1.department_id, t1.department_name, t2.staff
    FROM departments t1,
         ( SELECT department_id, COUNT(*) AS staff
           FROM employees
           GROUP BY department_id
         ) t2
    WHERE (t1.department_id = t2.department_id) AND t2.staff > 5
    ORDER BY t2.staff;

BEGIN
   FOR dept IN c1
   LOOP
     DBMS_OUTPUT.PUT_LINE ('Department = ' || dept.department_name || ', staff = ' || dept.staff);
   END LOOP;
END;
/

相关子查询。例如： 

DECLARE
  CURSOR c1 IS
    SELECT department_id, last_name, salary
    FROM employees t
    WHERE salary >= ( SELECT AVG(salary)
                     FROM employees
                     WHERE t.department_id = department_id
                   )
    ORDER BY department_id, last_name;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Making above-average salary =  ' || person.last_name);
  END LOOP;
END;
/

44.8.4. 游标变量

cursor变量类似于显式游标，但具有以下特点：

  • 它不限于一个查询。 您可以为一个查询打开一个游标变量，处理结果集，然后将游标变量用于另一个查询。

  • 您可以为它赋值。

  • 您可以在表达式中使用它。

  • 它可以是子程序参数。 您可以使用游标变量在子程序之间传递查询结果集。

  • 它可以是宿主变量。 您可以使用游标变量在PL/oraSQL存储的子程序和它们的客户端之间传递查询结果集。

  • 它无法接受参数。 您不能向游标变量传递参数，但可以将整个查询传递给它。 查询可以包括变量。

44.8.4.1. 创建游标变量

要创建游标变量，请声明一个预定义类型 SYS_REFCURSOR 的变量， 或者定义一个 REF CURSOR 类型，然后声明一个该类型的变量。

REF CURSOR 类型定义的基本语法如下： 

TYPE type_name IS REF CURSOR RETURN return_type%TYPE

游标变量声明。例如： 

DECLARE
  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE genericcurtyp IS REF CURSOR; 

  cursor1  empcurtyp;
  cursor2  genericcurtyp;
  my_cursor SYS_REFCURSOR;

  TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
  dept_cv deptcurtyp;  -- strong cursor variable
BEGIN
  NULL;
END;
/

具有用户定义返回类型的游标变量。例如： 

DECLARE
  TYPE EmpRecTyp IS RECORD (
    employee_id NUMBER,
    last_name VARCHAR2(25),
    salary   NUMBER(8,2));

  TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
  emp_cv EmpCurTyp;
BEGIN
  NULL;
END;
/

使用 CREATE DOMAIN 创建的游标类型的变量。例如： 

CREATE DOMAIN mycursor AS refcursor;
SELECT dbms_output.serveroutput(true);
DECLARE
  employee_cursor mycursor;

  v_employee_name VARCHAR2(50);
BEGIN
  OPEN employee_cursor for SELECT first_name FROM employees_curs WHERE employee_id = 1500;

  LOOP
    FETCH employee_cursor INTO v_employee_name;
    EXIT WHEN employee_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
  END LOOP;

  CLOSE employee_cursor;
END;
/

使用sys_refcursor作为参数变量。例如： 

CREATE TABLE customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,
    ca_street_name            varchar(60)                   ,
    ca_street_type            char(15)                      ,
    ca_suite_number           char(10)                      ,
    ca_city                   varchar(60)                   ,
    ca_county                 varchar(30)                   ,
    ca_state                  char(2)                       ,
    ca_zip                    char(10)                      ,
    ca_country                varchar(20)                   ,
    ca_gmt_offset             decimal(5,2)                  ,
    ca_location_type          char(20)                      ,
    PRIMARY KEY (ca_address_sk)
);

DECLARE
    i_id                   integer := 1;
    address_id             char(16);
    street_number          char(10);
    street_name            varchar(60) ;
    street_type            char(15);
    suite_number           char(10);
    city                   varchar(60);
    county                 varchar(30);
    state                  char(2);
    zip                    char(10);
    country                varchar(20);
    gmt_offset             decimal(5,2);
    location_type          char(20);
BEGIN
    -- insert
    address_id     := 'address_id';
    street_number  := '3588' ;
    street_name    := 'street_name';
    street_type    := 'street_type';
    suite_number   := '1';
    city           := 'Hang Zhou';
    county         := 'Bin Jiang';
    state          := 'CN';
    zip            := '000000';
    country        := 'China';
    gmt_offset     := 1.02;
    location_type  := 'location_type';

    INSERT INTO customer_address(ca_address_sk, ca_address_id, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type)
    VALUES (i_id, address_id, street_number, street_name, street_type, suite_number, city, county, state, zip, country, gmt_offset, location_type);
END;
/

CREATE OR REPLACE PROCEDURE getCustomerAddress(cur OUT sys_refcursor) IS
BEGIN
    OPEN cur FOR SELECT * FROM customer_address;
END;
/


DECLARE
    ad   sys_refcursor;
    res  customer_address%rowtype;
BEGIN
    getCustomerAddress(ad);

    LOOP
    FETCH ad INTO res;
    EXIT WHEN ad%NOTFOUND;
    dbms_output.put_line('ca_address_sk:' || res.ca_address_sk);
    dbms_output.put_line('ca_address_id:' || res.ca_address_id);
    dbms_output.put_line('ca_street_number:' || res.ca_street_number);
    dbms_output.put_line('ca_street_name:' || res.ca_street_name);
    dbms_output.put_line('ca_street_type:' || res.ca_street_type);
    dbms_output.put_line('ca_suite_number:' || res.ca_suite_number);
    dbms_output.put_line('ca_city:' || res.ca_city);
    dbms_output.put_line('ca_county:' || res.ca_county);
    dbms_output.put_line('ca_state:' || res.ca_state);
    dbms_output.put_line('ca_zip:' || res.ca_zip);
    dbms_output.put_line('ca_country:' || res.ca_country);
    dbms_output.put_line('ca_gmt_offset:' || res.ca_gmt_offset);
    dbms_output.put_line('ca_location_type:' || res.ca_location_type);
    END LOOP;
END;
/

44.8.4.2. 打开和关闭游标变量

您可以使用 OPEN FOR 语句打开游标变量，该语句执行以下操作：

  • 将游标变量与查询关联（通常情况下，查询返回多行）。 查询可以包含绑定变量的占位符，在 OPEN FOR 语句的 USING 子句中指定其值。

  • 分配数据库资源以处理查询。

  • 处理查询，即：

    • 识别结果集。 如果查询引用变量，则它们的值会影响结果集。

    • 如果查询具有 FOR UPDATE 子句，则锁定结果集中的行。

  • 将游标定位在结果集的第一行之前。

在for循环中，将select查询的结果给一个record类型， 这一操作也被称为隐式游标操作。 record类型中一个字段用来接收查询结果中的一个select查询语句 （update,delete,insert在这个语法中都会报错）， 这个字段被用作open for动态打开游标的对象。

LightDB不支持包括以下两点：

  • FOR和IN中间的格式可以为A或者A.B格式。

    举例，FOR和IN中间的格式可以为A： 

    select dbms_output.serveroutput('t');

create table employees(salary int,
    job_id varchar2(100), employee_id int,
    last_name varchar(100), first_name varchar(100),
    department_id int, hire_date date);

create table V2_text (
    object_name VARCHAR2(40),
    object_id VARCHAR2(8),
    sql_text VARCHAR2(1000)
);

create table orders (id int,org_id int,user_id int
    ,order_id int,customer_id int, order_date date, price int
    ,primary key (org_id, id),status varchar(20));

insert into employees values (12308, 'SA_REP', 100, 'huaxiang', 'zhao', 10, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'));
insert into employees values (12301, 'AD_FG', 12301, 'huahai', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));
insert into employees values (12302, 'SA_REP', 12302, 'huaduo', 'zhao', 10, TO_DATE('31-DEC-2004', 'DD-MON-YYYY'));

insert into orders values (12301, 100, 2346, 456043, 23490, TO_DATE('31-DEC-2005', 'DD-MON-YYYY'), 2000, 'Completed');

-- select
insert into V2_text (object_name, object_id, sql_text)
values ('EmployeeTable', 'OBJ0001', 'SELECT job_id FROM employees WHERE department_id = 10');

DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
END;
/

    举例，FOR和IN中间的格式可以为A.B： 

    declare
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_rec_type is record
    (ename varchar2(25),
     sql_text varchar2(10),
     sal number(7,2)
    );

    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec emp_rec_type
    );
    sys emp_record_type;
BEGIN
   FOR sys.rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      NULL;
   END LOOP;
END;
/

  • FOR之后的动态语句的格式，不支持多个复杂类型进行嵌套对变量的访问，只支持单个复杂类型。

    举例，支持单个复杂类型对变量的访问： 

    DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
BEGIN
   FOR rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
      OPEN dynamic_cur FOR rec.sql_text;
      -- Perform cursor operations
       fetch dynamic_cur bulk collect into rec_all_code;
        for i in 1..rec_all_code.count loop
           DBMS_OUTPUT.PUT_LINE('OBJECTNAME: ' ||rec_all_code(i));
        end loop;
      close dynamic_cur;
   END LOOP;
END;
/

    举例，不支持多个复杂类型进行嵌套对变量的访问： 

    DECLARE
    type t_dynamic_cursor is ref cursor;
    dynamic_cur t_dynamic_cursor;
    type t_inter_code_table is table of varchar2(128);
    rec_all_code t_inter_code_table;
    type emp_rec_type is record
    (ename varchar2(25),
     sql_text varchar2(100),
     sal number(7,2)
    );

    type emp_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec emp_rec_type
    );

    type empl_record_type is record
    (ename varchar2(25),
     job varchar2(10),
     sal number(7,2),
     rec_emp emp_record_type
    );
    sys empl_record_type;
BEGIN
   FOR sys.rec_emp.rec IN (
      SELECT object_name,object_id,sql_text FROM V2_text
   ) LOOP
    NULL;
   END LOOP;
END;
/

44.8.4.3. 使用游标变量提取数据

打开游标变量后，您可以使用 FETCH 语句获取查询结果集的行。 游标变量的返回类型必须与 FETCH 语句的 into_clause 兼容。

第一个OPEN FOR语句包含查询本身。 第二个OPEN FORi语句引用一个变量，其值是一个查询。 例如： 

DECLARE
  cv SYS_REFCURSOR;  -- cursor variable
 
  v_lastname  employees.last_name%TYPE;  -- variable for last_name
  v_jobid     employees.job_id%TYPE;     -- variable for job_id
 
  query_2 VARCHAR2(200) :=
    'SELECT * FROM employees
    WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')
    ORDER BY job_id';
 
  v_employees employees%ROWTYPE;  -- record variable row of table
 
BEGIN
  OPEN cv FOR
    SELECT last_name, job_id FROM employees
    WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
    ORDER BY last_name;
 
  LOOP  -- Fetches 2 columns into variables
    FETCH cv INTO v_lastname, v_jobid;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (RPAD(v_lastname, 25, ' ') || v_jobid);
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE ('-------------------------------------');
 
  CLOSE CV;
 
  OPEN cv FOR query_2;
 
  LOOP  -- Fetches entire row into the v_employees record
    FETCH cv INTO v_employees;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id);
  END LOOP;
 
  CLOSE cv;
END;
/

44.8.4.4. 给光标变量赋值

您可以将一个PL/oraSQL光标变量的值赋给另一个PL/oraSQL光标变量或主机光标变量。 

target_cursor_variable := source_cursor_variable;

如果source_cursor_variable是打开的，在赋值后，target_cursor_variable也将是打开的。 这两个光标变量指向相同的SQL工作区。

如果source_cursor_variable没有打开，在赋值后打开target_cursor_variable并不会打开source_cursor_variable。

从光标变量中提取数据到集合中。例如： 

DECLARE
  TYPE empcurtyp IS REF CURSOR;
  emp_cv  empcurtyp;
  emp_cf empcurtyp;
  item employees%rowtype;
BEGIN
  OPEN emp_cv FOR SELECT * FROM employees WHERE job_id = 'SA_REP' ORDER BY salary DESC;
  OPEN emp_cf FOR SELECT * FROM employees;
  emp_cf := emp_cv;
  LOOP
  	FETCH emp_cf INTO item;
  	DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', salary = ' || item.salary);
    EXIT WHEN emp_cf%notfound;
  END LOOP;
  CLOSE emp_cf;
END;
/

44.8.4.5. 光标变量查询中的变量

与光标变量相关联的查询可以引用其作用域内的任何变量。

当您使用OPEN FOR语句打开光标变量时，PL/oraSQL将评估查询中的任何变量，并在识别结果集时使用这些值。稍后更改变量的值不会更改结果集。

从光标变量中提取数据到集合中。例如： 

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  cv SYS_REFCURSOR;
 
BEGIN
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE ('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
    factor := factor + 1;  -- Does not affect sal_multiple
  END LOOP;
 
  CLOSE cv;
END;
/

从光标变量中提取数据到集合中。例如： 

DECLARE
  sal           employees.salary%TYPE;
  sal_multiple  employees.salary%TYPE;
  factor        INTEGER := 2;
 
  cv SYS_REFCURSOR;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);
 
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
  END LOOP;
 
  factor := factor + 1;
 
  DBMS_OUTPUT.PUT_LINE ('factor = ' || factor);

  CLOSE cv;
  
  OPEN cv FOR
    SELECT salary, salary*factor
    FROM employees
    WHERE job_id LIKE 'AD_%';   -- PL/SQL evaluates factor
 
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('sal          = ' || sal);
    DBMS_OUTPUT.PUT_LINE ('sal_multiple = ' || sal_multiple);
  END LOOP;
 
  CLOSE cv;
END;
/

44.8.4.6. 光标变量属性

光标变量具有与显式光标相同的属性（请参见显式光标属性）。光标变量属性的值的语法是光标变量名称后紧跟属性（例如，cv%ISOPEN）。 如果光标变量没有打开，则引用%ISOPEN以外的任何属性都会引发预定义的INVALID_CURSOR异常。

Prev Up Next
44.7. 控制结构 Home 44.9. 集合