Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/plsql/plsql-introduction/ below:

PL/SQL Introduction - GeeksforGeeks

Last Updated : 05 Aug, 2025

PL/SQL (Procedural Language/SQL) is Oracle’s extension of SQL that adds procedural features like loops, conditions, and error handling. It allows developers to write powerful programs that combine SQL queries with logic to control how data is processed. With PL/SQL, complex operations, calculations, and error handling can be performed directly within the Oracle database, making data manipulation more efficient and flexible.

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.

PL/SQL brings the benefits of procedural programming to the relational database world. Some of the most important features of PL/SQL include:

• Block Structure: PL/SQL can execute a number of queries in one block using single command.
• 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.
• Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block.
• Reusable Code: Create stored procedures, functions, triggers, and packages, which can be executed repeatedly.
• Performance: Reduces network traffic by executing multiple SQL statements within a single 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.

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.

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.

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.

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.

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.

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.

Feature

Purpose

Nature

Execution

Use Case

Syntax

Data Handling

Performs actions directly on the database.

Can contain SQL inside its blocks and is used for more control over data handling

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4