oracle - 游标循环内的多个 IF

Question

在这个问题之后，我无法IF在一个循环中使用不同的 s 语句进行评估，我的执行以ORA-01403: no data found

我有的：

宣言：

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS

FUNCTION MYFUNCTION ( description OUT VARCHAR2 ) RETURN INTEGER;

END MYPKG;

身体：

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS

    FUNCTION MYFUNCTION ( description OUT VARCHAR2 ) RETURN INTEGER 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;

    retcode INTEGER := 0;

    BEGIN
        description := 'OK';

        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 name in l_name.first .. l_name.last loop
                IF l_socnum(name) IS NULL THEN 
                    (select oo.socnum from other_origin where oo.id=l_id(name)) 
                END IF;

                IF length(l_lastname(name)) < 2 THEN
                    l_lastname(name) := 'default lastname';
                END IF;
            end loop;

            forall i IN l_name.first .. l_name.last    
                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;
            COMMIT;
            RETURN retcode;
     EXCEPTION
     WHEN OTHERS THEN
        description := SQLERRM;
        retcode := SQLCODE;
        CLOSE CUR_MYDATA;
        RETURN retcode;
    END MYFUNCTION;

END MYPKG;

但我得到了ORA-01403: no data found

我也试过：

for name in l_name.first .. l_name.last loop
    IF l_socnum(name) IS NULL THEN 
        (select oo.socnum 
        into l_socnum(name)
        from other_origin where oo.id=l_id(name))

        for ln in l_name.first .. l_name.last loop
            IF length(l_lastname(ln)) < 2 THEN
                l_lastname(ln) := 'default lastname';
            END IF;
        end loop;
    END IF;

end loop;

但是错误是一样的。

那么，当我通过光标评估不同的列多次循环时，您认为我错过了什么？

标签： oracleplsqloracle11gbulk-operations