oracle 存储过程,导出sql到文本,动态sql,dbms_sql使用

1. createorreplacepackageSqlexp_packis
3. --Author:YANLEI
4. --Created:2008-12-2511:25:09
5. --Purpose:oracle导出
6. --sql导出文本字符串
7. functionSqlToText(sqlstrinvarchar2)returnlong;
8. --sql导出文件
9. functionSqlTofile(sqlstrinvarchar2,filenameinvarchar2)
10. returnvarchar2;
12. endSqlexp_pack;
13. /
14. createorreplacepackagebodySqlexp_packis
15. --sql导出文本字符串
16. functionSqlToText(sqlstrinvarchar2)returnlongis
18. colnamevarchar2(4000);
19. rownuminteger;
20. colnumnumberdefault0;--字段个数
22. linestrvarchar2(20000);
23. ylTbldbms_sql.desc_tab;--表信息
24. ylCursorintegerdefaultdbms_sql.open_cursor;--sql输出的游标
25. rstrlong;
26. begin
28. dbms_sql.parse(ylCursor,sqlstr,dbms_sql.native);--解析动态SQL语句；
29. dbms_sql.describe_columns(ylCursor,colnum,yltbl);--获得动态sql的元数据
30. foriin1..colnumloop
31. dbms_sql.define_column(ylCursor,i,colname,4000);--定义动态sql的列
33. endloop;
35. rownum:=dbms_sql.execute(ylCursor);--执行动态SQL语句。返回值，对于非查询的语句，execute将执行该语句并返回处理了的行的个数。对于查询，execute将确定活动集返回，返回值为0
36. ifrownum<>0then
37. return'';
38. endif;
39. rstr:='';
40. while(dbms_sql.fetch_rows(ylCursor)>0)loop
42. linestr:='';
43. foriin1..colnumloop
44. dbms_sql.column_value(ylCursor,i,colname);--获得字段值
45. linestr:=linestr||colname;
46. ifi<colnumthen
47. linestr:=linestr||',';
48. endif;
50. endloop;
52. rstr:=rstr||linestr||chr(10);
54. endloop;
56. dbms_sql.close_cursor(ylCursor);
57. returnrstr;
58. exception
59. whenothersthen
60. dbms_sql.close_cursor(ylCursor);
61. raise;
62. endSqlToText;
63. --sql导出文件
64. functionSqlTofile(sqlstrinvarchar2,filenameinvarchar2)
65. returnvarchar2is
66. strlong;
67. begin
68. str:=SqlToText(sqlstr);
69. YLFileop_pack.writefile(filename,str);
70. return'成功';
71. end;
73. endSqlexp_pack;
74. /
75. ---转载保留版权 by yanleigis email:landgis@126.com