Anonymous PL/SQL block

Structure of PL/SQL block

Anonymous Block:

The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the database. It is the P/L SQL Block without name.

Named Block:

Named Block is a type of block which starts with the HEADER section which specifies the name and the type of the block. There are 2 types of named blocks namely:-

Procedures:

It is a collection of statements which collectively perform a certain task. It passes variables through parameters and return one or more value through parameters.

Functions:

It is a series of statements performing a specific task and returning only one value.

Structure of a PL SQL Block:

Each PL/SQL program consists of SQL and PL/ SQL statements which form a PL /SQL block. A PL/SQL Block consists of four sections:

The Header section.

The Declaration section.

The Execution section.

The Exception (or Error) Handling section.

HEADER

<Type and Name of block >

DECLARE

<All variables, Cursors are declared here>

BEGIN

<All programming logic, queries, program statements are written here>

EXCEPTION

<All Error Handling code is written here>

END;

–It ends the program

Get PDF

Creation of an anonymous PL/ SQL Block:

The anonymous block is a type of PL SQL block which has no name associated with it. In fact, the anonymous block is missing the header section altogether.

Instead it simply uses the DECLARE reserved word to mark the beginning of its optional declaration section.

For Example,

To create a P/L SQL Block which inserts the following 3 records into the table “Prod_bill”.

Record #1: B1,02-FEB-2012, Rohini,Washing Machine,1,10000

Record #2:B2,25-MAR-2012,Mahesh,Refrigerator,1,12000

Record #3:B3,30-MAR-2012,Arpita,Mixer,2,8000

 

Leave a Reply

Your email address will not be published. Required fields are marked *