Explanation of PL/SQL block with its syntax

by

PL/SQL stands for procedural Structures query language which is an extension of the structural query language (SQL) and oracle relation database. It is independent of the operating system environment and tightly integrated with SQL.

It provides extensive error checking, numerous data types, and a variety of programming structures. It also supports structural programming through the function and development of web applications and server pages.

It consists of 4 blocks

  1. Block header: It defines the block types and the way of calling the block.
  2. Declaration header: It declares the variables using blocks.
  3. Execution section: It uses the variables and other PL/SQL objects to perform the execution.
  4. Exception block: It is an optional block that handles the exceptions that occur during block execution.

Let’s have a look at the syntax of PL/SQL

BLOCK HEADER
AS
   DECLARATION HEADER
BEGIN
   EXECUTION SECTION
EXCEPTION
   EXCEPTION SECTION
END
/

Now let’s have a look at an example where two variables are multiplied.

DECLARE
	A number;
	B number;
BEGIN
	A:= 10;
	B:=11;
	DBMS_OUTPUT.PUT.LINE(“THE MULTIPLICATION OF TWO NUMBERS IS:”|| A*B);
END;
/

The generated output for the above program will be


‘THE MULTIPLICATION OF TWO NUMBERS IS 110’

The PL/SQL is not a case-sensitive language and contains the same data types as SQL but it uses ‘:=’ as an assignment operator and ‘;’ as the end of line operation.

‘END’ is used to indicate the end of PL/SQL and ‘/’ is used to run the code from the SQL command line.