Oracle8i中生成创建对象的SQL

Oracle8i中如何生成创建对象的SQL脚本呢

 --
      -- 包头定义
      --
      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
      /