oracle - 如何使用参数 '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;