sql - 在表中保存嵌套 XML 时出现 PL/SQL 错误

Question

我正在尝试将 XML 保存在表格中。 XML 的格式如下:

<employees>
  <employees_id> 1</employees_id>
  <employees_name>Teradata</employees_name>
     <department_id>100</department_id>
     <department_name>Techinical</department_name>
     <department_id>200</department_id>
     <department_name>Management</department_name>
     <department_id>300</department_id>
     <department_name>Telecom</department_name>
  </employees>
  <employees>
  <employees_id> 2</employees_id>
  <employees_name>AT&T</employees_name>
     <department_id>400</department_id>
     <department_name>Techinical</department_name>
     <department_id>500</department_id>
     <department_name>Management</department_name>
     <department_id>600</department_id>
     <department_name>Telecom</department_name>
  </employees>

我的表只有一个 id 列和 xmltype 列。

我使用的过程如下:

   DECLARE
   CTX         DBMS_XMLGEN.CTXHANDLE;
   LCLOB$XML_AS_CLOB  CLOB;
   LREC$EMPLOYEES_EMPNO NUMBER;
 BEGIN
  FOR LREC$EMPLOYEES_EMPNO IN (198, 197, 180)
   LOOP
    --Create context for XML
    CTX :=
      DBMS_XMLGEN.NEWCONTEXT (
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and 
        employees.employees_id =' || LREC$EMPLOYEES_EMPNO);
    --Get XML file of Context
     -- Set the row header to be EMPLOYEE
    DBMS_XMLGEN.setRowTag(CTX, 'EMPLOYEE');
    LCLOB$XML_AS_CLOB := DBMS_XMLGEN.GETXML (CTX);
    DBMS_XMLGEN.CLOSECONTEXT (CTX); 
    INSERT INTO HR.OFFLINE_XML (id, XML_FILE)
       VALUES ( LREC$EMPLOYEES_EMPNO, XMLTYPE (LCLOB$XML_AS_CLOB));
    END LOOP;
   COMMIT;
 END;

错误报告:

 ORA-06550: line 7, column 4:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   * & - + / at mod remainder rem .. <an exponent (**)> ||
   multiset year day
ORA-06550: line 18, column 16:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 19, column 23:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 20, column 16:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national charac
ORA-06550: line 21, column 5:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "b
ORA-06550: line 24, column 4:
PLS-00103: Encountered the symbol "COMMIT" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

正如 Noel 所说，您不能像那样向 for 循环提供固定值。 (你报告中的所有其他错误都是由此产生的链式 react ，尽管还有其他问题)。您可以提供一个范围，如 for i in 1..10 loop，但这对您没有帮助，即使对于您的示例数据也是如此。由于它仅用于测试，您现在可以使用虚拟光标:

    FOR LREC$EMPLOYEES_EMPNO IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP

(虽然我实际上更喜欢 DazzaL 的阵列，但我现在会坚持使用它)。您不必声明游标，您用该名称声明的 number 变量将被此版本取代。当您引用其中一个值时，它返回的值必须是名称，因此在本例中为 LREC$EMPLOYEES_EMPNO.employee_id。 Persoanlly 我会选择一个更短的名字 *8-)

正如 DazzaL 还提到的，您也不需要中间 CLOB。当您可以使用绑定(bind)变量代替时，似乎也没有必要在每次循环时创建和关闭上下文。

这有效并得到与您的版本相同的结果(适当修改)，但不是您建议您在问题中想要的输出:

DECLARE
    ctx DBMS_XMLGEN.CTXHANDLE;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT(
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and      
        employees.employee_id = :id');
    DBMS_XMLGEN.setRowTag(ctx, 'EMPLOYEE');
    FOR r IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP
        DBMS_XMLGEN.SETBINDVALUE(ctx, 'ID', r.employee_id);
        INSERT INTO OFFLINE_XML (id, xml_file)
        VALUES (r.employee_id, DBMS_XMLGEN.GETXMLTYPE(ctx));
    END LOOP;
    DBMS_XMLGEN.CLOSECONTEXT(ctx); 
END;
/

用以下内容填充表格:

        ID XML_FILE
---------- --------------------------------------------------
       198 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>198</EMPLOYEE_ID>
               <FIRST_NAME>Bob</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       197 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>197</EMPLOYEE_ID>
               <FIRST_NAME>Adam</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       180 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>180</EMPLOYEE_ID>
               <FIRST_NAME>Charlie</FIRST_NAME>
               <DEPARTMENT_ID>2</DEPARTMENT_ID>
               <DEPARTMENT_NAME>Second</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>