Anonymous Blocks

Anonymous blocks






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;