如何把动态sql的结果集转为XML格式

转载自：http://jackywood.itpub.net/post/1369/161508

在存储过程中, 我们可以用动态sql返回结果集. 动态sql的来源可以是参数, 也可以是存储过程中描述逻辑, 那么如何把动态sql的结果集以XML格式返回呢?

在存储过程中, 我们可以用动态sql返回结果集. 动态sql的来源可以是参数, 也可以是存储过程中描述逻辑, 那么如何把动态sql的结果集以XML格式返回呢?

处理这个问题, 首先先要考虑2个问题:
1. XML格式的数据通过什么方式来转换?
2. 动态sql中的字段名如何获取?

解答:
1. XML格式的数据可以通过Oracle提供的函数xmlelement和xmlattributes来实现
示例如下:

---

SQL> select * from tb_test;

        ID VAL
---------- ----------------
         1 JGSLQQQY
         2 BLIAELER
         3 WSXAJVHQ
         4 FLTGINNS

4 rows selected.

SQL> select xmlelement("row",
  2          xmlattributes(id as "ID",
  3                        val as "Val"
  4                       )
  5                   ) as val
  6    from tb_test;

VAL
--------------------------------------------------------------------------------
<row ID="1" Val="JGSLQQQY"/>
<row ID="2" Val="BLIAELER"/>
<row ID="3" Val="WSXAJVHQ"/>
<row ID="4" Val="FLTGINNS"/>

4 rows selected.

注意: XML中是大小写敏感的, 因此在选择最后属性节点名称的时候要注意用双引号来, 使字段名大小写敏感.

---

2. 动态sql中的字段名通过Oracle提供的DBMS_SQL包来实现. (DBMS_SQL的内容在这里不作描述, 详细的参见官方文档.)
示例如下:

---

  调用DBMS_SQL中的方法, 获取 select * from tb_test 和 select id as "ID", val as "Val" from tb_test 的字段名.
SQL> set serveroutput on
SQL> declare
  2    v_sql varchar2(100);
  3    i_sql integer;
  4    i_col integer;
  5    t_desc dbms_sql.desc_tab;
  6  begin
  7    v_sql := 'select * from tb_test';
  8    i_sql := dbms_sql.open_cursor;
  9    dbms_sql.parse(i_sql, v_sql, dbms_sql.native);
 10    dbms_sql.describe_columns(i_sql, i_col, t_desc);
 11    dbms_output.put_line('The columns of the first SQL:');
 12    for i in 1..i_col loop
 13      dbms_output.put_line(t_desc(i).col_name);
 14    end loop;
 15
 16    v_sql := 'select id as "ID", val as "Val" from tb_test';
 17    i_sql := dbms_sql.open_cursor;
 18    dbms_sql.parse(i_sql, v_sql, dbms_sql.native);
 19    dbms_sql.describe_columns(i_sql, i_col, t_desc);
 20    dbms_output.put_line('The columns of the second SQL:');
 21    for i in 1..i_col loop
 22      dbms_output.put_line(t_desc(i).col_name);
 23    end loop;
 24  end;
 25  /
The columns of the first SQL:
ID
VAL
The columns of the second SQL:
ID
Val

PL/SQL procedure successfully completed.

注意: 两个sql输出的字段名大小写是不同的.

---

好了, 上述两个问题在技术上解决了, 剩下的就是用存储过程来实现了.

---

/* 动态sql结果集以XML格式返回, 输入参数就是动态sql, xml的属性节点名的大小写由输入的sql来决定*/
create or replace function func_rec_dynamic(p_sql varchar2) 
return sys_refcursor
as
  v_sql varchar2(4000);
  i_sql integer;
  i_col integer;
  t_desc  dbms_sql.desc_tab;
  c sys_refcursor;
begin
  /* Parse input SQL, and get column names */
  i_sql := dbms_sql.open_cursor;
  dbms_sql.parse(i_sql, p_sql, dbms_sql.native);
  dbms_sql.describe_columns(i_sql, i_col, t_desc);

  /* Obtain dynamic SQL according to parsing input SQL */
  for i in 1..i_col-1 loop
    v_sql := v_sql || '"' || t_desc(i).col_name || '" as "' || t_desc(i).col_name || '",';
  end loop;
  v_sql := v_sql || '"' || t_desc(i_col).col_name || '" as "' || t_desc(i_col).col_name || '"';
  v_sql := 'SELECT xmlelement("row", xmlattributes(' || v_sql ||') ) as val FROM (' || p_sql ||') t';

  /* Execute dynamic SQL to obtain records */
  open c for v_sql;

  return c;
end;
/

---

func_rec_dynamic函数的使用示例:

---

SQL> select func_rec_dynamic('select * from tb_test') as col from dual;

COL
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

VAL
--------------------------------------------------------------------------------
<row ID="1" VAL="JGSLQQQY"/>
<row ID="2" VAL="BLIAELER"/>
<row ID="3" VAL="WSXAJVHQ"/>
<row ID="4" VAL="FLTGINNS"/>

4 rows selected.


1 row selected.

SQL> select func_rec_dynamic('select id as "ID", val as "Val" from tb_test') as col from dual;

COL
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

VAL
--------------------------------------------------------------------------------
<row ID="1" Val="JGSLQQQY"/>
<row ID="2" Val="BLIAELER"/>
<row ID="3" Val="WSXAJVHQ"/>
<row ID="4" Val="FLTGINNS"/>

4 rows selected.


1 row selected.