oracle将表中数据print成insert语句的sql

最新推荐文章于 2024-02-05 10:21:30 发布

转载最新推荐文章于 2024-02-05 10:21:30 发布 · 1.4k 阅读

文章标签：

#insert #oracle #sql #date #function #null

oracle 专栏收录该内容

64 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中批量生成INSERT语句的方法。通过使用PL/SQL过程和函数，可以灵活地针对不同表结构生成对应的INSERT语句，支持DATE、VARCHAR2等多种数据类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

将表中数据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