What is a PLSQL Block

In this tutorial, you will learn about the PL/SQL block structure and how to write and execute the first PL/SQL block in SQL*PLUS.Let’s examine the PL/SQL block structure in Detail

[DECLARE]

   Declaration statements;

BEGIN

   Execution statements;

  [EXCEPTION]

      Exception handling statements;

END;

The declaration section allows you to define data types, structures, and

variables. You can declare variables in the declaration section by giving them

names, data types and initial values.

Execution section is required in a block structure and it must have at leastone statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements.inside the execution section.The exception handling section is starting with the EXCEPTION keyword.The exception section is where you  can write  the code to handle exceptions.You can either catch or handle exceptions in the exception section.

Let’s take a Simple  Example .First, you need to login to the Oracle database via command

prompt by providing a username and password as shown the following picture.

sql > DECLARE

  v_bonus NUMBER(8,2);

BEGIN

  SELECT salary*0.10

  INTO   v_bonus

  FROM   employees

  WHERE  employee_id=&empid;

  DBMS_OUTPUT.PUT_LINE(v_bonus);

END;

Set Serveroutput on command to instruct sql *Plus to display database ouput.

Use DBMS_OUTPUT.PUT_LINE procedure to output a string on a screen.

Here you will get message Enter value for empid:

Enter any employee_id  for example 200

Output you can see :-

old   7:   WHERE  employee_id=&empid;

new   7:   WHERE  employee_id=200;

280

Example:- 2.   Let us take another example using Bind Variable.

Write a PLSQL block to display monthly salary by accepting annual salary using local variable with in block.

VARIABLE g_monthly_sal NUMBER     --  We declare a variable from Host environment.

DECLARE

  v_sal NUMBER(9,2) := &p_annual_sal;

BEGIN

  :g_monthly_sal := v_sal/12;

END;

/

To  Print the value of Bind Variable you can give below this command.

PRINT g_monthly_sal

Example :- 3 Write a PLSQL block to display monthly salary by accepting annual salary using

DEFINE variable

DEFINE p_annual_sal = 60000

DECLARE

  v_sal number(9,2) := &p_annual_sal;

BEGIN

  v_sal := v_sal/12;

  DBMS_OUTPUT.PUT_LINE('The monthly salary is '||v_sal);

END;

/

Pl Sql is a block structure language.A pl Sql block contains three keywords Begin,Exception,End which break up the block into three sections.

Declare :- In this section we declare variables,constants,other code language which can be used
within that block.

Executable section that run when the block is exceuted.
Exceptional handling section you can trap any exception that are raised when executable
section run.

Here are some example
Begin
dbms_output.put_line('Hello');
End;
Once you have written a pl sql block you can execute it Enter a slash(/) to tell sql> to execute this plsql block
or you can enter run command.

An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.

Running The PLSQL block:-

Once you have written a block of PLSQL code ,you can execute(run) it.

There are many tools are available for executing PLSQL code.

A Command Line interface for executing SQL statements as well as PLSQL blocks are shown below.

To tell it to execute ,a block must be followed by a "/" use the following code

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:

DECLARE
   Emp_name    VARCHAR2(10);
   Cursor      c1 IS SELECT Ename FROM Emp_tab
                  WHERE Deptno = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
END;


Example 2:

VARIABLE g_char varchar2(30)
VARIABLE g_num number

DECLARE
  v_char varchar2(30) := '42 is the answer';
  v_num  number := substr(v_char,1,2);
BEGIN
  :g_char := v_char;
  :g_num := v_num;
END;
/
PRINT g_char
PRINT g_num 


Example 3:-


DECLARE
  v_bonus NUMBER(8,2);
BEGIN
  SELECT salary*0.10
  INTO   v_bonus
  FROM   employees
  WHERE  employee_id=&empid;
  DBMS_OUTPUT.PUT_LINE(v_bonus);
END; 

Example 4 :- Write a PLSQL Block to display employee_id of given person.

DECLARE
  employee_id NUMBER(6);
BEGIN
  SELECT employee_id
  INTO   employee_id
  FROM   employees
  WHERE  last_name='Kochhar';
  DBMS_OUTPUT.PUT_LINE(employee_id);
END;