动态创建表和生成数据-白红宇

动态创建表和生成数据

阅读量：5164 次

发布时间：2019-06-13

本文共 7391 字，大约阅读时间需要 24 分钟。

--XMLTABLE中的to变量只能是小写

--或者用FROM dual connect by rownum<=3;

CREATE OR REPLACE PROCEDURE PKG_AUTOCREATETABLEANDDATA(

PN_COL_NUM NUMBER DEFAULT 0,

PN_ROW_NUM NUMBER DEFAULT 0,

PC_TABLE_NAME OUT VARCHAR2)

LN_COL_NUM NUMBER;

LN_ROW_NUM NUMBER;

LC_TABLE_NAME VARCHAR2(30);

LC_COLUMN_TYPE_LIST VARCHAR2(30000):='|VARCHAR2(LNG)<DBMS_RANDOM.STRING(''U'',LNG)>|NUMBER<DBMS_RANDOM.VALUE(-99999999999999,+99999999999999)>|DATE<SYSDATE-DBMS_RANDOM.VALUE(-100000,100000)>|';

LC_TABLE_CREATE VARCHAR2(30000);

LC_TABLE_INSERT VARCHAR2(30000);

LC_TMP_COLTYPE VARCHAR2(30000);

LC_TMP_DATA VARCHAR2(30000);

LN_TMP_LENGTH NUMBER;

LN_TMP_NB NUMBER;

BEGIN

FOR K IN 1..100 LOOP

LC_TABLE_CREATE:='';

LC_TABLE_INSERT:='';

IF PN_COL_NUM BETWEEN 1 AND 5 THEN

LN_COL_NUM:=PN_COL_NUM;

ELSE

LN_COL_NUM:=ROUND(DBMS_RANDOM.VALUE(1,5));

END IF;

IF PN_ROW_NUM BETWEEN 1 AND 5 THEN

LN_ROW_NUM:=PN_ROW_NUM;

ELSE

LN_ROW_NUM:=ROUND(DBMS_RANDOM.VALUE(1,1000));

END IF;

LC_TABLE_NAME:='TETB_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||DBMS_RANDOM.STRING('U',11);

FOR I IN 1..LN_COL_NUM LOOP

--GET COLUMN TYPE

LN_TMP_NB:=LENGTH(REPLACE(LC_COLUMN_TYPE_LIST,'|','||'))-LENGTH(LC_COLUMN_TYPE_LIST)-1;

LN_TMP_NB:=ROUND(DBMS_RANDOM.VALUE(1,LN_TMP_NB));

LC_TMP_COLTYPE:=SUBSTR(LC_COLUMN_TYPE_LIST,INSTR(LC_COLUMN_TYPE_LIST,'|',1,LN_TMP_NB)+1);

LC_TMP_DATA:=SUBSTR(LC_TMP_COLTYPE,INSTR(LC_TMP_COLTYPE,'<')+1);

LC_TMP_DATA:=SUBSTR(LC_TMP_DATA,1,INSTR(LC_TMP_DATA,'>')-1);

LC_TMP_COLTYPE:=SUBSTR(LC_TMP_COLTYPE,1,INSTR(LC_TMP_COLTYPE,'<')-1);

--VARCHAR2 LENGTH

IF INSTR(LC_TMP_DATA,'LNG')>0 OR INSTR(LC_TMP_COLTYPE,'LNG')>0 THEN

LN_TMP_LENGTH:=ROUND(DBMS_RANDOM.VALUE(1,1500));

LC_TMP_DATA:=REPLACE(LC_TMP_DATA,'LNG',LN_TMP_LENGTH);

LC_TMP_COLTYPE:=REPLACE(LC_TMP_COLTYPE,'LNG',LN_TMP_LENGTH);

END IF;

LC_TABLE_CREATE:=LC_TABLE_CREATE||', COL'||I||' '||LC_TMP_COLTYPE;

LC_TABLE_INSERT:=LC_TABLE_INSERT||', '||LC_TMP_DATA;

END LOOP;

--LC_TABLE_CREATE:='CREATE TABLE '||LC_TABLE_NAME||' ('||SUBSTR(LC_TABLE_CREATE,2)||')';

LC_TABLE_CREATE:='CREATE TABLE '||LC_TABLE_NAME||' (ID NUMBER PRIMARY KEY,'||SUBSTR(LC_TABLE_CREATE,2)||')';

LC_TABLE_INSERT:='INSERT INTO '||LC_TABLE_NAME||' SELECT ROWNUM,'||SUBSTR(LC_TABLE_INSERT,2)||' FROM XMLTABLE(''1 to '||LN_ROW_NUM||''')';

PC_TABLE_NAME:=LC_TABLE_NAME||' COL:'||LN_COL_NUM||' ROW:'||LN_ROW_NUM;

EXECUTE IMMEDIATE LC_TABLE_CREATE;

EXECUTE IMMEDIATE LC_TABLE_INSERT;

COMMIT;

END LOOP;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(LC_TABLE_CREATE);

DBMS_OUTPUT.PUT_LINE(LC_TABLE_INSERT);

RAISE_APPLICATION_ERROR(-20010,SQLCODE||'--'||SQLERRM);

END PKG_AUTOCREATETABLEANDDATA;

--赋予权限

GRANT CREATE TABLE TO USER_NAME;

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

--查询生成的表和数据

SELECT 'SELECT * FROM '||T.TABLE_NAME||';','DROP TABLE '||T.TABLE_NAME||';',T.* FROM USER_TABLES T WHERE T.TABLE_NAME LIKE '%TETB%';

--生成表和数据

DECLARE

v_table_name VARCHAR2(500);

BEGIN

PKG_autocreatetableanddata(2,2,pc_table_name =>v_table_name);

END;

--删除表

DECLARE

V_EXE VARCHAR2(1000);

BEGIN

FOR REC IN (SELECT 'DROP TABLE '||T.TABLE_NAME AS V_EXE FROM USER_TABLES T WHERE T.TABLE_NAME LIKE '%TETB%') LOOP

EXECUTE IMMEDIATE REC.V_EXE;

END LOOP;

END;

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

--Oracle XMLTable

从Oracle 10g开始，甲骨文公司新增了XQuery和XMLTable两个功能作为处理XML的武器。 XMLQuery一样，您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。

本文我们将了解Oracle XMLTable函数，并且通过例子介绍XMLTable函数的用法。

考虑到员工会有一些XML数据，所以我们创建一个EMPLOYEES表：

Create TABLE EMPLOYEES

(

id NUMBER,

data XMLTYPE

)

表创建完成后，我们往表里插入一些数据:

Insert INTO EMPLOYEES

VALUES (1, xmltype ('<Employees>

<lastname>Watson</lastname>

<email>johnwatson@sh.com</email>

</Employee>

<firstname>Sherlock</firstname>

<lastname>Homes</lastname>

<email>sherlock@sh.com</email>

</Employee>

<lastname>Moriarty</lastname>

</Employee>

<firstname>Mycroft</firstname>

<lastname>Holmes</lastname>

<email>mycroft@sh.com</email>

</Employee>

</Employees>'));

注意：XML包含了员工的相关数据，在我们开始学习之前我们首先明确几个数据：

1、有4名员工在我们的XML文件

2、每个员工都有通过属性定义一个唯一的员工id emplid

3、每个员工也有一个属性type，定义雇员是否是管理员或用户。

4、每个员工都有四个子节点： firstname ， lastname ， age和email

5、年龄是多少

现在我们可以使用Oracle XMLTable函数从XML中检索不同的信息。

1、学习XPath表达式

使用XMLTable函数之前最好知道一点关于XPath。XPath使用路径表达式来选择XML文档中的节点或节点列表。看下面的列表：

Expression

Description

nodename

选择所有名称为"nodename"的节点

选择根节点

从当前节点选择文档中相匹配的节点，无论他们在哪里

选择当前节点

选择当前节点的父节点

选择属性

employee

选择所有名称为"employee"的节点

employees/employee

选择所有子节点为employee的employees节点

//employee

选择所有employee的元素，无论他们在哪里

下面的表达式称为谓词列表。谓词在方括号中定义 [ ... ]。他们被用来找到一个特定的节点或包含一个特定值的节点。

Path Expression

Result

/employees/employee[1]

选择第一个employee节点,它是employees的子节点。

/employees/employee[last()]

选择最后一个employee元素，它是employees的子节点

/employees/employee[last()-1]

选择是employees子元素的倒数第二个employee元素

//employee[@type='admin']

选择所有具有与'admin'的值的属性命名类型的employee元素

其他更多的表达式可以参考Oracle官方手册

2、Oracle XMLTable函数的基础知识

读取Employees中所有firstname和lastname

在这个查询中,我们使用XMLTable函数从EMPLOYEES表解析XML内容。

select t.id, x.*

from employees t,

xmltable('/Employees/Employee' passing t.data columns firstname

varchar2(30) path 'firstname',

lastname varchar2(30) path 'lastname') x

where t.id = 1;

注XMLTable函数的语法：

XMLTable('<XQuery>'

PASSING <xml column>

COLUMNS <new column name> <column type> PATH <XQuery path>)

XMLTABLE函数包含一个XQuery行表达式和由一个或多个列表达式组成的COLUMNS子句。在上面的语句中，行表达式是 XPath /Employees/Employee。PASSING子句中的t.data指的是employees表中的XML列中的数据。

COLUMNS 子句用于将XML数据转换成关系数据，这里每个参数都定义了一个列名和SQL数据类型。在上面的查询中，我们定义了firstname 和 lastname列并指向PATH的firstname 和 lastname或者选定的节点。

输出：

使用text()读取节点值

在上面的教程中，我们读取到了firstname / lastname节点。通常我们还需要获取节点的文本值，下面的例子中，我们选取/Employees/Employee/firstname路径，并使用text()获取节点的值。

下面查询employees中所有的firstname

select t.id, x.*

from employees t,

xmltable('/Employees/Employee/firstname' passing t.data columns

firstname varchar2(30) path 'text()') x

where t.id = 1

输出：

不仅仅是text()表达式，Oracle还提供了其他很多有用的表达式，如 item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string。

读取所选节点的属性

XML节点定了相关属性，我们也可以读取到节点的这些属性，下面的查询是找出employee节点的type属性：

select emp.id, x.*

from employees emp,

xmltable('/Employees/Employee' passing emp.data columns firstname

varchar2(30) path 'firstname',

type varchar2(30) path '@type') x;

输出：

使用ID读取特定的记录

select t.id, x.*

from employees t,

xmltable('/Employees/Employee[@emplid=2222]' passing t.data columns

lastname varchar2(30) path 'lastname') x

where t.id = 1;

输出：

读取所有类型是admin的员工的firstname 和 lastname

select t.id, x.*

from employees t,

xmltable('/Employees/Employee[@type="admin"]' passing t.data columns

firstname varchar2(30) path 'firstname',

lastname varchar2(30) path 'lastname') x

where t.id = 1;

输出：

读取年龄超过40的所有员工的firstname 和 lastname

select t.id, x.*

from employees t,

xmltable('/Employees/Employee[age>40]' passing t.data columns

firstname varchar2(30) path 'firstname',

lastname varchar2(30) path 'lastname',

age varchar2(30) path 'age') x

where t.id = 1;

输出：

本文由UncleToo翻译整理，转载请注明出处！

原文（英文）地址：http://viralpatel.net/blogs/oracle-xmltable-tutorial/

转载于:https://www.cnblogs.com/buffercache/p/10176774.html

你可能感兴趣的文章

35. Search Insert Position(C++)

快速切题 poj 1002 487-3279 按规则处理模拟难度:0

查看>>

Codeforces Round #277 (Div. 2)

深入理解 JavaScript 事件循环（一）— event loop