Introduction to Blocks in Procedural Language – SQL

PL/SQL blocks are of two groups:

1. Named Block
2. Anonymous Block

Named Blocks:

• Named blocks are useful when creating subroutines. The subroutines may be procedures, functions, and even packages also.
• The subroutines can be stored in the database and referenced by their names later on.

---

Anonymous Blocks:

Anonymous PL/SQL blocks do not have names. As a result, they cannot be stored in the database and referenced later

---

General Structure of a PL/SQL Block:

The following figure shows the various types of blocks that are present in PL/SQL

Syntax

DECLARE (optional)

- variable declarations

BEGIN (required)

- SQL statements

- PL/SQL statements or sub-blocks

EXCEPTION (optional)

- actions to perform when errors occur

END; (required)

 

 

• Declaration section is optional
• The executable section is the only mandatory section of the block.
• Both the declaration and exception-handling sections are optional.

Declaration Section

• The declaration section is the first section of the PL/SQL block.
• It contains definitions of PL/SQL identifiers such as variables, constants, cursors and so on.
• The below example shows the declaration of variables needed for the Student table and its operations.

DECLARE
v_first_name VARCHAR2(35) ;
v_last_name VARCHAR2(35) ;
v_counter NUMBER := 0 ;

Here the variables v_first_name and v_last_name are of variable length character type and their maximum size is 35 characters. The variable v_counter is of type number and its initial value is 0.

Executable Section

• The executable section is the next section of the PL/SQL block
• This section contains executable statements that allow you to manipulate the variables that have been declared in the declaration section.
• Example: The below example is useful in finding the student with id number 123 in the student table

BEGIN
SELECT first_name, last_name INTO v_first_name, v_last_name FROM student
WHERE student_id = 123 ;
DBMS_OUTPUT.PUT_LINE(‘Student name :’ || v_first_name ||‘ ’|| v_last_name);
END;

In the above code the select statement selects first name, last name of student whose id is 123 and stores them into v_first_name, v_last_name and prints them.

Exception Handling Section

• The exception-handling section is the last section of the PL/SQL block.
• This section contains statements that are useful when a runtime error occurs within a block.
• Generally, runtime errors will occur while the program is running and hence they cannot be visible during compilation by the PL/SQL compiler.So Exception Handling is used to handle them.
• Example: The below example is used to handle the exception when the student with required id number is not present in the database

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘There is no student with student id123 ’);
END;

If no student with id 123 is found, then the above code executes and prints ‘There is no student with student id123 ’.