迁移 Oracle 到 PostgreSQL: DBMS_SQL 包

七月 15, 2023

Oracle 的 DBMS_SQL 包提供了一个接口来解析和运行动态 SQL 语句、DML 命令和 DDL 命令（通常是在 PL/SQL 包、函数或过程里面）。DBMS_SQL 支持对 SQL 游标进行非常精细的控制，并在某些情况下可以提高游标性能。

Oracle 用法

例子

以下示例演示如何使用DBMS_SQL的 PL/SQL 接口手动打开、解析、绑定、运行和从游标读取数据。

用DBMS_SQL.OPEN_CURSOR打开空白游标并返回游标句柄。
用DBMS_SQL.PARSE将语句解析为引用的游标。
用DBMS_SQL.BIND_VARIABLES将绑定变量的值与游标附加。
用DBMS_SQL.EXECUTE运行游标。
用DBMS_SQL.GET_NEXT_RESULT循环访问光标，获取下一个结果。
用DBMS_SQL.CLOSE_CURSOR关闭光标。

DECLARE
  c1           INTEGER;
  rc1          SYS_REFCURSOR;
  n            NUMBER;
  first_name   VARCHAR2(50);
  last_name    VARCHAR2(50);
  email        VARCHAR2(50);
  phone_number VARCHAR2(50);
  job_title    VARCHAR2(50);
  start_date   DATE;
  end_date     DATE;
BEGIN
  c1 := DBMS_SQL.OPEN_CURSOR(true);
  DBMS_SQL.PARSE(c1, 'BEGIN emp_info(:id); END;', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(c1, ':id', 176);
  n := DBMS_SQL.EXECUTE(c1);
  -- Get employee info
  DBMS_SQL.GET_NEXT_RESULT(c1, rc1);
  FETCH rc1 INTO first_name, last_name, email, phone_number;
  -- Get employee job history
  DBMS_SQL.GET_NEXT_RESULT(c1, rc1);
  LOOP
    FETCH rc1 INTO job_title, start_date, end_date;
    EXIT WHEN rc1%NOTFOUND;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c1);
END;
/

DBMS_SQL包包括其他三个过程。

RETURN_RESULT（oracle 12c 中的新功能）— 获取结果集并将其返回给客户端。由于该过程已返回结果集，因此调用程序不必知道结果的格式或其包含的列（最常与 SQL*Plus 一起使用）。
TO_REFCURSOR— 使用DBMS_SQL.OPEN_CURSOR时，返回数字光标 ID。如果您知道游标结果的结构，则可以调用TO_REFCURSOR过程，停止使用 DBMS_SQL，并转到常规命令，如FETCH、WHEN CURSOR%notfound等。在使用TO_REFCURSOR之前，请使用过程OPEN_CURSOR、PARSE和EXECUTE。
TO_CURSOR_NUMBER— 获取在本机动态 SQL 中打开的游标。游标打开后，可以将其转换为数字（游标 ID），然后使用 DBMS_SQL 过程进行管理。

有关详细信息，请参阅 Oracle 文档中的 DBMS_SQL。

PostgreSQL 用法

PostgreSQL不支持对编程游标的精细控制，因此没有 Oracle DBMS_SQL的等效项。

但是，您可以在 PostgreSQL 中动态解析和运行 SQL 语句。查找下面的两个示例。

例子

使用带有SELECT的FOR创建动态游标。

CREATE OR REPLACE FUNCTION GetErrors() RETURNS VARCHAR AS $$
DECLARE
  _currow RECORD;
  msg VARCHAR(200);
  TITLE VARCHAR(10);
  CODE_NUM VARCHAR(10);
BEGIN
  msg := '';
  
  FOR _currow IN SELECT TITLE, CODE_NUM, count(*) FROM A group by TITLE, CODE_NUM
  LOOP
    TITLE := _currow.TITLE;
    CODE_NUM := _currow.CODE_NUM;
    msg := msg || rpad(TITLE,20) || rpad(CODE_NUM,20);
  END LOOP;
  RETURN msg;
END;
$$ LANGUAGE plpgsql;

创建游标，然后打开它以使用给定的 SQL 运行。

CREATE OR REPLACE FUNCTION GetErrors () RETURNS VARCHAR AS $$
declare
  refcur refcursor;
  c_id integer;
  title varchar (10);
  code_num varchar (10);
  alert_mesg VARCHAR(1000) := '';
BEGIN
  OPEN refcur FOR execute('select * from Errors');
  loop
    fetch refcur into title, code_num;
      if not found then
        exit;
      end if;
    alert_mesg := alert_mesg||rpad(title,20)||rpad(code_num,20);
  end loop;
  close refcur;
  return alert_mesg;
END;
$$ LANGUAGE plpgsql;