解析一个大的XML，然后使用forall插入

Question

我正在尝试解析一个大的XML，然后使用FORALL语句将其插入到表中。

我创建了下面的表:

create table users(EMPNO NUMBER(4),SAL NUMBER(4),HIREDATE DATE);

这是我的代码

declare
TYPE import_q_rec IS RECORD (
      EMPN     dbms_sql.NUMBER_TABLE,
      SALN     dbms_sql.NUMBER_TABLE,
      HIREDATE dbms_sql.Date_Table  );
l_import_q_tab import_q_rec;
l_row_index    number := 0;
l_temp_nd      XMLTYPE;
l_result_xml   XMLTYPE:=XmlType('<ROWSET>
          <ROW>
         <EMPNO>2290</EMPNO>
         <SAL>2000</SAL>
         <HIREDATE>31-DEC-1992</HIREDATE>
       </ROW>
     </ROWSET>');
begin
   WHILE l_result_xml.Existsnode('/ROWSET/Row[' || To_Char(l_row_index) || ']') > 0
   LOOP
     l_temp_nd :=l_result_xml.Extract('/ROWSET/Row[' || To_Char(l_row_index) || ']');
     l_import_q_tab.EMPN(l_row_index) := (l_temp_nd, '/Row/EMPNO/text()', NULL);
     l_import_q_tab.SALN(l_row_index) :=  (l_temp_nd, '/Row/SAL/text()', NULL);
     l_import_q_tab.HIREDATE(l_row_index) := (l_temp_nd, '/Row/HIREDATE/text()', NULL);
     l_row_index := l_row_index + 1;
   END LOOP;
   FORALL i IN 1 .. l_row_index - 1
     INSERT INTO users
     VALUES (l_import_q_tab.EMPN(i), l_import_q_tab.SALN(i), l_import_q_tab.HIREDATE);
end;

显示如下错误

ORA-06550: line 21, column 38:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 21, column 2:
PL/SQL: Statement ignored
ORA-06550: line 22, column 39:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 22, column 2:
PL/SQL: Statement ignored
ORA-06550: line 23, column 42:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 23, column 2:
PL/SQL: Statement ignored
ORA-06550: line 30, column 51:
PLS-00382: expression is of wrong type

Answer 1

你有几个语法错误

1. l_row_index Number:=0应该从1开始
2. (l_temp_nd, '/Row/EMPNO/text()', NULL);没有意义。你必须从这里的xmltype extract。
3. 最后一列的FORALL上缺少索引。
4. 日期字段需要to_date。

基本修复:

SQL> create table users(EMPNO NUMBER(4),SAL NUMBER(4),HIREDATE DATE);
Table created.
SQL> Declare
  2    TYPE import_q_rec IS RECORD
  3                             (EMPN          dbms_sql.NUMBER_TABLE,
  4                             SALN           dbms_sql.NUMBER_TABLE,
  5                             HIREDATE       dbms_sql.Date_Table
  6                              );
  7    l_import_q_tab import_q_rec;
  8    l_row_index      Number:=1;
  9    l_temp_nd        XMLTYPE;
 10    l_result_xml         XMLTYPE:=XmlType('<ROWSET>
 11                                              <ROW>
 12                                             <EMPNO>2290</EMPNO>
 13                                             <SAL>2000</SAL>
 14                                             <HIREDATE>31-DEC-1992</HIREDATE>
 15                                           </ROW>
 16                                              <ROW>
 17                                             <EMPNO>111</EMPNO>
 18                                             <SAL>123</SAL>
 19                                             <HIREDATE>30-NOV-1992</HIREDATE>
 20                                           </ROW>
 21                                         </ROWSET>');
 22  begin
 23    WHILE l_result_xml.Existsnode('/ROWSET/ROW[' || To_Char(l_row_index) || ']') > 0
 24    LOOP
 25      l_temp_nd :=l_result_xml.Extract('/ROWSET/ROW[' || To_Char(l_row_index) || ']');
 26      l_import_q_tab.EMPN(l_row_index) := l_temp_nd.extract('/ROW/EMPNO/text()').getstringval();
 27      l_import_q_tab.SALN(l_row_index) :=  l_temp_nd.extract('/ROW/SAL/text()').getstringval();
 28      l_import_q_tab.HIREDATE(l_row_index) := to_date(l_temp_nd.extract('/ROW/HIREDATE/text()').getstringval(), 'dd-mon-yyyy');
 29      l_row_index := l_row_index + 1;
 30    END LOOP;
 31
 32  FORALL i IN 1..l_row_index-1
 33      INSERT INTO users
 34    VALUES
 35    (l_import_q_tab.EMPN(i), l_import_q_tab.SALN(i),l_import_q_tab.HIREDATE(i));
 36  End;
 37  /
PL/SQL procedure successfully completed.
SQL> select * from users;
     EMPNO        SAL HIREDATE
---------- ---------- ---------
      2290       2000 31-DEC-92
       111        123 30-NOV-92

简化重写以避免所有数组+批量处理:

SQL> declare
  2    l_result_xml         XMLTYPE:=XmlType('<ROWSET>
  3                                              <ROW>
  4                                             <EMPNO>2290</EMPNO>
  5                                             <SAL>2000</SAL>
  6                                             <HIREDATE>31-DEC-1992</HIREDATE>
  7                                           </ROW>
  8                                              <ROW>
  9                                             <EMPNO>111</EMPNO>
 10                                             <SAL>123</SAL>
 11                                             <HIREDATE>30-NOV-1992</HIREDATE>
 12                                           </ROW>
 13                                         </ROWSET>');
 14  begin
 15
 16    insert into users
 17    select /*+ cursor_sharing_exact */
 18           empno, sal, to_date(hiredate, 'dd-mon-yyyy')
 19      from xmltable('/ROWSET/ROW' passing l_result_xml
 20                    columns
 21                      empno number path 'EMPNO',
 22                      sal number path 'SAL',
 23                      hiredate varchar2(20) path 'HIREDATE'
 24                   );
 25
 26  end;
 27  /
PL/SQL procedure successfully completed.
SQL> select * from users;
     EMPNO        SAL HIREDATE
---------- ---------- ---------
      2290       2000 31-DEC-92
       111        123 30-NOV-92