PL/SQL Programming block

In PL/SQL, all statements are classified into small units called as Blocks. It include variables, SQL statements, loops, constants, conditional statements and exception handling. Using blocks we can also build a SQL Function or a Stored Procedure or a Package.

Comments in PL/SQL:

Like in many other programming languages, in PL/SQL also, comments can be put within the code which has no effect in the code.

There are two syntaxes to create comments in PL/SQL:-

1)      Single Line Comment:

 

To create a single line comment, the symbol – – is used.

 

2)      Multi Line Comment:

 

To create comments that span over several lines, the symbol /* and */ is used.

Example comment line in PL/SQL :

DECLARE

    name varchar(50) :=’DEEPAK’;

BEGIN

     --dbms_output.put_line(‘deepak’);

    dbms_output.put_line(name);

END;

                /

 

Taking input from user:

Just like in other programming languages, in PL/SQL also, we can take input from the user and store it in a variable. Let us see an example to show how to take input from users in PL/SQL:

Syntax

DECLARE

    name varchar(50);

BEGIN

     name :=&Name;

    dbms_output.put_line(name);

END;

                /

 

Sample PL/SQL program

Let us write a sample program to “Print the sum of two numbers taken/input from the user” on PL/SQL to demonstrate all above concepts.

SQL> SET SERVEROUTPUT ON;

SQL>

 

Example

DECLARE

first_no number := &1stNo;

second_no number := &2ndNo;

result number;

BEGIN

result := first_no+ second_no;

dbms_output.put_line(‘Sum of two no =’|| result);

END;

            /

 

Output

 

Enter value for 1stNo: 10

Enter value for 2ndNo: 20

Sum of two no = 30

PL/SQL procedure successfully completed.

 

PL/SQL Execution Environment:

The PL/SQL engine resides in the Oracle engine. The Oracle engine can process not only single SQL statement but also block of many statements. The call to Oracle engine needs to be made only once to execute any number of SQL statements if these SQL statements are bundled inside a PL/SQL block.