生成某一使用者下所有表資料的insert語句

壹頁書發表於2013-12-28
轉自王工的部落格
http://blog.csdn.net/wzy0623/article/details/8445228

功能：生成某一使用者下所有資料表資料的insert語句，放入d:\insert.sql檔案。

限制：只支援number、char、varchar2、date、long、clob資料型別。

提示：資料量小還可以，大了就別用這種方式了，會很慢。



CREATE OR REPLACE DIRECTORY mydir AS 'C:\';  
 
DECLARE  
  ROW_NUMBER   NUMBER := 0;  
  col_str      VARCHAR2 (32767) := '';  
  select_str   VARCHAR2 (32767) := '';  
  ins_tab      DBMS_SQL.varchar2_table;  
  l_count      INT := 0;  
  log_file     UTL_FILE.file_type;  
BEGIN  
  log_file := UTL_FILE.fopen ('MYDIR', 'insert.sql', 'w');  
  UTL_FILE.put_line (log_file, 'set define off;');  
 
  FOR x IN (SELECT table_name FROM user_tables)  
  LOOP  
     EXECUTE IMMEDIATE 'select count(*) from ' || x.table_name  
        INTO ROW_NUMBER;  
 
     IF ROW_NUMBER > 0  
     THEN  
        col_str := '';  
        select_str := '';  
 
        FOR y IN (  SELECT column_name, data_type  
                      FROM user_tab_columns  
                     WHERE table_name = x.table_name AND data_type != 'BLOB'  
                  ORDER BY column_id)  
        LOOP  
           col_str := col_str || y.column_name || ',';  
 
           IF y.data_type = 'NUMBER'  
           THEN  
              select_str :=  
                    select_str  
                 || 'decode('  
                 || y.column_name  
                 || ',null,''null'','  
                 || y.column_name  
                 || ')||'',''||';  
           ELSE  
              IF y.data_type IN ('CHAR', 'VARCHAR2', 'LONG', 'CLOB')  
              THEN  
                 select_str :=  
                       select_str  
                    || 'decode('  
                    || y.column_name  
                    || ',null,''null'',''''''''||replace('  
                    || y.column_name  
                    || ','''''''','''''''''''')||'''''''')||'',''||';  
              ELSE  
                 IF y.data_type = 'DATE'  
                 THEN  
                    select_str :=  
                          select_str  
                       || 'decode('  
                       || y.column_name  
                       || ',null,''null'',''to_date(''''''||'  
                       || 'to_char('  
                       || y.column_name  
                       || ',''yyyy-mm-dd hh24:mi:ss'')||'''  
                       || '''||'''''',''''yyyy-mm-dd hh24:mi:ss'''')'')||'',''||';  
                 END IF;  
              END IF;  
           END IF;  
        END LOOP;  
 
        col_str := 'insert into ' || x.table_name || ' (' || col_str;  
        col_str := SUBSTR (col_str, 1, LENGTH (col_str) - 1) || ') values (';  
        select_str :=  
              'select '''  
           || col_str  
           || ''' ||'  
           || SUBSTR (select_str, 1, LENGTH (select_str) - 7)  
           || '|| '');'' from '  
           || x.table_name;  
 
        -- dbms_output.put_line(select_str);  
        EXECUTE IMMEDIATE select_str BULK COLLECT INTO ins_tab;  
 
        l_count := ins_tab.COUNT;  
        UTL_FILE.put_line (log_file, '');  
        UTL_FILE.put_line (log_file, '-- table name: ' || x.table_name);  
 
        FOR i IN 1 .. l_count  
        LOOP  
           UTL_FILE.put_line (log_file, ins_tab (i));  
        END LOOP;  
     END IF;  
  END LOOP;  
 
  UTL_FILE.put_line (log_file, '');  
  UTL_FILE.put_line (log_file, 'commit;');  
  UTL_FILE.fclose (log_file);  
END;  
/  


注意王工部落格程式碼的75行，有個筆誤，少了兩個  '   符號。
  || '|| ''); from '  
應為
  || '|| '');'' from '  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1065181/，如需轉載，請註明出處，否則將追究法律責任。

相關文章