如何在PL/SQL过程中生成Dynamic Order by子句? [英] How to generate Dynamic Order by clause in PL/SQL procedure?

问题描述

我正在尝试编写一个PL/SQL过程，该过程将具有SQL查询以获取结果.但是要求是顺序可以是动态的，并且主要用于对屏幕中的列进行排序.我将2个参数传递给此过程-in_sort_column和in_sort_order. 要求是在文本列上按ASC排序，对于数字则按DESC排序. 我的查询看起来像这样，没有添加in_sort_order-

I am trying to write a PL/SQL procedure which will have the SQL query to get the results. But the requirement is that the order by can be dynamic and is mainly for sorting the columns in the screen. I am passing 2 parameters to this procedure - in_sort_column and in_sort_order. The requirement is such that on text columns the sorting is in ASC and for numbers it is DESC. My query looks something like this without adding the in_sort_order -

SELECT col1, col2, col3 from tabl e1 where col1 > 1000 
ORDER BY decode(in_sort_column,'col1', col1, 'col2', col2, 'col3', col3);

在这种情况下，我不知道如何使用in_sort_order参数.可以在帮助之前完成此操作的人可以帮忙吗?

I am not able to figure out how to use the in_sort_order parameter in this case. Can someone who has done this before help out ?

谢谢

推荐答案

进行动态排序时，建议使用 separate 子句:

When doing a dynamic sort, I recommend using separate clauses:

order by (case when in_sort_column = 'col1' then col1 end),
         (case when in_sort_column = 'col2' then col2 end),
         (case when in_sort_column = 'col3' then col3 end)

这保证了，如果列的类型不同，则类型转换不会出现意外问题.请注意，case返回NULL时没有else子句.

This guarantees that you will not have an unexpected problem with type conversion, if the columns are of different types. Note that case return NULL without an else clause.

这篇关于如何在PL/SQL过程中生成Dynamic Order by子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！