I have created the following Stored Procedure on Oracle using SQL Plus. This particular Stored Procedure will Add new orders to the Orders Table.
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. When you are using Input Parameters, it is necessary to specify their Data Types as well.
Please Notice that the first item OrderID is not listed as a Parameter in the Stored Procedure. Subsequently, in the Insert section of the Stored Procedure, the OrderIDSequence is listed in the place of the First Parameter. This is because the OrderID is not, in fact, a Parameter. This number will be automatically generated by the Server using the OrderIDSequence.
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 or Replace Procedure AddOrder(P_CustomerID IN NUMBER,
P_OrderDate IN Date, P_ShipDate IN Date,
P_PaidDate IN Date, P_Status IN Char) AS
BEGIN
Insert Into Orders
Values(OrderIDSequence.NextVal, P_CustomerID, P_OrderDate,
P_ShipDate, P_PaidDate, P_Status);
End AddOrder;