• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

PL/SQL is an Oracle procedural extension for SQL. They have designed this language for easy use of complex SQL statements.

Before we start learning program logic in PL/SQL, let's discuss the important factors in PL/SQL that is mandatory for the programmers to understand how the PL/SQL works.





In this tutorial, you will learn-

What Is PL/SQL?

PL SQL basically stands for "Procedural Language extensions to SQL". This is the extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in procedural format.

It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries.

It allows the programmers to instruct the compiler 'what to do' through SQL and 'how to do' through its procedural way.

Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts.

Architecture of PL/SQL

The PL/SQL architecture mainly consists of following 3 components:

  1. PL/SQL block
  2. PL/SQL Engine
  3. Database Server

PL/SQL block:

  • This is the component which has the actual PL/SQL code.
  • This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)
  • It also contains the SQL instruction that used to interact with the database server.
  • All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.
  • Following are the different type of PL/SQL units.
    • Anonymous Block
    • Function
    • Library
    • Procedure
    • Package Body
    • Package Specification
    • Trigger
    • Type
    • Type Body

PL/SQL Engine

  • PL/SQL engine is the component where the actual processing of the codes takes place.
  • PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below).
  • The separated PL/SQL units will be handled with the PL/SQL engine itself.
  • The SQL part will be sent to database server where the actual interaction with database takes place.
  • It can be installed in both database server and in the application server.

Database Server:

  • This is the most important component of Pl/SQL unit which stores the data.
  • The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server.
  • It consists of SQL executor which actually parses the input SQL statements and execute the same.

Below is the pictorial representation of Architecture of PL/SQL.

Introduction to PL/SQL

Advantage of Using PL/SQL

  1. Better performance, as sql is executed in bulk rather than a single statement
  2. High Productivity
  3. Tight integration with SQL
  4. Full Portability
  5. Tight Security
  6. Support Object Oriented Programming concepts.

Basic Difference between SQL and PL/SQL

In this section, we will discuss some differences between SQL and PL/SQL

SQL

PL/SQL
  • SQL is a single query that is used to perform DML and DDL operations.
  • PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
  • It is declarative, that defines what needs to be done, rather than how things need to be done.
  • PL/SQL is procedural that defines how the things needs to be done.
  • Execute as a single statement.
  • Execute as a whole block.
  • Mainly used to manipulate data.
  • Mainly used to create an application.
  • Interaction with Database server.
  • No interaction with the database server.
  • Cannot contain PL/SQL code in it.
  • It is an extension of SQL, so it can contain SQL inside it.

 

YOU MIGHT LIKE:
PL-SQL

Loops in PL/SQL

In this chapter, we are going to see the loop concept in PL/SQL and flow of control in loops. You...

PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...