Oracle SQL-动态案例声明 [英] Oracle SQL - dynamic case statement

问题描述

是否可以将下面的Oracle SQL查询转换为动态查询?我的意思是，我已将case语句的值硬编码为"INTERNET"，"SALES" ETC ...是否可以避免硬编码?我的源列是动态的.我当时在考虑for循环和数组，但是在SQL中可用吗?如果有人能让我继续下去，那将是很棒的.谢谢.

Is it possible to turn the below Oracle SQL query into a dynamic query? What I mean is, I have hard coded the values for the case statement as 'INTERNET', 'SALES' ETC... Is it possible to avoid hard coding? My source column is dynamic. I was thinking a for loop and array, but is that available in SQL? If someone could get me going on this, that would be great. Thank you.

SELECT
        NVL(status, 'Grand Total') AS "ROW LABELS",
        COUNT(case when source = 'INTERNET' THEN 1 end) AS "INTERNET",
        COUNT(case when source = 'SALES' THEN 1 end) AS "SALES",
        COUNT(case when source = 'DEMO' THEN 1 end) AS "DEMO",
        COUNT(case when source = 'COM' THEN 1 end) AS "COM",
        COUNT(CASE WHEN order_source IN ('INTERNET', 'SALES', 'DEMO', 'COM') THEN 1 END) AS "Grand Total"
FROM
SOMETABLE
GROUP BY ROLLUP(status);

推荐答案

您需要具有动态列定义的PIVOT函数.最简单的方法是透视xml:

You need a PIVOT function with dynamic columns definition. The simplest way is pivot xml:

create table tst_data (id int primary key, source varchar2(255));

insert into tst_data values (1, 'INTERNET');
insert into tst_data values (2, 'DEMO');
insert into tst_data values (3, 'INTERNET');
insert into tst_data values (4, 'SALES');
insert into tst_data values (5, 'INTERNET');
insert into tst_data values (6, 'DEMO');
insert into tst_data values (7, 'INTERNET');
insert into tst_data values (8, 'COM');

commit;

select * from (
  select source from tst_data
) 
pivot xml 
(
  count(1)
  for source in (select distinct t.source from tst_data t)
)  

需要处理XML数据后:

After you need to process XML data:

<PivotSet>
    <item>
        <column name = "SOURCE">COM</column>
        <column name = "COUNT(1)">1</column>
    </item>
    <item>
        <column name = "SOURCE">DEMO</column>
        <column name = "COUNT(1)">2</column>
    </item>
    <item>
        <column name = "SOURCE">INTERNET</column>
        <column name = "COUNT(1)">4</column>
    </item>
    <item>
        <column name = "SOURCE">SALES</column>
        <column name = "COUNT(1)">1</column>
    </item>
</PivotSet>

PIVOT XML支持动态列定义(for source in (select distinct t.source from tst_data t))，但是它返回XML数据. Extractvalue和xmltable函数允许从服务器端XML查询特定列，但是您必须预先指定字段名称.因此，我假设在客户端进行解析.

PIVOT XML supports dynamic columns definition (for source in (select distinct t.source from tst_data t)) however it returns XML data. Extractvalue and xmltable functions allow to query particular columns from the XML on server side but you have to specify field names in advance. So I assume to parse it on client side.

如果您想在DB层上执行所有操作，则可以使用另一种方法. PIVOT(不是XML)需要列名称for source in ('INTERNET', 'DEMO', 'COM', ...).可以生成这样的查询并将 cursor 返回给客户端:

If you want to do everything on DB-layer there is another approach. PIVOT (not XML) requires columns names for source in ('INTERNET', 'DEMO', 'COM', ...). It's possible to generate such a query and return a cursor to client side:

CREATE OR REPLACE FUNCTION FUNCTION1 RETURN SYS_REFCURSOR AS 
 cur sys_refcursor;
BEGIN
  open cur for 'select * from dual'; // generate PIVOT query here
  RETURN cur;
END FUNCTION1;

我不知道有任何方法可以从游标(在服务器端)创建一个简单的无类型查询，因此，如果您希望使用普通的SQL查询，请分两个步骤进行操作:

I don't know any method to create a simple untyped query from the cursor (on server side), so if you desire to use a plain SQL query do it in two steps:

1. 使用PL/SQL函数中的命名列生成PIVOT查询；
2. 从客户端运行查询.

这篇关于Oracle SQL-动态案例声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！