What is Anonymous block in PL/SQL

Starting with PL/SQL | Anonymous block in PL/SQL :

In my previous articles, I have explained about different oracle tutorials, various interview questions and BI tutorials as well. In this article, I will try to explain about the advanced database language, which is PL/SQL. In this article, we are Starting with PL/SQL which stands for Procedural Language- Structured Query language. Oracle has developed the advanced SQL language to do the transactions in the database. PL SQL is nothing but extension to the standard SQL with newly added transactional language features. In this article, I will try to give the basic idea of Anonymous block in PL/SQL and how to write Anonymous block in PL SQL.

Why PL/SQL?

The PL/SQL language is extension to SQL language. There are lot of limitations while working with single SQL statement. Lot of times as per business requirements user needs to execute the set of SQL statements. There is need to execute the procedural logic in SQL statement. For these reasons oracle has defined the procedural language with additional procedural features:

1. User will able to define the procedural logic on client side.
2. User will able to execute the set of SQL statements together
3. User will able to add the Business logic using PL SQL
4. User will able to add the Loops, if. Else statements in the procedure.
5. Using packages user will encapsulate all the business logic in to single package.
6. User will able to add complex business logic as well as business rules using PL SQL.

PL SQL provides the mechanism for developers to add the procedural component at server level. Means PL SQL is used to add the complex business logic, business rules in database language.

Starting with Anonymous block in PL/SQL :

Let us start with the Anonymous block in PL/SQL. The PL/SQL language has a block structure. The block structure should contain set of SQL statements, Set of Exceptions, Error handlers, functions, variable declaration etc.

Syntax of PL SQL Block:

DECLARE

variable_declarations

BEGIN

program_code

EXCEPTION

 exception_handlers

END;

The above is the simple block structure of PL/SQL code. Every user will start with how to write the PL/SQL block. This is universal block structure of PL/SQL. In this article I will try to give you the idea of writing simple PL/SQL code using the block structure. The block structure is converted in to 3 parts :

1. Declarative Section :

In Declarative section all the variable declaration as well as function declaration is defined.

Example:

Declare

Variable1 Number (10, 2);

2. Begin section:

In between Begin and End the actual program, code is written. The business logic needs to be added in Begin section. The set of SQL statement has been written in begin section. User will be able to add the looping as well as if. Else structure in begin section.

Example:

Declare

Variable1 Number (10, 2);

Begin

Select 6/2 into Variable1 from dual;

Dbms_output.Put_line (‘The Division of 6/2 is’||Variable1);

End;

3. Exception Section:

After Begin, section user needs to deal with the errors and exceptions. The Oracle has given the provision to handle exceptions. The exceptions are written in exception handling block. There are two types of exceptions in PL/SQL. One is system exceptions and other is user defined exceptions. The exceptions block is non mandatory block.

Example :

Declare

Variable1 Number (10, 2);

Begin

Select 6/2 into Variable1 from dual;

Dbms_output.Put_line (‘The Division of 6/2 is’||Variable1);

Exception when divide_by_zero then

Dbms_output.Put_line (‘Number can not be divided by zero’);

End;

I have explained the simple PL SQL block in above example.

Things to Remember:

When you are defining PL/SQL functions, procedures, and triggers, the keyword DECLARE is not used. When defining a function, the function specification, or function header as it is sometimes called, begins the block. Means everything, which has not started with Declare keyword is known as PL SQL objects.

How to Run Simple PL SQL block:

The following are steps to run the PL SQL program using SQL developer. The user can run the PL SQL program using following two types:

1. Run the PL SQL program directly on the server
2. Run the PL SQL program-using editor like SQL developer.

1. Run the PL SQL program using Server :

User can run the PL SQL program using direct server. There are following steps.

Step 1 :

Connect with SQL* PLUS :

1. Click Start –> Programs  –> Oracle –> OraHomeName –> Application Development –> SQL Plus.
2. There is second way to open SQL plus .Open the windows terminal and use command as : sqlplusw.
3. The SQL*Plus Windows GUI opens and the Log On dialog is displayed.
4. Click OK.

Step 2 : Use backslash to Run Program

Just write the program and put backslash before running it :

declare

x  integer;

begin

x := 111;

dbms_output.Put_line(x);

end;

/         –Backslash is important to run program-using SQLPLUS

Output : PL/SQL procedure successfully completed

Using this the output of procedure is not displaying.So you need to set server output as On.

Step 3: Set serveroutput On

User need to set Server output as On to display the output of any procedure.

SQL> SET SERVEROUTPUT ON

Step 4: Run program again with backslash

declare

x  integer;

begin

x := 111;

dbms_output.Put_line(x);

end;

/         –Backslash is important to run program-using SQLPLUS

Output : 3

2. Run the PL SQL program-using editor like SQL developer.

User can run the PL SQL block in very simple steps using Editor.I will explain how to run PL SQL using SQL developer in this section.

Step 1 :

Connect with SQL developer

User needs to open the SQL developer and click on + sign on SQL developer.

Step 2 :

Put all required server credentials and connect with SQL developer.

Step 3 :

Write the code and Run it using key F9 or use the green arrow to run the program.

These are two different processes used to run the simple PLSQL block. Hope everyone get the idea of anonymous block in PL/SQL. If you really like this article or if you have any suggestions related to this article of Anonymous block in PL/SQL kindly comment in comment section.