Using Input/output Parameters
Go Up to Understanding Stored Procedure Parameters
Input/output parameters serve both function that input and output parameters serve individually. Applications use an input/output parameter to pass a singleton data value to a stored procedure, which in turn reuses the input/output parameter to pass a singleton data value to the calling application. As with input parameters, the input value for an input/output parameter must be set before the using stored procedure or query component is activated. Likewise, the output value in an input/output parameter will not be available until after the stored procedure has been executed.
In the example Oracle stored procedure below, the parameter IN_OUTVAR is an input/output parameter.
CREATE OR REPLACE PROCEDURE UPDATE_THE_TABLE (IN_OUTVAR IN OUT INTEGER)
AS
BEGIN
UPDATE ALLTYPETABLE
SET NUMBER82FLD = IN_OUTVAR
WHERE KEYFIELD = 0;
IN_OUTVAR:=1;
END UPDATE_THE_TABLE;
In the Delphi program code below, IN_OUTVAR
is assigned an input value, the stored procedure executed, and then the output value in IN_OUTVAR
is inspected and stored to a memory variable.
with StoredProc1 do begin ParamByName(‘IN_OUTVAR’).AsInteger := 103; ExecProc; IntegerVar := ParamByName(‘IN_OUTVAR’).AsInteger; end;