如何在Oracle关联数组中使用%TYPE?

您可以定义自己的类型来处理 varchar2 使用您的类型,无需重复大小。 例如:

CREATE OR REPLACE PACKAGE testpck AS
    SUBTYPE myType IS VARCHAR2(4096);     /* a type for the values */
    SUBTYPE myIndexType is VARCHAR2(512); /* a type for the index  */
    TYPE ParamArray IS TABLE OF myType
        INDEX BY myIndexType;
    PROCEDURE IterateArray(Params ParamArray);
END;

CREATE OR REPLACE PACKAGE BODY testpck AS
    PROCEDURE IterateArray(Params ParamArray) AS
        v_ParamName     myType; 
        v_index         myIndexType;
    BEGIN
        v_index    := Params.FIRST;

        WHILE v_index IS NOT NULL
        LOOP
            -- do something with the array entry
            v_ParamName := Params(v_index);
            dbms_output.put_line('value of ' || v_index || ' is ' || v_ParamName);
            v_index     := Params.NEXT(v_index);             
        END LOOP;
    END;
END;

电话:

declare
    myArray         testpck.ParamArray;
    myValue         testpck.myType;
    myIndex         testpck.myIndexType;     
begin    
    myIndex := 'ONE';
    myValue := 'VALUE OF ONE';
    myArray(myIndex) := myValue;     
    --
    myIndex := 'TWO';
    myValue := 'VALUE OF TWO';
    myArray(myIndex) := myValue;
    --
    testpck.IterateArray(myArray);
end;