Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1209538
  • 博文数量: 398
  • 博客积分: 10110
  • 博客等级: 上将
  • 技术积分: 4055
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-23 20:01
个人简介

新博客http://www.cnblogs.com/zhjh256 欢迎访问

文章分类

全部博文(398)

文章存档

2012年(1)

2011年(41)

2010年(16)

2009年(98)

2008年(142)

2007年(100)

我的朋友

分类: Oracle

2007-12-25 13:40:56

-- Description  : Creates the DDL to recreate the specified table.
-- Call Syntax  : @recreate_table (table-name) (schema-name)
SET SERVEROUTPUT ON
SET LINESIZE 100
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL f:\ReCreate_&&1
PROMPT
-- ----------------------------------------------
-- Reset the buffer size and display script title
-- ----------------------------------------------
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('-- Description   : Re-creation script for ' ||  Upper('&&1'));
    DBMS_Output.Put_Line('-------------------------------------------------------------');
END;
/
      
-- ------------------------------------
-- Drop existing FKs to specified table
-- ------------------------------------
DECLARE
    CURSOR cu_fks IS
        SELECT *
        FROM   all_constraints a
        WHERE  a.owner      = Upper('&&2')
        AND    a.constraint_type = 'R'
        AND    a.r_constraint_name IN (SELECT a1.constraint_name
                                       FROM   all_constraints a1
                                       WHERE  a1.table_name = Upper('&&1')
                                       AND    a1.owner      = Upper('&&2'));
BEGIN
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping Foreign Keys to ' || Upper('&&1'));
    FOR cur_rec IN cu_fks LOOP
        DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
    END LOOP;
END;
/
-- -----------------
-- Drop FKs on table
-- -----------------
DECLARE
    CURSOR cu_fks IS
        SELECT *
        FROM   all_constraints a
        WHERE  a.constraint_type = 'R'
        AND    a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
        AND    a.owner      = Upper('&&2');
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping Foreign Keys on ' || Upper('&&1'));
    FOR cur_rec IN cu_fks LOOP
        DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
    END LOOP;
END;

   
-- -------------------------
-- Drop constraints on table
-- -------------------------
DECLARE
    CURSOR cu_cons IS
        SELECT *
        FROM   all_constraints a
        WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
        AND    a.owner      = Upper('&&2')
        AND    a.constraint_type IN ('P','U');
    -- ----------------------------------------------------------------------------------------
    FUNCTION Con_Columns(p_tab  IN  VARCHAR2,
                         p_con  IN  VARCHAR2)
        RETURN VARCHAR2 IS
    -- ----------------------------------------------------------------------------------------   
        CURSOR cu_col_cursor IS
            SELECT  a.column_name
            FROM    all_cons_columns a
            WHERE   a.table_name      = p_tab
            AND     a.constraint_name = p_con
            AND     a.owner           = Upper('&&2')
            ORDER BY a.position;
    
        l_result    VARCHAR2(1000);       
    BEGIN   
        FOR cur_rec IN cu_col_cursor LOOP
            IF cu_col_cursor%ROWCOUNT = 1 THEN
                l_result   := cur_rec.column_name;
            ELSE
                l_result   := l_result || ',' || cur_rec.column_name;
            END IF;
        END LOOP;
        RETURN Lower(l_result);       
    END;
    -- ----------------------------------------------------------------------------------------
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping Constraints on ' || Upper('&&1'));
    FOR cur_rec IN cu_cons LOOP
        IF    cur_rec.constraint_type = 'P' THEN
            DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP PRIMARY KEY;');
        ELSIF cur_rec.constraint_type = 'R' THEN
            DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP CONSTRAINT ' || Lower(cur_rec.constraint_name) || ';');
        ELSIF cur_rec.constraint_type = 'U' THEN
            DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' DROP UNIQUE (' || Con_Columns(cur_rec.table_name, cur_rec.constraint_name) || ');');
        END IF;
    END LOOP;
 
END;
/
   
-- ---------------------
-- Drop indexes on table
-- ---------------------
DECLARE
    CURSOR cu_idx IS
        SELECT *
        FROM   all_indexes a
        WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
        AND    a.owner      = Upper('&&2');
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping Indexes on ' || Upper('&&1'));
    FOR cur_rec IN cu_idx LOOP
        DBMS_Output.Put_Line('DROP INDEX ' || Lower(cur_rec.index_name) || ';');
    END LOOP;
END;
/
   
-- -----------------------------------------
-- Rename existing table - prefix with 'tmp'
-- -----------------------------------------
SET VERIFY OFF
SET FEEDBACK OFF
BEGIN
    DBMS_Output.Put_Line(' ');
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Renaming ' || Upper('&&1') || ' to TMP_' || Upper('&&1'));
    DBMS_Output.Put_Line('RENAME ' || Lower('&&1') || ' TO tmp_' || Lower('&&1'));
    DBMS_Output.Put_Line('/');
END;
/
   
-- ---------------
-- Re-Create table
-- ---------------
DECLARE
    CURSOR cu_tab IS
        SELECT a.table_name,
               a.pct_free,
               a.pct_used,
               a.ini_trans,
               a.max_trans,
               a.tablespace_name,
               a.initial_extent,
               a.next_extent,
               a.min_extents,
               a.max_extents,
               a.pct_increase
        FROM   all_tables a
        WHERE  a.table_name  = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND    a.owner       = Upper('&&2');
 
    CURSOR cu_col (in_table  IN  VARCHAR2) IS
        SELECT a.column_name,
               a.data_type,
               Decode(a.data_scale,NULL,'',0,'',',' || To_Char(a.data_scale)) data_scale,
               To_Char(a.data_length) data_length,
               To_Char(a.data_precision) data_precision,
               Decode(a.nullable,'Y','NULL','N','NOT NULL') nullable
        FROM   all_tab_columns a
        WHERE  a.table_name  = in_table
        AND    a.owner       = Upper('&&2');
    CURSOR cu_tab_com IS
        SELECT a.table_name,
               a.comments
        FROM   all_tab_comments a
        WHERE  a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND    a.owner      = Upper('&&2')
        AND    a.comments  IS NOT NULL;
    CURSOR cu_col_com IS
        SELECT a.table_name,
               a.column_name,
               a.comments
        FROM   all_col_comments a
        WHERE  a.table_name = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND    a.owner      = Upper('&&2')
        AND    a.comments  IS NOT NULL;
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Script does not cope with DEFAULT VALUES or Check Constraints');
    FOR cur_tab IN cu_tab LOOP
        DBMS_Output.Put_Line('PROMPT');
        DBMS_Output.Put_Line('PROMPT Creating Table ' || cur_tab.table_name);
        DBMS_Output.Put_Line('CREATE TABLE ' || Lower(cur_tab.table_name));
        DBMS_Output.Put_Line('(');
        FOR cur_col IN cu_col (in_table => cur_tab.table_name) LOOP
            IF cu_col%ROWCOUNT != 1 THEN
                DBMS_Output.Put_Line(',');
            END IF;
            IF cur_col.data_type = 'DATE' THEN
                DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type,20,' ') || ' ' || cur_col.nullable);    
            ELSIF cur_col.data_type = 'NUMBER' THEN
                DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_precision || cur_col.data_scale || ')',20,' ') || ' ' || cur_col.nullable);    
            ELSE
                DBMS_Output.Put(RPad(Lower(cur_col.column_name),30,' ') || ' ' || RPad(cur_col.data_type || '(' || cur_col.data_length || ')',20,' ') || ' ' || cur_col.nullable);    
            END IF;
        END LOOP;
        DBMS_Output.Put_Line(' ');
        DBMS_Output.Put_Line(')');
        DBMS_Output.Put_Line('PCTFREE ' || cur_tab.pct_free);
        DBMS_Output.Put_Line('PCTUSED ' || cur_tab.pct_used);
        DBMS_Output.Put_Line('INITRANS ' || cur_tab.ini_trans);
        DBMS_Output.Put_Line('MAXTRANS ' || cur_tab.max_trans);
        DBMS_Output.Put_Line('TABLESPACE ' || cur_tab.tablespace_name);
        DBMS_Output.Put_Line('STORAGE (');
        DBMS_Output.Put_Line('  INITIAL     ' || Trunc(cur_tab.initial_extent/1024) || 'K');
        DBMS_Output.Put_Line('  NEXT        ' || Trunc(cur_tab.next_extent/1024) || 'K');
        DBMS_Output.Put_Line('  MINEXTENTS  ' || cur_tab.min_extents);
        DBMS_Output.Put_Line('  MAXEXTENTS  ' || cur_tab.max_extents);
        DBMS_Output.Put_Line('  PCTINCREASE ' || cur_tab.pct_increase);
        DBMS_Output.Put_Line(' )');
        DBMS_Output.Put_Line('/');       
        DBMS_Output.Put_Line(' ');       
    END LOOP;
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Creating Table Comment');
    FOR cur_tab_com IN cu_tab_com LOOP
        DBMS_Output.Put_Line('COMMENT ON TABLE ' || Lower(cur_tab_com.table_name) || ' IS ''' || cur_tab_com.comments || ''';');
    END LOOP;
    DBMS_Output.Put_Line(' ');
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Creating Column Comments');
    FOR cur_col_com IN cu_col_com LOOP
        DBMS_Output.Put_Line('COMMENT ON COLUMN ' || Lower(cur_col_com.table_name || '.' || cur_col_com.column_name) || ' IS ''' || cur_col_com.comments || ''';');
    END LOOP;
    DBMS_Output.Put_Line(' ');
END;
/
-- ---------------------
-- Re-Create constraints
-- ---------------------
DECLARE
    CURSOR cu_con IS
        SELECT *
        FROM   all_constraints a
        WHERE  a.table_name       = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND    a.owner            = Upper('&&2')
        AND    a.constraint_type IN ('U','P')
        ORDER BY a.constraint_name;
    CURSOR cu_idx (p_idx  IN  VARCHAR2) IS
        SELECT *
        FROM   all_indexes a
        WHERE  a.index_name  = p_idx
        AND    a.table_owner = Upper('&&2')
        ORDER BY a.index_name;
    -- ----------------------------------------------------------------------------------------
    FUNCTION Con_Columns(p_tab  IN  VARCHAR2,
                         p_con  IN  VARCHAR2)
        RETURN VARCHAR2 IS
    -- ----------------------------------------------------------------------------------------   
        CURSOR cu_col_cursor IS
            SELECT  a.column_name
            FROM    all_cons_columns a
            WHERE   a.table_name      = p_tab
            AND     a.constraint_name = p_con
            AND     a.owner           = Upper('&&2')
            ORDER BY a.position;
    
        l_result    VARCHAR2(1000);       
    BEGIN   
        FOR cur_rec IN cu_col_cursor LOOP
            IF cu_col_cursor%ROWCOUNT = 1 THEN
                l_result   := cur_rec.column_name;
            ELSE
                l_result   := l_result || ',' || cur_rec.column_name;
            END IF;
        END LOOP;
        RETURN Lower(l_result);       
    END;
    -- ----------------------------------------------------------------------------------------
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    FOR cur_rec IN cu_con LOOP
        DBMS_Output.Put_Line('PROMPT');
        DBMS_Output.Put_Line('PROMPT Creating Constraint ' || cur_rec.constraint_name);
        DBMS_Output.Put_Line('ALTER TABLE ' || Lower(cur_rec.table_name) || ' ADD');
        DBMS_Output.Put_Line('(');
        DBMS_Output.Put_Line(' CONSTRAINT ' || Lower(cur_rec.constraint_name));
        IF cur_rec.constraint_type = 'U' THEN
            DBMS_Output.Put_Line(' UNIQUE ' || ' ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
        ELSE
            DBMS_Output.Put_Line(' PRIMARY KEY ' || ' ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
        END IF;
        FOR cur_idx IN cu_idx (cur_rec.constraint_name) LOOP
            DBMS_Output.Put_Line(' USING INDEX');
            DBMS_Output.Put_Line('  INITRANS ' || cur_idx.ini_trans);
            DBMS_Output.Put_Line('  MAXTRANS ' || cur_idx.max_trans);
            DBMS_Output.Put_Line('  PCTFREE  ' || cur_idx.pct_free);
            DBMS_Output.Put_Line('  TABLESPACE ' || cur_idx.tablespace_name);
            DBMS_Output.Put_Line('  STORAGE (');
            DBMS_Output.Put_Line('    INITIAL     ' || Trunc(cur_idx.initial_extent/1024) || 'K');
            DBMS_Output.Put_Line('    NEXT        ' || Trunc(cur_idx.next_extent/1024) || 'K');
            DBMS_Output.Put_Line('    MINEXTENTS  ' || cur_idx.min_extents);
            DBMS_Output.Put_Line('    MAXEXTENTS  ' || cur_idx.max_extents);
            DBMS_Output.Put_Line('    PCTINCREASE ' || cur_idx.pct_increase);
            DBMS_Output.Put_Line('   )');
        END LOOP;
        DBMS_Output.Put_Line(')');
        DBMS_Output.Put_Line('/');
        DBMS_Output.Put_Line(' ');
    END LOOP;
END;
/
-- ---------------------
-- Recreate FKs on table
-- ---------------------
DECLARE
    CURSOR cu_cons IS
        SELECT  a.table_name,
                a.constraint_name,
                a.r_constraint_name,
                NVL(a.delete_rule,' ') delete_rule,
                NVL(a.status,' ') status
        FROM    all_constraints a
        WHERE   a.table_name        = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND     a.owner             = Upper('&&2')
        AND     a.constraint_type   = 'R'
        ORDER BY a.table_name, a.constraint_name;
  v_r_table_name all_constraints.table_name%TYPE;
    -- ----------------------------------------------------------------------------------------
    FUNCTION Con_Columns(p_tab  IN  VARCHAR2,
                         p_con  IN  VARCHAR2)
        RETURN VARCHAR2 IS
    -- ----------------------------------------------------------------------------------------   
        CURSOR cu_col_cursor IS
            SELECT  a.column_name
            FROM    all_cons_columns a
            WHERE   a.table_name      = p_tab
            AND     a.constraint_name = p_con
            AND     a.owner           = Upper('&&2')
            ORDER BY a.position;
    
        l_result    VARCHAR2(1000);       
    BEGIN   
        FOR cur_rec IN cu_col_cursor LOOP
            IF cu_col_cursor%ROWCOUNT = 1 THEN
                l_result   := cur_rec.column_name;
            ELSE
                l_result   := l_result || ',' || cur_rec.column_name;
            END IF;
        END LOOP;
        RETURN Lower(l_result);       
    END;
    -- ----------------------------------------------------------------------------------------
BEGIN
   
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    FOR cur_rec IN cu_cons LOOP
        SELECT a.table_name
        INTO   v_r_table_name
        FROM   all_constraints a
        WHERE  a.constraint_name = cur_rec.r_constraint_name;
        DBMS_Output.Put_Line('ALTER TABLE '||Lower(cur_rec.table_name)||' ADD');
        DBMS_Output.Put_Line(' (');
        DBMS_Output.Put_Line(' CONSTRAINT '||Lower(cur_rec.constraint_name));
        DBMS_Output.Put_Line(' FOREIGN KEY ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
        DBMS_Output.Put_Line(' REFERENCES '||Lower(v_r_table_name)||' ('||Con_Columns(v_r_table_name, cur_rec.r_constraint_name)||')');
           
        IF cur_rec.delete_rule = 'CASCADE' THEN
            DBMS_Output.Put_Line('  ON DELETE CASCADE');
        END IF;
        IF cur_rec.status = 'DISABLE' THEN
            DBMS_Output.Put_Line('  DISABLE');
        END IF;
        DBMS_Output.Put_Line(' )');
        DBMS_Output.Put_Line('/');
        DBMS_Output.Put_Line(' ');
    END LOOP;
END;
/
-- -----------------
-- Re-Create indexes
-- -----------------
DECLARE
    CURSOR cu_idx IS
        SELECT Decode(a.uniqueness,'UNIQUE','UNIQUE ',NULL) AS uniqueness,
               a.index_name,
               a.table_name,
               a.ini_trans,
               a.max_trans,
               a.pct_free,
               a.tablespace_name,
               a.initial_extent,
               a.next_extent,
               a.min_extents,
               a.max_extents,
               a.pct_increase
        FROM   all_indexes a
        WHERE  a.table_name  = Decode(Upper('&&1'), 'ALL',a.table_name, Upper('&&1'))
        AND    a.table_owner = Upper('&&2')
        AND    NOT EXISTS (SELECT '1'
                           FROM   all_constraints b
                           WHERE  b.constraint_name = a.index_name
                           AND    b.constraint_type = 'P'
                           AND    b.owner           = Upper('&&2'))
        ORDER BY a.index_name;
    CURSOR cu_col (p_idx  IN  VARCHAR2) IS
        SELECT a.column_name
        FROM   all_ind_columns a
        WHERE  a.index_name  = p_idx
        AND    a.index_owner = Upper('&&2')
        ORDER BY a.column_position;
BEGIN
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    FOR cur_idx IN cu_idx LOOP
        DBMS_Output.Put_Line('PROMPT');
        DBMS_Output.Put_Line('PROMPT Creating Index ' || cur_idx.index_name);
        DBMS_Output.Put_Line('CREATE ' || cur_idx.uniqueness || 'INDEX ' || Lower(cur_idx.index_name));
        DBMS_Output.Put_Line('ON ' || Lower(cur_idx.table_name));
        DBMS_Output.Put_Line('(');
        FOR cur_col IN cu_col (cur_idx.index_name) LOOP
            IF cu_col%ROWCOUNT != 1 THEN
                DBMS_Output.Put_Line(',');
            END IF;
            DBMS_Output.Put(' ' || lower(cur_col.column_name));    
        END LOOP;
        DBMS_Output.Put_Line(' ');
        DBMS_Output.Put_Line(')');
        DBMS_Output.Put_Line('INITRANS ' || cur_idx.ini_trans);
        DBMS_Output.Put_Line('MAXTRANS ' || cur_idx.max_trans);
        DBMS_Output.Put_Line('PCTFREE ' || cur_idx.pct_free);
        DBMS_Output.Put_Line('TABLESPACE ' || cur_idx.tablespace_name);
        DBMS_Output.Put_Line('STORAGE (');
        DBMS_Output.Put_Line('  INITIAL     ' || Trunc(cur_idx.initial_extent/1024) || 'K');
        DBMS_Output.Put_Line('  NEXT        ' || Trunc(cur_idx.next_extent/1024) || 'K');
        DBMS_Output.Put_Line('  MINEXTENTS  ' || cur_idx.min_extents);
        DBMS_Output.Put_Line('  MAXEXTENTS  ' || cur_idx.max_extents);
        DBMS_Output.Put_Line('  PCTINCREASE ' || cur_idx.pct_increase);
        DBMS_Output.Put_Line('  )');
        DBMS_Output.Put_Line('/');       
        DBMS_Output.Put_Line(' ');       
    END LOOP;
END;
/
   
-- --------------------------
-- Build up population insert
-- --------------------------
SET VERIFY OFF
SET FEEDBACK OFF
DECLARE
    CURSOR cu_columns IS
        SELECT Lower(column_name) column_name
        FROM   all_tab_columns atc
        WHERE  atc.table_name = Upper('&&1')
        AND    atc.owner      = Upper('&&2');
BEGIN
    DBMS_Output.Put_Line(' ');
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Populating ' || Upper('&&1') || ' from TPM_' || Upper('&&1'));
    DBMS_Output.Put_Line('INSERT INTO ' || Lower('&&1'));
    DBMS_Output.Put('SELECT ');
    FOR cur_rec IN cu_columns LOOP
        IF cu_columns%ROWCOUNT != 1 THEN
            DBMS_Output.Put_Line(',');
        END IF;
        DBMS_Output.Put(' a.' || cur_rec.column_name);
    END LOOP;
    DBMS_Output.New_Line;
    DBMS_Output.Put_Line('FROM tmp_' || Lower('&&1') || ' a');
    DBMS_Output.Put_Line('/');
     
    -- --------------
    -- Drop tmp table
    -- --------------
    DBMS_Output.Put_Line(' ');
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Droping TMP_' || Upper('&&1'));
    DBMS_Output.Put_Line('DROP TABLE tmp_' || Lower('&&1'));
    DBMS_Output.Put_Line('/');
END;
/
-- ---------------------
-- Recreate FKs to table
-- ---------------------
DECLARE
    CURSOR cu_cons IS
        SELECT  a.table_name,
                a.constraint_name,
                a.r_constraint_name,
                NVL(a.delete_rule,' ') delete_rule,
                NVL(a.status,' ') status
        FROM    all_constraints a
        WHERE   a.r_constraint_name IN (SELECT  b.constraint_name
                                        FROM    all_constraints b
                                        WHERE   b.table_name       = Upper('&&1')
                                        AND     b.owner            = Upper('&&2')
                                        AND     b.constraint_type IN ('P', 'U') )
        AND     a.owner = Upper('&&2')
        ORDER BY a.table_name, a.constraint_name;
    -- ----------------------------------------------------------------------------------------
    FUNCTION Con_Columns(p_tab  IN  VARCHAR2,
                         p_con  IN  VARCHAR2)
        RETURN VARCHAR2 IS
    -- ----------------------------------------------------------------------------------------   
        CURSOR cu_col_cursor IS
            SELECT  a.column_name
            FROM    all_cons_columns a
            WHERE   a.table_name      = p_tab
            AND     a.constraint_name = p_con
            AND     a.owner           = Upper('&&2')
            ORDER BY a.position;
    
        l_result    VARCHAR2(1000);       
    BEGIN   
        FOR cur_rec IN cu_col_cursor LOOP
            IF cu_col_cursor%ROWCOUNT = 1 THEN
                l_result   := cur_rec.column_name;
            ELSE
                l_result   := l_result || ',' || cur_rec.column_name;
            END IF;
        END LOOP;
        RETURN Lower(l_result);       
    END;
    -- ----------------------------------------------------------------------------------------
BEGIN
   
    DBMS_Output.Disable;
    DBMS_Output.Enable(1000000);
    DBMS_Output.Put_Line('PROMPT');
    DBMS_Output.Put_Line('PROMPT Createing Foreign Keys to ' || Upper('&&1'));
    FOR cur_rec IN cu_cons LOOP
        DBMS_Output.Put_Line('ALTER TABLE '||Lower(cur_rec.table_name)||' ADD');
        DBMS_Output.Put_Line('(');
        DBMS_Output.Put_Line(' CONSTRAINT '||Lower(cur_rec.constraint_name));
        DBMS_Output.Put_Line(' FOREIGN KEY ('||Con_Columns(cur_rec.table_name, cur_rec.constraint_name)||')');
        DBMS_Output.Put_Line(' REFERENCES '||Lower('&&1')||' ('||Con_Columns(Upper('&&1'), cur_rec.r_constraint_name)||')');
           
        IF cur_rec.delete_rule = 'CASCADE' THEN
            DBMS_Output.Put_Line('  ON DELETE CASCADE');
        END IF;
        IF cur_rec.status = 'DISABLE' THEN
            DBMS_Output.Put_Line('  DISABLE');
        END IF;
        DBMS_Output.Put_Line(')');
        DBMS_Output.Put_Line('/');
        DBMS_Output.Put_Line(' ');
    END LOOP;
END;
/
SET VERIFY OFF
SET FEEDBACK OFF
BEGIN   
    DBMS_Output.Put_Line(' ');
    DBMS_Output.Put_Line('-------------------------------------------------------------');
    DBMS_Output.Put_Line('-- END Re-creation script for ' || Upper('&&1'));
    DBMS_Output.Put_Line('-------------------------------------------------------------');
END;
/
SPOOL OFF
PROMPT
SET VERIFY ON
SET FEEDBACK ON
SET TERMOUT ON
 
阅读(781) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~