Basic PL/SQL Block Structure

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE

Definition of any variables or objects that are used within the declared block.

BEGIN

Statements that make up the block.

EXCEPTION

All exception handlers.

END;

example:
DECLARE
TEMP_COST NUMBER(10,2);
BEGIN
SELECT COST FROM JD11.BOOK INTO TEMP_COST WHERE ISBN = 21;
IF TEMP_COST > 0 THEN
UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21;
ELSE
UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND');
END;

As you can see there are several elements in the example that haven't been covered in SQL , these elements are the PL/SQL extensions. They include:
- Variables and Constants
- SQL Support
- Composite Datatypes
- Flow Control
- Built In Functions
- Cursor Handling
- Exception Handling
- Code Storage

The rules of block structure are :

1. Every unit of PL/SQL must constitute a block. As a minimum there must be the delimiting words BEGIN and END around the executable statements.
2. SELECT statements within PL/SQL blocks are embedded SQL (an ANSI category). As such they must return one row only. SELECT statements that return no rows or more than one row will generate an error. If you want to deal with groups of rows you must place the returned data into a cursor. The INTO clause is mandatory for SELECT statements within PL/SQL blocks (which are not within a cursor definition), you must store the returned values from a SELECT.
3. If PL/SQL variables or objects are defined for use in a block then you must also have a DECLARE section.
4. If you include an EXCEPTION section the statements within it are only processed if the condition to which they refer occurs. Block execution is terminated after an exception handling routine is executed.
5. PL/SQL blocks may be nested, nesting can occur wherever an executable statement could be placed (including the EXCEPTION section).