如何使用参数“table of foo”执行 SP？

Question

使用 sqlplus 如何执行具有“table of foo”参数的存储过程？

例如，在下面的包中，我如何执行 'Get_AnnotationsForEmp' ？

create or replace PACKAGE "PKG_DROM"   as
    TYPE tblCostCentreIdentifier    IS TABLE OF BLA_COST_CENTRE.CCE_IDENTIFIER%TYPE INDEX BY BINARY_INTEGER;
    TYPE tblCCEAutoID       IS TABLE OF BLA_COST_CENTRE.CCE_AUTOID%TYPE INDEX BY BINARY_INTEGER;

    --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    --
    --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    PROCEDURE Get_AnnotationsForEmp(
        EMP_EMPLOYEE_ID_IN      IN BLA_EMPLOYEE.EMP_EMPLOYEE_ID%TYPE,
        CCE_IDENTIFIER_OUT  OUT tblCostCentreIdentifier,
        CCE_AUTOID_OUT      OUT tblCCEAutoID);

    PROCEDURE Get_PastAnnotationsForEmp(
        EMP_EMPLOYEE_ID_IN      IN BLA_EMPLOYEE.EMP_EMPLOYEE_ID%TYPE,
        CCE_IDENTIFIER_OUT  OUT tblCostCentreIdentifier,
        CCE_AUTOID_OUT      OUT tblCCEAutoID);
END PKG_DROM;

如果该过程位于包“BAR”中并且看起来像这样……

PROCEDURE FOO (ID IN NUMBER);

那么我知道我可以像这样执行它：

declare
    r number;
begin
    r := BAR.FOO (1);
end;

但是我如何扩展这个想法以适应“表”的 OUT 参数？

Answer 1

通过使用 package.type 语法声明变量：

DECLARE
  outTBL     PKG_DROM.tblCostCenereIdentifier;
  outAutoTBL PKG_DROM.tblCCEAutoID;
BEGIN
  PKG_DROM.GET_AnnotationsForEmp(id, outTBL, outAutoTBLZ);
END;