PL/SQL - SPLessons

PL/SQL Variables

Chapter 7

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Variables

PL/SQL Variables

shape Description

Variables are used to manipulate and store the data in the database. All PLSQL Variables contains a specific datatype that describes the layout and size of the variable. The spectrum of esteems that is stored inside the collections and performs certain operations on those variables.

The PLSQL Variables consisting of optional letters succeeded by dollar signs, numerals, letters, number signs and underscores doesn’t exceed more than 30 characters. These naming variables are not case sensitive by default. And, various type of variables can be defined in programming language of PL/SQL that contains collection of records, time and date datatype.

The initialization and declaration of PLSQL Variables will be declared inside the block declarations in PL/SQL block. The variable that is declared in PL/SQL will also be utilized in procedural and SQL statements.

uses of variables in PL/SQL

Variable handling in PL/SQL

Declaring and initializing variables in PL/SQL

shape Description

Each and every variable in PL/SQL should be declared in the declaration section. The key function of the declaration section is to store a value in the storage space determining the name and data type. In the declaration section, the variable can be declared under any sub-program, package or PL/SQL block.

shape Syntax

identifier

[CONSTANT] datatype [NOT NULL]
[:=expr | DEFAULT expr];

Identifier => Name of the variable
Constant => Variable value must be initialized
Datatype => May be composite, LOB and scaler data type
Not Null => Is a constrain that contains values
Expr => Any expression in pl/sql

shape Examples

The below example illustrates how to declare and initialize variables

SQL> DECLARE
2 v_emp_hiredate DATE;
3 v_emp_deptno NUMBER(2) NOT NULL := 10;
4 v_location VARCHAR2(13):= 'Atlanta';
5 c_comm CONSTANT NUMBER : =1500;
6 v_population INTEGER;
7 v_firstname VARCHAR2(20):= 'RAJIV';
8 v_lastname VARCHAR2(20):= 'KUMAR';

Initializing variables in PL/SQL

shape Description


All variables will be declared as null by default. One can insert a variable value without assigning a null value in the declaration section using the default keyword and assignment operator.

A variable that shouldn’t contain null values can be specified by performing NOT NULL constraint, where assigning values and initialization of values can be done explicitly on any variable. If the variable initialization is not done properly, then it will give unexpected output. 

shape Examples

The following example illustrates initializing aPL/SQL variable.

SQL> Declare
  2  a integer :=10;
  3  b integer :=20;
  4  c integer;
  5  f real;
  6  BEGIN
  7  c:=a+b;
  8  dbms_output.put_line('value of c:'||c);
  9  f:=80.0/3.0;
 10  dbms_output.put_line('value of f:'||f);
 11  END;
 12  /

PL/SQL procedure successfully completed.
value of c : 30
value of f : 23.3333

variable scope in PL/SQL

shape Description

Nesting of blocks will be allowed under PL/SQL i.e, all the PL/SQL program blocks will contain another inner block. Suppose, in the inner block a variable is declared, it cannot be accessed by the outer block. If any variable is accessed and declared in the outer block, it can be operated in every nested inner block. And, variable scope is of 2 types-local and global variables.

Local variables

Local variables cannot be accessed in the outer block and can be accessed in the inner block.

Global variables

Global variables will be declared in the outer most package.

Summary

shape Key Points

  • PLSQL Variables – Is a temporary storage location in the PL/SQL.
  • Declaration and initialization variables – All the variables must be declared in the declaration block.
  • Initializing of variable – Initializing the variable other than null values.
  • Variable scope – Defines the scope of a variable in inner and outer blocks.