Oracle中的动态交叉表查询 [英] Dynamic Cross Tab Query in Oracle

问题描述

我需要创建一个动态的交叉表查询，其中列的数目不会总是固定的，因此不能使用硬编码.我已经在Google上搜索过，它的确找到了一个有关在SQL Server中执行相同操作的博客，但是我想知道是否有任何有关在Oracle中执行相同操作的文章博客.尚未在SQL Server中工作. Fol是有关我的问题的信息.

i need to create a dynamic cross tab query where columns will not always be fixed in number so cant hard code using case when. i ve googled, it did find a blog about doing the same in SQL Server but i was wondering if there is any such article blog on doing the same in Oracle. Have not worked in SQL Server. Fol is the info about my problem.

我编写的硬编码交叉表查询

the hard coded cross tab query i wrote

SELECT

LU_CITY.CITY_NAME as "City",
count(CASE WHEN emp.emp_category='Admin'  THEN emp.emp_id END) As "Admins",
count(CASE WHEN emp.emp_category='Executive'  THEN emp.emp_id END) As "Executive",
count(CASE WHEN emp.emp_category='Staff'  THEN emp.emp_id END) As "Staff",
count(emp.emp_id) As "Total"

FROM emp, LU_CITY

where

   LU_CITY.CITY_ID = EMP.CITY_ID(+)
group by
LU_CITY.CITY_NAME, LU_CITY.CITY_ID
order by
LU_CITY.CITY_ID

表格

        emp (emp_id, emp_name, city_id, emp_category)
        lu_city(city_id,city_name)

查询结果

          ------------------------------------------
          City | Admins | Executive | Staff . . . .
          ------------------------------------------ 
          A    |    1   |   2       | 3
          B    |    0   |   0       | 4
          .    |    .   |   .       | . 
          .
          .

用户可以根据需要添加emp_category.该查询应能够动态生成所有此类类别.

The emp_category can be added by the user as per their need. the query should be such that it should generate all such categories dynamically.

在这方面的任何指导都将受到高度赞赏.

Any guidance in this regard would be highly appreciated.

预先感谢

推荐答案

您可以使用动态游标执行从VARCHAR2变量编译的动态SQL:

You can use dynamic cursors to execute dynamic SQL compiled from a VARCHAR2 variable:

DECLARE 
       w_sql             VARCHAR2 (4000);
       cursor_           INTEGER;
       v_f1    NUMBER (6);
       v_f2    NUMBER (2);
       v_some_value_2_filter_4    NUMBER (2);
       rc      INTEGER         DEFAULT 0;
BEGIN
        -- join as many tables as you need and construct your where clause
        w_sql :='SELECT f1, f2 from TABLE1 t1, TABLE2 t2, ... WHERE t1.f1 =' || v_some_value_2_filter_4 ; 

       -- Open your cursor
       cursor_ := DBMS_SQL.open_cursor;
       DBMS_SQL.parse (cursor_, w_sql, 1);
       DBMS_SQL.define_column (cursor_, 1, v_f1);
       DBMS_SQL.define_column (cursor_, 2, v_f2);
      -- execute your SQL
       rc := DBMS_SQL.EXECUTE (cursor_);

       WHILE DBMS_SQL.fetch_rows (cursor_) > 0
       LOOP
          -- get values from record columns
          DBMS_SQL.COLUMN_VALUE (cursor_, 1, v_f1);
          DBMS_SQL.COLUMN_VALUE (cursor_, 2, v_f2);

          -- do what you need with v_f1 and v_f2 variables

       END LOOP;

END;

或者您可以使用立即执行，更容易实施是否只需要检查一个值或执行并插入/更新/删除查询

Or you can use execute immediate, easier to implement if you just need to check a value or execute and insert/update/delete query

    w_sql :='select f1 from table where f1 = :variable';
    execute immediate w_sql into v_f1 using 'valor1'

有关动态游标的更多信息: http://docs.oracle.com/cd/B10500_01/appdev .920/a96590/adg09dyn.htm

Here more info about dynamic cursors: http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

这篇关于Oracle中的动态交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！