Sams Teach Yourself SQL in 10 Minutes, Second Edition

Creating Stored Procedures

As already explained, writing a stored procedure is not trivial. To give you a taste for what is involved, let's look at a simple example—a stored procedure that counts the number of customers in a mailing list who have e-mail addresses.

Here is the Oracle version:

CREATE PROCEDURE MailingListCount
(ListCount OUT NUMBER)
IS
BEGIN
    SELECT * FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := SQL%ROWCOUNT;
END;

This stored procedure takes a single parameter named ListCount. Instead of passing a value to the stored procedure, this parameter passes a value back from it. The keyword OUT is used to specify this behavior. ...