PL/SQL Tutorial: Blocks in PL/SQL Programs

Jul 17, 2024

PL/SQL Tutorial: Blocks in PL/SQL Programs

Introduction to PL/SQL Language

  • PL/SQL stands for Procedural Language - Standard Query Language.
  • It is a significant member of the Oracle programming toolset.
  • Extensively used for server-side programming.
  • Case-insensitive language (like SQL).

Structure of PL/SQL Programs

  • PL/SQL programs are divided into blocks.
  • These blocks are the basic programming units in PL/SQL.

Types of PL/SQL Blocks

  1. Anonymous Block
  2. Named Block

Sections of PL/SQL Blocks

  • Both types of PL/SQL blocks are divided into three sections:

    1. Declaration Section
    2. Execution Section
    3. Exception-Handling Section

  • Execution Section is the only mandatory section.

  • Declaration and Exception-Handling sections are optional.

Basic Prototype of an Anonymous PL/SQL Block

DECLARE
  Declaration Statements
BEGIN
  Executable Statements
EXCEPTION
  Exception handling statements
END;

Detailed Explanation of Each Section

Declaration Section

  • First section of the PL/SQL block.
  • Contains definitions for PL/SQL identifiers such as:
    • Variables
    • Constants
    • Cursors
  • This is where all local variables used in the program are defined and documented.
  • Always starts with the keyword DECLARE.

Execution Section

  • Contains executable statements to manipulate the variables declared in the Declaration Section.
  • Begins with the keyword BEGIN and ends with the keyword END.
  • The only mandatory section of a PL/SQL block.
  • Supports:
    • All DML commands
    • SQL*PLUS built-in functions
    • DDL commands via Native Dynamic SQL (NDS) or DMBS_SQL built-in package.

Exception-Handling Section

  • The last section of a PL/SQL block.
  • Optional like the declaration section.
  • Contains statements executed when a runtime error occurs within the block.

Additional Resources

  • Visit the blog for detailed explanations and examples.
    • Link provided in the description box of the tutorial video.

Closing Remarks

  • Like, share, and subscribe to the channel.
  • Follow on Twitter and Instagram for updates.
    • Links are on the video screen.