3,490
社区成员
发帖
与我相关
我的任务
分享
SQL> exec getTable_XMLQUERY('emp');
3171
1___..___3171
PL/SQL procedure successfully completed
SQL>
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>1000</MGR>
<HIREDATE>12/17/1980 3:1:0</HIREDATE>
<SAL>800.1</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 4:2:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 1:1:1</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="4">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 10:1:59</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="5">
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/28/1981 5:1:59</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="6">
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>5/1/1981 2:1:0</HIREDATE>
<SAL>2850</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="7">
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>6/9/1981 10:1:0</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="8">
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>4/19/1987 1:1:1</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="9">
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>11/17/1981 8:0:0</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="10">
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/8/1981 0:3:0</HIREDATE>
<SAL>1500</SAL>
<COMM>0</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="11">
<EMPNO>7876</EMPNO>
<ENAME>ADAMS</ENAME>
<JOB>CLERK</JOB>
<MGR>7788</MGR>
<HIREDATE>5/23/1987 9:1:1</HIREDATE>
<SAL>1100</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="12">
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<JOB>CLERK</JOB>
<MGR>7698</MGR>
<HIREDATE>12/3/1981 1:59:59</HIREDATE>
<SAL>950</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="13">
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>12/3/1981 6:1:0</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="14">
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>1/23/1982 9:1:1</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
create or replace procedure getTable_XMLQUERY(table_name varchar2)
is
data clob;--用于保存数据
xmlfile utl_file.file_type;--文件类型变量
length pls_integer;
buffer varchar2(32767);
start_idx pls_integer default 1;
end_idx pls_integer default 32767;
begin
--将查询的数据转换为XML格式,并保存到clob中
data := dbms_xmlquery.getxml('select * from '||table_name);
--获取clob类型的变量data的长度
length := dbms_lob.getlength(data);
--得到文件实例,如果没有当前指明的文件系统会自动创建w表示写(write)
xmlfile := utl_file.fopen('EMP_DIR',table_name||'.xml','w');
--循环截取
if start_idx=1 and length <=32767 then
end_idx=length;
end if;
loop
dbms_output.put_line(length);
--截取一段字符串放到BUFFER变量中
dbms_lob.read(data,end_idx,start_idx,buffer);
dbms_output.put_line(start_idx||'___..___'||end_idx);
--把BUFFER中的数据输出
utl_file.put(xmlfile,buffer);
exit when end_idx=length;
--重设循环变量
if end_idx+32767<length then
end_idx := end_idx+32767;
start_idx := start_idx+32767;
else
start_idx := start_idx+(length-end_idx);
end_idx := end_idx+(length-end_idx);
end if;
dbms_output.put_line(start_idx||'______'||end_idx);
--清空BUFFER
buffer := null;
end loop;
utl_file.fclose(xmlfile);
end;
还是这样吧,效率高点.create or replace procedure getTable_XMLQUERY(table_name varchar2)
is
data clob;--用于保存数据
xmlfile utl_file.file_type;--文件类型变量
length pls_integer;
buffer varchar2(32767);
start_idx pls_integer default 1;
end_idx pls_integer default 32767;
begin
--将查询的数据转换为XML格式,并保存到clob中
data := dbms_xmlquery.getxml('select * from '||table_name);
--获取clob类型的变量data的长度
length := dbms_lob.getlength(data);
--得到文件实例,如果没有当前指明的文件系统会自动创建w表示写(write)
xmlfile := utl_file.fopen('EMP_DIR',table_name||'.xml','w');
--循环截取
loop
dbms_output.put_line(length);
--截取一段字符串放到BUFFER变量中
if start_idx=1 and length<=32767 then
end_idx=length;
end if;
dbms_lob.read(data,end_idx,start_idx,buffer);
dbms_output.put_line(start_idx||'___..___'||end_idx);
--把BUFFER中的数据输出
utl_file.put(xmlfile,buffer);
exit when end_idx=length;
--重设循环变量
if end_idx+32767<length then
end_idx := end_idx+32767;
start_idx := start_idx+32767;
else
start_idx := start_idx+(length-end_idx);
end_idx := end_idx+(length-end_idx);
end if;
dbms_output.put_line(start_idx||'______'||end_idx);
--清空BUFFER
buffer := null;
end loop;
utl_file.fclose(xmlfile);
end;