迁移 Oracle 到 PostgreSQL: 游标

摘要：Oracle PL/SQL 和 PostgreSQL PL/pgSQL 都支持游标的功能。语法TYPE … IS REF CURSOR不受 PostgreSQL 支持。语法上的细微差异可能需要重写一些代码。PostgreSQL 不支持%ISOPEN、%BULK_EXCEPTIONS和%BULK_ROWCOUNT。

Oracle 用法

PL/SQL 游标是指应用程序代码逻辑中可以迭代访问数据集的指针。数据集保存 SQL 语句返回的行。您可以在程序内使用命名游标引用活动的数据集。

有两种类型的 PL/SQL 游标：

• 隐式游标是由 PL/SQL 自动构造和管理的会话游标，无需用户创建或定义。PL/SQL 每次运行SELECT或 DML 语句时都会打开一个隐式游标。隐式游标也称为 SQL 游标。
• 显式游标是由用户创建、构造和管理的会话游标。游标是通过变量命名并将其与查询关联来声明和定义的。与隐式游标不同，你可以使用游标名称引用显式游标。显式游标也称为命名游标。

例子

以下示例演示了游标用法：

1. 定义一个名为c1的显式 PL/SQL 游标。
2. 游标运行 SQL 语句以从数据库返回行。
3. PL/SQL 循环逐行从游标读取数据，并将值存储到两个变量中：v_lastname和v_jobid。
4. 循环使用了%NOTFOUND属性在从数据库中读取到最后一行后终止。

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 to store last_name
    v_jobid employees.job_id%TYPE; -- variable to store job_id
  BEGIN
    OPEN c1;
    LOOP -- Fetches 2 columns into variables
      FETCH c1 INTO v_lastname, v_jobid;
      EXIT WHEN c1%NOTFOUND;
    END LOOP;
  CLOSE c1;
END;

1. 使用FOR循环定义隐式 PL/SQL 游标。
2. 游标运行查询并将返回的值存储到记录中。
3. 循环迭代访问游标数据集并打印结果。

BEGIN
FOR item IN
  (SELECT last_name, job_id FROM employees WHERE job_id LIKE '%MANAGER%'
    AND manager_id > 400 ORDER BY last_name) LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/

有关详细信息，请参阅 Oracle 文档中显式游标的声明和定义和隐式游标属性。

PostgreSQL 用法

与 Oracle PL/SQL 游标类似，PostgreSQL 具有 PL/pgSQL 游标，使您能够对从数据库读取的行迭代业务逻辑。它们可以封装查询并一次读取几行查询结果。PL/pgSQL 中对游标的所有访问都是通过游标变量执行的，这些游标变量始终是 refcursor 数据类型。

通过将 PL/pgSQL 游标声明为 refcursor 类型的变量来创建它。

声明游标的示例

在 PL/pgSQL 中声明一个游标以用于任何查询。

DECLARE c1 refcursor;

变量 c1 是未绑定的，因为它未绑定到任何特定查询。

在 PL/pgSQL 中使用绑定查询声明一个游标。

DECLARE c2 CURSOR FOR SELECT * FROM employees;

在以下示例中，您可以替换FOR为IS以实现 Oracle 兼容性。在 PL/pgSQL 中声明一个游标以用于任何查询。

DECLARE c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;

• 打开游标时，id 变量将替换为整数参数值。
• 声明指定为SCROLL的游标时，游标可以向后滚动。
• 如果指定NO SCROLL，则拒绝向后提取。

使用SCROLL选项声明向后滚动兼容的游标。

DECLARE c3 SCROLL CURSOR FOR SELECT id, name FROM employees;

• SCROLL指定可以向后检索行。NO SCROLL指定不能向后检索行。
• 根据查询的运行计划的复杂性，SCROLL可能会产生性能问题。
• 当查询包含FOR UPDATE或FOR SHARE时，不允许向后提取。

打开游标的示例

打开声明为“未绑定”的游标变量，并指定要运行的查询。

OPEN c1 FOR SELECT * FROM employees WHERE id = emp_id;

打开声明为“未绑定”的游标变量，并指定要作为字符串表达式运行的查询。这种方法提供了更大的灵活性。

OPEN c1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1', tabname) USING keyvalue;

可以使用format()和USING将参数值插入到动态命令中。例如，使用format()将表名插入到查询中。col1 的比较值是使用USING参数插入的。

打开在声明游标时绑定到查询且声明为接受参数的游标。

DO $$
DECLARE
  c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;
BEGIN
  OPEN c3(var1 := 42);
END$$;

对于 c3 游标，提供参数值表达式。如果未将游标声明为接受参数，则可以在游标外部指定参数。

DO $$
DECLARE
  var1 integer;
  c3 CURSOR FOR SELECT * FROM employees where id = var1;
BEGIN
  var1 := 1;
  OPEN c3;
END$$;

读取游标的示例

PL/pgSQL 的FETCH命令将游标中的下一行检索到变量中。将从游标c3返回的值提取到行变量中。

DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
  OPEN c3;
  FETCH c3 INTO rowvar;
END$$;

将从 c3 游标返回的值提取为两种标量数据类型。

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH c3 INTO emp_id, emp_name;
END$$;

PL/pgSQL 在从游标获取数据时支持特殊的方向子句，你可以使用NEXT、PRIOR、FIRST、LAST、ABSOLUTE count、RELATIVE count、FORWARD或BACKWARD这些参数。省略方向等效于指定NEXT。例如，将最后一行从游标提取到声明的变量中。

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
END$$;

有关详细信息，请参阅 PostgreSQL 文档中的 FETCH。

关闭游标的示例

使用CLOSE命令关闭 PL/pgSQL 游标。

DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
  CLOSE c3;
END$$;

循环访问游标的示例

PL/pgSQL 支持检测游标何时没有更多数据要返回，并且可以与循环结合使用以迭代游标引用的所有行。

以下 PL/pgSQL 代码使用循环从游标中获取所有行，然后在读取最后一条记录后退出（使用EXIT WHEN NOT FOUND）。

PL/pgSQL 支持检测游标何时没有更多数据要返回，并且可以与循环结合使用以迭代游标引用的所有行。

以下 PL/pgSQL 代码使用循环从游标中获取所有行，然后在读取最后一条记录后退出（使用EXIT WHEN NOT FOUND）。

DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
OPEN c3;
  LOOP
    FETCH FROM c3 INTO rowvar;
    EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE c3;
END$$;

在不读取数据的情况下移动游标的示例

MOVE重新定位游标而不检索任何数据，其工作方式类似于命令FETCH，但它仅在数据集中重新定位游标，而不返回游标移动到的行。可以检查特殊变量FOUND以确定是否有下一行。

移动到游标 c3 的最后一行（空值或未找到数据）。

MOVE LAST FROM c3;

将光标向后移动两条记录。

MOVE RELATIVE -2 FROM c3;

将 c3 光标向前移动两条记录。

MOVE FORWARD 2 FROM c3;

更新或删除当前示例

当游标位于表行上时，可以更新或删除该行。游标的查询可以选择哪些内容才能使这种类型的 DML 成功。

例如，更新 C3 游标指向的当前行。

UPDATE employee SET salary = salary * 1.2 WHERE CURRENT OF c3;

使用隐式游标（FOR 循环查询）的示例

DO $$
DECLARE
  item RECORD;
BEGIN
  FOR item IN (
    SELECT last_name, job_id
    FROM employees
    WHERE job_id LIKE '%MANAGER%'
    AND manager_id > 400
    ORDER BY last_name
  )
  LOOP
    RAISE NOTICE 'Name = %, Job=%', item.last_name, item.job_id;
  END LOOP;
END $$;

总结

有关详细信息，请参阅 PostgreSQL 文档中的游标和基本声明。