How to do conditional processing in a 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:

Declaration:

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;

Body:

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;

but when I check body status it is INVALID

any thoughs?

回答1:

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

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; 

Other notes.

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.

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

回答2:

• IF condition is not allowed inside FOR ALL.
• FOR ALL can execute a single DML: INSERT, UPDATE, or DELETE statement which is written following it. It is not normal for loop.

You can try the following code:

Package:

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS
    CURSOR CUR_MYDATA IS
    SELECT
        O.NAME,
        O.LAST_NAME,
        O.ID,
        -- ADDED THIS CASE STATEMENT
        CASE
            WHEN O.SOCNUM IS NOT NULL THEN O.SOCNUM
            ELSE OO.SOCNUM
        END AS SOCNUM
    FROM
        -- ADDED THIS LEF JOIN
        ORIGIN O
        LEFT JOIN OTHER_ORIGIN OO ON ( OO.ID = O.ID )
    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;

Package Body

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    PROCEDURE MYPROCEDURE IS
    BEGIN
        OPEN CUR_MYDATA;
        FETCH CUR_MYDATA BULK COLLECT INTO
            L_NAME,
            L_LASTNAME,
            L_ID,
            L_SOCNUM
        LIMIT 1000;
        FORALL I IN 1..L_NAME.COUNT
--
-- REMOVED THIS CONDITION
--
--            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)
            );

        CLOSE CUR_MYDATA;
    END MYPROCEDURE;

END MYPKG;