The Basic Structure of PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's extension of SQL that allows you to create and execute procedural logic within the database. PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block.

PL/SQL block contains 3 sections.

The Declaration Section (optional)

The Procedural Execution Section (mandatory)

The Exception handling Section (optional)

Here's the general structure of a PL/SQL block:

DECLARE

    -- Declaration section (optional)

    variable1 datatype1;

    variable2 datatype2;

    -- ... more variables ...

BEGIN

    -- Procedural logic section

    -- SQL statements

    -- PL/SQL statements

    -- Control flow statements (IF, LOOP, etc.)

    -- ... more logic ...

EXCEPTION

    -- Exception handling section (optional)

    WHEN exception1 THEN

        -- handle exception1

    WHEN exception2 THEN

        -- handle exception2

    -- ... more exception handling ...

END;

Explanation of the sections:

DECLARE: This is the optional declaration section where you define variables, constants, and cursors to be used within the PL/SQL block.

BEGIN: This is the mandatory procedural logic section where you write the actual PL/SQL code. It contains the SQL and PL/SQL statements that perform the desired operations or computations.

EXCEPTION: This is an optional section where you handle exceptions that may occur during the execution of the block. When an exception is encountered, PL/SQL searches for the appropriate handler to process the exception. If no suitable handler is found, the block terminates, and the exception propagates to the calling environment.

You can have nested blocks within a PL/SQL block, which allows you to organize your code more effectively and handle exceptions at different levels of granularity.

Here's a simple example of a PL/SQL block:

DECLARE

    name VARCHAR2(50) := 'John Doe';

    age NUMBER := 30;

BEGIN

    -- Output the name and age

    DBMS_OUTPUT.PUT_LINE('Name: ' || name);

    DBMS_OUTPUT.PUT_LINE('Age: ' || age);

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END;

In this example, we have a declaration section with two variables (name and age) and a BEGIN-EXCEPTION block that prints their values. If any unexpected error occurs during execution, the exception block will handle it and display the error message using the DBMS_OUTPUT package.