簡單的反向生產DDL語句的指令碼

abstractcyj發表於2013-10-09
指令碼
DECLARE
  v_ddlsql VARCHAR2(32767);
BEGIN
  FOR rec IN (SELECT * FROM user_tables WHERE table_name = 'T2') LOOP
    v_ddlsql := 'create table ' || rec.table_name || '(';
    FOR cols IN (SELECT *
                   FROM user_tab_cols c
                  WHERE c.table_name = rec.table_name
                  ORDER BY c.column_id) LOOP
    
      v_ddlsql := v_ddlsql || ' ' || cols.column_name;
    
      IF cols.data_type = 'VARCHAR2' THEN
        IF cols.char_used = 'B' THEN
          v_ddlsql := v_ddlsql || ' varchar2(' || cols.data_length || ')';
        ELSE
          v_ddlsql := v_ddlsql || ' varchar2(' || cols.char_length ||
                      ' char)';
        END IF;
      ELSIF cols.data_type = 'NUMBER' THEN
        IF cols.data_precision IS NOT NULL THEN
          v_ddlsql := v_ddlsql || ' number(' || cols.data_precision || ',' ||
                      cols.data_scale || ')';
        ELSE
          v_ddlsql := v_ddlsql || ' number(38,0)';
        END IF;
      ELSIF cols.data_type = 'BLOB' THEN
        v_ddlsql := v_ddlsql || ' blob ';
      ELSIF cols.data_type = 'CLOB' THEN
        v_ddlsql := v_ddlsql || ' clob ';
      ELSIF cols.data_type = 'DATE' THEN
        v_ddlsql := v_ddlsql || ' date ';
      END IF;
    
      IF cols.data_default IS NOT NULL THEN
        v_ddlsql := v_ddlsql || ' default ' || cols.data_default;
      END IF;
    
      IF cols.nullable = 'N' THEN
        v_ddlsql := v_ddlsql || ' not null ';
      END IF;
    
      v_ddlsql := v_ddlsql || ',' || chr(10);
    
    END LOOP;
    --處理約束
  
    FOR rec_cons IN (SELECT uc.constraint_name,
                            decode(uc.constraint_type,
                                   'C',
                                   'check',
                                   'P',
                                   'primary key',
                                   'U',
                                   'unique') constraint_type,
                            uc.table_name,
                            uc.search_condition search_condition
                       FROM user_constraints uc
                      WHERE uc.table_name = rec.table_name
                        AND uc.constraint_name NOT LIKE 'SYS%') LOOP
      v_ddlsql := v_ddlsql || ' constraint ' || rec_cons.constraint_name || ' ' ||
                  rec_cons.constraint_type;
    
      IF rec_cons.constraint_type = 'check' THEN
        v_ddlsql := v_ddlsql || ' (' || rec_cons.search_condition || '),' ||
                    chr(10);
      ELSE
        v_ddlsql := v_ddlsql || ' (';
        FOR rec_cons_cols IN (SELECT *
                                FROM user_cons_columns s
                               WHERE s.constraint_name =
                                     rec_cons.constraint_name
                               ORDER BY s.position) LOOP
          v_ddlsql := v_ddlsql || rec_cons_cols.column_name || ',';
        END LOOP;
        v_ddlsql := substr(v_ddlsql, 1, length(v_ddlsql) - 1);
        v_ddlsql := v_ddlsql || ') using index,' || chr(10);
      END IF;
    
    END LOOP;
  
    v_ddlsql := substr(v_ddlsql, 1, length(v_ddlsql) - 2);
    v_ddlsql := v_ddlsql || ');' || chr(10);
  
    --處理索引
    FOR rec_index IN (SELECT *
                        FROM user_indexes ind
                       WHERE ind.table_name = rec.table_name
                         AND ind.index_name NOT LIKE 'SYS%'
                         AND ind.uniqueness <> 'UNIQUE') LOOP
    
      v_ddlsql := v_ddlsql || 'create index ' || rec_index.index_name ||
                  ' on ' || rec.table_name || '(';
    
      FOR rec_index_cols IN (SELECT *
                               FROM user_ind_columns uic
                              WHERE uic.table_name = rec.table_name
                                AND uic.index_name = rec_index.index_name
                              ORDER BY uic.column_position) LOOP
        v_ddlsql := v_ddlsql || rec_index_cols.column_name || ',';
      END LOOP;
    
      v_ddlsql := substr(v_ddlsql, 1, length(v_ddlsql) - 1);
      v_ddlsql := v_ddlsql || ');' || chr(10);
    END LOOP;
  
    dbms_output.put_line(v_ddlsql);
  END LOOP;
END;

測試：

create table t2(
 a number not null,
 b varchar2(40),
 c date default sysdate  not null ,
 d varchar2(10 char),
 e clob,
 f blob
)

alter table t2 add constraint pk_t2 primary key (a) using index;

create index t2_index1 on t2(b,c);

程式碼輸出：

create table T2( A number(38,0) not null ,
 B varchar2(40),
 C date  default sysdate   not null ,
 D varchar2(10 char),
 E clob ,
 F blob ,
 constraint PK_T2 primary key (A) using index);
create index T2_INDEX1 on T2(B,C);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-773969/，如需轉載，請註明出處，否則將追究法律責任。

相關文章