PL/SQL Introduction

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

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.

3. Comments in PL/SQL

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.

Example: Adding Comments

-- This is a single-line comment

/*
  This is a multi-line comment
  that spans over multiple lines.
*/

4. Taking input from users

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: 

Example: Taking Input from Users

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.