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
-
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.
-
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]
-
What are the default variables in PL SQL?
Variables are default initialized with NULL.
-
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.
-
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.