PL/SQL Overview and Advantages

PL/SQL Overview

• PL/SQL stands for "Procedural Language extension of SQL," integrated with Oracle database since version 7.
• It is a block-structured language with logical blocks that can nest sub-blocks.
• PL/SQL functionalities are extended with each Oracle database release.
• PL/SQL includes programming elements like conditions, loops, and allows the declaration of constants, variables, procedures, functions, and triggers.

Advantages of PL/SQL

• Supports SQL data manipulation, conditional checking, branching, and looping.
• Provides error handling capabilities with user-friendly messages for errors.
• Allows variables to store intermediate query results for further processing.
• Enables quick and efficient calculations, enhancing transaction performance without using the Oracle engine.
• Code written in PL/SQL is portable across any hardware and operating system supporting Oracle.

Disadvantages of SQL

• Lacks programming techniques for condition checking, looping, and branching necessary for data testing.
• SQL statements are executed one at a time, increasing network traffic and reducing data processing speed, especially in multi-user environments.
• No built-in error handling facilities in SQL.

PL/SQL Blocks

• PL/SQL code is executed as grouped elements called Blocks.
• Blocks contain both PL/SQL and SQL instructions, executed collectively rather than sequentially.

Structure of PL/SQL Blocks

• Declaration Section:

  • Optional section for declaring variables, cursors, exceptions, subprograms, and collections.
  • Begins with the keyword ‘DECLARE’ (for triggers and anonymous blocks) and must precede the execution section.

• Execution Section:

  • Mandatory section where the actual code execution occurs.
  • Contains executable statements, can include both PL/SQL and SQL code, and can have nested blocks.
  • Begins with the keyword ‘BEGIN’ and must be followed by ‘END’ or an Exception-Handling section.

• Exception-Handling Section:

  • Handles run-time exceptions, providing a structure for responding to errors.
  • Optional portion of PL/SQL blocks, structured to manage exceptions raised in the execution block.