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. As with C, Perl and similar languages, each statement is terminated with a ';' (semi-colon). Here's a simple example...
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;
Thre's effectively no limit to 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.
:
