在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程
来源:互联网 发布:python 有什么用 编辑:程序博客网 时间:2024/05/19 10:16
学校里要求做个题,题目要求是写出将已知格式xml文件中的数据导入Oracle数据库、把数据库中的数据导出成xml文件的过程。在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程
已知格式xml文件如下:
<PEOPLE>
<PERSON PERSONID="E01">
<NAME>Tony Blair</NAME>
<ADDRESS>10 Downing Street, London, UK</ADDRESS>
<TEL>(061) 98765</TEL><FAX>(061) 98768</FAX>
<EMAIL>blair@everywhere.com</EMAIL>
</PERSON>
<PERSON PERSONID="E02">
<NAME>Bill Clinton</NAME>
<ADDRESS>White House, USA</ADDRESS>
<TEL>(001) 6400 98765</TEL><FAX>(001) 6400 98769</FAX>
<EMAIL>bill@everywhere.com</EMAIL>
</PERSON>
<PERSON PERSONID="E03">
<NAME>Tom Cruise</NAME>
<ADDRESS>57 Jumbo Street, New York, USA</ADDRESS>
<TEL>(001) 4500 67859</TEL><FAX>(001) 4500 67895</FAX>
<EMAIL>cruise@everywhere.com</EMAIL>
</PERSON>
<PERSON PERSONID="E04">
<NAME>Linda Goodman</NAME>
<ADDRESS>78 Crax Lane, London, UK</ADDRESS>
<TEL>(061) 54 56789</TEL><FAX>(061) 54 56772</FAX>
<EMAIL>linda@everywhere.com</EMAIL>
</PERSON>
</PEOPLE>
于是写出过程如下:
CREATE TABLE PEOPLE
(
PERSONID VARCHAR2(10) PRIMARY KEY,
NAME VARCHAR2(20),
ADDRESS VARCHAR2(60),
TEL VARCHAR2(20),
FAX VARCHAR2(20),
EMAIL VARCHAR2(40)
);
--从xml文件载入数据保存到数据库的过程
create or replace procedure xml2db(xmlfile varchar2) as
p xmlparser.Parser;
doc xmldom.DOMDocument;
n xmldom.DOMNode;
nl xmldom.DOMNodeList;
len number;
--根据PERSON结点生成插入SQL语句的过程
function insertsql(node xmldom.DOMNode) return varchar2
is
n xmldom.DOMNode;
nl xmldom.DOMNodeList;
len number;
nnm xmldom.DOMNamedNodeMap;
isql varchar2(200);
begin
--取得PERSON结点所有属性
nnm := xmldom.getAttributes(node);
isql := 'insert into people values(';
isql := isql || '''';
--取得所有属性中的第一个属性,即"PERSONID",并拼接到SQL语句中
isql := isql || xmldom.getNodeValue(xmldom.item(nnm,0));
isql := isql || '''';
--取得PERSON结点下的所有结点,准备遍历
nl := xmldom.getChildNodes(node);
len := xmldom.getLength(nl);
for i in 0..len-1 loop
--取出第i个结点
n := xmldom.item(nl,i);
isql := isql || ',';
isql := isql || '''';
--将结点的文本值取出并拼接到SQL语句中
isql := isql || xmldom.getNodeValue(xmldom.getFirstChild(n));
isql := isql || '''';
end loop;
isql := isql || ')';
return isql;
end insertsql;
begin
p := xmlparser.newParser;
xmlparser.parse(p,xmlfile);
--转换xml文件成DOM对像
doc := xmlparser.getDocument(p);
xmlparser.freeParser(p);
--取出所有PERSON元素
nl := xmldom.getElementsByTagName(doc,'PERSON');
len := xmldom.getLength(nl);
--清空people表的内容
delete from people;
for i in 0..len-1 loop
--取出第i个PERSON元素
n := xmldom.item(nl,i);
--执行插入该PERSON元素所用的SQL语句
execute immediate insertsql(n);
end loop;
commit;
xmldom.freeDocument(doc);
end xml2db;
/
--将数据库中的数据导出成xml文件的过程
create or replace procedure db2xml(xmlfile varchar2) as
doc xmldom.DOMDocument;
ret xmldom.DOMNode;
peoplenode xmldom.DOMNode;
--遍历整个people表的游标
Cursor cur_people is select * from people;
--将people表中一行记录转换为元素
--并插入到DOM文档对像根结点PEOPLE下的过程
procedure addperson(doc xmldom.DOMDocument,people xmldom.DOMNode,
v_pid varchar2,v_name varchar2,v_addr varchar2,
v_tel varchar2,v_fax varchar2,v_email varchar2)
is
personelem xmldom.DOMElement;
personnode xmldom.DOMNode;
itemelem xmldom.DOMElement;
itemnode xmldom.DOMNode;
text xmldom.DOMText;
begin
--创建PERSON结点
personelem := xmldom.createElement(doc,'PERSON');
--设置PERSONID属性
xmldom.setAttribute(personelem,'PERSONID',v_pid);
personnode := xmldom.appendChild(peoplenode,xmldom.makeNode(personelem));
--向PERSON结点中添加NAME元素
itemelem := xmldom.createElement(doc,'NAME');
-- 将NAME结点添加到PERSON结点中
itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem));
-- 创建文本结点
text := xmldom.createTextNode(doc,v_name);
-- 将文本结点添加到NAME结点下,以构成完整NAME元素
itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text));
--向PERSON结点中添加ADDRESS元素
itemelem := xmldom.createElement(doc,'ADDRESS');
itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem));
text := xmldom.createTextNode(doc,v_addr);
itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text));
--向PERSON结点中添加TEL元素
itemelem := xmldom.createElement(doc,'TEL');
itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem));
text := xmldom.createTextNode(doc,v_tel);
itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text));
--向PERSON结点中添加FAX元素
itemelem := xmldom.createElement(doc,'FAX');
itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem));
text := xmldom.createTextNode(doc,v_fax);
itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text));
--向PERSON结点中添加EMAIL元素
itemelem := xmldom.createElement(doc,'EMAIL');
itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem));
text := xmldom.createTextNode(doc,v_email);
itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text));
end addperson;
begin
--创建一个新DOM文档对像
doc := xmldom.newDOMDocument;
--为文档添加根结点PEOPLE
peoplenode := xmldom.makeNode(xmldom.createElement(doc,'PEOPLE'));
ret := xmldom.appendChild(xmldom.makeNode(doc),peoplenode);
--使用游标遍历people中的每行,生成每一行对应的PERSON元素并添加到PEOPLE根结点中
for v_row in cur_people loop
addperson(doc,peoplenode,v_row.personid,v_row.name,
v_row.address,v_row.tel,v_row.fax,v_row.email);
end loop;
--将结果写入指定文件
xmldom.writeToFile(doc,xmlfile);
xmldom.freeDocument(doc);
end db2xml;
dir varchar2(50);
infile varchar2(20);
errlog varchar2(20);
p xmlparser.parser;
doc xmldom.DOMDocument;
n1 xmldom.DOMNodeList;
len1 number(3);
n2 xmldom.DOMNodeList;
len2 number(3);
n3 xmldom.DOMNodeList;
len3 number(3);
v1 xmldom.DOMNode;
v2 xmldom.DOMNode;
v3 xmldom.DOMNode;
attn xmldom.DOMNode;
strSQL varchar2(200);
nnm xmldom.DOMNamedNodeMap;
begin
dir:= 'C:';
infile:= 'people.xml';
errlog:= 'err.log ';
p:= xmlparser.newParser;
xmlparser.setValidationMode(p,false);
xmlparser.setBaseDir(p,dir);
xmlparser.parse(p,dir||'/ '||infile);
doc:= xmlparser.getDocument(p);
xmlparser.freeParser(p);
n1:= xmldom.getElementsByTagName(doc,'PEOPLE ');
len1:= xmldom.getLength(n1);
for i in 0.. len1-1 loop
v1:= xmldom.item(n1,i);
n2:= xmldom.getChildNodes(v1);
len2:= xmldom.getLength(n2);
for j in 0..len2-1 loop
strSQL := 'INSERT INTO PEOPLE VALUES( ';
v2 := xmldom.item(n2,j);
if xmldom.getNodeName(v2) = 'PERSON ' then
nnm:= xmldom.getAttributes(v2);
attn := xmldom.item(nnm,0);
strSQL := strSQL|| '"'||to_char(xmldom.getNodeValue(attn))|| '"';
end if;
n3 := xmldom.getChildNodes(v2);
len3 := xmldom.getLength(n3);
for z in 0 ..len3 - 1 loop
v3 := xmldom.item(n3, z);
strSQL:= strSQL || ', '''|| to_char(xmldom.getNodeValue(xmldom.getFirstChild(v3)))||'"';
end loop;
strSQL := strSQL || ') ';
dbms_output.put_line(strSQL);
execute immediate(strSQL);
commit;
strSQL:= ' ';
end loop;
end loop;
-- 释放文档对象
xmldom.freeDocument(doc);
end;
/*****************************************************************************
过程名称:add_to_stockmarket
参数1:fileName in varchar2 接收用户输入的xml文件名
功能:将xml文件中的数据导入到Stockmarket数据表中
*****************************************************************************/
create or replace procedure add_to_stockmarket(fileName IN varchar2) as
document xmldom.DOMDocument;--声明文档对象模型
subelement xmldom.DOMElement;--声明元素类型
nodelistStock xmldom.DOMNodeList;
nodelistStockChild xmldom.DOMNodeList;
stock_code char(6);--stock字段
stock_name varchar2(30);--stockname字段
stock_shortname varchar(30);--stockshortname字段
stock_buydate date;--stockbuydate字段
recordCountOuter number;--存储xml文档中stock元素的个数
quantity exception;
begin
subelement:=xmldom.getDocumentElement(document);--获得根元素
nodelistStock:=xmldom.getElementsByTagName(subelement,'Stock');
recordCountOuter:=xmldom.getLength(nodelistStock);
for outerCycle in 0..recordCountOuter-1 loop--循环记录信息
nodelistStockChild:=xmldom.getChildNodes(xmldom.item(nodelistStock,outerCycle));
if xmldom.getLength(nodelistStockChild)<>4 then
raise quantity;
end if;
stock_code:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockChild,0)));
stock_name:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockChild,1)));
stock_shortname:=xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockChild,2)));
stock_buydate:=to_date(xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(nodelistStockChild,3))),'yyyy-fmMM-fmDD HH24:MI:SS');
insert into stockmarket values(stock_code,stock_name,stock_shortname,stock_buydate);
end loop;
commit;
exception
when quantity then
raise_application_error(-20010,'请核对该xml文档与数据库表Stockmarket的结构是否一致,'
||chr(10)||'若仍不能解决问题,请与系统管理员联系!');
end;
/************************************************************************************************************
http://topic.csdn.net/t/20061003/11/5061557.html
存储过程名称: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')
//存储过程执行方式如下:
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);--解析游标
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);--执行游标
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;
http://www.cnblogs.com/zxsoft/archive/2008/05/17/1201126.html
- 解决 在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程
- 在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程
- 在Oracle表数据和xml文件间相互导入导出
- PL/SQL导出导入Oracle表数据
- [PL/SQL]导入文件内的数据到数据库(针对本博的导出存储过程)
- Oracle pl/sql 复制表 数据导入 导出
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- PL/SQL Developer导入导出表结构和表数据
- PL/SQL Developer导入、导出表结构和表数据
- pl/sql导入导出表数据,表结构,oracle不改变表的所有者 ZT
- 关于pl /sql导入导出表结构,表数据,Oracle不能改变表的所有者
- PL /SQL的数据导入导出
- Oracle--oracle导出表结构到sql文件 ,数据库的导入导出CMD命令exp和imp、使用数据泵导入(impdp)和导出(expdp)
- Linux设备驱动程序第三版学习(6)- 高级字符驱动程序操作(续1) - 进程休眠
- glib和glibc区别
- 关于linux前台转后台
- CGX’s Apology
- C++ 归并排序实现(算法导论)
- 在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程
- FW:理解矩阵(三)(2)
- 虚拟机安装Ubuntu的上网设置(有线网络和无线网络)
- FW:理解矩阵(三)(1)
- 大端和小端
- FW:理解矩阵(二)
- PKCS#7格式的数字签名
- 饼图的实现
- FW:理解矩阵(一)