Tenho um cursor dentro de um PL/SQL e gostaria de através de parametros que o usuário pudesse escolher a forma de ordenação desta query. Por exemplo neste caso, que ele pudesse escolher se quer por ordem de Código do Produto ou por sua Descrição.
cursor c_produtos is
SELECT msi.segment1 codigo,
msi.description descricao
FROM mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories mc,
mtl_system_items msi
WHERE msi.organization_id = 104
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.structure_id = mc.structure_id
AND mic.category_id = mc.category_id
AND mcs.category_set_name = 'Compras'
ORDER BY 1;
Você pode utilizar um decode no "order by" utilizando um parâmetro de verificação do tipo de ordenação.
Depois retornar o campo desejado. O Importante é que os campo do decode DEVEM ser do mesmo tipo de dado, ou tudo NUMBER ou VARCHAR e etc...
Quanto ao ODER BY dinâmico, não sabia que poderíamos utilizar o DECODE juntamente com a clausula ORDER BY, achei interessante.
Mas gostaria de aproveitar o tópico para levantar uma dúvida. Dependendo de como a estrutura da tabela estiver definida, pela quantidade de registros retornados, capacidade de hardware, número de conexões simultânea, este tipo de recurso é usual, aplicável e prático?! Pois sabemos que o ORDER BY por si só e por ter a função de classificação dos dados já é muito custoso para o banco.
Por isso, questiono, é viável? Para mim, o mais correto seria tratar isso na aplicação através de um dataset e trabalhar com os dados em cache, pois é uma facilidade de visualização para o usuário o que de certa forma não deveria impactar no banco consumindo seus recursos. O que uma hora ou outra vai resultar em rede congestionada, objeto lockado, aplicação travando e usuário reclamando.
Concordo com você que deve-se analisar esta situação. Acredito que tudo DEPENDE da situação. Não existe uma formula única que seja a melhor solução para todos casos.
Num banco em estado "normal", o custo do "order by" é mísero pois a ordenação já feita na montagem/seleção dos dados de forma otimizada pelo banco. Já um DISTINCT é mais pesado dependendo dos dados e quantidade.
Ao passar passar por um dataset, é adicionado 1 passo a mais no processo, que é justamente atribuir o resultado a ele. O que inclui nisto o "tempo de conversa" do banco com a aplicação, da rede, etc... Depois existe a ordenação do dataset na aplicação, que é diferente da do banco. Onde o servidor de aplicação também pode estar congestionado.
Enfim, existem diversos fatores que podem influenciar o desempenho. Se trabalhar com dataset e a vantagem de seus recursos é prioridade, então ótimo. Acredito que vai depender do caso e da prioridade.
Minha opinião, se o order by for realmente necessário, o melhor lugar para fazer o ordenamento é no próprio banco.
Fazer na aplicação significa que você terá que trazer dados fora de ordem, o que implica trazer mais linhas do que estritamente necessário; por sua vez consumindo mais recursos do banco com I/O, CPU no processamento e banda de rede trafegando dados ainda não prontos para serem consumidos. Além de exigir do servidor de aplicação que massageie um pouco mais os dados numa etapa posterior.
Por outro lado, acontece muitas vezes e vemos por aí todo dia, de selects que fazem order by sem motivo funcional. Isso sim caracteriza recursos desperdiçados.