Oracle函数中如何将列作为参数传入ORDER BY子句？

当然可以实现动态指定ORDER BY的排序列！我来梳理下整个解决过程：

问题背景 #

你最初尝试直接在静态SQL里把列序号作为参数传入ORDER BY子句，这种写法是无法运行的——因为静态SQL会把p_column_number当作字面量处理，而不是识别为列的位置索引。你最初的尝试代码如下：

FUNCTION foo_function(p_date IN DATE, p_column_number IN NUMBER) RETURN foo_bar IS
BEGIN
  SELECT * FROM bar WHERE date = p_date ORDER BY p_column_number
  <...其他代码...>
END;

动态SQL方案的初始尝试及问题 #

后来你采用了动态SQL的方案，但遇到了inconsistent datatypes: expected %s got %s的错误。先来看你当时的代码：

首先是自定义对象和表类型的定义：

-- 创建包含2列的自定义对象
CREATE OR REPLACE TYPE MY_OBJECT AS OBJECT(IDOP NUMBER, EMISSION_DATE DATE);
-- 创建MY_OBJECT类型的表类型
CREATE OR REPLACE TYPE TB_OBJECT AS TABLE OF MY_OBJECT;

然后是函数代码：

CREATE OR REPLACE FUNCTION GET_OBJECT(
  P_INITIAL_DATE IN DATE,
  P_FINAL_DATE IN DATE,
  P_COLUMN_NUMBER IN NUMBER) RETURN TB_OBJECT IS
  V_TB TB_OBJECT;
  V_SQL VARCHAR2(1000);
BEGIN
  V_SQL := 'SELECT IDOP, EMISSION_DATE FROM OP WHERE EMISSION_DATE BETWEEN :p_initial_date AND :p_final_date ORDER BY :p_column_number';
  EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_TB USING P_COLUMN_NUMBER;
  RETURN (V_TB);
END;

调用函数的语句：

SELECT * FROM TABLE (GET_OBJECT(TO_DATE('01/06/2017','dd/MM/yyyy'), TO_DATE('09/01/2018', 'dd/MM/yyyy'), 1));

出现错误的核心原因有两个：

• 动态SQL查询返回的是单独的IDOP和EMISSION_DATE列，但我们要收集到的是TB_OBJECT类型（即MY_OBJECT的表类型），两者数据类型不匹配；
• USING子句只传入了P_COLUMN_NUMBER，漏掉了P_INITIAL_DATE和P_FINAL_DATE两个参数，这也会导致参数绑定错误。

修正后的正确代码 #

只需要两个小调整就能解决问题：

1. 在动态SQL的查询语句中，把返回的列包装成自定义的MY_OBJECT类型；
2. 修正USING子句的参数顺序，传入所有需要绑定的参数。

修改后的函数代码如下：

CREATE OR REPLACE FUNCTION GET_OBJECT(
  P_INITIAL_DATE IN DATE,
  P_FINAL_DATE IN DATE,
  P_COLUMN_NUMBER IN NUMBER) RETURN TB_OBJECT IS
  V_TB TB_OBJECT;
  V_SQL VARCHAR2(1000);
BEGIN
  V_SQL := 'SELECT MY_OBJECT(IDOP, EMISSION_DATE) FROM OP WHERE EMISSION_DATE BETWEEN :p_initial_date AND :p_final_date ORDER BY :p_column_number';
  EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO V_TB USING P_INITIAL_DATE, P_FINAL_DATE, P_COLUMN_NUMBER;
  RETURN (V_TB);
END;

这样修改后，函数就能正常运行，根据你传入的P_COLUMN_NUMBER参数动态指定ORDER BY的排序列了。

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