select a.text from ( select decode(rownum ,1,'CREATE OR REPLACE '||RTRIM(RTRIM(t.text,CHR(10))),RTRIM(RTRIM(t.text,CHR(10)))) text ,line from dba_source t where t.owner = 'USE1' and (t.type = 'PROCEDURE' or t.type = 'FUNCTION') UNION select '--' || name text,0 line from dba_source where owner='USE1' and (type = 'PROCEDURE' or type = 'FUNCTION') and line=1 ) a order by a.line ;
完整代码select a.text from ( select decode(line ,1,'CREATE OR REPLACE '||RTRIM(RTRIM(text,CHR(10))),RTRIM(RTRIM(text,CHR(10)))) text ,line ,name from dba_source where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION') UNION select '--' || name text,0 line ,name from dba_source where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION') and line=1 UNION select '/' text,10000 line ,name from dba_source where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION') UNION select ' ' text,10001 line ,name from dba_source where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION') ) a order by a.name,a.line ;
Oracle8i中生成创建对象的SQL REM REM author: 朱伟民, time:15:20 01-8-4 REM 版权 :sinosoft REM version:1.0 REM 从 oracle8i Enterprise Edition 数据库生成创建对象的脚本文件 REM -- -- 包头定义 -- 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 := tempColStr||curcol.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 := tempStr||curcon.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 '; ELSE tempStr := tempStr||' NOCYCLE '; END IF; IF curseq.order_flag = 'Y' THEN tempStr := tempStr||' ORDER '; ELSE tempStr := tempStr||' NOORDER '; END IF; IF curseq.cache_size IS NULL THEN tempStr := tempStr||' NOCACHE '; ELSE tempStr := tempStr||' CACHE '||curseq.cache_size||' '; END IF; dbms_output.put_line(tempStr||';'); END LOOP; dbms_output.put_line(chr(10)||chr(10)); END makseq; END srcmake; / set feedback off set serveroutput on set linesize 255 spool &文件名 exec srcmake.maktab exec srcmake.makview exec srcmake.makseq spool off set serveroutput off set feedback on set linesize 80 drop package srcmake /
select
decode(rownum ,1,'CREATE OR REPLACE '||RTRIM(RTRIM(t.text,CHR(10))),RTRIM(RTRIM(t.text,CHR(10)))) text ,line from dba_source t
where
t.owner = 'USE1' and (t.type = 'PROCEDURE' or t.type = 'FUNCTION')
UNION
select '--' || name text,0 line from dba_source where owner='USE1' and (type = 'PROCEDURE' or type = 'FUNCTION')
and line=1
) a order by a.line
;
select decode(line ,1,'CREATE OR REPLACE '||RTRIM(RTRIM(text,CHR(10))),RTRIM(RTRIM(text,CHR(10)))) text ,line ,name
from dba_source
where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION')
UNION
select '--' || name text,0 line ,name
from dba_source
where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION')
and line=1
UNION
select '/' text,10000 line ,name
from dba_source
where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION')
UNION
select ' ' text,10001 line ,name
from dba_source
where owner = 'USE1' and (type = 'PROCEDURE' or type = 'FUNCTION')
) a order by a.name,a.line
;
但这代码不是实现指定的存储过程,而且执行效率很慢。因为是在从2000多个存储过程中执行的。请问有更简洁一点的实现方法吗?
REM author: 朱伟民, time:15:20 01-8-4
REM 版权 :sinosoft
REM version:1.0
REM 从 oracle8i Enterprise Edition 数据库生成创建对象的脚本文件
REM --
-- 包头定义
--
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 := tempColStr||curcol.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 := tempStr||curcon.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 ';
ELSE
tempStr := tempStr||' NOCYCLE ';
END IF;
IF curseq.order_flag = 'Y' THEN
tempStr := tempStr||' ORDER ';
ELSE
tempStr := tempStr||' NOORDER ';
END IF;
IF curseq.cache_size IS NULL THEN
tempStr := tempStr||' NOCACHE ';
ELSE
tempStr := tempStr||' CACHE '||curseq.cache_size||' ';
END IF;
dbms_output.put_line(tempStr||';');
END LOOP;
dbms_output.put_line(chr(10)||chr(10));
END makseq; END srcmake;
/ set feedback off
set serveroutput on
set linesize 255
spool &文件名
exec srcmake.maktab
exec srcmake.makview
exec srcmake.makseq
spool off
set serveroutput off
set feedback on
set linesize 80
drop package srcmake
/