oracle將表中資料print成insert語句的sql

 將表中資料print成insert語句的sql

create or replace procedure print_insert(v_tname varchar2,v_cbatch number default 0)
/*
        v_tname  要輸出sql的表名        作者:xzh2000@hotmail.com
        v_cbatch 輸出commit間隔                部落格:http://blog.itpub.net/xzh2000
*/
as
/* 聲明動態資料指標變數 */
        type cur_alldata is ref cursor;
  l_alldata cur_alldata;
/* 將單行資料寫入v_row*/
  v_sql varchar2(3999);
        v_row varchar2(3999);
/* 函數的前向聲明 */
  function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2;
/* 格式化資料輸出 */       
        function formatdata(v_tname varchar2,v_row varchar2) return varchar2
        as
                v_ldata varchar2(32765);
                v_rdata varchar2(32765);
                v_cname varchar2(3999);
                v_instr number(8);
                v_count number(6);
        begin
                v_instr := instr(v_row,'(');
                v_ldata := substr(v_row,1,v_instr);
                v_rdata := substr(v_row,v_instr+1);
                v_instr := instr(v_rdata,')');
                v_rdata := substr(v_rdata,1,v_instr-1);

                v_count := 0;
                loop
                        v_instr := instr(v_rdata,',');
                        v_count := v_count + 1;

                        exit when v_instr = 0;

                        v_cname := substr(v_rdata,1,v_instr-1);
                        v_rdata := substr(v_rdata,v_instr+1);
      
                        /* 格式化不同的資料類型 */
                        v_cname := formatfield(v_tname,v_cname,v_count);

                        /* 將處理後的欄位值加入v_ldata */
                        if v_count = 1 then
                                 v_ldata := v_ldata||v_cname;
                        else
                                 v_ldata := v_ldata||','||v_cname;
                        end if;

                end loop;

                /* 添加最後一個欄位的值 */
                if v_count = 1 then
                         v_ldata := v_ldata||formatfield(v_tname,v_rdata,v_count)||');';
                else
                         v_ldata := v_ldata||','||formatfield(v_tname,v_rdata,v_count)||');';
                end if;
                return v_ldata;
        end;
/* 針對不同的資料類型進行處理 */
  function formatfield(v_tname varchar2,v_cname varchar2,v_colno number) return varchar2       
        as
                v_name varchar2(3999);
          v_type varchar2(99);
  begin
                select coltype into v_type from col where tname = upper(v_tname) and colno = v_colno;
                if v_type = 'DATE' then
                        v_name := 'to_date('||''''||v_cname||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
                elsif v_type = 'VARCHAR2' then
                        v_name := ''''||v_cname||'''';
                else
                        v_name := v_cname;
                end if;
                return v_name;
        end;
/* 求輸入表的欄位列表 */
        function getfields(v_tname varchar2) return varchar2
        as
         v_fields varchar2(3999);
        begin
         for cur_fname in (select cname,coltype from col where tname = upper(v_tname) order by colno) loop
                 if v_fields is null then
                         v_fields := 'nvl('||cur_fname.cname||','||''''||'0'||''''||')';
                 else
                         v_fields := v_fields||'||'',''||'||'nvl('||cur_fname.cname||','||''''||'0'||''''||')';
                 end if;         
         end loop;
         v_fields := 'select '||''''||'insert into '||v_tname||' values('||''''||'||'||v_fields||'||'||''''||')'||''''||' from '||v_tname;
         return v_fields;
        end;
begin
        execute immediate 'alter session set nls_date_format='||''''||'yyyy-mm-dd hh24:mi:ss'||'''';
  dbms_output.put_line('  *** xzxh2000向你問好！ ***  ');

        v_sql := getfields(v_tname);
        --dbms_output.put_line(v_sql);
        open l_alldata for v_sql;
  
        loop
   fetch l_alldata into v_row;
         exit when l_alldata%notfound;
         --dbms_output.put_line(v_row);

   dbms_output.put_line(formatdata(v_tname,v_row));
         if mod(l_alldata%rowcount,v_cbatch) = 0 then
                 dbms_output.put_line('commit;');
         end if;
        end loop;

  close l_alldata;
end;

create or replace function sum_string(v_sql varchar2)
return varchar2
/*
                 作者:xzh2000@hotmail.com
                部落格:http://blog.itpub.net/xzh2000
*/
as
/* 聲明動態資料指標變數 */
        type cur_alldata is ref cursor;
  l_alldata cur_alldata;
/* 查詢sql及其變數 */
        v_row varchar2(99);
  v_sum varchar2(3999);
begin
  open l_alldata for v_sql;
  loop
                fetch l_alldata into v_row;
                /* 沒有資料卻退出 */
                exit when l_alldata%notfound;
   
                v_sum := v_sum||','||v_row;
        end loop;

  v_sum := substr(v_sum,2);
  close l_alldata;

        return v_sum;
end;

 

20樓 大 中 小 發表於 2005-7-8 18:25  只看該作者 我也仿寫了一個CREATE OR REPLACE PROCEDURE export_data (    v_tname    VARCHAR2,    v_cbatch   NUMBER DEFAULT 0 ) AS    v_sql       VARCHAR2 (3999);    v_sql1      VARCHAR2 (3999);    sql_stmt    VARCHAR2 (3999);    TYPE r_fields IS RECORD (       tname     col.tname%TYPE,       cname     VARCHAR2 (3999),       coltype   col.coltype%TYPE,       width     col.width%TYPE    );    TYPE t_fields IS TABLE OF r_fields;    vn_fields   t_fields;    TYPE r_stmt IS RECORD (       stmt   VARCHAR2 (3999)    );    TYPE t_stmt IS TABLE OF r_stmt;    vn_stmt     t_stmt; BEGIN    EXECUTE IMMEDIATE    'alter session set nls_date_format='                      || ''''                      || 'yyyy-mm-dd hh24:mi:ss'                      || '''';    sql_stmt :=       'select tname, cname,coltype,width from col where tname=:1 order by colno';    EXECUTE IMMEDIATE sql_stmt    BULK COLLECT INTO vn_fields                USING TRIM (v_tname);    v_sql := '''' || 'insert into ' || TRIM (v_tname) || ' values ( ''||';    FOR i IN 1 .. vn_fields.COUNT    LOOP       IF vn_fields (i).coltype = 'NUMBER'       THEN          vn_fields (i).cname :=                  'nvl(to_char(' || vn_fields (i).cname || '),''NULL''' || ')';          v_sql := v_sql || vn_fields (i).cname;       ELSIF    vn_fields (i).coltype = 'VARCHAR2'             OR vn_fields (i).coltype = 'CHAR'       THEN          vn_fields (i).cname :=                      'nvl(to_char(' || vn_fields (i).cname || '),NULL' || ')';          v_sql :=                  v_sql || '''''''''||' || vn_fields (i).cname || '||''''''''';       ELSIF vn_fields (i).coltype = 'DATE'       THEN          v_sql :=                v_sql             || '''to_date(''''''||'             || 'to_char('             || vn_fields (i).cname             || ','''             || 'YYYY-MM-DD HH24:MI:SS'             || ''')'             || '||'''''',''||'''''''             || 'YYYY-MM-DD HH24:MI:SS'             || ''''             || ''')''';       ELSE          vn_fields (i).cname :=                  'nvl(to_char(' || vn_fields (i).cname || '),''NULL''' || ')';          v_sql := v_sql || vn_fields (i).tname;       END IF;       IF i < vn_fields.COUNT       THEN          v_sql := v_sql || '||'',''||';       END IF;    END LOOP;    v_sql := v_sql || '||''' || ');''';    sql_stmt := 'select ' || v_sql || ' from ' || TRIM (v_tname);    EXECUTE IMMEDIATE sql_stmt    BULK COLLECT INTO vn_stmt;    FOR j IN 1 .. vn_stmt.COUNT    LOOP       DBMS_OUTPUT.put_line (vn_stmt (j).stmt);       IF MOD (j, v_cbatch) = 0       THEN          DBMS_OUTPUT.put_line ('COMMIT;');       END IF;    END LOOP;    IF v_cbatch = 0 OR vn_stmt.COUNT < v_cbatch    THEN       DBMS_OUTPUT.put_line ('COMMIT;');    END IF; END export_data來自:http://www.cnoug.org/viewthread.php?tid=31600&extra=&page=1