PL/SQL存储过程/函数中创建两列复合索引的方法

你的两段代码分别存在两个核心问题：

• 第一版代码触发PLS-00201编译错误：拼接动态SQL时，trx_date和customer_id没有被包裹在字符串字面量中，PL/SQL编译器会将其识别为当前代码块内的自定义变量，但你并未声明这两个变量，因此直接抛出标识符未定义的错误。
• 第二版代码触发ORA-0904运行时错误：你虽然把列名写成了字符串字面量，但错误地使用下划线_拼接两个列名，最终生成的动态SQL里索引列只有一个名为TRX_DATE_CUSTOMER_ID的单列；而创建两列复合索引时，两个列名之间需要用英文逗号分隔，数据库找不到你拼出来的这个单列，自然报无效标识符错误。另外你声明了table_space变量但未拼接到SQL语句中，最终索引不会建在你预期的users表空间上。

创建多列复合索引时，列列表中不同列必须用英文逗号分隔，不需要额外给列名单独加单引号（建表时未用双引号定义大小写敏感列名的场景下），修正后的可运行代码如下：

declare 
   t_name varchar2(100) := upper('test_etl');
   index_name varchar2(100) := upper('trx_cst_indx');
   table_space varchar2(100) := 'users';
begin
   execute immediate 
           'create index ' || index_name || ' ON ' || t_name || '(trx_date, customer_id) tablespace ' || table_space || ' local unusable';
end;
/

补充注意事项 #

• Oracle中推荐使用varchar2类型存储字符串，不建议使用varchar，后者是预留类型，虽然当前版本兼容但存在后续版本行为变更的风险。
• 如果你的列名是大小写敏感（建表时用双引号包裹定义了小写/混合大小写列名）、或列名包含特殊字符，才需要在动态SQL中用双引号包裹对应列名，例如列名为"trx_date"的场景下，列列表部分要写为("trx_date", "customer_id")。
• 执行create index时如果不指定tablespace，索引会创建在当前用户的默认表空间下，如果有明确的表空间规划，必须像示例代码一样把表空间参数拼接到动态SQL中。

内容的提问来源于stack exchange，提问作者M_Gh