PL/SQL——游标(CURSOR)

2018-06-03  本文已影响0人  renyjenny

官方文档

SELECT-INTO

用于从SELECT语句中，获取单行数据的最快最简单的方法。
语法：
SELECT select_list INTO variable_list FROM remainder_of_query;

关于SELECT-INTO的一些异常：

异常代码 异常原因
ORA-00947: not enough values INTO变量列表少于SELECT变量列表
ORA-00913: too many values INTO变量列表多于SELECT变量列表
ORA-06502: PL/SQL: numeric or value error 变量数量匹配，但类型不匹配，且ORACLE无法隐式转换类型。

从显示游标中获取

SELECT-INTO也称为隐式查询，因为ORACLE数据库隐式地为查询语句打开游标，获取行，再关闭游标（或抛出异常）。也可以选择显示地声明游标，亲自执行打开、FETCH、关闭操作。

1   DECLARE
2       l_total INTEGER := 10000;
3       CURSOR employee_id_cur
4       IS
5           SELECT employee_id FROM plch_employees ORDER BY salary ASC;
6       l_employee_id employee_id_cur%ROWTYPE;
7   BEGIN
8       OPEN employee_id_cur;
9       LOOP
10          FETCH employee_id_cur INTO l_employee_id;
11          EXIT
12      WHEN employee_id_cur%NOTFOUND;
13      END LOOP;
14      CLOSE employee_id_cur;
15  END;
行 描述
3-5 显示游标声明。从可执行块（写SELECT-INTO语句的地方）移动查询语句，使用CURSOR关键字声明这个查询。
6 根据查询返回的数据行的类型声明记录。最好使用%ROWTYPE声明一个记录。即使游标的SELECT列表发生变化，变量也会随之改变。
8 打开游标，就可以从查询中获取数据行。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。
9 开始循环获取数据行。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。
10 获取游标的下一行，把这行的信息存入INTO子句中指定的记录中
11、12 如果不能获取到数据行，结束LOOP
14 关闭游标。注意：这是ORACLE数据库执行SELECT-INTO语句中的一步。

注意：

使用游标循环

游标循环：

BEGIN
   FOR employee_rec IN (
        SELECT *
          FROM employees
         WHERE department_id = 10)
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;

显式声明的游标循环：

DECLARE
   CURSOR employees_in_10_cur
   IS
      SELECT *
        FROM employees
       WHERE department_id = 10;
BEGIN
   FOR employee_rec 
   IN employees_in_10_cur
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;

使用EXECUTE IMMEDIATE进行动态查询

使用动态查询，就不用硬编码SQL，而是在运行时再完成SQL语句，然后解析、执行它。

CREATE OR REPLACE FUNCTION 
single_number_value (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
   RETURN NUMBER
IS
   l_return   NUMBER;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      INTO l_return;
   RETURN l_return;
END;

如示例代码，本来应该使用SELECT-INTO语句，这里使用的是EXECUTE IMMEDIATE-INTO。并且SELECT语句的查询参数、表名、WHERE子句都是从参数中获取的。
EXCUTE IMMEDIATE-INTO也会在查询结果为空时抛出NO_DATA_FOUND，在返回多行数据时抛出TOO_MANY_ROWS。
也可以使用EXECUTE IMMEDIATE来获取多行数据，这就需要用集合来存储，所以要用到BULK COLLECT。

CREATE OR REPLACE PROCEDURE 
show_number_values (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
IS
   TYPE values_t IS TABLE OF NUMBER;
   l_values   values_t;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      BULK COLLECT INTO l_values;
   FOR indx IN 1 .. l_values.COUNT
   LOOP
      DBMS_OUTPUT.put_line 
      (l_values (indx));
   END LOOP;
END;

游标变量

游标变量：指向游标或结果集的变量，可以作为参数传递给存储过程或方法。
适用于：

 1  CREATE OR REPLACE FUNCTION names_for (
 2        name_type_in IN VARCHAR2)
 3     RETURN SYS_REFCURSOR
 4  IS
 5     l_return   SYS_REFCURSOR;
 6  BEGIN
 7     CASE name_type_in
 8        WHEN 'EMP'
 9        THEN
10           OPEN l_return FOR
11                SELECT last_name
12                  FROM employees
13              ORDER BY employee_id;
14        WHEN 'DEPT'
15        THEN
16           OPEN l_return FOR
17                SELECT department_name
18                  FROM departments
19              ORDER BY department_id;
20     END CASE;
21
22     RETURN l_return;
23  END names_for;
行数 描述
3 返回的数据，数据类型是SYS_REFCURSOR
5 声明函数要返回的游标变量
7 使用CASE语句，根据name_type_in的值决定打开哪个查询
10-13 打开查询员工表的游标变量
16-19 打开查询部门表的游标变量

选择正确的查询方式

上一篇 下一篇

猜你喜欢

热点阅读