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: