Table of contents
1.
Introduction
1.1.
Declaring variables in PL/SQL
1.2.
Initializing Variables in PL/SQL
1.3.
Variable Scope in PL/SQL
1.4.
Hello World program in PL/SQL
2.
FAQs
3.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Variables

Author Sanjana Yadav
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

A variable in PL/SQL is referred to as a storage location that holds a value of a certain data type. Throughout the program, the variable's value varies. A variable must be declared in the declaration section of a block before being used.

PL/SQL variables naming rules

  • Variables in PL/SQL, like variables in other programming languages, must be defined before they may be used. They must also have a valid name and data type.
  • A variable name cannot be longer than 30 characters.
  • Variable names must begin with an ASCII letter and end with a number, underscore (_), or dollar symbol ($).
  • Because PL/SQL is case-insensitive, var and VAR both refer to the same variable. 

Declaring variables in PL/SQL

A PL/SQL variable must be declared in the declaration section or in a package as a global variable. After the variable is declared, PL/SQL allocates memory for it, and the variable name is used to identify the storage location.

Syntax

variable_name datatype [NOT NULL] [:= initial_value];

In this syntax:

First, give the variable's name. The variable name should be as descriptive as possible.

Second, select a suitable data type for the variable based on the type of value you wish to store, such as integer, character, Boolean, or datetime.

Local variable names should begin with l_, while global variable names should begin with g_. 

Initializing Variables in PL/SQL

When we declare a variable in PL/SQL, the default value is NULL. We can initialize a variable with a non-NULL value during the declaration process. 

The assignment operator (:=) or the DEFAULT keyword is used to assign a default value to a variable:

DECLARE  
   l_site_name VARCHAR2( 100 ) := 'CodingNinjas';
BEGIN
   NULL;
END; 

It corresponds to the following block:

DECLARE
  I_site_name VARCHAR2(100) DEFAULT ‘CodingNinjas’;
BEGIN
  NULL;
END;

 

Must Read SQL Clauses

Variable Scope in PL/SQL

As we covered previously, PL/SQL permits blocks to be nested, i.e., blocks can be nested within blocks. PL/SQL variables can be classified into the following groups based on their nesting structure:

Local variables: Variables that are defined in an inner block but are not available to outer blocks.

Global variables: Variables that are defined in the outer block or package and are available to themselves and other inner blocks.

Example

DECLARE
  —- Global variables 
  g_numOne number := 30; 
  g_numTwo number := 40; 
BEGIN 
  dbms_output.put_line('Outer Variable g_numOne: ' || g_numOne);
  dbms_output.put_line('Outer Variable g_numTwo: ' || g_numTwo);
  DECLARE 
      -- Local variables
      l_numThree number := 50; 
      l_numFour number := 60; 
  BEGIN 
      dbms_output.put_line('Outer variable inside inner block g_numOne: ' || g_numOne);
      dbms_output.put_line('Outer variable inside inner block g_numTwo: ' || g_numTwo);
      dbms_output.put_line('Inner Variable l_numThree: ' ||  l_numThree);
      dbms_output.put_line('Inner Variable l_numFour: ' || l_numFour);
  END; 
END;

 

Output

Outer Variable g_numOne: 30
Outer Variable g_numTwo: 40
Outer variable inside inner block g_numOne: 30
Outer variable inside inner block g_numTwo: 40
Inner Variable l_numThree: 50
Inner Variable l_numFour: 60

Hello World program in PL/SQL

DECLARE
  -- variable declaration
    l_info(20):= 'Hello World...';
BEGIN
  --output
  dbms_output.put_line(l_info);
END;

Output

Hello World...

Also see, Tcl Commands in SQL

FAQs

  1. What are PL SQL variables?
    A variable is a meaningful name that allows a programmer to store data while running code temporarily. It facilitates data manipulation in PL/SQL systems. In PL/SQL, each variable has a data type that specifies the size and structure of the variable's memory.
     
  2. How do you declare a variable in PL SQL?
    Syntax. The syntax for declaring variables in Oracle is: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 
     
  3. What are the default variables in PL SQL?
    Variables are default initialized with NULL.
     
  4. What are the naming rules for the variables in PL SQL?
    The name of the variable must be fewer than 31 characters long. Within 31 characters, try to make it as significant as possible. An ASCII letter must be the first character in the variable name. It can be written in either lowercase or uppercase letters.
     
  5. In what part of PL SQL statements can we declare a variable?
    You can specify constants and variables in the declarative component of any PL/SQL block, subprogram, or package. Declarations give value storage, a datatype, and a name that may be referenced. In addition, declarations can set a starting value and enforce the NOT NULL requirement. 

Key Takeaways

  • Cheers if you reached here! In this blog, we learned about variables in PL/SQL.
  • We have covered the rules for declaring a variable in PL/SQL.
  • We have also seen how to initialize them with the help of an example.
  • Further, we learned to write a Hello World program using PL/SQL.

On the other hand, learning never ceases, and there is always more to learn. So, keep learning and keep growing, ninjas!

If you need more practice in DBMS and SQL, try these Top 100 SQL Problems asked in various companies.

With this fantastic course from CodingNinjas, you can make learning enjoyable and stress-free.

Good luck with your preparation!

Live masterclass