oracle取xml节点值,写一个存储过程来读写表中的xml中节点的值，怎么写效率最高...

oracle_cj 发表于 2014-6-3 20:40

贴上来学习下啊。。。

这是其中一个stored proc

PROCEDURE depersonalizeClienRequest(

birthDate IN DATE,

appId    IN NUMBER)

IS

CURSOR CR1

IS

SELECT CLIENT_REQUEST_ID FROM CLIENT_REQUEST cr, APPLICATION app WHERE cr.deal_id = app.deal_id AND cr.external_id = app.external_id AND app.application_id = appId;

XmlElementLength NUMBER;

position1        NUMBER;

position2        NUMBER;

xmlData CLOB;

xml_element CLOB;

idx NUMBER;

v_request_ID   NUMBER;

requestId varchar(50);

TYPE tag_list

IS

TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

startTag tag_list;

closeTag tag_list;

t_index NUMBER;

BEGIN

startTag(1)   :='';

closeTag(1)   := '';

startTag(2)   :='';

closeTag(2)   := '';

startTag(3)   :='';

closeTag(3)   := '';

startTag(4)   :='';

closeTag(4)   := '';

startTag(5)   :='';

closeTag(5)   := '';

startTag(6)   :='';

closeTag(6)   := '';

startTag(8)   :='';

closeTag(8)   := '';

startTag(9)   :='';

closeTag(9)   := '';

startTag(10)  :='';

closeTag(10)  := '';

startTag(11)  :='';

closeTag(11)  := '';

startTag(12)  :='';

closeTag(12)  := '';

startTag(13)  :='

startTag(14)  :='';

closeTag(14)  := '';

startTag(15)  :='';

closeTag(15)  := '';

startTag(16)  :='';

closeTag(16)  := '';

startTag(17)  :='';

closeTag(17)  := '';

open CR1;

LOOP

fetch CR1 into requestId;

t_index       := startTag.first;

EXIT WHEN CR1%NOTFOUND;

v_request_ID := requestId;

SELECT cr.request_data

INTO xmlData

FROM client_request cr

WHERE cr.client_request_id = requestId;

WHILE t_index IS NOT NULL

LOOP

position1                 :=INSTR(xmlData,startTag(t_index),1,1);

IF position1              <> 0 THEN

idx                     :=1;

position2               :=INSTR(xmlData,closeTag(t_index),-1,idx);

WHILE INSTR(xmlData,closeTag(t_index),-1,idx) > position1

LOOP

position2 := INSTR(xmlData,closeTag(t_index),-1,idx);

idx       := (idx                            + 1);

END LOOP;

XmlElementLength := (position2 - position1 + LENGTH(closeTag(t_index)));

xml_Element      := SUBSTR(xmlData,position1,XmlElementLength);

IF xml_Element LIKE '%DOB%' OR xml_Element LIKE '%ate%'

THEN

xmlData := REPLACE(xmlData, xml_Element,startTag(t_index)

||birthDate

||closeTag(t_index));

ELSE

xmlData := REPLACE(xmlData, xml_Element,startTag(t_index)

||'XXXX'

||closeTag(t_index));

END IF;

END IF;

t_index := startTag.NEXT(t_index);

END LOOP;

update CLIENT_REQUEST cr

set cr.request_data = xmldata

where cr.client_request_id = requestId;

COMMIT;

end loop;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('Depersonalization Client Request could not be completed: ' || SQLERRM);

ROLLBACK;

RAISE;

END depersonalizeClienRequest;