循环遍历user_tables获取所有表email值的Oracle SQL问题求助

错误原因分析 #

• PLS-00306类型不匹配：你直接将游标记录对象get_table_list和字符串拼接，而get_table_list是一个包含table_name字段的记录，必须使用get_table_list.table_name来获取具体表名，才能和字符串进行拼接。
• SQL语法错误：单独执行EXECUTE IMMEDIATE 'UNION ALL';不符合SQL语法，UNION ALL必须作为多表查询的连接符，和前后的SELECT语句组成完整查询后才能执行。

正确实现脚本 #

下面的脚本会先拼接出完整的UNION ALL查询语句，跳过没有email字段的表，最后执行查询并输出结果：

DECLARE
    v_sql CLOB;
BEGIN
    -- 遍历用户表，拼接UNION ALL查询语句
    FOR get_table_list IN (SELECT table_name FROM user_tables) LOOP
        -- 检查当前表是否包含email字段，避免报错
        BEGIN
            SELECT 'SELECT email FROM ' || get_table_list.table_name || ' UNION ALL '
            INTO v_sql
            FROM user_tab_columns
            WHERE table_name = get_table_list.table_name
              AND column_name = 'EMAIL';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                CONTINUE; -- 跳过无email字段的表
        END;
    END LOOP;
    
    -- 移除末尾多余的UNION ALL
    IF v_sql IS NOT NULL THEN
        v_sql := RTRIM(v_sql, 'UNION ALL ');
        
        -- 执行查询并输出结果
        DECLARE
            v_email VARCHAR2(255);
            CURSOR c_emails IS EXECUTE IMMEDIATE v_sql;
        BEGIN
            OPEN c_emails;
            LOOP
                FETCH c_emails INTO v_email;
                EXIT WHEN c_emails%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_email);
            END LOOP;
            CLOSE c_emails;
        END;
    END IF;
END;
/

关键说明 #

• 字段合法性检查：通过user_tab_columns系统表检查表是否包含email字段，避免遍历到不符合要求的表时触发错误。
• 完整SQL拼接：将所有符合条件的SELECT语句用UNION ALL连接成一个完整的查询，确保SQL语法合法。
• 结果输出方式：示例中用DBMS_OUTPUT输出结果，你也可以根据需求将结果插入到指定的结果表中。

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