如何将许多选择语句的结果作为一个自定义表返回

Question

我有一个表(我们把它命名为source_tab(，我在其中存储满足某些条件的所有数据库表的列表。

tab_name:     description:
table1        some_desc1
table2        some_desc2

现在我需要对每个表执行一个 select 语句，并将结果作为表返回(我创建了自定义 TYPE(。但是我有一个问题 - 使用批量收集时，只返回最后一个选择语句。打开光标也存在同样的问题。是否有可能实现此目标，然后使用联合所有连接所有选择语句并将其作为一个语句执行？因为我是sql的乞丐，所以我的第二个问题是，在SQL注入问题上使用这个动态sql可以吗？以下是我的代码的简化版本：

CREATE OR REPLACE FUNCTION my_function RETURN newly_created_table_type IS
   ret_tab_type newly_created_table_type;
BEGIN
     for r in (select * from source_tab)
     loop
        execute immediate 'select value1, value2,''' || r.tab_name || ''' from ' || r.tab_name bulk collect into ret_tab_type; 
     end loop;
   return ret_tab_type;
END;

我正在使用甲骨文 11。

Answer 1

在您的情况下，您正在尝试动态填充集合，并希望在单个集合中产生结果。在您的情况下，不可能在单个循环中做到这一点。此外，正如@OldProgrammer所提到的，从性能角度来看，piperow 将是一个更好的解决方案。请参阅下面的演示：

-

-表格和值：

CREATE TABLE SOURCE_TAB(TAB_NAME VARCHAR2(100),   DESCRIPTION  VARCHAR2(100));
/
SELECT * FROM SOURCE_TAB;
/
INSERT INTO SOURCE_TAB VALUES('table1','some_desc1');
INSERT INTO SOURCE_TAB VALUES('table2','some_desc2');
/
CREATE TABLE TABLE1(COL1 NUMBER, COL2 NUMBER);
/
INSERT INTO TABLE1 VALUES(1,2);
INSERT INTO TABLE1 VALUES(3,4);
INSERT INTO TABLE1 VALUES(5,6);
/
Select * from TABLE1;
/
CREATE TABLE TABLE2(COL1 NUMBER, COL2 NUMBER);
/
INSERT INTO TABLE2 VALUES(7,8);
INSERT INTO TABLE2 VALUES(9,10);
INSERT INTO TABLE2 VALUES(11,12);
/
Select * from TABLE2;
/

-

-对象创建

--UDT 
CREATE OR REPLACE TYPE NEWLY_CREATED_TABLE_TYPE IS OBJECT (
     VALUE1                        NUMBER,
     VALUE2                        NUMBER
);
/
--Type of UDT
CREATE OR  TYPE NEWLY_CRTD_TYP AS TABLE OF NEWLY_CREATED_TABLE_TYPE;
/

--功能：

--Function
CREATE OR REPLACE FUNCTION MY_FUNCTION
     RETURN NEWLY_CRTD_TYP PIPELINED
AS
     CURSOR CUR_TAB
     IS
          SELECT *
            FROM SOURCE_TAB;
     RET_TAB_TYPE                  NEWLY_CRTD_TYP;
BEGIN
     FOR I IN CUR_TAB
     LOOP
          --Here i made sure that all the tables have col1 & col2 columns since you are using dynamic sql. 
          EXECUTE IMMEDIATE    'select  NEWLY_CREATED_TABLE_TYPE(COL1, COL2)  from '|| I.TAB_NAME
          BULK COLLECT INTO RET_TAB_TYPE;
          EXIT WHEN CUR_TAB%NOTFOUND;
          FOR REC IN 1 .. RET_TAB_TYPE.COUNT
          LOOP
               PIPE ROW (RET_TAB_TYPE (REC) );
          END LOOP;
     END LOOP;
     RETURN;
END;
/

输出：

    SQL>  Select *  from table(MY_FUNCTION);
    VALUE1     VALUE2
---------- ----------
         1          2
         3          4
         5          6
         7          8
         9         10
        11         12
6 rows selected.

Answer 2

如果要从所有表中检索的列的数量和类型相同，则可以在执行之前使用 UNION ALL 将所有查询合并为一个查询。

CREATE OR REPLACE FUNCTION my_function
   RETURN newly_created_table_type
IS
   ret_tab_type   newly_created_table_type;
   v_query        VARCHAR2 (4000);
BEGIN
   SELECT LISTAGG (' select VALUE1,VALUE2 FROM ' || tab_name, ' UNION ALL ')
             WITHIN GROUP (ORDER BY tab_name)
     INTO v_query
     FROM source_tab;
   EXECUTE IMMEDIATE v_query BULK COLLECT INTO ret_tab_type;
   RETURN ret_tab_type;
END;

然后，可以使用单个 select 语句来获取所有值。

select * FROM TABLE ( my_function );