--
-- 包頭定義
--
CREATE OR REPLACE PACKAGE srcmake AS
PROCEDURE maktab;
PROCEDURE makview;
PROCEDURE makseq;
PROCEDURE makcon(tabName VARCHAR2);
END srcmake;
/
--
-- 包體定義
--
CREATE OR REPLACE PACKAGE BODY srcmake AS
--
-- 處理超過255個字符的行的輸出
--
PROCEDURE dealline(initStr VARCHAR2) IS
lineCount INTEGER;
i INTEGER;
BEGIN
lineCount := ceil(length(initStr)/255);
FOR i IN 1..lineCount LOOP
dbms_output.put_line(substr(initStr,1 + 255 * (i - 1),255));
END LOOP;
END dealline;
--
-- 生成創建表的SQL文件
--
PROCEDURE maktab IS
tempStr varchar2(4000);
countNum integer;
i integer;
BEGIN
dbms_output.enable(9E38);
-- 輸出sql文件說明信息
dbms_output.put_line('REM create table''s sql');
dbms_output.put_line('REM database user name:'user);
dbms_output.put_line('REM outputTime:'sysdate);
-- 查詢用戶的所有的表
FOR curtab IN(
SELECT a.table_name table_name,a.tablespace_name,b.comments comments
FROM user_tables A,user_tab_comments b
WHERE a.table_name = b.table_name AND b.table_type = 'TABLE'
ORDER BY a.table_name)
LOOP
-- 輸出表信息
dbms_output.put_line(chr(10)'DROP TABLE 'curtab.table_name';');
dbms_output.put_line('-- 表名：'curtab.table_name);
dbms_output.put_line('-- 備注：'curtab.comments);
dbms_output.put_line('CREATE TABLE 'curtab.table_name'(');
SELECT count(column_name) INTO countNum FROM user_tab_columns
WHERE table_name = curtab.table_name;
i := 0;
-- 查詢表所有的列
FOR curcol IN(
SELECT a.*,b.comments
FROM user_tab_columns a,user_col_comments b
WHERE a.table_name = curtab.table_name AND a.table_name = b.table_name
AND a.column_name = b.column_name ORDER BY column_id)
LOOP
tempStr := chr(9)rpad(curcol.column_name,31,' ')curcol.data_type;
-- 以下類型需要指定長度
IF curcol.data_type IN('VARCHAR2','CHAR','VARCHAR','RAW') THEN
tempStr := tempStr'('curcol.data_length')';
-- 數字類型存在精度問題
ELSIF curcol.data_type = 'NUMBER' THEN
IF curcol.data_precision IS NOT NULL THEN
tempStr := tempStr'('curcol.data_precision;
IF curcol.data_scale IS NOT NULL THEN
tempStr := tempStr','curcol.data_scale')';
ELSE
tempStr := tempStr')';
END IF;
ELSIF curcol.data_scale = 0 THEN
tempStr := tempStr'(38)';
END IF;
END IF;
-- LONG,LONG RAW,CLOB,NLOB,BLOB,ROWID類型不需指定長度
IF curcol.nullable = 'N' THEN -- 指定非空標志
tempStr := tempStr' NOT NULL';
END IF;
i := i + 1;
-- 最后一列不需逗號
IF i !
= countNum THEN
tempStr := tempStr',';
END IF;
-- 輸出列的信息
IF curcol.comments IS NOT NULL THEN
dbms_output.put_line(rpad(tempStr,60,' ')'-- 'curcol.comments);
ELSE
dbms_output.put_line(tempStr);
END IF;
END LOOP;
-- 輸出表空間信息
dbms_output.put_line(') TABLESPACE 'curtab.tablespace_name';');
-- 輸出表約束
makcon(curtab.table_name);
END LOOP;
dbms_output.put_line(chr(10)chr(10));
END maktab;
--
-- 生成表的約束(primary key,foreign key)
-- parameter:tabName表名稱
PROCEDURE makcon(tabName VARCHAR2) IS
tempStr VARCHAR2(4000);
tempColStr VARCHAR2(2000);
BEGIN
FOR curcon IN(
SELECT owner,constraint_name name,constraint_type type,
r_constraint_name rname,delete_rule,r_owner,table_name
FROM user_constraints WHERE table_name = tabName AND constraint_type IN('P','R','U'))
LOOP
-- 輸出約束信息
tempStr := 'ALTER TABLE 'tabName' ADD CONSTRAINTS 'curcon.name;
FOR curcol IN(SELECT column_name FROM user_cons_columns
WHERE constraint_name = curcon.name) LOOP
tempColStr := tempColStrcurcol.column_name',';
END LOOP;
tempColStr := substr(tempColStr,0,length(tempColStr) - 1);
-- 輸出約束的列信息
IF curcon.type = 'P' THEN -- 主鍵
tempStr := tempStr' PRIMARY KEY('tempColStr');';
ELSIF curcon.type = 'R' THEN -- 外鍵
tempStr := tempStr' FOREIGN KEY('tempColStr') 'chr(10);
tempStr := tempStr' REFERENCES 'curcon.r_owner'.'curcon.table_name'('tempColStr') ';
tempStr := tempStrcurcon.delete_rule';';
ELSIF curcon.type = 'U' THEN -- 唯一約束
tempStr := tempStr' UNIQUE('tempColStr');';
END IF;
dbms_output.put_line(tempStr);
END LOOP;
END makcon;
--
-- 生成創建視圖的SQL文件
--
PROCEDURE makview IS
i INTEGER;
BEGIN
dbms_output.enable(9E38);
-- 輸出sql文件說明信息
dbms_output.put_line(chr(10)chr(10)'REM create view''s sql');
dbms_output.put_line('REM database user name:'user);
dbms_output.put_line('REM outputTime:'sysdate);
-- 查詢用戶的所有的表
FOR curview IN(
SELECT a.view_name,a.text,b.comments comments
FROM user_views A,user_tab_comments b
WHERE a.view_name = b.table_name AND b.table_type = 'VIEW'
ORDER BY a.view_name)
LOOP
-- 輸出表信息
dbms_output.put_line(chr(10)'DROP VIEW 'curview.view_name';');
dbms_output.put_line('-- 視圖名：'curview.view_name);
dbms_output.put_line('-- 備注：'curview.comments);
dbms_output.put_line('CREATE VIEW 'curview.view_name' AS ');
dealline(curview.text';
');
END LOOP;
dbms_output.put_line(chr(10)chr(10));
END makview;
--
-- 生成創建序列的SQL文件
--
PROCEDURE makseq IS
tempStr VARCHAR2(4000);
BEGIN
dbms_output.enable(9E38);
-- 輸出sql文件說明信息
dbms_output.put_line('REM create sequence''s sql');
dbms_output.put_line('REM database user name:'user);
dbms_output.put_line('REM outputTime:'sysdate);
-- 查詢用戶的所有的表
FOR curseq IN(select * from seq) LOOP
dbms_output.put_line('DROP SEQUENCE 'curseq.sequence_name';');
tempStr := 'CREATE SEQUENCE 'curseq.sequence_name;
IF curseq.min_value IS NULL THEN
tempStr := tempStr' NOMINVALUE ';
ELSE
tempStr := tempStr' MINVALUE 'curseq.min_value;
END IF;
IF curseq.max_value IS NULL THEN
tempStr := tempStr' NOMAXVALUE ';
ELSE
tempStr := tempStr' MAXVALUE 'curseq.max_value;
END IF;
tempStr := tempStr' INCREMENT_BY 'curseq.increment_by;
tempStr := tempStr' STRART_WITH 'curseq.last_number;
IF curseq.cycle_flag = 'Y' THEN
tempStr := tempStr' CYCLE
