PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks.
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
- Declarative : Contains all the variables, constants, cursors and user-defined exceptions that are referenced in the executable and declarative section. This is an OPTIONAL section in a PL/SQL Block.
- Executable : Contains SQL statements to manipulate data in the database and PL?SQL statements to manipulate data in the block. This is a MANDATORY section.
- Exception Handling : Specifies the actions to perform when errors and abnormal conditions arise in the executable section. This is an OPTIONAL section in a PL/SQL Block.
- SQL statements
- PL/SQL statements
Actions to perform when errors occur
A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures and functions, also known as subprograms, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code.
Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. You can declare them either as procedures or as functions. Generally use a procedure to perform an action and a function to compute a value.
You can store subprograms at the server or application level. Using Oracle Developer components (Forms, Reports, and Graphics), you can declare procedures and functions as part of the application (a form or report) and call them from other procedures, functions, and triggers (see next page) within the same application whenever necessary.
Note: A function is similar to a procedure, except that a function must return a value.
Anonymous blocks are unnamed blocks. They are declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at run time. You can embed an anonymous block within a precompiler program and within iSQL*Plus or Server Manager. Triggers in Oracle Developer components consist of such blocks.
Procedure/Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. You can declare them either as procedures or as functions. Generally use a procedure to perform an action and a function to compute a value.
A function is similar to a procedure, except that a function must return a value.
Awaiting for Administrator approval