使用Oracle:是否可以在数据透视子句中使用“动态"创建的变量? [英] Using Oracle: Can I use 'dynamically' created variable in the pivot clause?

问题描述

我正在尝试创建从变量获取参数的数据透视子句.

I'm trying to create pivot clause that gets it parameters from variable.

我有一些测试代码，例如:

I have some test code like:

SELECT * FROM (
SELECT U.USER_ID, U.USER_NAME, E.EXAM_ID, EU.EXAM_DATE
FROM USERS U, EXAMS E, EXAM_USER EU
WHERE U.USER_ID = EU.USER_ID(+)
AND E.EXAM_ID(+) = EU.EXAM_ID
ORDER BY U.USER_ID
  )
  PIVOT (MAX(EXAM_DATE) FOR EXAM_ID IN ('3' AS "exam 3",'2' AS "exam 2",'1' AS "exam 1"))
  order by 1
;

这很好用.然后我将变量EXAM_IDS声明为:

This works just fine. Then I declared the variable EXAM_IDS like:

DECLARE
  EXAM_IDS VARCHAR2 (255);
BEGIN
  SELECT LISTAGG('''' || EXAM_ID || ''' AS "' || EXAM_NAME || '"', ',')
  WITHIN GROUP (ORDER BY EXAM_ID DESC) 
  INTO EXAM_IDS
  FROM EXAMS;
END;

我很确定变量EXAM_IDS现在具有在数据透视子句(?)中使用的字符串，但是我不知道如何将这两者结合起来:

I'm pretty sure the variable EXAM_IDS has now a string as used in pivot clause(?) but I don know how to combine these two:

DECLARE
  EXAM_IDS VARCHAR2 (255);
BEGIN
  SELECT LISTAGG('''' || EXAM_ID || ''' AS "' || EXAM_NAME || '"', ',')
  WITHIN GROUP (ORDER BY EXAM_ID DESC) 
  INTO EXAM_IDS
  FROM EXAMS;
END;

SELECT * FROM (
SELECT U.USER_ID, U.USER_NAME, E.EXAM_ID, EU.EXAM_DATE
FROM USERS U, EXAMS E, EXAM_USER EU
WHERE U.USER_ID = EU.USER_ID(+)
AND E.EXAM_ID(+) = EU.EXAM_ID
ORDER BY U.USER_ID
  )
  PIVOT (MAX(EXAM_DATE) FOR EXAM_ID IN (' || EXAM_IDS || '))
  order by 1
;

这不起作用.有没有办法做到这一点，还是我应该只运行两个单独的SQL查询?

And this does not work. Is there a way to do this or should I just run two separate SQL queries?

有关此设置(如我的课程)的更多信息，可以在

More info about this setup (like my classes) can be found from Using Oracle combine three tables to one with PIVOT

推荐答案

对于12c及更高版本，您可以通过为动态PIVOT查询打开REFCURSOR来使用DBMS_SQL.RETURN_RESULT.

For 12c and above, you may use DBMS_SQL.RETURN_RESULT by opening a REFCURSOR for the dynamic PIVOT query.

我已删除了left join臭名昭著的(+)语法，始终使用ANSI join语法.

I have removed the notorious (+) syntax for left join, always use the ANSI join syntax.

DECLARE
    exam_ids   VARCHAR2(255);
    x          SYS_REFCURSOR;
BEGIN
    SELECT
        LISTAGG(''''
                  || exam_id
                  || ''' AS "'
                  || exam_name
                  || '"',',') WITHIN GROUP(
            ORDER BY
                exam_id DESC
        )
    INTO exam_ids
    FROM
        exam;

    OPEN x FOR 'SELECT
        *
               FROM
        (
            SELECT
                u.user_id,
                u.user_name,
                e.exam_id,
                eu.exam_date
            FROM
                users u
                LEFT JOIN exam_user eu ON u.user_id = eu.user_id
                LEFT JOIN exam e ON e.exam_id = eu.exam_id
            ORDER BY
                u.user_id
        )
            PIVOT ( MAX ( exam_date )
                FOR exam_id
                IN ( ' || EXAM_IDS || ' )
            )
    ORDER BY
        1';

    dbms_sql.return_result(x);
END;
/

对于11g，您可以使用绑定变量和print命令(当以脚本(F5)身份运行时，在sqlplus和sql developer/Toad中有效)

For 11g, you may use a bind variable and print command ( works in sqlplus and in sql developer/Toad when run as script (F5))

variable x REFCURSOR  -- bind variable declared.
DECLARE
    ..   -- no need to declare sys_refcursor
BEGIN
     ..

    OPEN :x FOR 'SELECT . --note the change with colon
        *
               FROM
        (
            SELECT
            ..

END;
/


print x

这篇关于使用Oracle:是否可以在数据透视子句中使用“动态"创建的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！