如何在批量收集循环中进行条件处理? [英] How to do conditional processing in a bulk collect loop?

问题描述

我们有Oracle 11G，并且我正尝试使用bulk collect将数据从一个表移动到另一个表.问题是，当我尝试评估起源中的一个字段是否为空时，我的包装无效了.我所拥有的:

we have Oracle 11G and i'm trying to move data from one table to another using bulk collect. Problem is when I tried to evaluate if one field from origin is empty my package got invalidated. What I have:

声明:

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;

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

but when I check body status it is INVALID

有事吗?

推荐答案

FORALL不是循环结构:它不能与其DML语句分开.

FORALL is not a loop construct: it cannot be split from its DML statement.

当我尝试评估起源中的一个字段是否为空

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

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

You need to loop round the populated collection and fix that before executing the 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定义一个集合:这比基于列定义和处理多个集合要简单.

1. Check whether the fetch returns any records immediately after executing the fetch. Otherwise your code will attempt to execute code over an empty collection, which will fail.
2. You should define a collection based on the target table %rowtype: this is simpler than defining and handling multiple collections based on columns.

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

Also, your real code may be way more complicated than what you posted here, but if you have a large amount of data to shift there is a lot of performance gain in using pure SQL rather than a procedure:

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%');

这篇关于如何在批量收集循环中进行条件处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！