实现一个删除表约束为Load数据做准备的处理流程

--1创建临时存储元数据的表
CREATE OR REPLACE PROCEDURE create_temp_table
AS
    v_crtable varchar2(32767):=Q'[CREATE TABLE temp_meta (metadata VARCHAR2(4000))]';
BEGIN
    EXECUTE IMMEDIATE v_crtable; 
END create_temp_table;

--2存储过程实现删除用户表约束为Load数据作准备
CREATE OR REPLACE PROCEDURE drop_user_constraints(p_table_name varchar2)
AS
    v_object_name varchar2(50);
    v_object_type varchar2(50); 
    v_drop_stmt varchar2(32767);
    v_alter_stmt varchar2(32767):=Q'[ALTER TABLE ]';
    TYPE cur IS REF CURSOR;
    meta_cur cur; 
BEGIN
    --通过忽略创建脚本的存储参数来减少元数据长度
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
    --获取所创建的约束元数据
    OPEN meta_cur FOR SELECT uc.constraint_type,uc.constraint_name 
                        FROM user_constraints uc
                       WHERE uc.table_name = UPPER(p_table_name)
                         AND uc.constraint_type <> 'C';
    LOOP
    FETCH meta_cur INTO v_object_type,v_object_name;
    EXIT WHEN meta_cur%NOTFOUND;
        v_alter_stmt := v_alter_stmt||p_table_name
                                    ||Q'[ DISABLE CONSTRAINT ]'
                                    ||v_object_name;
        EXECUTE IMMEDIATE v_alter_stmt;
        v_alter_stmt := Q'[ALTER TABLE ]';                           
    END LOOP;
    --获取所创建的索引的元数据 
    OPEN meta_cur FOR SELECT ui.index_type,ui.index_name 
                        FROM user_indexes ui
                       WHERE ui.table_name = UPPER(p_table_name);
    LOOP
    FETCH meta_cur INTO v_object_type,v_object_name;
    EXIT WHEN meta_cur%NOTFOUND;                   
        --将元数据插入到临时表中保存
        INSERT INTO temp_meta 
            SELECT to_char(dbms_metadata.get_ddl('INDEX',v_object_name)) FROM dual;
        --动态SQL删除原表上的索引
        v_drop_stmt := Q'[DROP INDEX ]'||v_object_name;
        EXECUTE IMMEDIATE v_drop_stmt;              
    END LOOP;
END drop_user_constraints;


--3创建存储过程回复原表约束
CREATE OR REPLACE PROCEDURE recovery_user_constrains(p_table_name varchar2)
AS
    v_create_sql varchar2(32767);
    v_object_name varchar2(50);
    v_alter_sql varchar2(32767);
    TYPE cur IS REF CURSOR;
    con_cur cur;
BEGIN
    --重创建的索引
    OPEN con_cur FOR SELECT tm.metadata FROM temp_meta tm;
    LOOP
    FETCH con_cur INTO v_create_sql;
    EXIT WHEN con_cur%NOTFOUND; 
        EXECUTE IMMEDIATE v_create_sql;
    END LOOP;
    --启用所有约束
    OPEN con_cur FOR SELECT uc.constraint_name FROM user_constraints uc
                      WHERE uc.table_name = UPPER(p_table_name)
                        AND uc.status = 'DISABLED';
    LOOP
        FETCH con_cur INTO v_object_name;
        EXIT WHEN con_cur%NOTFOUND;
        v_alter_sql := Q'[ALTER TABLE ]' || p_table_name
                                         || Q'[ ENABLE CONSTRAINT ]'
                                         || v_object_name;
        EXECUTE IMMEDIATE v_alter_sql;               
    END LOOP;                    
END recovery_user_constrains;