166.Oracle数据库SQL开发之 XML和ORACLE——将XML保持到数据库中

166.Oracle数据库SQL开发之 XML和ORACLE——将XML保持到数据库中

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50186113

将purchase_order.xml文件保存在数据库中。

内容如下：

<?xml version="1.0"?>

<purchase_order>

 <customer_order_id>176</customer_order_id>

 <order_date>2007-05-17</order_date>

 <customer_name>Best Products 456 Inc.</customer_name>

 <street>10 Any Street</street>

 <city>Any City</city>

 <state>CA</state>

 <zip>94440</zip>

 <phone_number>555-121-1234</phone_number>

 <products>

   <product>

     <product_id>1</product_id>

     <name>Supernova video</name>

      <quantity>5</quantity>

   </product>

   <product>

     <product_id>2</product_id>

     <name>Oracle SQL book</name>

     <quantity>4</quantity>

   </product>

 </products>

</purchase_order>

创建对象如下：

CREATE TYPE t_product AS OBJECT (

  product_idINTEGER,

  nameVARCHAR2(15),

  quantityINTEGER

);

/

CREATE TYPE t_nested_table_product AS TABLE OFt_product;

/

 

-- create the table

CREATETABLE purchase_order (

 purchase_order_id INTEGER CONSTRAINT purchase_order_pk PRIMARY KEY,

 customer_order_id INTEGER,

  order_dateDATE,

  customer_nameVARCHAR2(25),

  streetVARCHAR2(15),

  cityVARCHAR2(15),

  stateVARCHAR2(2),

  zipVARCHAR2(5),

  phone_numberVARCHAR2(12),

  productst_nested_table_product,

 xml_purchase_order XMLType

)

NESTEDTABLE products

STORE AS nested_products;

创建目录对象如下：

store@PDB1> CREATE OR REPLACE DIRECTORYXML_FILES_DIR AS '/home/oracle/xml_files';

 

Directory created.

然后向purchase_order表添加一行如下：

INSERT INTO purchase_order (

 purchase_order_id,

 xml_purchase_order

) VALUES (

  1,

  XMLType(

   BFILENAME('XML_FILES_DIR', 'purchase_order.xml'),

   NLS_CHARSET_ID('AL32UTF8')

  )

);

1 row created.

然后查看表如下：

store@PDB1> selectpurchase_order_id,xml_purchase_order from purchase_order;

 

PURCHASE_ORDER_ID

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

XML_PURCHASE_ORDER

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

                   1

<?xml version="1.0"?>

<purchase_order>

 <customer_order_id>176</customer_order_id>

 <order_date>2007-05-17</order_date>

 <customer_name>Best Products 456 Inc.</customer_name>

 <street>10 Any Street</street>

 <city>Any City</city>

 <state>CA</state>

 <zip>94440</zip>

 <phone_number>555-121-1234</phone_number>

 <products>

   <product>

     <product_id>1</product_id>

     <name>Supernova video</name>

     <quantity>5</quantity>

   </product>

   <product>

     <product_id>2</product_id>

     <name>Oracle SQL book</name>

     <quantity>4</quantity>

   </product>

 </products>

</purchase_order>

此外EXTRACT函数从xml_purchase_order列中存储的XML抽取customer_order_id等。

如下：

store@PDB1> selectextract(xml_purchase_order,'/purchase_order/customer_order_id') cust_order_idfrom purchase_order where purchase_order_id=1;

 

CUST_ORDER_ID

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

<customer_order_id>176</customer_order_id>

可以使用EXTRACTVALUE函数获得字符串值。

例如：

store@PDB1> selectextractvalue(xml_purchase_order,'/purchase_order/customer_order_id')cust_order_id from purchase_order where purchase_order_id=1;

 

CUST_ORDER_ID

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

176

使用EXISTSNODE函数检查XML元素是否存在。

store@PDB1> select 'Exists' as "EXISTS"from purchase_order where purchase_order_id=1 and existsnode( xml_purchase_order,'/purchase_order/products/product[product_id=1]')=1;

 

EXISTS

------

Exists

XMLSEQUENCE函数将产品检索为XMLType对象变长数组

store@PDB1> select product.* from table(selectxmlsequence(extract(xml_purchase_order,'/purchase_order//product')) frompurchase_order where purchase_order_id=1 ) product;

 

COLUMN_VALUE

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

<product>

 <product_id>1</product_id>

 <name>Supernova video</name>

 <quantity>5</quantity>

</product>

 

<product>

 <product_id>2</product_id>

 <name>Oracle SQL book</name>

 <quantity>4</quantity>

</product>

创建过程UPDATE_PURCHASE_ORDER

CREATE PROCEDURE update_purchase_order(

 p_purchase_order_id IN purchase_order.purchase_order_id%TYPE

) AS

  v_countINTEGER := 1;

 

  -- declare anested table to store products

 v_nested_table_products t_nested_table_product :=

   t_nested_table_product();

 

  -- declare atype to represent a product record

  TYPE t_product_record IS RECORD (

    product_idINTEGER,

    nameVARCHAR2(15),

    quantityINTEGER

  );

 

  -- declare aREF CURSOR type to point to product records

  TYPEt_product_cursor IS REF CURSOR RETURN t_product_record;

 

  -- declare acursor

 v_product_cursor t_product_cursor;

 

  -- declare avariable to store a product record

  v_productt_product_record;

BEGIN

  -- openv_product_cursor to read the product_id, name, and quantity for

  -- eachproduct stored in the XML of the xml_purchase_order column

  -- in thepurchase_order table

  OPENv_product_cursor FOR

  SELECT

   EXTRACTVALUE(product.COLUMN_VALUE, '/product/product_id')

      ASproduct_id,

   EXTRACTVALUE(product.COLUMN_VALUE, '/product/name') AS name,

   EXTRACTVALUE(product.COLUMN_VALUE, '/product/quantity') AS quantity

  FROM TABLE(

    SELECT

     XMLSEQUENCE(EXTRACT(xml_purchase_order, '/purchase_order//product'))

    FROMpurchase_order

    WHEREpurchase_order_id = p_purchase_order_id

  ) product;

 

  -- loop overthe contents of v_product_cursor

  LOOP

    -- fetchthe product records from v_product_cursor and exit when there

    -- are nomore records found

    FETCHv_product_cursor INTO v_product;

EXIT WHENv_product_cursor%NOTFOUND;

   -- extend v_nested_table_products so that aproduct can be stored in it

    v_nested_table_products.EXTEND;

 

    -- create a new product and store it inv_nested_table_products

    v_nested_table_products(v_count) :=

      t_product(v_product.product_id,v_product.name, v_product.quantity);

 

    -- display the new product stored inv_nested_table_products

    DBMS_OUTPUT.PUT_LINE('product_id = ' ||

     v_nested_table_products(v_count).product_id);

    DBMS_OUTPUT.PUT_LINE('name = ' ||

      v_nested_table_products(v_count).name);

    DBMS_OUTPUT.PUT_LINE('quantity = ' ||

      v_nested_table_products(v_count).quantity);

 

    -- increment v_count ready for the nextiteration of the loop

    v_count := v_count + 1;

  END LOOP;

 

  -- close v_product_cursor

  CLOSE v_product_cursor;

 

  -- update the purchase_order table using thevalues extracted from the

  -- XML stored in the xml_purchase_ordercolumn (the products nested

  -- table is set to v_nested_table_productsalready populated by the

  -- previous loop)

  UPDATE purchase_order

  SET

    customer_order_id =

      EXTRACTVALUE(xml_purchase_order,

        '/purchase_order/customer_order_id'),

    order_date =

      TO_DATE(EXTRACTVALUE(xml_purchase_order,

        '/purchase_order/order_date'),'YYYY-MM-DD'),

    customer_name =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/customer_name'),

    street =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/street'),

    city =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/city'),

    state =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/state'),

    zip =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/zip'),

    phone_number =

      EXTRACTVALUE(xml_purchase_order,'/purchase_order/phone_number'),

    products = v_nested_table_products

  WHERE purchase_order_id =p_purchase_order_id;

 

  -- commit the transaction

  COMMIT;

ENDupdate_purchase_order;

/

调用如下：

store@PDB1>call update_purchase_order(1);

product_id = 1

name =Supernova video

quantity = 5

product_id = 2

name = OracleSQL book

quantity = 4

 

Call completed.

查询如下：

store@PDB1>select purchase_order_id,customer_order_id,order_date,customer_name,street,city,state,zip,phone_number,productsfrom purchase_order where purchase_order_id=1;

 

PURCHASE_ORDER_IDCUSTOMER_ORDER_ID ORDER_DAT CUSTOMER_NAME                 STREET

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

CITY          ST ZIP       PHONE_NUMBER

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

PRODUCTS(PRODUCT_ID, NAME, QUANTITY)

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

                   1                176 17-MAY-07 Best Products 456 Inc.   10 Any Street

Any City    CA 94440 555-121-1234

T_NESTED_TABLE_PRODUCT(T_PRODUCT(1,'Supernova video', 5), T_PRODUCT(2, 'Oracle SQL book', 4))