如何排序的动态列在oracle?

Question

我有一些复杂的oracle查询，但我会尽量使它简单。像这样:

SELECT TBL1.*, TBL2.*
FROM TABLE_1 TBL1
LEFT JOIN (
SELECT *
FROM
(
SELECT TBL2.VERSION_ID, TBL2.CONFIG_ID, TBL2.VALUE
FROM TABLE_2 TBL2
)
PIVOT
(
MAX(VALUE) FOR CONFIG_ID IN (:metadataClassConfigs)
)
) TBL2 ON TBL1.VERSION_ID = TBL2.VERSION_ID
ORDER BY
CASE
WHEN :orderByCustomClass IS NOT NULL THEN
CASE
WHEN :orderByCustomClass = 1 THEN TBL2."1"
WHEN :orderByCustomClass = 21 THEN TBL2."21"
WHEN :orderByCustomClass = 22 THEN TBL2."22"
WHEN :orderByCustomClass = 23 THEN TBL2."23"
WHEN :orderByCustomClass = 24 THEN TBL2."24"
WHEN :orderByCustomClass = 25 THEN TBL2."25"
WHEN :orderByCustomClass = 26 THEN TBL2."26"
WHEN :orderByCustomClass = 27 THEN TBL2."27"
WHEN :orderByCustomClass = 28 THEN TBL2."28"
WHEN :orderByCustomClass = 29 THEN TBL2."29"
WHEN :orderByCustomClass = 30 THEN TBL2."30"
WHEN :orderByCustomClass = 31 THEN TBL2."31"
WHEN :orderByCustomClass = 32 THEN TBL2."32"
WHEN :orderByCustomClass = 34 THEN TBL2."34"
WHEN :orderByCustomClass = 35 THEN TBL2."35"
WHEN :orderByCustomClass = 36 THEN TBL2."36"
WHEN :orderByCustomClass = 41 THEN TBL2."41"
WHEN :orderByCustomClass = 52 THEN TBL2."42"
END
END;

，这工作得很好。输入参数为::metadataClassConfigs为数字列表(1、21、22、23、24、25、26、27、28、29、30、31、32、34、35、36、41、42)，orderByCustomClass为任意数字

我有更多的数字然后这个列表，超过1000，所以我想知道我如何通过动态列排序，如:

WHEN :orderByCustomClass IS NOT NULL THEN TBL2."{:orderByCustomClass}"

?

Answer 1

在Oracle PL/SQL中有多种方式实现动态SQL。我假设你说的是PL/SQL，因为在其他类型的客户端(如python-oracle, jdbc)发送"查询"的唯一方法;是从字符串创建游标。所以你总是被迫动态地构建查询…

1. 本地动态SQL -execute immediate

适用于简单的情况(看看如何获得结果-对一行最有效-对数组更复杂)。

查询是一个字符串-所以你可以"build"它。如果需要，您可以使用USING子句的参数(查询中的每个参数必须以冒号作为前缀)。请注意，它们是按查询中的位置映射的，而不是按名称。

declare
type t_rec is record (
<describe returned columns>
);
type t_result_array is table of t_rec index by pls_integer;
v_result_array t_result_array;
v_sort_column varchar2(4000);
begin
-- do some logic to determine name of column for order by:
v_sort_column := <some_logic determining column name for sorting>;
-- if logic is based on raw user input, then you should sanitize it:
v_sort_column := DBMS_ASSERT.QUALIFIED_SQL_NAME(v_sort_column);
-- build query based on v_sort_column value
execute immediate 'select ... from ...
order by '||v_sort_column
bulk collect into v_result_array;

<do something with result stored in v_result_array>
end;
/

2. Native Dynamic SQL -OPEN FOR

与execute immediate非常相似，但基于游标变量和OPEN FOR语句。要完成它，你需要做3个步骤:打开光标，获取行和关闭光标。

declare
type t_rec is record (
<describe returned columns>
);
type t_result_array is table of t_rec index by pls_integer;
v_result_array t_result_array;
v_sort_column varchar2(4000);

type t_ret_cursor is ref cursor return t_rec;
v_cursor t_ret_cursor;
begin
-- do some logic to determine name of column for order by:
v_sort_column := <some_logic determining column name for sorting>;
-- if logic is based on raw user input, then you should sanitize it:
v_sort_column := DBMS_ASSERT.QUALIFIED_SQL_NAME(v_sort_column);
open v_cursor for 'select ... from ...
order by '||v_sort_column;
fetch v_cursor bulk collect into v_cursor;
close v_cursor;
<do something with result stored in v_result_array>
end;
/

3. 动态SQL -DBMS_SQL包

这是最灵活的方法——您甚至可以有条件地更改所选列或动态检查结果中的行类型(列数，数据类型等)。此外，它在性能方面也是最好的之一。

我只是把这个选项的信息放在这里，这样你就可以自己看看你是否需要这些特性和功能。

这里有更多的步骤，他们更复杂:打开游标，解析，绑定每个参数(可选)，定义列，执行，获取，访问数据，所以我不会发布任何例子。对于你的目的来说，可能有点过头了。