Next: Pro*C: The Oracle SQL Up: Oracle Programming Previous: SQL Contents

PL/SQL

PL/SQL is a basic scripting language for Oracle dialect of SQL (based around SQL99). Along with all the typical SQL-eque things you can do there is added a helpful dose of basic logic handling and data constructs to let you do all the normal things you'd expect. It's very similar to ADA in its design. A PL/SQL script contains 1 to 3 sections. At the least you need a BEGIN section, and optionally DECLARE and EXCEPTION sections. Variables use standard SQL datatypes (a string might be VARCHAR2). In the DECLARE section you can declare variable or setup procedures. In the BEGIN section you you put the real meat of your script, this is where the proccessing is done. The EXCEPTION section is for handling exceptions (errors). Every script ends with "END" signalling the end of execution. In addition to these sections, you can subclassify scripts as a procedure or a functions. This allows for re-usablity, used in associate with packages, etc. Here is an example:

-- PL/SQL Example
DECLARE
   acct_balance NUMBER(11,2);
   acct         CONSTANT NUMBER(4) := 3;
   debit_amt    CONSTANT NUMBER(5,2) := 500.00;
BEGIN
   SELECT bal INTO acct_balance FROM accounts
      WHERE account_id = acct
      FOR UPDATE OF bal;
   IF acct_balance >= debit_amt THEN
      UPDATE accounts SET bal = bal - debit_amt
         WHERE account_id = acct;
   ELSE
      INSERT INTO temp VALUES
         (acct, acct_balance, 'Insufficient funds');
            -- insert account, current balance, and message
   END IF;
   COMMIT;
END;

You can see that in the DECLARE section we initialize 3 variables, and then in BEGIN we process some SQL using PL/SQL provided logic. PL/SQL code is executed just like SQL statements in a file, as seen in the last section. You'll notice that variables are assigned values with ":=". In fact, if you haven't looked at any ADA lately, I'll toss in an ADA example, look a little familiar?

-- ADA95 Example (Not Oracle)
procedure OneInt is
   Index : INTEGER; -- A simple Integer type
begin
   Index := 23;
   Put("The value of Index is");
   Put(Index);   -- The default field width is 11 columns
   New_Line;
   Index := Index + 12;
   Put("The value of Index is");
   Put(Index, 8);
   New_Line;
end OneInt;
-- Result of execution
-- The value of Index is         23
-- The value of Index is      35

PL/SQL itself is a big subject, and we won't bother to touch it here. Damn near everything an SA could want to do can be done in a plain ol' SQL file. Any SA would likely rather use PERL than PL/SQL, but there are piles and piles of books devoted to PL/SQL out there. Find more PL/SQL information in the PL/SQL User's Guide and Reference: http://download-west.oracle.com/docs/cd/B12037_01/appdev.101/b10807/toc.htm

Next: Pro*C: The Oracle SQL Up: Oracle Programming Previous: SQL Contents

2005-02-10