Oracle 使用获取游标批量收集限制的递归函数

我正在从复杂的XML文档中提取数据到数据库中。我处理的方式是编写处理元素列表的函数。因此，初始过程打开整个XMl文档，我使用游标从XMLTABLE中选择数据。重复的子元素的数据作为包含这些元素列表的XMLTYPE片段返回。然后，我调用传递这些XMLTYPE片段的其他函数，具体取决于它们是什么元素，它们在哪里以类似的方式处理。

现在，我有一种情况，我有这种XML:

...
<入口关系>
...
<观察>
...
<入口关系>
...
<观察>

所以每个函数看起来和工作相同。

      
   FUNCTION insert_entryRelationships (p_msg_id          IN NUMBER,
                                       p_parent_entity   IN VARCHAR2,
                                       p_parent_eid      IN NUMBER,
                                       p_xml             IN XMLTYPE,
                                       p_text_xml        IN XMLTYPE)
      RETURN NUMBER
   IS
      TYPE extract_rt IS RECORD
      (
         EID                     NUMBER,
         MSG_ID                  NUMBER,
         PARENT_ENTITY           VARCHAR2 (1024),
         PARENT_EID              NUMBER,
         PARENT_SEQ              NUMBER,
         TYPE_CODE               VARCHAR2 (16),
         observationElements     XMLTYPE
      );

         CURSOR cs_extract (
         msg_id           NUMBER,
         parent_entity    VARCHAR,
         parent_eid       NUMBER,      
         erElements       XMLTYPE)
      IS
         SELECT NULL AS "EID",
                msg_id AS "MSG_ID",
                parent_entity AS "PARENT_ENTITY",
                parent_eid AS "PARENT_EID",
                er.entity_seq as "PARENT_SEQ",
                er.typeCode as "TYPE_CODE",
                er.observationElements as "observationElements" 
           FROM XMLTABLE (
                   xmlnamespaces (
                      DEFAULT 'urn:hl7-org:v3',
                      'urn:hl7-org:sdtc' AS "sdtc",
                      'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                   '/entryRelationship'
                   PASSING erElements
                   COLUMNS entity_seq            FOR ORDINALITY,
                           typeCode              VARCHAR2 (16) PATH '@typeCode',
                           observationElements   XMLTYPE PATH 'observation') er;

      TYPE tempTable IS TABLE OF extract_rt;

      TYPE tempEIDTable IS TABLE OF DW_GENESIS.E_ENTRY_RELATIONSHIP.EID%TYPE;

      v_table              tempTable;
      v_eid_table          tempEIDTable;
      v_records_inserted   NUMBER := 0;
      
   BEGIN
      IF (p_xml IS NULL)
      THEN
         DBMS_OUTPUT.put_line (
            'no entryRelationship element xml to process, returning 0');
      ELSE
         OPEN cs_extract (p_msg_id,
                          p_parent_entity,
                          p_parent_eid,
                          p_xml);

         LOOP
            FETCH cs_extract BULK COLLECT INTO v_table LIMIT 40;
            FORALL i IN 1 .. v_table.COUNT
               INSERT INTO DW_GENESIS.E_ENTRY_RELATIONSHIP
                    VALUES (v_table (i).EID,
                            v_table (i).MSG_ID,
                            v_table (i).PARENT_ENTITY,
                            v_table (i).PARENT_EID,
                            v_table (i).PARENT_SEQ,
                            v_table (i).TYPE_CODE)
                 RETURNING eid
                      BULK COLLECT INTO v_eid_table;

            FOR i IN 1 .. v_table.COUNT
            LOOP
             v_records_inserted :=
                 v_records_inserted
               + insert_observations (p_msg_id,
                                      'E_ENTRY_RELATIONSHIP',
                                      v_eid_table(i),
                                      v_table(i).observationElements,
                                      p_text_xml);

            END LOOP;

            EXIT WHEN cs_extract%NOTFOUND;
         END LOOP;
         CLOSE cs_extract;

      END IF;
      RETURN v_records_inserted;
   END;
复制

它似乎是，当函数被调用的entryrection，它然后调用的观察元素的函数，递归到的entryrection的函数，但当它弹出到entryrection函数的初始调用，我得到的错误:

[错误] ORA-20100: ORA-01002: fetch out of sequence

我认为这是因为cs_extract游标在递归调用中已关闭，因此当循环时它会失败...结束循环尝试获取下一个块。

那么，游标被排除在递归堆栈之外？

不，游标是私有的 (因此包含在递归堆栈中)，例如

SQL> create table t as select rownum*10 x from dual
  2  connect by level <= 10 ;

Table created.

SQL>
SQL> create or replace
  2  procedure recurs(p_x int) is
  3    type nlist is table of number index by pls_integer;
  4    n nlist;
  5
  6    cursor C is select * from t where p_x <= 3;
  7  begin
  8    dbms_output.put_line('Opening at '||p_x);
  9    open c;
 10    dbms_output.put_line('Fetching at '||p_x||', starting row '||c%rowcount);
 11    fetch c bulk collect into n;
 12
 13    for i in 1 .. n.count loop
 14      dbms_output.put_line('Level '||p_x||': '||n(i));
 15    end loop;
 16
 17    if n.count > 0 then
 18       recurs(p_x+1);
 19    end if;
 20    dbms_output.put_line('Closing at '||p_x);
 21    close c;
 22  end;
 23  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec recurs(1)
Opening at 1
Fetching at 1, starting row 0
Level 1: 10
Level 1: 20
Level 1: 30
Level 1: 40
Level 1: 50
Level 1: 60
Level 1: 70
Level 1: 80
Level 1: 90
Level 1: 100
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Closing at 3
Closing at 2
Closing at 1

PL/SQL procedure successfully completed.
复制

同样，如果我们将其设置为 “部分提取” 循环，那么我们仍然可以

SQL> create table t as select rownum*10 x from dual
  2  connect by level <= 10 ;

Table created.

SQL>
SQL> create or replace
  2  procedure recurs(p_x int) is
  3    type nlist is table of number index by pls_integer;
  4    n nlist;
  5
  6    cursor C is select * from t where p_x <= 3;
  7  begin
  8    dbms_output.put_line('Opening at '||p_x);
  9    open c;
 10    loop
 11      dbms_output.put_line('Fetching at '||p_x||', starting row '||c%rowcount);
 12      fetch c bulk collect into n limit 5;
 13
 14      for i in 1 .. n.count loop
 15        dbms_output.put_line('Level '||p_x||': '||n(i));
 16      end loop;
 17
 18      if n.count > 0 then
 19         recurs(p_x+1);
 20      end if;
 21      exit when c%notfound;
 22    end loop;
 23    dbms_output.put_line('Closing at '||p_x);
 24    close c;
 25  end;
 26  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec recurs(1)
Opening at 1
Fetching at 1, starting row 0
Level 1: 10
Level 1: 20
Level 1: 30
Level 1: 40
Level 1: 50
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 5
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 10
Closing at 2
Fetching at 1, starting row 5
Level 1: 60
Level 1: 70
Level 1: 80
Level 1: 90
Level 1: 100
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 5
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 10
Closing at 2
Fetching at 1, starting row 10
Closing at 1

PL/SQL procedure successfully completed.
复制