PL/SQL (Procedural Language/Structured Query Language) is a block-structured language developed by Oracle that allows developers to combine the power of SQL with procedural programming constructs. The PL/SQL language enables efficient data manipulation and control-flow logic, all within the Oracle Database.
In this article, we’ll cover PL/SQL basics, including its core features, PL/SQL block structure, and practical examples that demonstrate the power of PL/SQL. We’ll also explore the differences between SQL and PL/SQL, how variables and identifiers work, and how the PL/SQL execution environment operates within Oracle.
What is PL/SQL?
PL/SQL is a combination of SQL and procedural programming constructs, enabling developers to write code that performs database operations efficiently. It was developed by Oracle to enhance SQL’s capabilities and allow for advanced error handling, complex calculations, and programmatic control over database operations.
PL/SQL allows developers to:
- Execute SQL queries and DML commands inside procedural blocks.
- Define variables and perform complex calculations.
- Create reusable program units, such as procedures, functions, and triggers.
- Handle exceptions, ensuring the program runs smoothly even when errors occur
Key Features of PL/SQL
PL/SQL brings the benefits of procedural programming to the relational database world. Some of the most important features of PL/SQL include:
1. Block Structure: PL/SQL can execute a number of queries in one block using single command.
2. Procedural Constructs: One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications.
3. Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block.
4. Reusable Code: Create stored procedures, functions, triggers, and packages, which can be executed repeatedly.
5. Performance: Reduces network traffic by executing multiple SQL statements within a single block
Differences Between SQL and PL/SQL
| Feature | SQL | PL/SQL |
| Purpose | 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. |
| Nature | 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. |
| Execution | Executes single statement. | Executes block of code |
| Use Case | Data retrieval, manipulation and definition( eg. SELECT, INSERT, UPDATE) | Mainly used to create an application. |
| Syntax | SQL statements only | SQL Statements combined with procedural logic |
| Data Handling | Performs actions directly on the database. | Can contain SQL inside its blocks and is used for more control over data handling |
Structure of PL/SQL Block
PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. 
Typically, each block performs a logical action in the program. A block has the following structure:
DECLARE
declaration statements;
BEGIN
executable statements
EXCEPTIONS
exception handling statements
END;
PL/SQL code is written in blocks, which consist of three main sections:
- Declare section starts with DECLARE keyword in which variables, constants, records as cursors can be declared which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part of the code is optional.
- Execution section starts with BEGIN and ends with END keyword.This is a mandatory section and here the program logic is written to perform any task like loops and conditional statements. It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.
- Exception section starts with EXCEPTION keyword.This section is optional which contains statements that are executed when a run-time error occurs. Any exceptions can be handled in this section.
PL/SQL Identifiers
In PL/SQL, identifiers are names used to represent various program elements like variables, constants, procedures, cursors, triggers etc. These identifiers allow you to store, manipulate, and access data throughout your PL/SQL code.
1. Variables in PL/SQL
Like several other programming languages, variables in PL/SQL must be declared prior to its use. A variable is like a container that holds data during program execution. Each variable must have a valid name and a specific data type.
Syntax for declaration of variables:
variable_name datatype [NOT NULL := value ];
- variable_name: The name of the variable.
- datatype: The data type of the variable (e.g.,
INTEGER, VARCHAR2). - NOT NULL: This optional constraint means the variable cannot be left empty.
- := value: This optional assignment assigns an initial value to the variable.
Example: Declaring Variables
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;
BEGIN
null;
END;
/
Output:
PL/SQL procedure successfully completed.
Explanation:
- SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.
- var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are many other data types that can be used like float, int, real, smallint, long etc. It also supports variables used in SQL as well like NUMBER(prec, scale), varchar, varchar2 etc.
- Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.
- Assignment operator (:=) : It is used to assign a value to a variable.
2. Displaying Output in PL/SQL
The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. Let us see an example to see how to display a message using PL/SQL :
Example: Displaying Output
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var varchar2(40) := 'I love GeeksForGeeks' ;
BEGIN
dbms_output.put_line(var);
END;
/
Output:
I love GeeksForGeeks
PL/SQL procedure successfully completed.
Explanation:
dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.
Like in many other programming languages, in PL/SQL also, comments can be put within the code which has no effect in the code. There are two syntaxes to create comments in PL/SQL :
- Single Line Comment: To create a single line comment , the symbol – – is used.
- Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used.
-- This is a single-line comment
/*
This is a multi-line comment
that spans over multiple lines.
*/
In PL/SQL we can take input from the user and store it in a variable using substitution variables. These variables are preceded by an & symbol. Let us see an example to show how to take input from users in PL/SQL:
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
-- taking input for variable a
a number := &a;
-- taking input for variable b
b varchar2(30) := &b;
BEGIN
null;
END;
/
Output:
Enter value for a: 24
old 2: a number := &a;
new 2: a number := 24;
Enter value for b: 'GeeksForGeeks'
old 3: b varchar2(30) := &b;
new 3: b varchar2(30) := 'GeeksForGeeks';
PL/SQL procedure successfully completed.
Explanation:
&a and &b are substitution variables where the user will be prompted to provide values.- The user is asked to enter values for
a and b when the code runs.
PL/SQL Practical Example
Let’s combine all the above concepts into one practical example. We’ll create a PL/SQL block that takes two numbers from the user, calculates their sum, and displays the result.
--PL/SQL code to print sum of two numbers taken from the user.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
-- taking input for variable a
a integer := &a ;
-- taking input for variable b
b integer := &b ;
c integer ;
BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);
END;
Execution:
Enter value for a: 2
Enter value for b: 3
Sum of 2 and 3 is = 5
PL/SQL procedure successfully completed.
PL/SQL Execution Environment
The PL/SQL engine resides in the Oracle engine. When a PL/SQL block is executed, it sends a single request to the Oracle engine, which processes the SQL and PL/SQL statements in the block together. This reduces network traffic, making PL/SQL more efficient for batch processing and handling complex logic.
Conclusion
PL/SQL is a powerful tool in Oracle for combining SQL with procedural programming capabilities. With PL/SQL features like error handling, reusable program units, and support for loops and conditionals, PL/SQL extends SQL’s data manipulation capabilities and enables developers to create sophisticated applications within the database. By understanding SQL vs PL/SQL and the advantages of the PL/SQL execution environment, developers can unlock the full potential of Oracle’s PL/SQL language for robust database applications.
Similar Reads
PL/SQL Injection
PL/SQL injection is a security issue where attackers use harmful code to exploit weak spots in Oracle's PL/SQL applications. If user input isn’t properly checked, attackers can access or change sensitive data and even take control of the system. In this article, we will explain what PL/SQL injection
6 min read
PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part o
4 min read
Introduction of MS SQL Server
Data is a collection of facts and figures and we have humungous data available to the users via the internet and other sources. To manipulate the data, Structured Query Language (SQL) in short has been introduced years ago. There are different versions of SQL available in the market provided by diff
2 min read
Introduction to PostgreSQL PL/pgSQL
PostgreSQL is an open-source, strong and highly extensible object-relational database system. It combines the power of SQL with additional procedural features, making it ideal for handling complex workloads. In PostgreSQL, PL/pgSQL (Procedural Language/PostgreSQL) enhances the SQL functionality by e
5 min read
PL/SQL INSERT INTO
PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows us to write complex queries and scripts that include procedural logic, control structures, and error handling. The INSERT INTO statement in PL/SQL is essential for adding new rows of data to tab
6 min read
PL/SQL MIN() Function
PL/SQL means Procedural Language / relational Structured Query Language, the extended language of Oracle. It is primarily used to manage and manipulate databases. One of the most frequently utilized SQL functions is the MIN() function. This powerful aggregate function is essential for finding the sm
6 min read
PL/SQL SUM() Function
The SUM() function in PL/SQL is used to calculate the sum of the numeric column. It is an aggregate function that performs calculations on a set of values and returns a single value. In this article, we will explore the syntax, usage, and examples of the PL/SQL SUM() function to help us understand i
5 min read
PL/SQL MAX() Function
The PL/SQL MAX() function is an essential aggregate function in Oracle databases, enabling users to efficiently determine the largest value in a dataset. Whether working with numerical data, dates, or strings, the MAX() function is flexible and widely applicable. In this article, we will provide a d
4 min read
PL/SQL JOIN
JOIN is a powerful operation in PL/SQL that allows us to combine data from two or more related tables based on a common key. The PL/SQL JOIN is used to select data from multiple tables using this key to match records. This powerful PL/SQL feature allows for selecting data across multiple tables usin
5 min read
Index in PL/SQL
PL/SQL, Oracle's extension to SQL, combines SQL with procedural programming features like loops, conditionals, and exception handling. It enables developers to create stored procedures, functions, triggers, and other database applications. As a block-structured language, PL/SQL allows seamless integ
5 min read