It’s easiest to explain the basics of stored procedures through an example. Consider the process of adding a customer to the sample database. Here are the steps that may be involved:
- Obtain the customer number, name, credit limit, and target sales amount for the customer, as well as the assigned salesperson and office.
- Add a row to the customer table containing the customer’s data.
- Update the row for the assigned salesperson, raising the quota target by the specified amount.
- Update the row for the office, raising the sales target by the specified amount.
- Commit the changes to the database, if all were successful.
Without a stored procedure capability, here is a SQL statement sequence that does this work for XYZ Corporation, new customer number 2137, with a credit limit of $30,000 and first-year target sales of $50,000 to be assigned to Paul Cruz (employee #103) of the Chicago office:
INSERT INTO CUSTOMERS (CUST_NUM, COMPANY, CUST_REP, CREDIT_LIMIT)
VALUES (2137, ‘XYZ Corporation’, 103, 30000.00);
UPDATE SALESREPS
SET QUOTA = QUOTA + 50000.00
WHERE EMPL_NUM = 103;
UPDATE OFFICES
SET TARGET = TARGET + 50000.00
WHERE CITY = ‘Chicago’;
COMMIT;
With a stored procedure, all of this work can be embedded into a single defined SQL routine. Figure 20-1 shows a stored procedure for this task, expressed in Oracle’s PL/SQL stored procedure dialect. The procedure is named ADD_CUST, and it accepts six parameters—the customer name, number, credit limit, and target sales, the employee number of the assigned salesperson, and the city where the assigned sales office is located.
Once this procedure has been created in the database, a statement like this one:
ADD_CUST(‘XYZ Corporation’, 2137, 30000.00, 50000.00, 103,’Chicago’)
calls the stored procedure and passes it the six specified values as its parameters. The DBMS executes the stored procedure, carrying out each SQL statement in the procedure definition one by one. If the ADD_CUST procedure completes its execution successfully, a committed transaction has been carried out within the DBMS. If not, the returned error code and message indicates what went wrong.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.