PL/SQL动态透视执行后无结果问题求助

你遇到的问题核心是：PL/SQL块中使用EXECUTE IMMEDIATE执行SELECT语句时，不会像直接在SQL客户端执行那样自动返回结果集，必须显式处理查询的返回数据才能看到输出。

下面提供两种可行的解决方案：

方案1：使用DBMS_SQL.RETURN_RESULT返回结果集（Oracle 12c及以上版本） #

这个方法可以直接将动态查询的结果集返回给SQL客户端（比如SQL*Plus、PL/SQL Developer），效果和手动执行SQL语句一致：

SET SERVEROUTPUT ON
DECLARE
  col_names VARCHAR2(4000);
  pivot_table VARCHAR2(4000);
  v_result SYS_REFCURSOR;
BEGIN
  SELECT LISTAGG('''' || base_name || '''', ',') WITHIN GROUP (ORDER BY base_name)
  INTO col_names
  FROM (SELECT DISTINCT base_name FROM vfact_basis WHERE vfact_id = 'CA000123-2822') t;

  pivot_table := 'WITH base_value AS (
    SELECT *
    FROM (
      SELECT vfact_base_period.vfact_id, amount, base_name
      FROM VFACT_BASE_PERIOD
      INNER JOIN VFACT_BASIS ON VFACT_BASE_PERIOD.VFACT_ID = VFACT_BASIS.VFACT_ID AND VFACT_BASE_PERIOD.BASE_ID = VFACT_BASIS.BASE_ID
      WHERE VFACT_BASE_PERIOD.VFACT_ID = ''CA000123-2822''
    )
    PIVOT (
      SUM(amount)
      FOR base_name
      IN (' || col_names || ')
    )
  )
  select * from base_value';

  dbms_output.put_line(pivot_table);
  
  -- 打开游标执行动态SQL，并将结果返回给客户端
  OPEN v_result FOR pivot_table;
  DBMS_SQL.RETURN_RESULT(v_result);
END;
/

方案2：使用DBMS_SQL逐行输出到DBMS_OUTPUT #

如果你的Oracle版本低于12c，或者需要将结果输出到DBMS_OUTPUT窗口，可以用这个方法：

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  col_names VARCHAR2(4000);
  pivot_table VARCHAR2(4000);
  v_cursor_id INTEGER;
  v_col_count INTEGER;
  v_desc_tab DBMS_SQL.DESC_TAB;
  v_value VARCHAR2(4000);
BEGIN
  SELECT LISTAGG('''' || base_name || '''', ',') WITHIN GROUP (ORDER BY base_name)
  INTO col_names
  FROM (SELECT DISTINCT base_name FROM vfact_basis WHERE vfact_id = 'CA000123-2822') t;

  pivot_table := 'WITH base_value AS (
    SELECT *
    FROM (
      SELECT vfact_base_period.vfact_id, amount, base_name
      FROM VFACT_BASE_PERIOD
      INNER JOIN VFACT_BASIS ON VFACT_BASE_PERIOD.VFACT_ID = VFACT_BASIS.VFACT_ID AND VFACT_BASE_PERIOD.BASE_ID = VFACT_BASIS.BASE_ID
      WHERE VFACT_BASE_PERIOD.VFACT_ID = ''CA000123-2822''
    )
    PIVOT (
      SUM(amount)
      FOR base_name
      IN (' || col_names || ')
    )
  )
  select * from base_value';

  dbms_output.put_line(pivot_table);
  
  -- 初始化游标并解析动态SQL
  v_cursor_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor_id, pivot_table, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_count, v_desc_tab);
  
  -- 定义列变量
  FOR i IN 1..v_col_count LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_value, 4000);
  END LOOP;
  
  -- 执行查询
  DBMS_SQL.EXECUTE(v_cursor_id);
  
  -- 逐行获取并输出结果
  WHILE DBMS_SQL.FETCH_ROWS(v_cursor_id) > 0 LOOP
    FOR i IN 1..v_col_count LOOP
      DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_value);
      DBMS_OUTPUT.PUT(v_desc_tab(i).COL_NAME || ': ' || v_value || '  ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;
  
  -- 关闭游标
  DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;
/

关键说明 #

• 方案1依赖Oracle 12c及以上版本的DBMS_SQL.RETURN_RESULT特性，能直接返回结构化结果集，适合大多数现代客户端工具。
• 方案2兼容性更好，通过DBMS_SQLAPI动态处理列和结果，将内容输出到DBMS_OUTPUT窗口，适合低版本Oracle环境。

内容的提问来源于stack exchange，提问作者brady stewart