熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle8i 中生成創建對象的 SQL

2013-11-13 22:22:41  來源: Oracle 

  
   包頭定義
  
  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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18930.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.