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.
Blocks
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
Anonymous 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.
Named Block
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
DECLARE
Declaration Statements
BEGIN
Executable statements
Exception
Exception handling statements
END;
Declaration Section
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.
Example 1
DECLARE
Var_first_name VARCHAR2(30);
Var_last_name VARCHAR2(30);
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.
Execution Section
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.
Example 2
BEGIN
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);
END;
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.
Exception-Handling Section
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.
Example 3
EXCEPTION
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!