包头定义
CREATE OR REPLACE PACKAGE srcmake AS
PROCEDURE maktab;
PROCEDURE makview;
PROCEDURE makseq;
PROCEDURE makcon(tabName VARCHAR);
END srcmake;
/
包体定义
CREATE OR REPLACE PACKAGE BODY srcmake AS
处理超过个字符的行的输出
PROCEDURE dealline(initStr VARCHAR) IS
lineCount INTEGER;
i INTEGER;
BEGIN
lineCount := ceil(length(initStr)/);
FOR i IN lineCount LOOP
dbms_outputput_line(substr(initStr + * (i )));
END LOOP;
END dealline;
生成创建表的SQL文件
PROCEDURE maktab IS
tempStr varchar();
countNum integer;
i integer;
BEGIN
dbms_outputenable(E);
输出sql文件说明信息
dbms_outputput_line(REM create tables sql);
dbms_outputput_line(REM database user name:||user);
dbms_outputput_line(REM outputTime:||sysdate);
查询用户的所有的表
FOR curtab IN(
SELECT atable_name table_nameatablespace_namements comments
FROM user_tables Auser_tab_comments b
WHERE atable_name = btable_name AND btable_type = TABLE
ORDER BY atable_name)
LOOP
输出表信息
dbms_outputput_line(chr()||DROP TABLE ||curtabtable_name||;);
dbms_outputput_line( 表名||curtabtable_name);
dbms_outputput_line( 备注||ments);
dbms_outputput_line(CREATE TABLE ||curtabtable_name||();
SELECT count(column_name) INTO countNum FROM user_tab_columns
WHERE table_name = curtabtable_name;
i := ;
查询表所有的列
FOR curcol IN(
SELECT a*ments
FROM user_tab_columns auser_col_comments b
WHERE atable_name = curtabtable_name AND atable_name = btable_name
AND lumn_name = lumn_name ORDER BY column_id)
LOOP
tempStr := chr()||rpad(lumn_name )||curcoldata_type;
以下类型需要指定长度
IF curcoldata_type IN(VARCHARCHARVARCHARRAW) THEN
tempStr := tempStr||(||curcoldata_length||);
数字类型存在精度问题
ELSIF curcoldata_type = NUMBER THEN
IF curcoldata_precision IS NOT NULL THEN
tempStr := tempStr||(||curcoldata_precision;
IF curcoldata_scale IS NOT NULL THEN
tempStr := tempStr||||curcoldata_scale||);
ELSE
tempStr := tempStr||);
END IF;
ELSIF curcoldata_scale = THEN
tempStr := tempStr||();
END IF;
END IF;
LONGLONG RAWCLOBNLOBBLOBROWID类型不需指定长度
IF curcolnullable = N THEN 指定非空标志
tempStr := tempStr|| NOT NULL;
END IF;
i := i + ;
最后一列不需逗号
IF i != countNum THEN
tempStr := tempStr||;
END IF;
输出列的信息
IF ments IS NOT NULL THEN
dbms_outputput_line(rpad(tempStr )|| ||ments);
ELSE
dbms_outputput_line(tempStr);
END IF;
END LOOP;
输出表空间信息
dbms_outputput_line() TABLESPACE ||curtabtablespace_name||;);
输出表约束
makcon(curtabtable_name);
END LOOP;
dbms_outputput_line(chr()||chr());
END maktab;
生成表的约束(primary keyforeign key)
parameter:tabName表名称
PROCEDURE makcon(tabName VARCHAR) IS
tempStr VARCHAR();
tempColStr VARCHAR();
BEGIN
FOR curcon IN(
SELECT ownerconstraint_name nameconstraint_type type
r_constraint_name rnamedelete_ruler_ownertable_name
FROM user_constraints WHERE table_name = tabName AND constraint_type IN(PRU))
LOOP
输出约束信息
tempStr := ALTER TABLE ||tabName|| ADD CONSTRAINTS ||curconname;
FOR curcol IN(SELECT column_name FROM user_cons_columns
WHERE constraint_name = curconname) LOOP
tempColStr := tempColStr||lumn_name||;
END LOOP;
tempColStr := substr(tempColStrlength(tempColStr) );
输出约束的列信息
IF curcontype = P THEN 主键
tempStr := tempStr|| PRIMARY KEY(||tempColStr||);;
ELSIF curcontype = R THEN 外键
tempStr := tempStr|| FOREIGN KEY(||tempColStr||) ||chr();
tempStr := tempStr|| REFERENCES ||curconr_owner||||curcontable_name||(||tempColStr||) ;
tempStr := tempStr||curcondelete_rule||;;
ELSIF curcontype = U THEN 唯一约束
tempStr := tempStr|| UNIQUE(||tempColStr||);;
END IF;
dbms_outputput_line(tempStr);
END LOOP;
END makcon;
生成创建视图的SQL文件
PROCEDURE makview IS
i INTEGER;
BEGIN
dbms_outputenable(E);
输出sql文件说明信息
dbms_outputput_line(chr()||chr()||REM create views sql);
dbms_outputput_line(REM database user name:||user);
dbms_outputput_line(REM outputTime:||sysdate);
查询用户的所有的表
FOR curview IN(
SELECT aview_nameatextments comments
FROM user_views Auser_tab_comments b
WHERE aview_name = btable_name AND btable_type = VIEW
ORDER BY aview_name)
LOOP
输出表信息
dbms_outputput_line(chr()||DROP VIEW ||curviewview_name||;);
dbms_outputput_line( 视图名||curviewview_name);
dbms_outputput_line( 备注||ments);
dbms_outputput_line(CREATE VIEW ||curviewview_name|| AS );
dealline(curviewtext||;);
END LOOP;
dbms_outputput_line(chr()||chr());
END makview;
生成创建序列的SQL文件
PROCEDURE makseq IS
tempStr VARCHAR();
BEGIN
dbms_outputenable(E);
输出sql文件说明信息
dbms_outputput_line(REM create sequences sql);
dbms_outputput_line(REM database user name:||user);
dbms_outputput_line(REM outputTime:||sysdate);
查询用户的所有的表
FOR curseq IN(select * from seq) LOOP
dbms_outputput_line(DROP SEQUENCE ||curseqsequence_name||;);
tempStr := CREATE SEQUENCE ||curseqsequence_name;
IF curseqmin_value IS NULL THEN
tempStr := tempStr|| NOMINVALUE ;
ELSE
tempStr := tempStr|| MINVALUE ||curseqmin_value;
END IF;
IF curseqmax_value IS NULL THEN
tempStr := tempStr|| NOMAXVALUE ;
ELSE
tempStr := tempStr|| MAXVALUE ||curseqmax_value;
END IF;
tempStr := tempStr|| INCREMENT_BY ||curseqincrement_by;
tempStr := tempStr|| STRART_WITH ||curseqlast_number;
IF curseqcycle_flag = Y THEN
tempStr := tempStr|| CYCLE
