PL/SQL Variables, Constants and Literals

In our previous tutorial we covered PL/SQL datatypes, in this tutorial we will learn about PL/SQL variables, contants and literals. We will also write some simple PL/SQL blocks to see how we can define variables and use them.

What is an Identifier?

Before proceeding ahead, let us understand what identifiers are. You are identified by your name, hence your name is your identifier. Similarly in programming we use identifiers to name our data which can be any form of data.

PL/SQL Variables

A variable is a reserved memory area for storing the data of a particular datatype. It is an identifier which is identifies memory locations where data is stored. This memory is reserved at the time of declaration of a variable which is done in the DECLARE section of any PL/SQL block.

Syntax for declaration of a variable:

Variable_name  datatype(size);

Let's take a simple example of how we can define a variable in PL/SQL,

roll_no NUMBER(2);

a int;

And if we want to assign some value to the variable at the time of declaration itself, the syntax would be,

Variable_name datatype(size) NOT NULL:=value;

Let's take a simple example for this too,

eid NUMBER(2) NOT NULL := 5;

In the PL/SQL code above, we have defined a variable with name eid which is of datatype NUMBER and can hold a number of length 2 bytes, which means it can hold a number upto 99(because 100 onwards we have 3 digits) and the default value for this variable is 5.

The keyword NOT NULL indicates that eid cannot be a blank field.

Here, := is an assignment operator used to assign a value to a variable.(We will learn about various operators in the next tutorial)

Rules for declaring a Variable in PL/SQL

Following are some important rules to keep in mind while defining and using a variable in PL/SQL:

1. A variable name is user-defined. It should begin with a character and can be followed by maximum of 29 characters.
2. Keywords (i.e, reserved words ) of PL/SQL cannot be used as variable name.
3. Multiple variables can be declared in a single line provided they must be separated from each other by at least one space and comma.

   For eg: a,b,c int;

4. Variable names containing two words should not contain space between them. It must be covered by underscore instead.

   For eg: Roll_no

5. A variable name is case sensitive, which means a_var is not same as A_var

Time for an Example!

Let's take a simple example to see how we can declare a variable and use it,

set serveroutput on;
DECLARE
	a NUMBER(2);
	b NUMBER(2) := 5;
BEGIN
	a := b;
	dbms_output.put_line(a);
END;

5

In the example above we have declared two variables a and b and we have assigned value to the variable b, then in the BEGIN block, we assign the value of variable b to the variable a and then print it's value on console.

PL/SQL Constants

Constants are those values which when declared remain fixed throughout the PL/SQL block. For declaring constants, a constant keyword is used.

Syntax for declaring constants:

Constant_Name  constant  Datatype(size) := value;

Let's take a simple code example,

school_name constant VARCHAR2(20) := "DPS";

In the above code example, constant name is user defined followed by a keyword constant and then we have declared its value, which once declared cannot be changed.

Time for an Example!

Below we have a simple program to demonstrate the use of constants in PL/SQL,

set serveroutput on;
DECLARE
	school_name constant varchar2(20) := "DPS";
BEGIN
	dbms_output.put_line('I study in '|| school_name);
END;

I study in DPS PL/SQL Procedure successfully completed.

PL/SQL Literals

A literal is a value that is expressed by itself and are generally constant. For example, if your name is Alex, then for you Alex is a literal(the value which is constant). In other words, the value that is declared as a constant in a program is said to be literal. A literal can be numeric, string or a date.

Time for an Example!

Here we have a simple program to demonstrate the use of literals in PL/SQL,

set serveroutput on;
DECLARE
	str varchar2(20):= 'Welcome to Studytonight.com';
BEGIN
	dbms_output.put_line(str);
END;

Welcome to Studytonight.com

In the next tutorial we will learn about operators in PL/SQL.