Original reference: http://plsql-tutorial.com/PL/SQL BlockConsists of three sections:
- The Declaration section (optional).
- The execution section (mandatory).
- The Exception handling (or Error) section (optional).
composition :
- Declarations section (optional)
- Executive section (required)
- Exception Handling (optional)
SoundMing: DECLARE
Execution:BEGIN ... END
Exception Handling:EXCEPTION Each statement must be ";" end; PL/SQL block can be nested;/represents the execution of PL/SQL blocks; These is the advantages of PL/SQL
- Block Structures: PL SQL consists of blocks of code, which can be nested within all other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can is stored in the database and reused.
- Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (for loops).
- Better Performance : PL SQL engine processes multiple SQL statements simultaneously as a single block thereby reducing network traffic.
- Error handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception are caught, specific actions can be taken depending upon the type of the exception or it can be displayed To the user with a message.
Advantages: A block structure can be nested, a task or a collection of logical modules, can be stored in the database reuse supporting process-enabled language contains such as conditional judgment statements and loops and other procedural statements structure of high-performance multi-SQL statement execution, reducing network traffic exception handling When an exception occurs, you can specify whether to perform a specific action or return exception information
PL/SQL variables and Constantsvariables
format:
Example:
Dept VARCHAR2 (Ten) not NULL: = "HR dept";
Two ways to specify the value of a variable:
1) variable_name:= value;
2)
SELECT column_name
Into variable_name
From table_name
[WHERE condition];
Variable range:
- Local variables-defined in nested blocks, cannot be accessed by external blocks
- Global variables-defined in an external block, can be used for its own use, or it can be used by nested blocks inside it.
Constants
format:
Example:
PL/SQL Learning (I.)
