I have created the following Stored Procedure on Oracle using SQL Plus. This particular Stored Procedure will Edit existing records in the Customer Table.
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.
As with the Deletions, in the Stored Procedures created for the Editing of Records, this is not necessary. However, In order for Oracle to recognize the proper Record to be deleted, I have provided a Variable for each field in the Stored Procedure. Each variable is preceded with My to distinguish them as Variables 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 EditCustomer (MyCustomerID in NUMBER,MyCompanyName in VARCHAR2,
MyLastName in VARCHAR2,
MyFirstName in VARCHAR2,
MyAddress in VARCHAR2,
MyCity in VARCHAR2,
MyZipCode in VARCHAR2,
MyPhone in VARCHAR2,
MyFax in VARCHAR2)
IS
BEGIN
UPDATE Customer
Set CustomerID = MyCustomerID,
CompanyName = MyCompanyName,
FirstName = MyFirstName,
LastName = MyLastName,
Address = MyAddress,
City = MyCity,
ZipCode = MyZipCode,
Phone = MyPhone,
Fax = MyFax
WHERE CustomerID = MyCustomerID;
End EditCustomer;