oracle报错stress,Oracle XML DB之浅入浅出-CSDN博客

备注：XML DB是Oracle 9.2中出现的新特性。

1XML DB安装

通过DBCA安装

2XML DB数据处理

2.1存储XML数据到XML表中

2.1.1创建一个有XMLType类型字段的表：

create table

xmlcontent (keyvalue varchar2(10) primary key,

xmlvalue xmltype);

2.1.2创建一个XMLType类型表

create table

xmltable of xmltype;

2.1.3从XML文件中读取数据存储到XML表中

创建Directory指向存放XML文件的路径：

SQL> grant

create any directory to xdb;

Grant

succeeded.

SQL> conn

xdb/xdb

Connected.

SQL> create

directory XMLDIR as 'C:oracleXMLDB';

Directory

created.

创建存储过程，从XML文件中读取数据存储到XML表中

create or

replace function getClobDocument(

filename in

varchar2,

charset in

varchar2 default NULL)

return CLOB

deterministic

file bfile := bfilename(‘XMLDIR’,filename);

charContent CLOB := ' ';

targetFile bfile;

lang_ctx number := DBMS_LOB.default_lang_ctx;

charset_id number := 0;

src_offset number := 1 ;

dst_offset number := 1 ;

warning number;

begin

if charset

is not null then

charset_id := NLS_CHARSET_ID(charset);

end

if;

targetFile

:= file;

DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);

DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,

DBMS_LOB.getLength(targetFile), src_offset, dst_offset,

charset_id, lang_ctx,warning);

DBMS_LOB.fileclose(targetFile);

return

charContent;

end;

-- you can use

getCLOBDocument() to generate a CLOB from a file containin

-- an XML

document. For example, the following statement inserts a row into the

-- XMLType

table Example2 created earlier:

向表中插入数据：

SQL> INSERT

INTO XMLTABLE

VALUES(XMLTYPE(getCLOBDocument('init.xml')));

1 row

created.

SQL> INSERT

INTO XMLContent

2 VALUES(1,

XMLTYPE(getCLOBDocument('init.xml', 'UTF8')));

1 row

created.

SQL>

commit;

Commit

complete.

2.2更新XML表数据

2.2.1普通Update方式

SQL> UPDATE

XMLTABLE X SET VALUE(X)=XMLTYPE(getCLOBDocument('init.xml'));

1 row

updated.

SQL> UPDATE

XMLContent SET xmlvalue=XMLTYPE(getCLOBDocument('init.xml'))

2 WHERE

keyvalue='1';

1 row

updated.

SQL>

commit;

Commit

complete.

2.2.2使用updatexml()更新节点值

SQL> UPDATE

xmlcontent

2 SET

xmlvalue = updateXML(xmlvalue,

3 '/sqlstress/config/userName/text()',

4 'shanxi')

5 WHERE

existsNode(xmlvalue,

'/sqlstress/config[userName="shanxi806"]') = 1;

1 row

updated.

SQL>

commit;

Commit

complete.

SQL> select

extractvalue(xmlvalue,'/sqlstress/config/userName')

2 from

xmlcontent;

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/CONFIG/USERNAME')

---------------------------------------------------------------------

shanxi

2.2.3使用updatexml()更新一个节点树

SQL> UPDATE

xmlcontent

2 SET

xmlvalue =

updateXML(xmlvalue,

'/sqlstress/functions/function[1]/parameters/prameter[2]/valuecope',

5 xmltype('

7 13111111111

13999999999

)

15 WHERE

existsNode(xmlvalue,

'/sqlstress/functions/function[1]/parameters/parameter[@pid=2]'

17 ) =

1 row

updated.

2.3从XML表中读取数据

主要利用extract(), extractValue(), and existsNode()等几个函数。”Init.xml”的内容参见附录。

2.3.1existsNode ()

EXISTSNODE函数检查XML中的某一个节点是否存在。如果存在，返回1，否则返回0。

SQL> SELECT

existsNode(value(X),'/sqlstress/config/connURL')

2 FROM

XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG/CONNURL')

------------------------------------------------

SQL> SELECT

existsNode(value(X),'/sqlstress/config/connURLFalse')

2 FROM

XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG/CONNURLFALSE')

-----------------------------------------------------

SQL> SELECT

existsNode(xmlvalue,'/sqlstress/config/connURL')

2 FROM

XMLContent X

3 WHERE

keyvalue = '1';

EXISTSNODE(XMLVALUE,'/SQLSTRESS/CONFIG/CONNURL')

------------------------------------------------

SQL> SELECT

existsNode(value(X),

'/sqlstress/config[userPWD="'||chr(10)||'

shanxi806'||chr(10)||' "'||']')

3 FROM

XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG[USERPWD="'||CHR(10)||'SHANXI806'||CHR(10)

--------------------------------------------------------------------------------

SQL> SELECT

count(*)

2 FROM

XMLContent x

3 WHERE

existsNode(xmlvalue,'/sqlstress/config[userName="shanxi806"]') = 1;

COUNT(*)

----------

SQL> SELECT

count(*)

2 FROM

XMLContent x

3 WHERE

existsNode(xmlvalue,'/sqlstress/functions/function[@id=1]') = 1;

COUNT(*)

----------

SQL> SELECT

count(*)

2 FROM

XMLContent x

3 WHERE

existsNode(xmlvalue,'/sqlstress/functions/function/parameters/parameter[2][@pid=2]')

= 1;

COUNT(*)

----------

2.3.2extractValue()

EXTRACTVALUE()是从某个节点中读取值

SQL> select

extractValue(xmlvalue, '/sqlstress/config/userPWD')

2 from

xmlcontent

3 where

keyvalue='1';

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/CONFIG/USERPWD')

----------------------------------------------------------------

shanxi806

SQL> SELECT

extractvalue(xmlvalue,

'/sqlstress/functions/function[1]/parameters/parameter[2]/@pid')

2 FROM

xmlcontent

3 WHERE

keyvalue = '1';

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/FUNCTIONS/FUNCTION[1]/PARAMETERS/PARAMETER[2]/

--------------------------------------------------------------------------------

ExtractValue只能返回一个确切的位置节点的值，如果存在多个相同节点，Oracle就会报错：

SQL> SELECT

extractvalue(xmlvalue, '/sqlstress/functions/function[1]/parameters/

parameter')

2 FROM

xmlcontent

3 WHERE

keyvalue = '1';

FROM

xmlcontent

ERROR at line

ORA-19025:

EXTRACTVALUE returns value of only one node

SQL> SELECT

extractvalue(xmlvalue, '/sqlstress/config')

2 FROM

xmlcontent

3 WHERE

keyvalue = '1';

FROM

xmlcontent

ERROR at line

ORA-19025:

EXTRACTVALUE returns value of only one node

2.3.3Extract()

EXTRACT函数返回一个XML文档的一个节点树，或者某一节点下所有符合条件的节点。

返回一个节点树：

SQL> set

line 100

SQL> set

lone 20000

SQL> SELECT

extract(xmlvalue, '/sqlstress/config')

2 FROM

xmlcontent

3 WHERE

keyvalue = '1';

EXTRACT(XMLVALUE,'/SQLSTRESS/CONFIG')

-----------------------------------------------------

jdbc:oracle:thin:@10.71.111.231:1521:P51

shanxi806

100

1000

返回所有符合条件的节点：

SQL> SELECT

extract(xmlvalue,

'/sqlstress/functions/function[1]/parameters/parameter/paraType')

2 FROM

xmlcontent

3 WHERE

keyvalue = '1';

EXTRACT(XMLVALUE,'/SQLSTRESS/FUNCTIONS/FUNCTION[1]/PARAMETERS/PARAMETER/PARATYPE')

--------------------------------------------------------------------------------

String

int

String

float

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-920380/，如需转载，请注明出处，否则将追究法律责任。