迁移 Oracle 到 PostgreSQL: EXECUTE IMMEDIATE

您可以使用 Oracle 的EXECUTE IMMEDIATE语句来解析和运行动态 SQL 语句或匿名 PL/SQL 块。它还支持绑定变量。

Oracle 用法

例子

从 PL/SQL 过程中运行动态 SQL 语句：

1. 创建名为raise_sal的 PL/SQL 过程。

2. 定义一个 SQL 语句，在它的WHERE语句中包含一个列名的动态值。

3. 使用EXECUTE IMMEDIATE命令，提供用作SELECT语句一部分的两个绑定变量：amount和col_val。

   CREATE OR REPLACE PROCEDURE raise_sal (col_val NUMBER,
   emp_col VARCHAR2, amount NUMBER) IS
     col_name VARCHAR2(30);
     sql_stmt VARCHAR2(350);
   BEGIN
     -- determine if a valid column name has been given as input
     SELECT COLUMN_NAME INTO col_name FROM USER_TAB_COLS
     WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_col;
   
     -- define the SQL statment (with bind variables)
     sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' ||
     col_name || ' = :2';
   
     -- Run the command
     EXECUTE IMMEDIATE sql_stmt USING amount, col_val;
   END raise_sal;
   /
   

4. 在EXECUTE IMMEDIATE命令中运行 DDL 操作。

   EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)';
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
   

5. 使用EXECUTE IMMEDIATE运行具有绑定变量的匿名块。

   EXECUTE IMMEDIATE 'BEGIN raise_sal (:col_val, :col_name, :amount); END;'
     USING 134, 'EMPLOYEE_ID', 10;
   

有关详细信息，请参阅 Oracle 文档中的 EXECUTE IMMEDIATE 语句。

PostgreSQL 用法

PostgreSQL 的EXECUTE命令动态准备和运行命令。EXECUTE命令还可以运行 DDL 语句并使用 SQL 命令检索数据。与 Oracle 类似，您可以将 PostgreSQL 的EXECUTE命令与绑定变量一起使用。

例子

使用绑定变量执行 SQL SELECT 查询，并将表名作为动态变量。此查询返回具有特定 ID 的经理下的员工数。

DO $$
DECLARE
  Tabname varchar(30) := 'employees';
  num integer := 1;
  cnt integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I WHERE manager = $1', tabname)
    INTO cnt USING num;
  RAISE NOTICE 'Count is % int table %', cnt, tabname;
END$$;

运行不带变量的 DML 命令，然后运行带变量的命令。

DO $$
DECLARE
BEGIN
  EXECUTE 'INSERT INTO numbers (a) VALUES (1)';
  EXECUTE format('INSERT INTO numbers (a) VALUES (%s)', 42);
END$$;

注意

%s将参数值格式化为简单字符串。空值被视为空字符串。

%I将参数值视为 SQL 标识符，并在必要时对其进行双引号。值为 null 是错误的。

运行 DDL 命令。

DO $$
DECLARE
BEGIN
  EXECUTE 'CREATE TABLE numbers (num integer)';
END$$;

有关详细信息，请参阅 PostgreSQL 文档中的字符串函数和运算符。

使用PREPARE语句可以提高可重用 SQL 语句的性能。

PREPARE命令可以接收SELECT、INSERT、UPDATE、DELETE或VALUES语句，并使用用户指定的限定名称对其进行解析，以便以后可以使用EXECUTE命令，而无需在每次运行时重新解析 SQL 语句。

• 使用PREPARE创建预准备语句时，它对于当前会话的范围是可行的。
• 如果对准备好的 SQL 语句引用的数据库对象运行 DDL 命令，则下一次EXECUTE命令需要对 SQL 语句进行硬解析。

例子

一起使用PREPARE和EXECUTE命令。

1. SQL 命令是使用用户指定的限定名称准备的。
2. SQL 命令运行多次，无需重新分析。

PREPARE numplan (int, text, bool) AS
INSERT INTO numbers VALUES($1, $2, $3);

EXECUTE numplan(100, 'New number 100', 't');
EXECUTE numplan(101, 'New number 101', 't');
EXECUTE numplan(102, 'New number 102', 'f');
EXECUTE numplan(103, 'New number 103', 't');

总结

有关详细信息，请参阅 PostgreSQL 文档中的基本语句。