oracle - EXECUTE IMMEDIATE 结果到表类型

Question

我需要一个 EXECUTE IMMEDIATE 将其结果返回到一个表类型(AS TABLE OF)中

如果立即执行返回单个值，我可以使用“INTO somevariable”保存结果

但是，我的立即执行将返回一个多列表，再多的谷歌搜索也无法帮助我解决这个问题!

首先我创建类型

CREATE OR REPLACE TYPE T_VALIDITY_RECORD AS OBJECT (
   TIME_COL  DATE,
   VALUE_COL NUMBER
);
/

CREATE OR REPLACE TYPE T_VALIDITY_TABLE AS TABLE OF T_VALIDITY_RECORD;
/

然后我尝试输入代码

DECLARE
   RET_TABLE   T_VALIDITY_TABLE;
BEGIN

EXECUTE IMMEDIATE 'SELECT my_date,
                          my_numbers  
                   FROM   my_table
                   WHERE  somthing = somthingelse' INTO RET_TABLE;
END;

这只是一个真正简化的例子，真正的代码会做(会做其他事情)

我已经尝试过各种方法，例如 BULK COLLECT INTO 等，但似乎无法正常工作。

我收到以下错误:

Error report
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 5
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

Answer 1

您需要BULK COLLECT INTO并且您需要收集T_VALIDITY_OBJECT的实例，而不是成对的NUMBER和DATE 值(value)观。

Oracle 设置:

CREATE OR REPLACE TYPE T_VALIDITY_RECORD AS OBJECT (
   VALUE_COL NUMBER,
   TIME_COL  DATE
);
/

CREATE OR REPLACE TYPE T_VALIDITY_TABLE AS TABLE OF T_VALIDITY_RECORD;
/

CREATE TABLE my_table ( my_date, my_number, something ) AS
SELECT 1, DATE '2019-01-01', 1 FROM DUAL UNION ALL
SELECT 2, DATE '2019-01-02', 1 FROM DUAL UNION ALL
SELECT 3, DATE '2019-01-03', 1 FROM DUAL;

PL/SQL:

DECLARE
  RET_TABLE   T_VALIDITY_TABLE;
BEGIN
  EXECUTE IMMEDIATE 'SELECT T_VALIDITY_RECORD( my_date, my_number )
                     FROM   my_table
                     WHERE  something = :value'
    BULK COLLECT INTO RET_TABLE
    USING 1;

  FOR i IN 1 .. RET_TABLE.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( i || ': ' || ret_table(i).time_col || ', ' || ret_table(i).value_col );
  END LOOP;
END;
/

输出:

1: 2019-01-01 00:00:00, 12: 2019-01-02 00:00:00, 23: 2019-01-03 00:00:00, 3

db<> fiddle here