Basic PL/SQL Block Structure PL/SQL is structured into blocks and can use conditional statements, loops and branches to control program flow. Variables can be scoped so that they are only visible within the block where they are defined. PL/SQL blocks come in three types, these are anonymous procedure, named procedure and named function. All of these block types share most PL/SQL features so during this tutorial the features that apply to all block types will be grouped into single subjects. An anonymous procedure is an unnamed procedure, it can't be called. It is placed where it is to be run, normally attached to a database trigger or application event. A named procedure may be called, it may accept inbound parameters but won't explicitly return any value. A named function may also be called, it may accept inbound parameters and will always return a value. 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; End of block marker. Example :- DECLARE TEMP_COST NUMBER(10,2); BEGIN SELECT COST INTO TEMP_COST FROM JD11.BOOK 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; Page No 1
As you can see there are several elements in the example that haven't been covered in the SQL tutorial, these elements are the PL/SQL extensions. They include :- Variables and Constants These objects are used to store and manipulate block level data. They can be CHAR, VARCHAR2, NUMBER, DATE or BOOLEAN data types. SQL support All SQL statements are supported within PL/SQL blocks including transaction control statements. Composite Datatypes Records allow groups of fields to be defined and manipulated in PL/SQL blocks. Flow Control Ifs, Loops, GOTOs and labels provide conditional actions, tests, branching and iterative program control. Built In functions Most SQL data functions are supported within PL/SQL blocks. Cursor handling Cursors (a memory area holding a result set) can be explicitly defined and manipulated allowing the processing of multiple rows. A group of PL/SQL system attributes provide the ability to test a cursor's internal state. Exception handling Blocks have the ability to trap and handle local error conditions (implicit exceptions). You may also self generate explicit exceptions that deal with logic and data errors. Code storage Blocks may be stored within an Oracle database as procedures, functions, packages (a group of blocks) and triggers. The rules of block structure are :- 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.
- Summer '12
- Control flow, ISBN