What is PL/SQL:
PL/SQL stands for Procedural Language extension of SQL. It was developed by Oracle Corporation in the late 1980s to enhance the capabilities of SQL. It is the procedural extension language for SQL.
PL/SQL block structure:
DECLARE Declaration statements; BEGIN Execution statements; EXCEPTION Exception handling statements; END; / |
PL/SQL Block sections:
1. Declaration section (optional).
2. Execution section (mandatory).
3. Exception handling section (optional).
Declaration section:
It is an optional section and starts with the DECLARE keyword. It is used to declare the variables, constants, records cursors, etc.
Execution section:
The execution section starts with the BEGIN keyword and ends with the END keyword. It is a mandatory section. It is used to write the program logic code.
Note: The execution section must have one statement.
Exception handling section:
The execution section starts with the EXCEPTION keyword. It is an optional section. It is used to handle the exceptions that occur in the execution section.
Important points:
1. Every PL/SQL statement will be followed by a semicolon (;).
2. PL/SQL blocks can be nested.
Advantages of PL/SQL:
1. PL/SQL is a procedural language.
2. PL/SQL is a block structure language.
3. PL/SQL handles the exceptions.
4. PL/SQL engine can process multiple SQL statements simultaneously as a single block hence reducing network traffic and providing better performance.
PLSQL tutorial:
Oracle PLSQL interview questions: