如何使用现有to_check_data存储过程实现按ID列过滤HR用户表数据

完全可以基于现有代码实现按ID列过滤数据的需求，无需重构核心逻辑，仅需做少量调整即可，同时可以兼容原有查全量数据的调用习惯。

修改要点 #

• 新增2个可选入参：p_id_col（指定要过滤的ID列名，默认空）、p_id_val（指定ID的过滤值，默认空）
• 新增ID列合法性校验：避免传入不存在的列名导致执行报错
• 调整动态SQL拼接逻辑：两个ID参数都非空时自动拼接WHERE过滤条件
• 原有列遍历、类型适配、数据输出的逻辑完全复用

修改后完整存储过程代码 #

CREATE OR REPLACE PROCEDURE to_check_data (
    p_table IN VARCHAR2,
    p_id_col IN VARCHAR2 DEFAULT NULL,
    p_id_val IN NUMBER DEFAULT NULL
) 
IS
    TYPE t_list IS TABLE OF user_tab_columns%rowtype INDEX BY PLS_INTEGER;
    v_array         t_list;
    to_store_column VARCHAR2(32767);
    v_number        NUMBER;
    v_varchar       VARCHAR2(32767);
    v_date          DATE;
    v_cursor        PLS_INTEGER;
    v_count         PLS_INTEGER;
    v_column_name   VARCHAR2(32767);
    v_sql           VARCHAR2(32767);
    v_col_exists    NUMBER;
BEGIN
    -- 校验ID列是否存在
    IF p_id_col IS NOT NULL THEN
        SELECT COUNT(*) INTO v_col_exists
        FROM user_tab_columns
        WHERE table_name = upper(p_table) 
        AND column_name = upper(p_id_col);
        
        IF v_col_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, '错误：表'||p_table||'中不存在列'||p_id_col);
        END IF;
    END IF;

    -- 获取表所有列
    SELECT *
    BULK COLLECT INTO v_array
    FROM user_tab_columns
    WHERE table_name = upper(p_table);

    to_store_column := v_array(1).column_name;
    FOR i IN 2..v_array.count LOOP
        to_store_column := to_store_column || ',' || v_array(i).column_name;
    END LOOP;

    -- 拼接动态SQL，带ID过滤条件
    v_sql := 'select ' || to_store_column || ' from ' || p_table;
    IF p_id_col IS NOT NULL AND p_id_val IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || upper(p_id_col) || ' = ' || p_id_val;
    END IF;

    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);

    -- 定义列映射
    FOR idx IN 1..v_array.count LOOP
        IF v_array(idx).data_type = 'NUMBER' THEN
            dbms_sql.define_column(v_cursor, idx, 1);
        ELSIF v_array(idx).data_type IN ( 'VARCHAR2', 'VARCHAR', 'CHAR' ) THEN
            dbms_sql.define_column(v_cursor, idx, 'Dumy Text', v_array(idx).char_length);
        ELSIF v_array(idx).data_type = 'DATE' THEN
            dbms_sql.define_column(v_cursor, idx, sysdate);
        END IF;

        v_column_name := v_column_name || '  ' || rpad(v_array(idx).column_name, 20);
    END LOOP;

    -- 输出表头
    dbms_output.put_line(v_column_name);
    v_number := dbms_sql.execute(v_cursor);
    
    -- 输出数据行
    WHILE dbms_sql.fetch_rows(v_cursor) > 0 LOOP
        v_column_name := NULL;
        FOR t IN 1..v_array.count LOOP
            IF v_array(t).data_type = 'NUMBER' THEN
                dbms_sql.column_value(v_cursor, t, v_number);
                v_column_name := v_column_name || '  ' || rpad(nvl(to_char(v_number), ' '), 20);
            ELSIF v_array(t).data_type IN ( 'VARCHAR2', 'VARCHAR', 'CHAR' ) THEN
                dbms_sql.column_value(v_cursor, t, v_varchar);
                v_column_name := v_column_name || '  ' || rpad(nvl(to_char(v_varchar), ' '), 20);
            ELSIF v_array(t).data_type = 'DATE' THEN
                dbms_sql.column_value(v_cursor, t, v_date);
                v_column_name := v_column_name || '  ' || rpad(nvl(to_char(v_date), ' '), 20);
            END IF;
        END LOOP;
        dbms_output.put_line(v_column_name);
    END LOOP;

    dbms_sql.close_cursor(v_cursor);
EXCEPTION
    WHEN OTHERS THEN
        IF dbms_sql.is_open(v_cursor) THEN
            dbms_sql.close_cursor(v_cursor);
        END IF;
        RAISE;
END;
/

调用示例 #

原有全量查询逻辑仍可用 #

EXEC to_check_data('employees');
EXEC to_check_data('departments');
EXEC to_check_data('countries');
EXEC to_check_data('locations');

新增按ID过滤查询 #

-- 查询employees表中employee_id为100的记录
EXEC to_check_data('employees','EMPLOYEE_ID',100);
-- 查询departments表中department_id为10的记录
EXEC to_check_data('departments','DEPARTMENT_ID',10);

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