从Oracle中提取xml文档

小春8516

--                PACKAGE OF DBMS_SQL 游标执行流程
  --
  --                      -----------
  --                    | open_cursor |
  --                      -----------
  --                           |
  --                           |
  --                           v
  --                         -----
  --          ------------>| parse |
  --         |               -----
  --         |                 |
  --         |                 |---------
  --         |                 v         |
  --         |           --------------  |
  --         |-------->| bind_variable | |
  --         |     ^     -------------   |
  --         |     |           |         |
  --         |      -----------|         |
  --         |                 |<--------
  --         |                 v
  --         |               query?---------- yes ---------
  --         |                 |                           |
  --         |                no                           |
  --         |                 |                           |
  --         |                 v                           v
  --         |              -------                  -------------
  --         |----------->| execute |            ->| define_column |
  --         |              -------             |    -------------
  --         |                 |------------    |          |
  --         |                 |            |    ----------|
  --         |                 v            |              v
  --         |           --------------     |           -------
  --         |       ->| variable_value |   |  ------>| execute |
  --         |      |    --------------     | |         -------
  --         |      |          |            | |            |
  --         |       ----------|            | |            |
  --         |                 |            | |            v
  --         |                 |            | |        ----------
  --         |                 |<-----------  |----->| fetch_rows |
  --         |                 |              |        ----------
  --         |                 |              |            |
  --         |                 |              |            v
  --         |                 |              |    --------------------
  --         |                 |              |  | column_value         |
  --         |                 |              |  | variable_value       |
  --         |                 |              |    ---------------------
  --         |                 |              |            |
  --         |                 |<--------------------------
  --         |                 |
  --          -----------------|
  --                           |
  --                           v
  --                      ------------
  --                    | close_cursor |
  --                      ------------
  --
  ---------------

  -------------
/************************************************************************************************************
存储过程名称：up_exptoxml
参数1：XMLfilepath in varchar2  接收将要生成的文件名(可带路径)
参数2：sqlQuery in varchar2 接收用户输入的查询字符串，默认值为："select * from stockmarket"
参数3：flag in varchar2 标志位，说明第二个参数的来源（即由用户输入还是来自于文件）默认值：
       "sql",即由用户输入
使用方法：
         1、默认方式：exec up_exptoxml(xmlfilepath) //将stockmarket表中的数据全部取出
                                                      保存到参数xmlfilepath指定的文件中
         2、用户指定查询语句方式：
            //将用户指定的查询语句所生成的结果集保存到参数xmlfilepath所指定的文件中
            1）exec up_exptoxml(xmlfilepath,'select stockname,buydate from stockmarket')
                                                
            //如果用户认为查询语句过长，直接输入不方便，也可以将语句保存成*.txt文件，
            //存储过程执行方式如下：
            2）exec up_exptoxml(xmlfilepath,'filepath,filename','file');
            该方式下存储过程将指定目录，指定文件中的sql语句读出并执行，将所生成的结果集保存到参数xmlfilepath
            所指定的文件中
功能：按照用户要求将查询结果保存为xml文件
************************************************************************************************************/
create or replace procedure up_exptoxml(XMLfilepath in varchar2, sqlQuery varchar2:='select * from stockmarket',flag varchar2:='sql') is
sqlstr_from_file clob;--保存sql查询字符串
document xmldom.DOMDocument;--文档对象
rootelement xmldom.DOMElement;--根元素
stockRecord xmldom.DOMElement;--记录
stockfield xmldom.DOMElement;--字段
l_cursor integer;--存储游标变量返回值
l_col_cot integer;--游标所包含的列数
l_desc_tab dbms_sql.desc_tab;--定义desc_tab类型的集合
tempfield dbms_sql.Varchar2_Table;--定义Varchar2_Table类型的集合
tempdate dbms_sql.Date_Table;--定义Date_Table类型的集合
tempnode xmldom.DOMNode;--定义临时节点
text xmldom.DOMText;--定义文本节点
executenum integer;--存储游标执行返回值
--fieldvalue varchar2(50);
sqlfile utl_file.file_type;
parameter exception;--自定义异常
begin
  if upper(flag)<>'SQL'and upper(flag)<>'FILE' then
     raise parameter;--如果flag参数不为上述两个选项,则抛出参数异常
  elsif upper(flag)='SQL' then
     sqlstr_from_file:=sqlQuery;
  else
     --调用存储过程以读取文件中存放的sql查询字符串
     readFile(sqlQuery,sqlstr_from_file);
  end if;
  l_cursor:=dbms_sql.open_cursor;
  dbms_sql.parse(l_cursor,sqlstr_from_file,dbms_sql.native);--解析游标
  
  dbms_sql.describe_columns(l_cursor,l_col_cot,l_desc_tab);--获取列描述信息
  for def in 1..l_col_cot loop
      if l_desc_tab(def).col_type=1 then
         tempfield(def):='';--初始化集合元素
         dbms_sql.define_column(l_cursor,def,tempfield(def),50);--定义列
      else
         tempdate(def):=sysdate;--初始化集合元素
         dbms_sql.define_column(l_cursor,def,tempdate(def));--定义列
      end if;
  end loop;
  executenum:=dbms_sql.execute(l_cursor);--执行游标
  
  document:=xmldom.newDOMDocument;--生成文档对象
  rootelement:=xmldom.createElement(document,'StockTable');--生成根元素
  loop--循环结果集(列方向)
      if dbms_sql.fetch_rows(l_cursor)>0 then
         stockrecord:=xmldom.createElement(document,'Stock');--建立临时记录元素
         for i in 1..l_col_cot loop--(循环列,行方向)
             stockfield:=xmldom.createElement(document,l_desc_tab(i).col_name);--建立字段元素
             if l_desc_tab(i).col_type=1 then--col_type=1 表示varchar2类型
                  --dbms_sql.define_column(l_cursor,i,tempfield(i),50);
                  dbms_sql.column_value(l_cursor,i,tempfield(i));
                  text:=xmldom.createTextNode(document,tempfield(i));
             else --col_type=12 表示date类型
                  --dbms_sql.define_column(l_cursor,i,tempdate(i));
                  dbms_sql.column_value(l_cursor,i,tempdate(i));
                  text:=xmldom.createTextNode(document,to_char(tempdate(i),'yyyy-mm-dd HH24:MI:SS'));
             end if;
             --将文本节点添加到字段节点
             tempnode:=xmldom.appendChild(xmldom.makeNode(stockfield),xmldom.makeNode(text));
             --将字段节点添加到记录节点
             tempnode:=xmldom.appendChild(xmldom.makeNode(stockrecord),xmldom.makeNode(stockfield));
         end loop;
         --将记录节点添加到根节点
         tempnode:=xmldom.appendChild(xmldom.makeNode(rootelement),xmldom.makeNode(stockrecord));
      else
          --no more row to copy
          dbms_sql.close_cursor(l_cursor);--遍历游标结束，关闭游标
          exit;
      end if;
   end loop;
   --将根节点添加到文档
   tempnode:=xmldom.appendChild(xmldom.makeNode(document),xmldom.makeNode(rootelement));
   xmldom.setVersion(document,'1.0');
   xmldom.writeToFile(document,XMLfilepath);
  exception
       when parameter then
            raise_application_error(-20004,'必须以"sql"或者"file"方式指定sql语句的来源');
       when others then
            if dbms_sql.is_open(l_cursor) then
               dbms_sql.close_cursor(l_cursor);
            end if;
            raise_application_error(-20005,'未知异常,游标已关闭！');
end up_exptoxml;
/