oracle - 如何在批量收集循环中进行条件处理？

Question

我们有Oracle 11G，我正在尝试使用批量收集将数据从一个表移动到另一个表。问题是，当我尝试评估来源中的一个字段是否为空时，我的包无效了。我有什么:

声明:

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS
CURSOR CUR_MYDATA IS
        SELECT
            o.name,
            o.last_name,
            o.id,
            o.socnum
        FROM
            origin o
        WHERE
            1=1
            AND o.name like upper ('a%');

        TYPE t_name IS TABLE OF origin.name%TYPE;
        TYPE t_lastname IS TABLE OF origin.last_name%TYPE;
        TYPE t_id IS TABLE OF origin.id%TYPE;
        TYPE t_socnum IS TABLE OF origin.socnum%TYPE;

        l_name t_name;
        l_lastname t_lastname;
        l_id t_id;
        l_socnum t_socnum;

PROCEDURE MYPROCEDURE;

END MYPKG;

正文:

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    PROCEDURE MYPROCEDURE IS

    BEGIN
        OPEN CUR_MYDATA;
        LOOP
        FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
            forall i IN 1 .. l_name.COUNT
            IF ( l_socnum(i) IS NULL) 
                THEN (select oo.socnum from other_origin where oo.id=l_id(i)) 
            END IF;
                INSERT INTO destiny (
                    d_name,
                    d_lastname,
                    d_id,
                    d_socnum) 
                VALUES (
                    l_name(i),
                    l_lastname(i),
                    l_id(i),
                    l_socnum(i),
            EXIT WHEN l_name.count = 0;
        END LOOP;
    END MYPROCEDURE;

END MYPKG;

但是当我检查 body 状态时，它是INVALID

有什么想法吗？

Answer 1

FORALL 不是循环结构:它不能从 DML 语句中分离出来。

when I tried to evaluate if one field from origin is empty

您需要循环填充的集合并在执行 FORALL ... INSERT 之前修复该问题。

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    PROCEDURE MYPROCEDURE IS

    BEGIN
        OPEN CUR_MYDATA;
        LOOP
            FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
            EXIT WHEN l_name.count = 0;

            for idx in 1 .. l_socnum.count() loop
                IF l_socnum(idx) IS NULL THEN
                      select oo.socnum 
                      into l_socnum(idx)
                      from other_origin 
                      where oo.id = l_id(idx);
                END IF;

            end loop;

            forall i IN 1 .. l_name.COUNT
                INSERT INTO destiny (
                    d_name,
                    d_lastname,
                    d_id,
                    d_socnum) 
                VALUES (
                    l_name(i),
                    l_lastname(i),
                    l_id(i),
                    l_socnum(i));
        END LOOP;
    END MYPROCEDURE;

END MYPKG; 

其他注释。

1. 执行提取后立即检查提取是否返回任何记录。否则，您的代码将尝试在空集合上执行代码，这将会失败。
2. 您应该根据目标表%rowtype定义一个集合:这比根据列定义和处理多个集合更简单。

此外，您的实际代码可能比您在此处发布的代码复杂得多，但如果您有大量数据需要转移，那么使用纯 SQL 而不是过程会带来很多性能提升:

INSERT INTO DESTINY (
            D_NAME,
            D_LASTNAME,
            D_ID,
            D_SOCNUM
        ) 
SELECT
        o.name,
        o.last_name,
        o.id,
        coalesce(o.socnum, oo.socnum)
FROM
    origin o
left outer join other_origin oo 
   on oo.id = o.id
WHERE
    1=1
    AND o.name like upper ('a%');