如何修改存储过程实现通过游标遍历指定Schema下的所有表并将表信息及对应DDL插入备份表？

Let's adjust your stored procedure to dynamically grab the DDL for every table in the MYSCHEMA schema instead of just the fixed INVOICING table. Here's the revised version, plus breakdowns of the key fixes:

Revised Stored Procedure #

create or replace procedure proc_getddl is
    v_tableschema varchar(30);
    v_tablename varchar(30);
    v_tabletype varchar(30);
    -- Swap to CLOB to avoid truncating long DDL statements (tables with constraints/indexes get big!)
    v_getddl clob;
    -- Cursor now pulls DDL dynamically for each table using the table_name column
    cursor getddl is
        select 
            table_schema,
            table_name,
            table_type,
            get_ddl('table', table_name)  -- Replace hardcoded table name with dynamic column value
        from information_schema.tables 
        where table_schema = 'MYSCHEMA';
begin
    open getddl;
    LOOP
        fetch getddl into v_tableschema, v_tablename, v_tabletype, v_getddl;
        EXIT WHEN getddl%NOTFOUND;
        -- Add explicit column names to INSERT for better maintainability
        INSERT INTO backup_table (table_schema, table_name, table_type, ddl_content)
        values (v_tableschema, v_tablename, v_tabletype, v_getddl);
    END LOOP;
    close getddl;
    commit;  -- Commit all inserts at once (adjust to commit per row if needed)
end proc_getddl;

Key Changes Explained #

• Dynamic DDL Fetch: The core fix is swapping the hardcoded 'INVOICING' in get_ddl with the table_name column from your cursor query. This ensures each row returns the DDL for the corresponding table in MYSCHEMA.
• DDL Variable Type Upgrade: Changed v_getddl from varchar(110) to clob—most table DDLs are way longer than 110 characters, so this prevents truncation of important schema details.
• Explicit INSERT Columns: Listing the target columns in the INSERT statement makes the code more robust, in case the backup_table schema is modified later.
• Transaction Handling: Added a final commit (you can move this inside the loop if you prefer committing after each table, but bulk commits are usually more efficient).

Quick Notes #

• Double-check that your backup_table has a column capable of storing large text (like clob or varchar2(4000) if using Oracle) for the DDL content.
• If you want to reuse this procedure for other schemas, you could add a schema name parameter instead of hardcoding 'MYSCHEMA'.

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