PL/SQL Variables

A variable is nothing more than a name given to a particular area in memory that stores a value created by a PL/SQL block. Each variable is assigned a specific type, which limits what values can be stored in it, for example, an integer, a decimal or floating-point number, or a string. Once created the value of a variable can be changed. All variables within a PL/SQL block must be declared within the declarative section before they can be used.

The name of a variable must start with a letter and can then be followed by more letters, numbers, dollar signs, underscores or number signs. Variable names are by default not case sensitive and must not exceed 30 characters. They must also not be the same as a PL/SQL reserved word, for example, it is not possible to give a variable a name of ‘begin’.

The basic datatypes in PL/SQL are as follows:

  • NUMBER – Used for storing numerical data.
  • CHAR – Used for storing fix length character data.
  • VARCHAR2 – Used for storing variable length character data.
  • DATE – Used for storing date values.
  • BOOLEAN – Used for storing TRUE, FALSE or NULL.

A link to a full list of datatypes can be found in the ‘Further Resources‘ section.

In its simplest form, a variable declaration contains its name and datatype, followed by a semicolon.

v1 NUMBER(2);
v2 CHAR(3);
v3 VARCHAR2(30);
v4 DATE;
v5 BOOLEAN;

All these variables are not initialised so contain null values. Notice that NUMBER, CHAR, and VARCHAR2 have numbers in brackets after the type. These refer to the size of the value that can be stored in the variable. Variable ‘v1’, a NUMBER variable, can store numbers up to and including 99 because its size has been specified as two. Variable ‘v2’, a CHAR variable, has a fixed size of three characters. Finally, variable ‘v3’, a VARCHAR2 variable, has a maximum size of 30 characters.

The NUMBER datatype need not just be used to store whole numbers. The following variable declaration specifies that the size is seven, comprising of up to five numbers before the decimal point and two after.

v6 NUMBER(7,2);

Initialise a variable can also be done at the point of declaration. Below are all the above variable declarations re-written to include an initial value.

v1 NUMBER(2) := 4;
v2 CHAR(3) := 'ABC';
v3 VARCHAR2(30) := 'stuartsplace.com';
v4 DATE := '01-JAN-01';
v5 BOOLEAN := FALSE;
v6 NUMBER(7,2) := 20.5;

It is also possible to specify that a variable must always contain a value other than null and therefore must be initialised. In this instance, the declaration for variable ‘v1’ can be re-written as follows.

v1 NUMBER(2) NOT NULL := 4;

In order to update the value of variables in the executable section of a PL/SQL block, or indeed to initialise them if this didn’t occur at the time of declaration, the following statements can be used.

v1 := 4;
v2 := 'ABC';
v3 := 'stuartsplace.com';
v4 := '01-JAN-01';
v5 := FALSE;
v6 := 20.5;

Variables to Store Data from Database Tables

When declaring variables to store data from a database table, the best way to get the datatype correct is to use ‘%TYPE’ in the declaration. The big advantage of this is that if the datatype changes in the database, nothing needs to be changed in the PL/SQL.

The following table definition and data can be used to demonstrate the use of ‘%TYPE’ and how the variables can then be used.

employee
emp_id NUMBER(10)
fname VARCHAR2(20)
sname VARCHAR2(20)
title VARCHAR2(10)
dob DATE
emp_id fname sname title dob
1 Fred Bloggs Mr 05-MAY-80
2 Simon Smith Mr 01-APR-60
3 Freida Bloggs Mrs 12-OCT-70
4 Fiona Jones Miss 19-MAY-85

The example PL/SQL block below declares five variables to hold each piece of information about an employee and initialises them with the values from the record where the ’emp_id’ is equal to 2. This is done using the ‘SELECT-INTO’ statement, which is the same as a normal SQL statement, but with an extra ‘INTO’ clause to specify the variable names to store the values. The variables are then displayed on the screen, with descriptive text before each. Notice how the variable datatypes are set to be the same as the table attribute datatypes.

DECLARE

   e_emp_id employee.emp_id%TYPE;
   e_fname employee.fname%TYPE;
   e_sname employee.sname%TYPE;
   e_title employee.title%TYPE;
   e_dob employee.dob%TYPE;

BEGIN
 
   SELECT emp_id, fname, sname, title, dob
   INTO e_emp_id, e_fname, e_sname, e_title, e_dob
   FROM employee
   WHERE emp_id=2;
   DBMS_OUTPUT.PUT_LINE ('Employees id: '||e_emp_id);
   DBMS_OUTPUT.PUT_LINE ('First Name: '||e_fname);
   DBMS_OUTPUT.PUT_LINE ('Surname: '||e_sname);
   DBMS_OUTPUT.PUT_LINE ('Title: '||e_title);
   DBMS_OUTPUT.PUT_LINE ('Date of Birth: '||e_dob);

END;

The output from the above will be as follows.

Employee id: 2
First Name: Simon
Surname: Smith
Title: Mr
Date of Birth: 01-APR-60