PL/SQL Block Structure and Block Types

PL/SQL follows block-structured coding. A PL/SQL block structure is a logical collection of SQL and PL/SQL statements. It BEGINs with declaration of variables and cursors. Declaration section is followed by the executable section which is the only mandatory part of a PL/SQL block. The executable section contains logic written by a programmer. Exceptions section can be included but is not mandatory to handle user-defined or pre-defined exceptions. We have seen the basic PL/SQL Block Structure is last topic.

Here, we are going to discuss the types of PL/SQL blocks. So let’s proceed

Anonymous PL/SQL blocks

A block defined without a name is called an Anonymous Block. The unnamed anonymous blocks are. Since they don’t have a name, they cannot be stored and called later. So they must be declared when an application needs them. These blocks are executed by passing them to the PL/SQL engine at runtime.

Syntax to create an anonymous PL/SQL block

[DECLARE               

  Variables, cursors, or user- defined exceptions

]

BEGIN

                SQL and PL/SQL atatements

[EXCEPTION

                Exception handing code

]

END;

Example-Anonymous Block

Without Declare Section

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello!! welcome to the World of PL/SQL Blocks');
END;

Output

With Declare Section

DECLARE
num1  INTEGER:=1;
num2  INTEGER:=9;
BEGIN
DBMS_OUTPUT.PUT_LINE(num1+num2);
END;

With EXCEPTION Block

DECLARE
num1 INTEGER:=20;
num2 INTEGER:=0;
num3 INTEGER;
BEGIN
num3:=num1/num2;
    DBMS_OUTPUT.PUT_LINE('output is'+num3);
EXCEPTION when ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE(' Denominator cannot be zero');
END;

Output

PL/SQL Functions

Functions are named PL/SQL blocks.  PL/SQL functions with a name declared with keyword Function always compute a value and return it when called. They are stored in your schema and can be called when required. They can be declared with parameters. While invoking the function you have to pass the parameters.

Syntax to create a function

CREATE [OR REPLACE] FUNCTION function-name

(parameter-name data-type[:=initial value] ,…..)

RETURN data-type

 IS

BEGIN

SQL or PL/SQL statements

RETURN (variable|value| expression);

END;

Example

 CREATE OR REPLACE FUNCTION addition
(num1  INTEGER,
num2  INTEGER)
RETURN INTEGER
AS
BEGIN
RETURN num1+num2;
EXCEPTION WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE(' Denominator cannot be zero');
END;

Calling the function in another block

BEGIN
DBMS_OUTPUT.PUT_LINE(addition(45,67));
END ; 

Output

PL/SQL Block Procedures

Procedures are subprograms similar to functions with only difference that they do not return a value. They are named blocks that can take parameters and can be invoked. You can declare a procedure with keyword PROCEDURE. A procedure only performs a specific action. Procedures are also stored in the server and can be called a number of times in an application or in other procedures or functions or anonymous blocks.

Syntax to create a procedure

CREATE [OR REPLACE] PROCEDURE procedure-name

(parameter-name data-type[:=initial value] ,…..)

IS

BEGIN

SQL or PL/SQL statements

END;

Example

CREATE OR REPLACE PROCEDURE calcage
(dob date)
as
age INTEGER;
BEGIN
age:= MONTHS_BETWEEN (sysdate,dob)/12;
DBMS_OUTPUT.PUT_LINE(age);
end;

Output after invoking Procedure

Try your hands on:

• Write a PL/SQL Anonymous Block/Procedure to display “Hello world”  message.
• Write a PL/SQL Function to find area of a circle .
• Write a PL/SQL Anonymous Block and Procedure to print sum, difference, product and division of two numbers.

Note: In the examples DBMS_OUTPUT.PUT_LINE procedure prints the output. Assignment operator in PL/SQL is :=.