谈谈oracle中的临时表

最新推荐文章于 2024-09-29 16:19:38 发布

dnil27295

最新推荐文章于 2024-09-29 16:19:38 发布

阅读量90

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/iyoume2008/p/4861583.html

版权

--------------------创建临时表临时保存从xml字符串解析来的数据---------------------------

会话级别临时表
SQL> create global temporary table temp_order01 (orderid number,ordernumber number,orderdate date) on commit delete rows;

Table created.

事务级别临时表
SQL> create global temporary table temp_order02 (orderid number,ordernumber number,orderdate date) on commit preserve rows;

Table created.

SQL> INSERT INTO TEMP_ORDER SELECT * FROM XMLTABLE('$B/orderlist/order' PASSING
XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&V_XML') AS B
COLUMNS orderid number PATH '/order/orderid',
ordernumber number PATH '/order/ordernumber',
orderdate date PATH '/order/orderprice');
Enter value for v_xml: <orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist>
old 2: XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&V_XML') AS B
new 2: XMLTYPE('<?xml version="1.0" encoding="gb2312" ?><orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist> ') AS B

1 row created.

SQL> select * from temp_order;

ORDERID ORDERNUMBER ORDERDATE
---------- ----------- ---------
1 1

SQL> commit;

Commit complete.

SQL> select * from temp_order;

no rows selected

<orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist>

转载于:https://www.cnblogs.com/iyoume2008/p/4861583.html