oracle导出表insert语句存储过程

Sql代码

1. createorreplaceprocedureprint_insert(v_tnamevarchar2,
2. v_cbatchnumberdefault0)
3. /*
4. v_tname要输出sql的表名
5. v_cbatch输出commit间隔
6. */
7. as
8. /*声明动态游标变量*/
9. typecur_alldataisrefcursor;
10. l_alldatacur_alldata;
11. /*将单行数据写入v_row*/
12. v_sqlvarchar2(3999);
13. v_rowvarchar2(3999);
14. /*函数的前向声明*/
15. functionformatfield(v_tnamevarchar2,v_cnamevarchar2,v_colnonumber)
16. returnvarchar2;
18. /*格式化数据输出*/
19. functionformatdata(v_tnamevarchar2,v_rowvarchar2)returnvarchar2as
20. v_ldatavarchar2(32765);
21. v_rdatavarchar2(32765);
22. v_cnamevarchar2(3999);
23. v_instrnumber(10);
24. v_countnumber(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);
32. v_count:=0;
33. loop
34. v_instr:=instr(v_rdata,',');
35. exitwhenv_instr=0;
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);
43. /*将处理后的字段值加入v_ldata*/
44. ifv_count=1then
45. v_ldata:=v_ldata||v_cname;
46. else
47. v_ldata:=v_ldata||','||v_cname;
48. endif;
50. endloop;
52. /*添加最后一个字段的值*/
53. ifv_count=1then
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. endif;
59. dbms_output.put_line(v_ldata);
60. returnv_ldata;
61. end;
63. /*针对不同的数据类型进行处理*/
64. functionformatfield(v_tnamevarchar2,v_cnamevarchar2,v_colnonumber)
65. returnvarchar2as
66. v_namevarchar2(3999);
67. v_typevarchar2(99);
68. begin
69. selectcoltype
70. intov_type
71. fromcol
72. wheretname=upper(v_tname)
73. andcolno=v_colno;
74. --判断数据类型
75. ifv_type='DATE'then
76. v_name:='to_date('||''''||v_cname||''''||','||''''||
77. 'yyyy-mm-ddhh24:mi:ss'||''''||')';
78. elsifv_type='VARCHAR2'then
79. v_name:=''''||v_cname||'''';
80. elsifv_type='CHAR'then
81. v_name:=''''||v_cname||'''';
82. else
83. v_name:=v_cname;
84. endif;
85. returnv_name;
86. end;
88. /*求输入表的字段列表*/
89. functiongetfields(v_tnamevarchar2)returnvarchar2as
90. v_fieldsvarchar2(3999);
91. v_fieldNamevarchar2(3999);
92. begin
93. forcur_fnamein(selectcname,coltype
94. fromcol
95. wheretname=upper(v_tname)
96. orderbycolno)loop
97. ifv_fieldsisnullthen
98. v_fields:='nvl('||cur_fname.cname||','||''''||'0'||''''||')';
99. else
100. v_fields:=v_fields||'||'',''||'||'nvl('||cur_fname.cname||','||''''||'0'||''''||')';
101. endif;
102. ifv_fieldNameisnullthen
103. v_fieldName:=cur_fname.cname;
104. else
105. v_fieldName:=v_fieldName||','||cur_fname.cname;
106. endif;
107. endloop;
109. v_fields:='select'||''''||'insertinto'||v_tname||'('||
110. v_fieldName||')values('||''''||'||'||v_fields||'||'||''''||')'||''''||
111. 'from'||v_tname;
112. returnv_fields;
113. end;
114. begin
115. DBMS_OUTPUT.ENABLE(buffer_size=>null);--解决PLSQLDeveloper出现ORU-10027:bufferoverflow,limitof10000bytes
116. executeimmediate'altersessionsetnls_date_format='||''''||
117. 'yyyy-mm-ddhh24:mi:ss'||'''';
118. dbms_output.put_line('***表SQL输出***');
120. v_sql:=getfields(v_tname);
121. --dbms_output.put_line(v_sql);
122. openl_alldataforv_sql;
123. loop
124. fetchl_alldata
125. intov_row;
126. exitwhenl_alldata%notfound;
127. --dbms_output.put_line(v_row);
129. dbms_output.put_line(formatdata(v_tname,v_row));
130. ifmod(l_alldata%rowcount,v_cbatch)=0then
131. dbms_output.put_line('commit;');
132. endif;
133. endloop;
135. closel_alldata;
136. end;

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

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

在命令行中执行：

Sql代码

1. setserveroutputon;--设置服务器输出
2. execprint_insert('表名',100);

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

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