Oracle 存储过程中有动态 SQL 语句，这样迁移就对了-51CTO.COM

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
INSERTINTO 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');

总结

功能	Oracle EXECUTE IMMEDIATE	PostgreSQL EXECUTE
使用结果执行 SQL 并绑定变量	`EXECUTE IMMEDIATE 'select salary from employees WHERE ' \|\| col_name \|\| ' = :1' INTO amount USING col_val;`	`EXECUTE format('select salary from employees WHERE %I = $1', col_name) INTO amount USING col_val;`
使用变量执行 DML 并绑定变量	`EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary + :1 WHERE ' \|\| col_name \|\| ' = :2' USING amount, col_val;`	`EXECUTE format('UPDATE employees SET salary = salary + $1 WHERE %I = $2', col_name) USING amount, col_val;`
执行 DDL	`EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)';`	`EXECUTE 'CREATE TABLE link_emp (idemp1 integer, idemp2 integer)';`
执行匿名块	`EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE ("Anonymous Block"); END;';`	`DO $$DECLARE BEGIN ... END$$;`

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