使用动态 PIVOT 时出现 PL/SQL 错误

35 浏览
0 Comments

使用动态 PIVOT 时出现 PL/SQL 错误

我正在尝试创建一个动态的PIVOT命令，因为我不知道在"in"子句中需要多少列。这种方法的问题是，尽管预期的值匹配，但在执行完execute immediate之后，我收到一个数据类型不一致的错误。我还尝试使用sys_refcursor，但是发生了同样的错误。可能是什么原因呢？

代码如下：

set serveroutput on;
declare 
  storage_var clob;
  storage_query clob;
  type table_model is table of varchar2(100) index by pls_integer;
  tabl table_model;
begin
 SELECT DISTINCT LISTAGG('''' || scd_local.descricao || '''',',')
 WITHIN GROUP (ORDER BY scd_local.descricao) INTO storage_var FROM  scd_local; 
 --创建一个用于在pivot命令中使用的值列表
 storage_query := 'select * from (select doc.nome, loc.descricao 
                from scd_documento doc, scd_local_doc doc_loc, scd_local loc 
                where doc.nome = doc_loc.id_doc and loc.id = doc_loc.id_local 
                order by 1, 2)
                pivot 
                (max(descricao) for descricao in ( ' || storage_var || ' ))';
 dbms_output.put_line(storage_query);
 execute immediate storage_query bulk collect into tabl;  
 --出现错误："数据类型不一致：预期%s，实际%s"
 for i in 1.. tabl.count 
 loop
   dbms_output.put_line(tabl(i));
 end loop;
end;
/

模型如下：

![enter image description here](https://i.stack.imgur.com/ZS9op.png)

0

2 答案

0 Comments

在使用动态PIVOT时，PL/SQL出现错误的原因是无法获取游标的未知列结构。然而，如果你只是想通过DBMS_OUTPUT将结果发送回客户端，你实际上可以通过使用DBMS_OUTPUT.RETURN_RESULTS来实现。

下面是一个修改过的PL/SQL块的示例，它打印了来自DBA_OBJECTS的用户和对象类型的交叉表，每个单元格显示给定类型的对象由给定用户拥有的数量。

declare 
  storage_var clob;
  storage_query clob;
  l_ref_cur SYS_REFCURSOR;
begin
 SELECT LISTAGG('''' || o.object_type || '''',',')
 WITHIN GROUP ( ORDER BY o.object_type)
 INTO   storage_var
 FROM ( SELECT DISTINCT OBJECT_TYPE FROM dba_objects ) o;
 storage_query := 'select *
                   from ( SELECT owner, object_type FROM dba_objects ) o
                   pivot
                   (count(*) for object_type in (' || storage_var || '))
                   order by 1';
 dbms_output.put_line(storage_query);
 OPEN l_ref_cur FOR storage_query;  
 dbms_sql.return_result(l_ref_cur);
end;
/

在Oracle 11g中，似乎不存在dbms_sql.return_result函数。

不，它是12c的一个功能。

可以参考stackoverflow.com/a/34075462/5174436上的链接，了解如何使用DBMS_SQL处理类似于您的动态游标。它在11g中也可以工作。

0
0 Comments

问题的原因是动态查询的结果具有可变数量的列，所以无法提前知道结果将具有多少列。因此，无法将结果获取到固定数量的结构中，因为在编译时无法知道需要使用多少个变量来获取结果。

解决方法是使用动态SQL来执行查询，并将结果存储在一个游标中。然后，可以使用游标的FETCH语句来逐行获取结果，并将每一行的数据存储在一个结构中。这样，就可以处理动态结果集中的可变数量的列。

以下是解决方法的示例代码：

DECLARE
  -- 定义游标和结果集的变量
  TYPE ResultCursor IS REF CURSOR;
  result_set ResultCursor;
  
  -- 定义用于存储结果的结构
  TYPE ResultRecord IS RECORD (
    column1 NUMBER,
    column2 VARCHAR2(50),
    -- 添加更多的列定义...
  );
  result_row ResultRecord;
BEGIN
  -- 执行动态查询并将结果存储在游标中
  OPEN result_set FOR 'SELECT column1, column2 FROM your_table';
  
  -- 循环获取结果集中的每一行数据
  LOOP
    -- 从游标中获取一行数据并存储在结构中
    FETCH result_set INTO result_row;
    
    -- 判断是否还有更多的行数据
    EXIT WHEN result_set%NOTFOUND;
    
    -- 处理获取到的行数据，可以根据需要执行其他操作
    -- ...
    
    -- 输出获取到的行数据
    DBMS_OUTPUT.PUT_LINE('Column 1: ' || result_row.column1);
    DBMS_OUTPUT.PUT_LINE('Column 2: ' || result_row.column2);
  END LOOP;
  
  -- 关闭游标
  CLOSE result_set;
END;

通过使用动态SQL和游标，可以解决在使用动态PIVOT时出现的PL/SQL错误。这种方法可以处理动态结果集中的可变数量的列，并且可以根据需要执行其他操作。

0