oracle 如何编写动态SQL来根据另一个表选择列名？

yvfmudvl 于 2023-06-05 发布在 Oracle

关注(0)|答案(3)|浏览(182)

我有一个用例，我想写一个动态SQL，它会根据另一个表动态地选择列名。
查询1：从MONTHS_MAPPING中选择T_SERIES_VALUE，其中TIME_PERIOD =“2023年6月”;-- T66为输出
查询2：在MAIN_TABLE中选择aaaa，bbbb，***OUTPUT OF QUERY 1***作为'Current_Month';
由于我无法在这个用例中处理sql函数/proc中的多个列，所以我创建了一个对象/表的sql类型，然后使用批量收集，如下所示。程序正在编译。但是当我执行proc时，我得到了不一致的数据类型错误。但是我看到对象类型的数据类型和来自MAIN_TABLE的db值是同一个。
我试着在下面：
类型1：

create or replace type cds_type_o1 as Object 
(
aaaa    VARCHAR2(26 BYTE)
,bbbb   VARCHAR2(256 BYTE)
,Tx NUMBER(38,10)
);

类型2：创建或替换类型cds_type_t1是cds_type_o1的表;
过程：

create or replace procedure test_bulk_collect_table (reporting_period IN VARCHAR2)
 is
   v_cm_sql varchar2(256);
   v_cm varchar2(256);

    v_output_execute varchar2(256);
    v_output cds_type_t1;
  begin
     v_cm_sql:='select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = '||reporting_period; -- T61
      execute immediate v_cm_sql into v_cm; --T66

     v_output_execute:= 'select aaaa, bbbb, '|| v_cm ||' from MAIN_TABLE';
      dbms_output.put_line(v_output_execute); --select aaaa, bbbb, T66 from MAIN_TABLE
      *execute immediate v_output_execute bulk collect into v_output;*  --Here I am getting the error while executing it
 dbms_output.put(v_output(0).scenario);
     for i in v_output.first..v_output.last loop
     dbms_output.put(v_output(i).aaaa);
       end loop;
 end test_bulk_collect_table;

现在，当我执行这个过程作为SET SERVEROUTPUT ON; EXEC test_bulk_collect_table（'JUN-2023'）;
我得到下面的错误：错误报告- ORA-00932：不一致的数据类型：预期UDT得到CHAR ORA-06512：在“SCHEMA.TEST_BULK_COLLECT_TABLE”，第15行（执行立即v_output_执行批量收集到v_output中）ORA-06512：100932号线00000 -“不一致的数据类型：预期的%s得到了%s”* 原因：

行动：
我对这个错误感到困惑，因为它期望%s，它也得到%s，对此的任何指导都将非常感谢。

oracle

来源：https://stackoverflow.com/questions/76392956/how-to-write-dynamic-sql-to-select-column-names-based-on-another-table

3条答案

按热度按时间

00jrzges1#

要获取（或批量收集）到对象列表中，您需要选择 * 对象 * 而不是列。因此，你只需要在查询中添加一个对象构造函数，例如

SQL> create or replace
  2  type myobj as object ( x int, y int );
  3  /

Type created.

SQL>
SQL> create or replace
  2  type mylist is table of myobj;
  3  /

Type created.

SQL>
SQL> declare
  2    v mylist;
  3  begin
  4    select empno, sal
  5    bulk collect into v
  6    from emp;
  7  end;
  8  /
  from emp;
  *
ERROR at line 6:
ORA-06550: line 6, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL>
SQL> declare
  2    v mylist;
  3  begin
  4    select myobj(empno, sal)
  5    bulk collect into v
  6    from emp;
  7  end;
  8  /

PL/SQL procedure successfully completed.

赞(0）回复(0）举报 2023-06-05

cs7cruho2#

你是不是把事情搞复杂了你得到的错误是由于康纳已经说过的，但是-因为你发布的代码中没有任何动态的东西，为什么你要使用动态SQL？一切都可以做得简单得多。这里有一个演示。
示例表：

SQL> create table months_mapping as select 'T66' t_series_value, 'T61' time_period from dual;

Table created.

SQL> create table main_table as select 1 aaaa, 2 bbbb from dual;

Table created.

类型：

SQL> create or replace type cds_type_o1 as Object
  2    (aaaa  VARCHAR2(26)
  3    ,bbbb  VARCHAR2(256)
  4    ,Tx    VARCHAR2(10)
  5    );
  6  /

Type created.

SQL> create or replace type cds_type_t1 is table of cds_type_o1;
  2  /

Type created.

程序：

SQL> create or replace procedure test_bulk_collect_table (reporting_period IN VARCHAR2)
  2  is
  3    v_output cds_type_t1;
  4  begin
  5    select cds_type_o1(a.aaaa, a.bbbb, b.t_series_value)
  6    bulk collect into v_output
  7    from months_mapping b cross join main_table a
  8    where b.time_period = reporting_period;
  9
 10    for i in v_output.first .. v_output.last loop
 11      dbms_output.put_line(v_output(i).aaaa ||' - '|| v_output(i).bbbb ||' - '|| v_output(i).tx);
 12    end loop;
 13  end test_bulk_collect_table;
 14  /

Procedure created.

测试：

SQL> set serveroutput on
SQL> exec test_bulk_collect_table('T61');
1 - 2 - T66

PL/SQL procedure successfully completed.

SQL>

赞(0）回复(0）举报 2023-06-05

bmp9r5qi3#

我是不是错过了最明显的？如果只使用一个 * 标量子查询 * 并在sql中实现呢？

select 
  aaaa, 
  bbbb, 
  (select T_SERIES_VALUE from MONTHS_MAPPING where TIME_PERIOD = 'JUN-2023') as "Current_Month" 
  from main_table

赞(0）回复(0）举报 2023-06-05

我来回答

oracle 如何编写动态SQL来根据另一个表选择列名？

3条答案

相关问题

热门标签

最新问答