oracle导出表insert语句存储过程

Sql代码  

1. create or replace procedure print_insert(v_tname  varchar2,  
2.                                          v_cbatch number default 0)  
3. /*  
4.   v_tname 要输出sql的表名  
5.   v_cbatch 输出commit间隔  
6.   */  
7.  as  
8.   /* 声明动态游标变量 */  
9.   type cur_alldata is ref cursor;  
10.   l_alldata cur_alldata;  
11.   /* 将单行数据写入v_row*/  
12.   v_sql varchar2(3999);  
13.   v_row varchar2(3999);  
14.   /* 函数的前向声明 */  
15.   function formatfield(v_tname varchar2, v_cname varchar2, v_colno number)  
16.     return varchar2;  
17.   
18.   /* 格式化数据输出 */  
19.   function formatdata(v_tname varchar2, v_row varchar2) return varchar2 as  
20.     v_ldata varchar2(32765);  
21.     v_rdata varchar2(32765);  
22.     v_cname varchar2(3999);  
23.     v_instr number(10);  
24.     v_count number(6);  
25.   begin  
26.     v_instr := instr(v_row, '(', 1, 2); --INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)  
27.     v_ldata := substr(v_row, 1, v_instr);  
28.     v_rdata := substr(v_row, v_instr + 1);  
29.     v_instr := instr(v_rdata, ')', -1, 1);  
30.     v_rdata := substr(v_rdata, 1, v_instr - 1);  
31.     
32.     v_count := 0;  
33.     loop  
34.       v_instr := instr(v_rdata, ',');  
35.       exit when v_instr = 0;  
36.       
37.       v_cname := substr(v_rdata, 1, v_instr - 1);  
38.       v_rdata := substr(v_rdata, v_instr + 1);  
39.       v_count := v_count + 1;  
40.       /* 格式化不同的数据类型 */  
41.       v_cname := formatfield(v_tname, v_cname, v_count);  
42.       
43.       /* 将处理后的字段值加入v_ldata */  
44.       if v_count = 1 then  
45.         v_ldata := v_ldata || v_cname;  
46.       else  
47.         v_ldata := v_ldata || ',' || v_cname;  
48.       end if;  
49.       
50.     end loop;  
51.     
52.     /* 添加最后一个字段的值 */  
53.     if v_count = 1 then  
54.       v_ldata := v_ldata || formatfield(v_tname, v_rdata, v_count + 1) || ');';  
55.     else  
56.       v_ldata := v_ldata || ',' ||  
57.                  formatfield(v_tname, v_rdata, v_count + 1) || ');';  
58.     end if;  
59.     dbms_output.put_line(v_ldata);  
60.     return v_ldata;  
61.   end;  
62.   
63.   /* 针对不同的数据类型进行处理 */  
64.   function formatfield(v_tname varchar2, v_cname varchar2, v_colno number)  
65.     return varchar2 as  
66.     v_name varchar2(3999);  
67.     v_type varchar2(99);  
68.   begin  
69.     select coltype  
70.       into v_type  
71.       from col  
72.      where tname = upper(v_tname)  
73.        and colno = v_colno;  
74.     --判断数据类型  
75.     if v_type = 'DATE' then  
76.       v_name := 'to_date(' || '''' || v_cname || '''' || ',' || '''' ||  
77.                 'yyyy-mm-dd hh24:mi:ss' || '''' || ')';  
78.     elsif v_type = 'VARCHAR2' then  
79.       v_name := '''' || v_cname || '''';  
80.     elsif v_type = 'CHAR' then  
81.       v_name := '''' || v_cname || '''';  
82.     else  
83.       v_name := v_cname;  
84.     end if;  
85.     return v_name;  
86.   end;  
87.   
88.   /* 求输入表的字段列表 */  
89.   function getfields(v_tname varchar2) return varchar2 as  
90.     v_fields    varchar2(3999);  
91.     v_fieldName varchar2(3999);  
92.   begin  
93.     for cur_fname in (select cname, coltype  
94.                         from col  
95.                        where tname = upper(v_tname)  
96.                        order by colno) loop  
97.       if v_fields is null then  
98.         v_fields := 'nvl(' || cur_fname.cname || ',' || '''' || '0' || '''' || ')';  
99.       else  
100.         v_fields := v_fields || '||'',''||' || 'nvl(' || cur_fname.cname || ',' || '''' || '0' || '''' || ')';  
101.       end if;  
102.       if v_fieldName is null then  
103.         v_fieldName := cur_fname.cname;  
104.       else  
105.         v_fieldName := v_fieldName || ',' || cur_fname.cname;  
106.       end if;  
107.     end loop;  
108.     
109.     v_fields := 'select ' || '''' || 'insert into ' || v_tname || ' (' ||  
110.                 v_fieldName || ') values(' || '''' || '||' || v_fields || '||' || '''' || ')' || '''' ||  
111.                 ' from ' || v_tname;  
112.     return v_fields;  
113.   end;  
114. begin  
115.   DBMS_OUTPUT.ENABLE(buffer_size => null); --解决PLSQL Developer 出现ORU-10027: buffer overflow, limit of 10000 bytes  
116.   execute immediate 'alter session set nls_date_format=' || '''' ||  
117.                     'yyyy-mm-dd hh24:mi:ss' || '''';  
118.   dbms_output.put_line(' *** 表SQL输出 *** ');  
119.   
120.   v_sql := getfields(v_tname);  
121.   --dbms_output.put_line(v_sql);  
122.   open l_alldata for v_sql;  
123.   loop  
124.     fetch l_alldata  
125.       into v_row;  
126.     exit when l_alldata%notfound;  
127.     --dbms_output.put_line(v_row);  
128.     
129.     dbms_output.put_line(formatdata(v_tname, v_row));  
130.     if mod(l_alldata%rowcount, v_cbatch) = 0 then  
131.       dbms_output.put_line('commit;');  
132.     end if;  
133.   end loop;  
134.   
135.   close l_alldata;  
136. end;  

 

  原博客:http://blog.itpub.net/xzh2000

  对原作者的代码进行了相应的修改。

 

在命令行中执行：

 

Sql代码  

1. set serveroutput on; --设置服务器输出  
2. exec print_insert('表名',100);  

 

 

--获取表的相关字段属性(相当于java反射获取对象属性)

 select * from col where tname = upper('表名');