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 DECLARE keyword. It is used to declare the variables, constants, records and cursors etc.
Execution section:
Execution section starts with BEGIN keyword and ends with END keyword. It is a mandatory section. It is used to write the program logic code.
Note: Execution section must have one statement.
Exception handling section:
Execution section starts with EXCEPTION keyword. It is an optional section. It is used to handle the exceptions occurred in execution section.
Important points:
1. Every PL/SQL statement will be followed by 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 the multiple SQL statements simultaneously as a single block hence reduce network traffic and provides better performance.
PLSQL tutorial:
Oracle PLSQL interview questions: