In a PL/SQL program, code is written in blocks. Each PL/SQL block has 3 sections, which are:
Followed by END statement at the end.
PL/SQL block creates the structured logical blocks of code that describes the process to be executed. Such a block consists of SQL statements and PL/SQL instructions that are then passed to the oracle engine for execution. PL/SQL block consists of the following four sections:
PL/SQL code starts with a declaration section in which memory variables and other oracle objects like cursor, triggers etc can be declared and if required can be initialized as well. Once declared/initialised we can use them in SQL statements for data manipulation. As it is not necessary that we would require variables etc in every PL/SQL code, hence this section is an optional section.
This section contains the SQL and PL/SQL statements that are required to be executed and contains the main logic. This section is responsible for handling the data retrieval and manipulation, may be working with branching, can use looping and conditional statements, etc.
This section is optional. It is mainly used to handle the errors that may occur between BEGIN and EXCEPTION sections.
This section is the indication of the end of the PL/SQL block.
Let's start with writing a basic PL/SQL block which will do nothing or simply print something in the console. This will help us understand the syntax.
BEGIN NULL; END;
The above PL/SQL block has no DECLARE section, only BEGIN and END section. It will do nothing because the
NULL statement does nothing.
If you want to display output of your PL/SQL block's execution, you can do so by using the
Let's take a simple example:
BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; /
If you look closely in the PL/SQL block above, we have put an extra
/ after the
END; statement, using that will execute the script automatically.
Generally PL/SQL blocks are a part of big programs and are either sub-programs, or functions, or procedures, or triggers etc, but they can be defined independently and can be executed as well.
When we define a PL/SQL block with no header, its known as Anonymous Block.
Let's take an example of a PL/SQL block which will add two numbers and show the result in output:
DECLARE -- declare variable a and b -- and these variables have integer datatype a number; b number; BEGIN a:= 7; b:= 77; dbms_output.put_line('Sum of the number is: ' || a + b); END; /
If you do not understand how we did this, don't worry. This tutorial is just an introduction, we will learn about PL/SQL operators, datatypes, variables etc in the next few tutorials, so click on Next → and go to next tutorial.