PL/SQL Introduction

PLSQL Introduction

     The PL/SQL block is the most basic unit. All PL/SQL programs are combined into blocks. These blocks can also be nested within each other. Usually, PL/SQL blocks combine statements that represent a single logical task. Therefore, different tasks within a single program can be separated into blocks. As a result, it is easier to understand and maintain the logic of the program.

    PL/SQL blocks can be divided into two groups: named and anonymous. Named PL/SQL blocks are used when creating subroutines. These subroutines are procedures, functions, and packages. The subroutines then can be stored in the database and referenced by their names later. In addition, subroutines such as procedures and functions can be defined within the anonymous PL/SQL block. These subroutines exist as long as this block executes and cannot be referenced outside the block.

PLSQL Anonymous Block :

    PLSQL block, those have no name are called PLSQL Anonymous Block. These block execute once. you can execute again by using SQL*Plus feature (/) but never called by name as they don't have any name.

     PL/SQL blocks contain three sections: the declaration section, the executable section, and the exception-handling section. The executable section is the only mandatory section of the block.

     The declaration and exception-handling sections are optional. As a result, a PL/SQL block has the following structure

    

Anonymous Block Structure:

 Declare
     Declaration statements;            --Optional 
 begin
     Executable statements;             --Mandatory 
 Exception
     Exception-handling statements;     --Optional
     
 end;     

DECLARATION SECTION :

    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.

Example :

 Declare
     l_first_name    VARCHAR2(50); 
     l_last_name     VARCHAR2(50); 
     l_salary        NUMBER(10,2); 
     l_DOB           DATE; 
     l_counter       CONSTANT NUMBER:= 0; 
     l_rate          NUMBER NOT NULL := 7;  
     

EXECUTABLE SECTION :

    This section contains executable statements that allow we to calculation/manipulate the variables that have been declared in the declaration section or insert/update data of table.

Example :

 BEGIN
     SELECT  first_name, last_name 
     INTO  l_first_name, l_last_name
     FROM  student
     WHERE  student_id = 2;

     DBMS_OUTPUT.PUT_LINE ('Student name: ' ||l_first_name||' '||l_last_name);     
 end;     

EXCEPTION SECTION :

    This section contains statements that are executed when a run-time error occurs within the block. Run-time errors occur while the program is running and cannot be detected by the PL/SQL compiler.When a run-time error occurs, control is passed to the exception section of the block. The error is then evaluated, and a specific exception is raised or executed.

Example :

 BEGIN
     SELECT  first_name, last_name 
     INTO  l_first_name, l_last_name
     FROM  student
     WHERE  student_id = 2;

     DBMS_OUTPUT.PUT_LINE ('Student name: ' ||l_first_name||' '||l_last_name);   
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('student id 2 is not found');  
 end;