PL SQL Tutorial

• Pl Sql Introduction
• Pl Sql Blocks
• Pl Sql Operators
• Pl Sql Variables
• Pl Sql Conditional Statements
• Pl Sql Iterative Statements
• Pl Sql Implicit Cursors
• Pl Sql Explicit Cursors

Pl Sql Tutorial

Pl Sql Blocks

Any PL/SQL unit contains one or more blocks, completely separated or nested.

The components of a PL/SQL block:

A PL/SQL block is composed of up to 3 sections: declarative(optional), executable(required) and to handle exceptions(optional).

Syntax:

DECLARE
   Declarative
BEGIN
   Executable Query
EXCEPTION
   Handle Exceptions
END;

- DECLARE section is optional, here we can decalre: variables, pointers, exceptions.
- BEGIN is mandatory, here we can execute SQL commands, structures of procedural PL/SQL programming.
- EXCEPTION is optional, here we can build actions(handle exceptions) that run when an error occurs.
- END is required.

Types of PL/SQL blocks:

- Anonymous blocks;
- Stored functions and functions of applications;
- Stored procedures and application procedures;
- Packages;
- Triggers on database.

Anonymous blocks:

- are not specified;
- are not stored in the database;
- shall be declared inline, in the place where you want to run them;
- runs when running.

Example:

DECLARE
  c varchar2 (20);
BEGIN
  SELECT c FROM table column INTO;
EXCEPTION
  WHEN exception THEN action
END;

Nested anonymous blocks

You can nest multiple blocks;
These may be labelled with the "label_block" variable.

Example:

BEGIN
   label_block
DECLARE
   .....
BEGIN
   .....
END label_block.variable;
END;