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.
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.
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:
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.
:= is an assignment operator used to assign a value to a variable.(We will learn about various operators in the next tutorial)
Following are some important rules to keep in mind while defining and using a variable in PL/SQL:
a_varis not same as
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;
In the example above we have declared two variables
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.
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.
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.
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.
|Type of Literal||Explanation||Example|
|Numeric||It can be a positive or a negative number.||2,-5,10,-50|
|String||It is a collection of characters and enclosed in single quotes when used.||'Hello world'|
|Date||It is a date in DD-MON-YYYY format and always enclosed in single quotes when used in any program.||'25-nov-1995'|
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.