問題描述
如何使用參數“foo 表”執行 SP？ (How to execute SP with arguments 'table of foo'?)
Using sqlplus how can I execute a stored procedure which has arguments which are 'table of foo' ?
So for instance in the following package how can I execute '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;
If the procedure was in a package 'BAR' and looked like this ...
PROCEDURE FOO (ID IN NUMBER);
... then I know I could execute it like this :
declare
r number;
begin
r := BAR.FOO (1);
end;
but how can I extend that idea to accomomdate the OUT arguments which are 'table of' ?
參考解法
方法 1:
By declaring the variables using the package.type syntax:
DECLARE
outTBL PKG_DROM.tblCostCenereIdentifier;
outAutoTBL PKG_DROM.tblCCEAutoID;
BEGIN
PKG_DROM.GET_AnnotationsForEmp(id, outTBL, outAutoTBLZ);
END;
(by glaucon、Thomas Jones-Low)