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

 

SQL vs PL/SQL engine

Overview of PLSQL

PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the features of procedural programming languages. Oracle  Corporation has developed this language to enhance the capabilities of SQL.
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client-side or in the server-side.Oracle application can be built on client/server architecture.
PL/SQL programs can be written on the client side and queries can be written to manipulate or retrieve the data from the server on the server side.

Difference between SQL and PL/SQL

  1. SQL is a Structured Query Language  fires a query to create or modify database objects at the Server.PL-SQL is a procedural language SQL,adds programming elements like variables, loops,operators etc. to SQL queries
  2. SQL is a data oriented language selects and manipulates sets of data.
    PL/SQL is a procedural language create applications.
  3. SQL query gets executed one by one causing two trips from the client to the server ,one for firing the request and another for bringing the result set. PL/SQL can execute multiple SQL queries together in a block at one go.

Benefits of PL/SQL:

  1. Structured blocks: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. These blocks can be stored in the database and supports re-usability.
  2. Integration with SQL: The PL/SQL language is tightly integrated with SQL. The user does not have to perform data conversion between SQL and PL/SQL data types. This integration saves both learning time and processing time. PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions and operators.
  3. Full Portability: Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs.
  4. Procedural Language: PL SQL includes the procedural language constructs such as control flow statements which consists of (if else statements) and loops like (FOR loops).
  5. Efficient Error handling mechanism: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Run time errors can be trapped with Exception handling statements.
    Once an exception is caught, specific actions can be taken depending upon the type of the exception .
  6. Security: PL/SQL stored procedures protect the application code from tampering, hide the internal details, and restrict user’s access.
  7. Object oriented programming support: Oracle object types are user-defined types that make it possible to model real-world entities such as customers and purchase orders as objects in the database.
    It supports encapsulation, modularity, maintainability and re-usability.
  8. Better efficiency and performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
SQL vs PL/SQL engine
SQL vs PL/SQL engine