获取指定用户表结构创建语句含索引和约束
通常我们可以使用dbms_metadata.get_ddl来获取表的ddl信息,但是此方法返回的是CLOB类型,不方便使用,并且需要相应的授权,这里我们直接使用all_tab_columns和all_constraints
、all_cons_columns字典视图来获取表的创建语句。
create or replace procedure p_get_tableddl(table_name varchar2, table_owner varchar2) AS
CURSOR cur_columns(iv_table_name VARCHAR2, iv_owner VARCHAR2) IS
SELECT column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable,
column_id,
data_default
FROM all_tab_columns
WHERE table_name = iv_table_name
AND owner = iv_owner
ORDER BY column_id;
rec_columns cur_columns%ROWTYPE;
--
CURSOR cur_constraint(iv_table_name VARCHAR2, iv_owner VARCHAR2) IS
SELECT constraint_name,
constraint_type,
search_condition,
r_owner,
r_constraint_name,
delete_rule,
status
FROM all_constraints
WHERE table_name = iv_table_name
AND owner = iv_owner;
--
CURSOR cur_cons_columns(iv_constraint_name VARCHAR2, iv_owner VARCHAR2) IS
SELECT column_name, table_name
FROM all_cons_columns
WHERE constraint_name = iv_constraint_name
AND owner = iv_owner
ORDER BY position;
--
CURSOR cur_index(iv_table_name VARCHAR2, iv_owner VARCHAR2) IS
SELECT index_name, lower(tablespace_name) tablespace_name
FROM all_indexes
WHERE table_name = iv_table_name
AND owner = iv_owner;
--
CURSOR cur_ind_columns(iv_index_name VARCHAR2, iv_owner VARCHAR2) IS
SELECT lower(column_name) column_name
FROM all_ind_columns
WHERE index_name = iv_index_name
AND index_owner = iv_owner
ORDER BY index_name, column_position;
--
v_table_name all_tables.table_name%TYPE;
v_owner all_tables.owner%TYPE;
v_tablespace_name all_tables.tablespace_name%TYPE;
v_pre_column_name all_tab_columns.column_name%TYPE;
v_pre_column VARCHAR2(200);
v_constraint VARCHAR2(200);
v_index VARCHAR2(200);
v_r_table_name all_cons_columns.table_name%TYPE;
BEGIN
v_table_name := upper(table_name);
v_owner := upper(table_owner);
--
SELECT lower(tablespace_name)
INTO v_tablespace_name
FROM all_tables
WHERE table_name = v_table_name
AND owner = v_owner;
-- --------------- --
-- get all columns --
-- --------------- --
OPEN cur_columns(v_table_name, v_owner);
LOOP
FETCH cur_columns
INTO rec_columns;
--
IF v_pre_column_name IS NOT NULL THEN
IF v_pre_column_name = lower(rec_columns.column_name) THEN
dbms_output.put_line(v_pre_column);
dbms_output.put_line(') tablespace ' || v_tablespace_name);
dbms_output.put_line('/');
ELSIF rec_columns.column_id = 2 THEN
dbms_output.put_line('create table ' || lower(v_table_name) || ' (');
dbms_output.put_line(v_pre_column || ',');
ELSE
dbms_output.put_line(v_pre_column || ',');
END IF;
END IF;
--
EXIT WHEN cur_columns%NOTFOUND;
--
v_pre_column_name := lower(rec_columns.column_name);
v_pre_column := rpad(lower(rec_columns.column_name), 31, ' ');
IF rec_columns.data_type = 'NUMBER' THEN
IF rec_columns.data_precision IS NULL THEN
v_pre_column := v_pre_column || 'number';
ELSIF rec_columns.data_scale = 0 THEN
v_pre_column := v_pre_column || 'number(' ||
to_char(rec_columns.data_precision) || ')';
ELSE
v_pre_column := v_pre_column || 'number(' ||
to_char(rec_columns.data_precision) || ',' ||
to_char(rec_columns.data_scale) || ')';
END IF;
ELSIF instr(rec_columns.data_type, 'CHAR') > 0 THEN
v_pre_column := v_pre_column || lower(rec_columns.data_type) || '(' ||
to_char(rec_columns.data_length) || ')';
ELSE
v_pre_column := v_pre_column || lower(rec_columns.data_type);
END IF;
--
IF rec_columns.data_default IS NOT NULL THEN
v_pre_column := rpad(v_pre_column, 46, ' ') || ' default ' ||
rtrim(rec_columns.data_default);
END IF;
--
IF rec_columns.nullable = 'N' THEN
IF length(v_pre_column) > 46 THEN
v_pre_column := v_pre_column || ' not null';
ELSE
v_pre_column := rpad(v_pre_column, 46, ' ') || ' not null';
END IF;
END IF;
--
END LOOP;
CLOSE cur_columns;
--
FOR rec_c IN cur_constraint(v_table_name, v_owner) LOOP
IF rec_c.constraint_type = 'P' THEN
v_constraint := ' (';
FOR rec_column IN cur_cons_columns(rec_c.constraint_name, v_owner) LOOP
v_constraint := v_constraint || lower(rec_column.column_name) || ', ';
END LOOP;
v_constraint := substr(v_constraint, 1, length(v_constraint) - 2) || ')';
dbms_output.put_line('alter table ' || lower(v_table_name));
dbms_output.put_line(' add constraint ' || rec_c.constraint_name ||
' primary key');
dbms_output.put_line(v_constraint);
--
SELECT lower(tablespace_name)
INTO v_tablespace_name
FROM all_indexes
WHERE table_name = v_table_name
AND index_name = rec_c.constraint_name
AND owner = v_owner;
dbms_output.put_line(' using index tablespace ' || v_tablespace_name);
dbms_output.put_line('/');
ELSIF rec_c.constraint_type = 'R' THEN
v_constraint := ' (';
FOR rec_column IN cur_cons_columns(rec_c.constraint_name, v_owner) LOOP
v_constraint := v_constraint || lower(rec_column.column_name) || ', ';
END LOOP;
v_constraint := substr(v_constraint, 1, length(v_constraint) - 2) || ')';
dbms_output.put_line('alter table ' || lower(v_table_name));
dbms_output.put_line(' add constraint ' || rec_c.constraint_name ||
' foreign key');
dbms_output.put_line(v_constraint);
v_constraint := ' (';
FOR rec_column IN cur_cons_columns(rec_c.r_constraint_name, v_owner) LOOP
v_constraint := v_constraint || lower(rec_column.column_name) || ', ';
v_r_table_name := lower(rec_column.table_name);
END LOOP;
v_constraint := substr(v_constraint, 1, length(v_constraint) - 2) || ')';
dbms_output.put_line(' references ' || v_r_table_name);
dbms_output.put_line(v_constraint);
dbms_output.put_line('/');
ELSIF rec_c.constraint_type = 'U' THEN
v_constraint := ' (';
FOR rec_column IN cur_cons_columns(rec_c.constraint_name, v_owner) LOOP
v_constraint := v_constraint || lower(rec_column.column_name) || ', ';
END LOOP;
v_constraint := substr(v_constraint, 1, length(v_constraint) - 2) || ')';
dbms_output.put_line('alter table ' || lower(v_table_name));
dbms_output.put_line(' add constraint ' || rec_c.constraint_name ||
' unique');
dbms_output.put_line(v_constraint);
--
SELECT lower(tablespace_name)
INTO v_tablespace_name
FROM all_indexes
WHERE table_name = v_table_name
AND index_name = rec_c.constraint_name
AND owner = v_owner;
dbms_output.put_line(' using index tablespace ' || v_tablespace_name);
dbms_output.put_line('/');
ELSIF rec_c.constraint_type = 'C' AND
instr(rec_c.search_condition, 'IS NOT NULL') = 0 THEN
dbms_output.put_line('alter table ' || lower(v_table_name));
dbms_output.put_line(' add constraint ' || rec_c.constraint_name ||
' check');
dbms_output.put_line(' (' || ltrim(rtrim(rec_c.search_condition)) || ')');
dbms_output.put_line('/');
END IF;
END LOOP;
--
FOR rec_i IN cur_index(v_table_name, v_owner) LOOP
dbms_output.put_line('create index ' || rec_i.index_name);
v_index := ' on ' || lower(v_table_name) || ' (';
FOR rec_c IN cur_ind_columns(rec_i.index_name, v_owner) LOOP
v_index := v_index || rec_c.column_name || ', ';
END LOOP;
v_index := substr(v_index, 1, length(v_index) - 2) || ')';
dbms_output.put_line(v_index);
dbms_output.put_line(' tablespace ' || rec_i.tablespace_name);
dbms_output.put_line('/');
END LOOP;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No this table!');
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || SQLERRM);
END;
