- create or replace package Sqlexp_pack is
- -- Author : YANLEI
- -- Created : 2008-12-25 11:25:09
- -- Purpose : oracle 导出
- --sql导出文本字符串
- function SqlToText(sqlstr in varchar2) return long;
- --sql导出文件
- function SqlTofile(sqlstr in varchar2, filename in varchar2)
- return varchar2;
- end Sqlexp_pack;
- /
- create or replace package body Sqlexp_pack is
- --sql导出文本字符串
- function SqlToText(sqlstr in varchar2) return long is
- colname varchar2(4000);
- rownum integer;
- colnum number default 0; --字段个数
- linestr varchar2(20000);
- ylTbl dbms_sql.desc_tab; --表信息
- ylCursor integer default dbms_sql.open_cursor; --sql输出的游标
- rstr long;
- begin
- dbms_sql.parse(ylCursor, sqlstr, dbms_sql.native); --解析动态SQL语句;
- dbms_sql.describe_columns(ylCursor, colnum, yltbl); --获得动态sql的元数据
- for i in 1 .. colnum loop
- dbms_sql.define_column(ylCursor, i, colname, 4000); --定义动态sql的列
- end loop;
- rownum := dbms_sql.execute(ylCursor); --执行动态SQL语句。返回值,对于非查询的语句,execute将执行该语句并返回处理了的行的个数。 对于查询,execute将确定活动集返回,返回值为0
- if rownum <> 0 then
- return '';
- end if;
- rstr := '';
- while (dbms_sql.fetch_rows(ylCursor) > 0) loop
- linestr := '';
- for i in 1 .. colnum loop
- dbms_sql.column_value(ylCursor, i, colname); --获得字段值
- linestr := linestr || colname;
- if i < colnum then
- linestr := linestr || ',';
- end if;
- end loop;
- rstr := rstr || linestr || chr(10);
- end loop;
- dbms_sql.close_cursor(ylCursor);
- return rstr;
- exception
- when others then
- dbms_sql.close_cursor(ylCursor);
- raise;
- end SqlToText;
- --sql导出文件
- function SqlTofile(sqlstr in varchar2, filename in varchar2)
- return varchar2 is
- str long;
- begin
- str := SqlToText(sqlstr);
- YLFileop_pack.writefile(filename, str);
- return '成功';
- end;
- end Sqlexp_pack;
- /
- ---转载保留版权 by yanleigis email:landgis@126.com
oracle 存储过程,导出sql到文本,动态sql,dbms_sql使用
最新推荐文章于 2021-04-08 01:06:50 发布