如何在Oracle动态SQL中按需传递WHERE条件并避免硬编码表名

你可以通过查询Oracle的数据字典视图来判断当前遍历的表是否包含CREATED_DATE列，完全替代硬编码表名的IF判断逻辑。这样后续新增表只要遵循列规则，不需要修改存储过程代码。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE my_proc AS
    v_count NUMBER;
    v_date VARCHAR2(50 BYTE) := '2024-01-01'; -- 示例日期，根据实际需求赋值
    v_sql VARCHAR2(500 BYTE);
    v_table VARCHAR2(128 BYTE);
    v_has_created_date BOOLEAN := FALSE;

    CURSOR my_cur IS 
        SELECT S_table_name FROM table_list;
BEGIN
    OPEN my_cur;
    LOOP
        FETCH my_cur INTO v_table;
        EXIT WHEN my_cur%NOTFOUND;

        -- 检查当前表是否存在CREATED_DATE列
        SELECT CASE 
                   WHEN EXISTS (
                       SELECT 1 
                       FROM USER_TAB_COLUMNS 
                       WHERE TABLE_NAME = UPPER(v_table) 
                         AND COLUMN_NAME = 'CREATED_DATE'
                   ) THEN TRUE 
                   ELSE FALSE 
               END 
        INTO v_has_created_date 
        FROM DUAL;

        -- 动态拼接SQL语句
        IF v_has_created_date THEN
            v_sql := 'SELECT COUNT(ID) FROM ' || v_table || ' WHERE CREATED_DATE > :1';
            EXECUTE IMMEDIATE v_sql INTO v_count USING v_date;
        ELSE
            v_sql := 'SELECT COUNT(ID) FROM ' || v_table;
            EXECUTE IMMEDIATE v_sql INTO v_count;
        END IF;

        -- 执行插入操作，补全你的字段和值即可
        INSERT INTO my_date (table_name, count_num, stat_date) 
        VALUES (v_table, v_count, SYSDATE);
    END LOOP;
    CLOSE my_cur;
END;
/

关键说明 #

• 数据字典查询：使用USER_TAB_COLUMNS视图（如果表属于其他用户，改用ALL_TAB_COLUMNS并加上OWNER条件）检查列是否存在，彻底摆脱硬编码表名的限制。
• 绑定变量优化：动态SQL中使用:1绑定变量替代字符串拼接，既避免SQL注入风险，又能提升执行效率。
• 语法修正：修复了原代码中游标拼写（CUSRSOR→CURSOR）、游标打开/获取的语法错误，以及动态SQL拼接的语法问题。

可选优化方案 #

如果不想每次循环都查询数据字典，可以提前把所有包含CREATED_DATE列的表缓存到集合里，减少重复查询：

CREATE OR REPLACE PROCEDURE my_proc AS
    v_count NUMBER;
    v_date VARCHAR2(50 BYTE) := '2024-01-01';
    v_sql VARCHAR2(500 BYTE);
    v_table VARCHAR2(128 BYTE);
    -- 定义集合存储有CREATED_DATE列的表名
    TYPE tab_name_list IS TABLE OF VARCHAR2(128);
    v_valid_tables tab_name_list;

    CURSOR my_cur IS 
        SELECT S_table_name FROM table_list;
BEGIN
    -- 提前加载所有包含CREATED_DATE列的表名
    SELECT TABLE_NAME BULK COLLECT INTO v_valid_tables
    FROM USER_TAB_COLUMNS
    WHERE COLUMN_NAME = 'CREATED_DATE';

    OPEN my_cur;
    LOOP
        FETCH my_cur INTO v_table;
        EXIT WHEN my_cur%NOTFOUND;

        -- 检查当前表是否在有效集合中
        IF v_table MEMBER OF v_valid_tables THEN
            v_sql := 'SELECT COUNT(ID) FROM ' || v_table || ' WHERE CREATED_DATE > :1';
            EXECUTE IMMEDIATE v_sql INTO v_count USING v_date;
        ELSE
            v_sql := 'SELECT COUNT(ID) FROM ' || v_table;
            EXECUTE IMMEDIATE v_sql INTO v_count;
        END IF;

        INSERT INTO my_date (table_name, count_num, stat_date) 
        VALUES (v_table, v_count, SYSDATE);
    END LOOP;
    CLOSE my_cur;
END;
/

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