Oracle:尝试使用表名的动态列表循环通过插入语句 [英] Oracle: Trying to loop thru insert statement using dynamic list of table names

问题描述

我不太了解此处找到的解决方案: 从Oracle表变量/数组中选择值?

I'm not quite understanding the solution found here: Selecting Values from Oracle Table Variable / Array?

我有一个表名列表.我想将它们作为数组循环使用，将它们的值用作要搜索的表.

I have a list of table names. I would like to loop thru them as an array, using their values as tables to search from.

TMP_DORMANT_FILTERS物理表.下面的数组是相同的列表. LM_DORMANT_EMAIL是电子邮件地址列表.

TMP_DORMANT_FILTERS physical table of table names. The array below is the same list. LM_DORMANT_EMAIL is a list of email addresses.

我想检查表列表中是否存在休眠的电子邮件地址.我意识到我可以写12次相同的查询来搜索每个表.但这并不会提高我的SQL技能.

I want to check the existence of the dormant email addresses in the list of tables. I realize I could write the same query 12 times to search each table. But that's not going to improve my SQL skills.

这是我的阵列尝试.在这种尝试中，Oracle不喜欢我在不存在sql的地方调用数组值的方式:

Here is my array attempt. In this attempt, Oracle doesn't like the way I'm calling the array value in my where not exists sql:

DECLARE 
TYPE array_t IS VARRAY(12) OF VARCHAR2(25);

ARRAY array_t := array_t('BT_ABANDONED_HIST', 'BT_BROWSED_HIST', 'BT_PURCHASED_HIST',       'CM_ABANDONED_HIST', 'CM_BROWSED_HIST', 'CM_PURCHASED_HIST', 'CM_PAGE_VIEWS_HIST', 'MB_ABANDONED_HIST', 'MB_BROWSED_HIST', 'MB_CARTED_HIST', 'MB_PAGE_VIEWS_HIST', 'MB_PURCHASED_HIST');

BEGIN
FOR i IN 1..array.count LOOP
INSERT INTO TMP1_DORMANT_EMAIL
SELECT feed.EMAIL_ADDRESS
FROM LM_DORMANT_EMAIL feed
WHERE NOT EXISTS (
        SELECT 1 FROM array(i) hist
            WHERE ACTIVITY_DATE >= TRUNC(SYSDATE - 90)
            AND hist.EMAIL = feed.EMAIL_ADDRESS
        );

COMMIT;

END LOOP;

END;
/

或者尝试使用上面链接中找到的解决方案. Oracle无法识别我在开始部分下插入dormant_filters的过程.告诉我我的物理表TMP_DORMANT_FILTERS不存在:

Or using the solution found at the link above, I tried. Oracle doesn't recognize my inserting into dormant_filters under the begin part. It's telling me my physical table TMP_DORMANT_FILTERS does not exist:

CREATE GLOBAL TEMPORARY TABLE dormant_filters
( filters varchar2(100)
)
ON COMMIT DELETE ROWS;

BEGIN 
INSERT INTO dormant_filters
  ( filters )
  ( SELECT TABLE_NAMES 
      FROM TMP_DORMANT_FILTERS 
  ); 

 FOR j IN ( SELECT filters FROM dormant_filters ) LOOP 
    INSERT INTO TMP1_DORMANT_EMAIL
        SELECT feed.EMAIL_ADDRESS, j as DORMANT_SOURCE
        FROM LM_DORMANT_EMAIL feed
        WHERE NOT EXISTS (
            SELECT 1 FROM j hist
            WHERE feed.ACTIVITY_DATE >= TRUNC(SYSDATE - 90)
            AND hist.EMAIL = feed.EMAIL_ADDRESS
            );
        NULL;
END LOOP; 
COMMIT;
END; 
/

推荐答案

此问题需要动态SQL.绑定变量只能用于值，而不能用于对象.

This problem requires dynamic SQL. Bind variables can be used for values but not for objects.

declare 
    type array_t is varray(12) of varchar2(25);
    array array_t := array_t('BT_ABANDONED_HIST', 'BT_BROWSED_HIST', 'BT_PURCHASED_HIST', 'CM_ABANDONED_HIST', 'CM_BROWSED_HIST', 'CM_PURCHASED_HIST', 'CM_PAGE_VIEWS_HIST', 'MB_ABANDONED_HIST', 'MB_BROWSED_HIST', 'MB_CARTED_HIST', 'MB_PAGE_VIEWS_HIST', 'MB_PURCHASED_HIST');
begin
    for i in 1 .. array.count loop
        execute immediate '
            INSERT INTO TMP1_DORMANT_EMAIL
            SELECT feed.EMAIL_ADDRESS
            FROM LM_DORMANT_EMAIL feed
            WHERE NOT EXISTS (
                    SELECT 1 FROM '||array(i)||' hist
                        WHERE ACTIVITY_DATE >= TRUNC(SYSDATE - 90)
                        AND hist.EMAIL = feed.EMAIL_ADDRESS
                    )
        ';
        commit;
    end loop;
end;
/

更新

如果每个表的列名都不相同，则可以使用数据字典选择正确的列名.

If the column names are different for each table you can use the data dictionary to pick the correct column name.

declare 
    type array_t is varray(12) of varchar2(25);
    array array_t := array_t('BT_ABANDONED_HIST', 'BT_BROWSED_HIST', 'BT_PURCHASED_HIST', 'CM_ABANDONED_HIST', 'CM_BROWSED_HIST', 'CM_PURCHASED_HIST', 'CM_PAGE_VIEWS_HIST', 'MB_ABANDONED_HIST', 'MB_BROWSED_HIST', 'MB_CARTED_HIST', 'MB_PAGE_VIEWS_HIST', 'MB_PURCHASED_HIST');
    v_column_name varchar2(30);
begin
    for i in 1 .. array.count loop
        select column_name
        into v_column_name
        from all_tab_columns
        where owner = 'SCHEMA NAME'
            and table_name = array(i)
            and column_name in ('ACTIVITY_TIME','DATE_ABANDONED');

        execute immediate '
            INSERT INTO TMP1_DORMANT_EMAIL
            SELECT feed.EMAIL_ADDRESS
            FROM LM_DORMANT_EMAIL feed
            WHERE NOT EXISTS (
                    SELECT 1 FROM '||array(i)||' hist
                        WHERE '||v_column_name||' >= TRUNC(SYSDATE - 90)
                        AND hist.EMAIL = feed.EMAIL_ADDRESS
                    )
        ';
        commit;
    end loop;
end;
/

这篇关于Oracle:尝试使用表名的动态列表循环通过插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！