XML文件中的数据要插入到表 dept 和 emp。看了Oracle的官方文档说Oracle不支持插入多表,除非用XSLT将数据划分为多个表内容分别插入,或者做一个View来插入。我做了一个View。可是主表的字段不给更新,哪位能解决此问题?环境:win2k Adv server, Oracle 10g R2XML数据:
<?xml version="1.0"?>
<ROOT>
<Element>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
<EMP_ROW>
<EMP_ROW_ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11月-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1月 -82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</EMP_ROW_ROW>
</EMP_ROW>
</Element>
<Element>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
<EMP_ROW>
</EMP_ROW>
</Element>
...
<Element>
...
</Element>
</ROOT>表dept:CREATE TABLE DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14 BYTE),
LOC VARCHAR2(13 BYTE)
)表emp:
CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)做了一个View:
CREATE OR REPLACE FORCE VIEW scott.dept_emp
AS
SELECT d.deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.mgr, e.sal,
e.comm
FROM dept d, emp e
WHERE d.deptno = e.deptno;准备插入View的数据:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>11</DEPTNO>
<DNAME>Sales</DNAME>
<LOC>Texas</LOC>
<EMPNO>123</EMPNO>
<ENAME>Bob</ENAME>
<JOB>Sales</JOB>
<MGR>555</MGR>
<SAL>35000</SAL>
<COMM>12</COMM>
</ROW>
<ROW>
<DEPTNO>22</DEPTNO>
<DNAME>Accounting</DNAME>
<LOC>Washington</LOC>
<EMPNO>321</EMPNO>
<ENAME>Sue</ENAME>
<JOB>Finance</JOB>
<MGR>555</MGR>
<SAL>42000</SAL>
<COMM>12</COMM>
</ROW>
...
</ROWSET>小弟初学,请高手给个完整范例。谢谢!
<?xml version="1.0"?>
<ROOT>
<Element>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
<EMP_ROW>
<EMP_ROW_ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-11月-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</EMP_ROW_ROW>
<EMP_ROW_ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>23-1月 -82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</EMP_ROW_ROW>
</EMP_ROW>
</Element>
<Element>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
<EMP_ROW>
</EMP_ROW>
</Element>
...
<Element>
...
</Element>
</ROOT>表dept:CREATE TABLE DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14 BYTE),
LOC VARCHAR2(13 BYTE)
)表emp:
CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)做了一个View:
CREATE OR REPLACE FORCE VIEW scott.dept_emp
AS
SELECT d.deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.mgr, e.sal,
e.comm
FROM dept d, emp e
WHERE d.deptno = e.deptno;准备插入View的数据:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>11</DEPTNO>
<DNAME>Sales</DNAME>
<LOC>Texas</LOC>
<EMPNO>123</EMPNO>
<ENAME>Bob</ENAME>
<JOB>Sales</JOB>
<MGR>555</MGR>
<SAL>35000</SAL>
<COMM>12</COMM>
</ROW>
<ROW>
<DEPTNO>22</DEPTNO>
<DNAME>Accounting</DNAME>
<LOC>Washington</LOC>
<EMPNO>321</EMPNO>
<ENAME>Sue</ENAME>
<JOB>Finance</JOB>
<MGR>555</MGR>
<SAL>42000</SAL>
<COMM>12</COMM>
</ROW>
...
</ROWSET>小弟初学,请高手给个完整范例。谢谢!
不知道你用什么开发语言如果是直接导入数据库的话,我不知道了