通过存储过程参数声明表类型的替代方案咨询

Oracle的%ROWTYPE属于编译时绑定的类型，存储过程编译阶段需要明确知道具体表名来确定行结构，但source_table_name是存储过程的输入参数，仅在运行时才会传入实际表名，编译阶段Oracle无法解析该参数对应的表结构，因此会抛出类型未定义的错误。

以下是两种实用的解决方案，可根据业务需求选择：

方案1：动态SQL + 弱类型集合批量提取数据 #

如果核心需求是读取源表指定范围的数据，可以用动态SQL打开游标，结合SYS.ANYDATA实现通用行类型的批量提取：

CREATE OR REPLACE PROCEDURE PRM_BKUP_TRNSACTION_TABLE_PROC
(
    source_table_name     IN VARCHAR2,
    archive_date_col      IN VARCHAR2,
    archive_form_date     IN VARCHAR2,
    archive_to_date       IN VARCHAR2
)
AS
    v_cursor SYS_REFCURSOR;
    TYPE source_table_collection IS TABLE OF SYS.ANYDATA;
    source_data source_table_collection;
    v_sql VARCHAR2(4000);
BEGIN
    -- 拼接动态查询SQL，筛选归档日期范围数据
    v_sql := 'SELECT SYS.ANYDATA.CONVERTOROW(t.*) FROM ' || 
             DBMS_ASSERT.SIMPLE_SQL_NAME(source_table_name) || ' t ' ||
             'WHERE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(archive_date_col) || 
             ' BETWEEN TO_DATE(:start_date, ''YYYY-MM-DD'') ' ||
             'AND TO_DATE(:end_date, ''YYYY-MM-DD'')';
    
    -- 打开游标并批量提取数据
    OPEN v_cursor FOR v_sql USING archive_form_date, archive_to_date;
    FETCH v_cursor BULK COLLECT INTO source_data;
    CLOSE v_cursor;
    
    -- 示例：遍历处理提取的数据
    IF source_data IS NOT NULL AND source_data.COUNT > 0 THEN
        FOR i IN source_data.FIRST .. source_data.LAST LOOP
            -- 如需解析具体列，可调用SYS.ANYDATA的GETNUMBER/GETVARCHAR2等方法
            DBMS_OUTPUT.PUT_LINE('已提取第' || i || '行数据');
        END LOOP;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
        RAISE;
END;
/

注：使用DBMS_ASSERT.SIMPLE_SQL_NAME可验证表名/列名合法性，避免SQL注入风险。

方案2：动态PL/SQL块封装强类型集合 #

如果必须使用源表原生行类型的集合，可以将类型声明和业务逻辑封装在动态PL/SQL块中（动态代码在运行时解析，此时已获取实际表名）：

CREATE OR REPLACE PROCEDURE PRM_BKUP_TRNSACTION_TABLE_PROC
(
    source_table_name     IN VARCHAR2,
    archive_date_col      IN VARCHAR2,
    archive_form_date     IN VARCHAR2,
    archive_to_date       IN VARCHAR2
)
AS
    v_plsql VARCHAR2(4000);
BEGIN
    -- 拼接动态PL/SQL块，内部声明对应表的行集合
    v_plsql := 'DECLARE ' ||
               '    TYPE source_table_collection IS TABLE OF ' || 
               DBMS_ASSERT.SIMPLE_SQL_NAME(source_table_name) || '%ROWTYPE; ' ||
               '    source_data source_table_collection; ' ||
               'BEGIN ' ||
               '    SELECT * BULK COLLECT INTO source_data FROM ' || 
               DBMS_ASSERT.SIMPLE_SQL_NAME(source_table_name) || ' ' ||
               '    WHERE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(archive_date_col) || 
               ' BETWEEN TO_DATE(:start_date, ''YYYY-MM-DD'') ' ||
               '    AND TO_DATE(:end_date, ''YYYY-MM-DD''); ' ||
               '    -- 在此添加你的归档业务逻辑（如插入归档表） ' ||
               '    DBMS_OUTPUT.PUT_LINE(''共提取到'' || source_data.COUNT || ''条待归档数据''); ' ||
               'END;';
    
    -- 执行动态PL/SQL块
    EXECUTE IMMEDIATE v_plsql USING archive_form_date, archive_to_date;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
        RAISE;
END;
/

• 若动态SQL语句长度超过VARCHAR2(4000)限制，可改用CLOB类型存储动态代码。
• 动态PL/SQL块内的业务逻辑调试相对繁琐，建议先单独验证逻辑正确性再嵌入存储过程。

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