Different Types of Blocks in PL/SQL.
PL/SQL stands for procedural language-standard query language. It is a significant member of Oracle programming tool set which is extensively used to code server side programming. Similar to SQL language PL/SQL is also a case-insensitive programming language.
Generally a program written in PL/SQL language is divided into blocks. We can say blocks are basic programming units in PL/SQL programming language.
PL/SQL Blocks contain set of instructions for oracle to execute, display information to the screen, write data to file, call other programs, manipulate data and many more.
Does Blocks supports DDL statements?
Yes, PL/SQL blocks support all DML statements and using Native Dynamic SQL (NDS) or they can run DDL statements using the build in DBMS_SQL package.
Types of PL/SQL Blocks
There are two types of blocks in PL/SQL
- Anonymous Block
- Named Block
As the title suggests these anonymous blocks do not have any names as a result they cannot be stored in database and referenced later.
On the other hand Named PL/SQL blocks are the one that have names and are used when creating subroutines such as procedures, functions and packages. These subroutines then can be stored in the database and referenced by their name later.
Both type of PL/SQL blocks are further divided into 3 different sections which are:
- The Declaration Section
- The Execution Section and
- The Exception-handling Section
The Execution Section is the only mandatory section of block whereas Declaration and Exception Handling sections are optional.
Basic prototype of Anonymous PL/SQL Block
Exception handling statements
This is the first section of PL/SQL block which contains definition of PL/SQL identifiers such as variables, Constants, cursors and so on. You can say this is the place where all local variables used in the program are defined and documented.
Con_flag CONSTANT NUMBER:=0;
The above example shows declaration section of an anonymous block. It begins with keyword declare and contains two variables var_first_name and var_last_name and one constant con_flag. Notice that semicolon terminates each declaration.
This section contains executable statements that allow you to manipulate the variables that have been declared in the declaration section. The content of this section must be complete to allow the block to compile. By complete I mean complete set of instruction for the PL/SQL engine must be between BEGIN and END keyword.
The execution Section of any PL/SQL block always begins with the Keyword BEGIN and ends with the Keyword END.
This is the only mandatory section in PL/SQL block. This section supports all DML commands and SQL*PLUS built-in functions and using Native Dynamic SQL (NDS) or using DMBS_SQL built-in package it also supports DDL commands.
SELECT first_name, last_name INTO var_first_name, var_last_name
FROM employees WHERE employee_id =100;
DBMS_OUTPUT.PUT_LINE(‘Employee Name ’||var_first_name||’ ‘||var_last_name);
This is very simple program where I fetched the value of first name and last name column from employees table where employee id is 100 and stored it into the variable var_first_name and var_last_name which we declared in our first example.
This is the last section of PL/SQL block which is optional like the declaration block. This section contains statements that are executed when a runtime error occurs within the block.
Runtime error occurs while the program is running and cannot be detected by the PL/SQL compiler. When a runtime error occurs, controlled is pass to the exception handling section of the block the error is evaluated and specific exception is raised.
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘No Employee Found with ’||employee_id);
That’s it for the first tutorial you can also watch my video on the same topic where I performed all the examples live. You can also tell me your feedback on my Twitter, for that follow me and tell me how you like this tutorial. Also help me in reaching out to more people by sharing this tutorial with your friends on social media. Thanks & have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
NO SQL Script used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE