Oracle Procedures

PL/SQL has many endearing features for the programmer and one or two which are less so. One of its nicer features is the ability to define procedures which can then be used as if they were a part of the language. Procedures may define and use variables which are local to that procedure. An extension to this is the concept of packages: bundles of procedures which can be used as complete libraries and slotted in as 'black boxes' wherever they're required. Packages may define variables which are local to the package but global to the procedures contained by the package.

Procedures.

A procedure consists of a name (with optional parameters), IS, BEGIN, any number of statements and END. Here's an example...

PROCEDURE GetSalesList(Customer IN VARCHAR2, Process in VARCHAR2)
IS
BEGIN
   IF Process = 'PURGE'
   THEN
      DELETE FROM SalesFeed
       WHERE CustId in (SELECT distinct Id
                          FROM Customers
                         WHERE Name = Customer);
   ELSE
      INSERT INTO Invoice (CustomerId, TranDate, Value)
      SELECT CustId,
             FeedDate,
             Amount
        FROM SalesFeed
       WHERE CustId in (SELECT distinct Id
                          FROM Customers
                         WHERE Name = Customer);
  END IF;
END;

This gives a hint at how large and complex procedures may be. Once installed in a package, procedures may be called by any query which has access to the package, making this a very useful facility.