What is Oracle PLSQL Variable

In this post we will discuss about PLSQL Variables in Oracle, Types of Variable, how to Declare variable in Oracle procedure, how to Declare variable in Oracle stored procedure, Scope of plsql Variables

Table of Contents

Oracle PLSQL Variable

Oracle PLSQL Variable are just like any other procedural language and it is used to store the result of a query or calculation
Variables must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.

How to declare a variable in Oracle procedure

All variables must be declared before they are referenced.
Declaration involves the name of the variable followed by its data type.
Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.

%TYPE – allows for unknown column datatypes when setting a variable. That is used to declare a variable that is of the same type as a specified table’s column. The advantage of this method of defining a variable is that, whenever the type and/or size of a column in the table is changed, it is automatically reflected in the variable declaration
The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table
BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.

( : ) is used to prefix a bind variable
By default, Oracle PLSQL variables are initialized as Null. Unless you explicitly assigned a value to the variable, it is undefined
We can have Not Null on a variable, then we must assign some value to it. We can change the value of that variable but we cannot make it null

We can initialize variables using the assignment operator (:=) or using Default

General Syntax

variable_name datatype [NOT NULL := value ];

(1) variable_name is the name of the variable.

(2) datatype is a valid PL/SQL datatype.

Example of declaring Variables:

v_name varchar2(100); 
creation_date Date;
v_num number default 5;
v_num1 number not null := 2;
v_emp varchar2(20) := Null;
Var5 varchar2(20) default Null;
V_DEPT   NUMBER (6) DEFAULT 160;

Examples as Full Procedure

DECLARE 
  l_ret     boolean; 
  l_user_id number; 
BEGIN
  select user_id 
    into l_user_id 
    from fnd_user 
   where user_name = '&USER_NAME'; 

  l_ret := fnd_profile.DELETE(X_NAME        => 'APPS_SERVLET_AGENT', 
                              X_LEVEL_NAME  => 'USER', 
                              X_LEVEL_VALUE => l_user_id); 
  commit; 

  dbms_output.put_line('Profile has erased successfully'); 

EXCEPTION 
  when others then 
    dbms_output.put_line('Failed to erase the profile: '||sqlerrm); 
END;

/

Scope of Oracle PLSQL Variable

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

Local variables – These are declared in an inner block and cannot be referenced by outside Blocks.

Global variables – These are declared in an outer block and can be referenced by themselves and by its inner blocks.

Types of variables in plsql

1) Scalar datatype: It holds a single value. The main datatypes are the datatypes which are supported as column types in the Oracle server. PLSQL also support Boolean type

2) Composite type: These are records and allow groups of the field to be defined and manipulated in PLSQL. Composite datatypes also known as collections are table, record, Varray and nested table. Composite datatype has internal components which can be manipulated individually

Related links

Oracle PLSQL records: Check out this article on the working of oracle plsql records. Also, find out the various ways to define it and assign value to it
PLSQL Tables: Check out this post for a detailed description of PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
What is cursor in oracle: Check out this post to get a great overview of What is cursor in oracle. Details about Cursor parameter, cursor loop, cursor for an update
PLSQL Constants : Check out this post on Oracle PLSQL Constants, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure
Oracle PLSQL documentation
Oracle PlSQL interview question