17,377
社区成员
发帖
与我相关
我的任务
分享
<fizer>
<Company CODE="abc">
<VALUE>12345678944555</VALUE>
</Company>
<Company CODE="def">
<VALUE>1011121314156156</VALUE>
</Company>
</fizer>
CREATE OR REPLACE PROCEDURE check_Pfizer_Companycode(file_path VARCHAR2,log_path VARCHAR2,vCompanyCode VARCHAR2)
AS
--//XML
xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
--//DOM
doc xmldom.DOMDocument;
len integer;
personNodes xmldom.DOMNodeList;
chilNodes xmldom.DOMNodeList;
tempNode xmldom.DOMNode;
tempArrMap xmldom.DOMNamedNodeMap;
--================================
--XML node value
pid varchar2(4);
vname varchar2(50);
tmp integer;
--================================
BEGIN
xmlPar := xmlparser.newParser;
xmlparser.setErrorLog( xmlPar, log_path);
xmlparser.parse(xmlPar, file_path);
doc := xmlparser.getDocument( xmlPar );
--
xmlparser.freeParser(xmlPar);
-- PERSON element
personNodes := xmldom.getElementsByTagName( doc, 'PERSON' );
len := xmldom.getLength( personNodes );
--loop all PERSON element
FOR i in 0..len-1
LOOP
--PERSON
tempNode := xmldom.item( personNodes, i );
--All attribute
tempArrMap := xmldom.getAttributes(tempNode);
--get key
pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap,'PERSONID'));
--get value
chilNodes := xmldom.getChildNodes(tempNode);
tmp := xmldom.GETLENGTH( chilNodes );
vname := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item( chilNodes, 0 )));
END LOOP;
-- doc
xmldom.freeDocument(doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_output.PUT_LINE(SQLERRM);
END addPerson;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL>
SQL> --1.使用具有dba权限用户如sys创建一个目录,如:
SQL> CREATE OR REPLACE DIRECTORY TESTDIR AS 'D:\temp\';
Directory created
SQL> --2.将读写访问权限赋给需要操作文件的用户,如test用户:
SQL> GRANT READ,WRITE ON DIRECTORY TESTDIR TO test;
Grant succeeded
SQL>
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
SQL>
SQL> --3.登录test用户,在其下创建一个函数:
SQL> CREATE OR REPLACE FUNCTION getCompanyValue(file_path VARCHAR2, company_code VARCHAR2)
2 RETURN VARCHAR2 IS
3 --//XML解析器
4 xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER;
5 --//DOM文档对象
6 doc xmldom.DOMDocument;
7 len INTEGER;
8 personNodes xmldom.DOMNodeList;
9 chilNodes xmldom.DOMNodeList;
10 tempNode xmldom.DOMNode;
11 tempArrMap xmldom.DOMNamedNodeMap;
12 --================================
13 --以下变量用于获取XML节点的值
14 pid VARCHAR2(100);
15 companyValue VARCHAR2(100) := '';
16 tmp INTEGER;
17 --================================
18 BEGIN
19 xmlPar := xmlparser.newParser;
20 xmlparser.parse(xmlPar, file_path);
21 doc := xmlparser.getDocument(xmlPar);
22 -- 释放解析器实例
23 xmlparser.freeParser(xmlPar);
24 -- 获取所有PERSON元素
25 personNodes := xmldom.getElementsByTagName(doc, 'Company');
26 len := xmldom.getLength(personNodes);
27 --遍历所有PERSON元素
28 FOR i IN 0 .. len - 1 LOOP
29 --获取第i个PERSON
30 tempNode := xmldom.item(personNodes, i);
31 --所有属性
32 tempArrMap := xmldom.getAttributes(tempNode);
33 --获取PERSONID的值
34 pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap, 'CODE'));
35 IF pid = company_code THEN
36 --获取子元素的值
37 chilNodes := xmldom.getChildNodes(tempNode);
38 tmp := xmldom.GETLENGTH(chilNodes);
39 companyValue := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 0)));
40 EXIT;
41 END IF;
42 END LOOP;
43 -- 释放文档对象
44 xmldom.freeDocument(doc);
45 RETURN companyValue;
46 EXCEPTION
47 WHEN OTHERS THEN
48 DBMS_output.PUT_LINE(SQLERRM);
49 END getCompanyValue;
50 /
Function created
SQL> --4.将company.xml文件复制到数据服务器的D:\temp\目录下
SQL> --5.测试该函数
SQL> var companyValue varchar2(100);
SQL> exec :companyValue := getCompanyValue('TESTDIR\company.xml','abc');
PL/SQL procedure successfully completed
companyValue
---------
12345678944555
SQL> print companyValue
companyValue
---------
12345678944555
SQL>
<?xml version="1.0"?>
<fizer>
<Company CODE="abc">
<VALUE>12345678944555</VALUE>
</Company>
<Company CODE="def">
<VALUE>1011121314156156</VALUE>
</Company>
</fizer>