I have created the following Stored Procedure on Oracle using SQL Plus to demonstrate using an Output Parameter. This particular Stored Procedure will add a new record to the Orders Table using three INput parameters, one of which is the Order Amount, and return an OUTput parameter of the Order Amount divided in half.
PLEASE NOTE THE P_'s WHICH PRECEED EACH FIELD:
It is necessary to distinguish each field which will have data inserted as Parameters to Oracle when creating the Stored Procedure. Therefore, I have used the P_ convention.
Also, the IN specifies that these are INput Parameters vs OUTput. When you are using Input Parameters, it is necessary to specify their Data Types as well.
NOTE: To execute the creation of the Stored Procedure, in addition to the Statement Terminator (;), a forward slash (/) is required to execute:
NOTE: For purposes of clarity, I have listed the items on individual lines. However, it is not necessary to use this convention in SQL Plus/Oracle. You may have one long statement; however, this format is much more readable.
CREATE PROCEDURE OPmdk(P_ORDERID IN NUMBER,
P_ORDERAMT IN VARCHAR2,
P_ORDERNAME IN VARCHAR2,
O_HALFORDERAMT OUT VARCHAR2)
AS
BEGIN
INSERT INTO MDKORDERS
VALUES
(P_ORDERID,
P_ORDERAMT,
P_ORDERNAME);
O_HALFORDERAMT := P_ORDERAMT/2;
End OPmdk;