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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-773969/