oracle - 为什么我不能在动态 SQL 的 DDL/SCL 语句中使用绑定(bind)变量？

Question

我正在尝试使用绑定(bind)变量在动态 SQL 中执行 SQL 命令:

-- this procedure is a part of PL/SQL package Test_Pkg
PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION
      SET NLS_CALENDAR = :cal'
      USING IN calendar_;
END Set_Nls_Calendar;

然后在客户端，我试图调用该过程:

Test_Pkg.Set_Nls_Calendar('Thai Buddha');

但这就是我 ORA-02248: invalid option for ALTER SESSION .

我的问题是: 为什么我不能在动态 SQL 的 DDL/SCL 语句中使用绑定(bind)变量？

Answer 1

DDL 语句中不允许绑定(bind)变量。所以以下语句会导致错误:

示例 #1:DDL 语句 .会导致ORA-01027: bind variables not allowed for data definition operations

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )'
  USING 42;

示例 #2:DDL 语句 .会导致ORA-00904: : invalid identifier

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( :col_name NUMBER )'
  USING var_col_name;

示例 #3:SCL 语句 .会导致ORA-02248: invalid option for ALTER SESSION

EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = :cal'
  USING var_calendar_option;

问题

要了解为什么会发生这种情况，我们需要查看 How Dynamic SQL Statements Are Processed .

Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. Then Oracle parses the SQL statement. That is, Oracle examines the SQL statement to make sure it follows syntax rules and refers to valid database objects. Parsing also involves checking database access rights¹, reserving needed resources, and finding the optimal access path.

¹ _{Emphasis added by answerer}

请注意，解析步骤发生在将任何变量绑定(bind)到动态语句之前。如果您检查上述四个示例，您将意识到解析器无法在不知道绑定(bind)变量的值的情况下保证这些动态 SQL 语句的语法有效性。

示例 #1 : 解析器无法判断绑定(bind)值是否有效。如果不是 USING 42 会怎样？ , 程序员写道 USING 'forty-two' ?

示例 #2 : 解析器无法判断 :col_name将是一个有效的列名。如果绑定(bind)的列名是 'identifier_that_well_exceeds_thirty_character_identifier_limit' 怎么办？ ?

示例#3 : NLS_CALENDAR 的值内置常量(对于给定的 Oracle 版本？)。解析器无法判断绑定(bind)变量是否具有有效值。

所以答案是 不能在动态 SQL 中绑定(bind)表名、列名等模式元素。你也不能绑定(bind)内置常量 .

解决方案

实现动态引用模式元素/常量的唯一方法是在动态 SQL 语句中使用字符串连接。

示例 #1:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT ' || to_char(42) || ')';

示例 #2:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table (' || var_col_name || ' NUMBER )';

示例 #3:

EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = ''' || var_calendar_option || '''';