What is PL/SQL?

PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in a procedural format. Full form of PL/SQL is “Procedural Language extensions to SQL”.

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

PL/SQL means instructing 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.

In this tutorial, you will learn-

• What is PL/SQL?
• Architecture of PL/SQL
• Advantage of Using PL/SQL

Architecture of PL/SQL

The PL/SQL architecture mainly consists of following three 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 by 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 parses the input SQL statements and execute the same.

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.

PL/SQL Tutorial

Audience

This tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you great understanding on PL/SQL Programming concepts, and after completing this tutorial, you will be at an intermediate level of expertise from where you can take yourself to a higher level of expertise.

Prerequisites

Before proceeding with this tutorial, you should have a basic understanding of software basic concepts like what is database, source code, text editor and execution of programs, etc. If you already have an understanding on SQL and other computer programming language, then it will be an added advantage to proceed.

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database. Following are certain notable facts about PL/SQL −

• PL/SQL is a completely portable, high-performance transaction-processing language.
• PL/SQL provides a built-in, interpreted and OS independent programming environment.
• PL/SQL can also directly be called from the command-line SQL*Plus interface.
• Direct call can also be made from external programming language calls to database.
• PL/SQL’s general syntax is based on that of ADA and Pascal programming language.
• Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Features of PL/SQL

PL/SQL has the following features −

• PL/SQL is tightly integrated with SQL.
• It offers extensive error checking.
• It offers numerous data types.
• It offers a variety of programming structures.
• It supports structured programming through functions and procedures.
• It supports object-oriented programming.
• It supports the development of web applications and server pages.