Explain PL/SQL Block Structure.

Q.3 Explain PL/SQL Block Structure.

Answer :- 

• The structure of a PL/SQL block is fundamental to writing and organizing code in Oracle’s Procedural Language/Structured Query Language (PL/SQL).

• PL/SQL blocks have a pre-defined structure in which the code is to be grouped.

• A PL/SQL block is divided into four main sections: Declaration, Execution, Exception Handling, and End Statement.

• Each part serves a specific purpose, and understanding their roles is essential for creating robust and efficient programs.

Components of a PL/SQL Block

1. Declaration Section (Optional)

• This section is used to declare variables, constants, cursors, and other objects required in the block.

• It begins with the keyword ‘DECLARE’.

• Variables defined here are available throughout the block.

• This particular section is optional and can be skipped if no declarations are needed.

Example :- 

DECLARE
total_sales NUMBER(10, 2);

2. Execution Section

• This is the main part of the PL/SQL block where the program logic is written.

• It begins with the keyword `BEGIN` and ends before the `EXCEPTION` or `END` statement.

• Contains SQL statements (like SELECT, INSERT, UPDATE) and procedural statements (like loops, conditionals).

• This can contain both PL/SQL code and SQL code.

• This can contain one or many blocks inside it as a nested block.

Example :- 

BEGIN
total_sales := total_sales + 1000;

3. Exception Handling Section (Optional)

• This section is used to handle runtime errors and exceptions.

• It begins with the keyword `EXCEPTION`.

• Allows the programmer to define custom actions when specific errors occur.

• This section is the last part of the PL/SQL block.

Example :- 

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found.’);

4. End Statement :- 

• The block ends with the `END;` statement.

• It signifies the conclusion of the block and is mandatory.

Structure of a PL/SQL Block

DECLARE
— Declaration section (optional)
variable_name datatype;
BEGIN
— Execution section (mandatory)
— Place your procedural and SQL statements here
EXCEPTION
— Exception handling section (optional)
— Handle errors and exceptions here
END;
/

Key Points

• A PL/SQL block can be anonymous (not named) or named (like procedures and functions).

• The declaration and exception sections are optional, but the execution section is mandatory.

• Nested blocks are supported, meaning a block can contain other blocks within it.