I have created the following Stored Procedure on Oracle using SQL Plus. This particular Stored Procedure will Delete a Customer from the Customer Table.
PLEASE NOTE:
In the Stored Procedure(s) created for Adding New Records, it was necessary to preceed each field having data inserted with a P_ to identify them as Parameters to Oracle when creating the Stored Procedure.
In the Stored Procedures created for the Deletion of Records, this is not necessary. However, In order for Oracle to recognize the proper Record to be deleted, a Variable must be provided in the Stored Procedure. Therefore, the variable name (MyCustomerID) is used in the Stored Procedure:
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 OR REPLACE PROCEDURE DELETECUSTOMER(MyCustomerID IN NUMBER) IS
BEGIN
Delete From Customer Where CustomerID = MyCustomerID;
End DeleteCustomer;
I have also created an Oracle Trigger which will store the Customer Last Name, First Name and Customer ID in a Customer History Table when a Delete Customer is Performed on the Customer Table.