PL/SQL Variables and Constants

Variables and constants are the most important lexical elements in any programming language. The ability to declare and use variables by PL/SQL is one of its advantages over SQL. We will learn to declare PL/SQL variables and constants and assign values.

PL/SQL Variables

Variables are also known as identifiers. They are representative names (identification) to memory locations. These memory locations hold the values to be used in a program. Variables can only be declared in DECLARE section in PL/SQL Blocks. Before you use any variable in PL/SQL for data manipulation or evaluation of an expression, you need to declare the required variables.  

Syntax of variable declaration

Variable-name datatype [:= initial-value];

variable-name is the name of the variable given by the programmer. A variable name cannot start with a number. It can begin only with an alphabet (A-Z, a-z). It may contain numbers or underscore (_). It is always preferable to name a variable in such a way so that its purpose in the program becomes obvious to anyone who is maintaining the code.

datatype is the type of value that can be stored in the variable. Refer to the details here

initial-value is the value that is assigned to the variable at the time of declaration. This is assigned with assignment operator :=. If you want to assign initial value while executing the code use  := &variable-name.

Example variable declaration

DOB  DATE;

count NUMBER:=10

start_value NUMBER :=& start_value;

Assigning value to variable in BEGIN Section

Once the variables are declared in DECLARE section they can be assigned a value in BEGIN section. It ca be done in three ways

Using assignment operator

The assignment operator := is used to assign value to a previously declared variable.

count:=50;

DOB=’01-jan-17’;

Using substitution variables

This method is used when you don’t want to hard code the value to a variable. Rather you want to give values to variables at the time of execution. You don’t need to declare a substitution variable.

start_value:=& start_value;

Using SELECT statement

You can also assign values to variables using the SELECT statement. It will allow you to fetch column values from a table. You have to ensure that the select statement returns a single row.

DECLARE

v_empno NUMBER;

v_empname VARCHAR2(30);

BEGIN

SELECT employee_id, employee_name INTO  v_empno, v_empname FROM employees WHERE employees_id=202;

DBMS_OUTPUT.PUT_LINE(“ Employee ID” || v_empno||” is “||v_empname);

END;

PL/SQL Constants

It is always better to declare/ define values that remain constant in a block instead of using the value itself. In PL/SQL constants are the named values that cannot be modified in the BEGIN section using assignment operator. If you do so, the code will generate an error.

Syntax of constant declaration

Constant-name CONSTANT datatype:= value;

Constant-name is the name of the constant by which you want to use a value in your code. The naming rules of a constant are the same as variable naming. For clarity some programmers prefer to name constants in uppercase.

CONSTANT is the mandatory keyword

datatype is the type of value that can be stored in the constant. Refer to the details here

value  is the value to be assigned to the constant at the time of declaration. This is assigned with assignment operator :=. A constant cannot be modified in the BEGIN section of the block at runtime.

Example of a PL/SQL constant

BEGIN_DATE CONSTANT date:=’01-MAR-2020’;

DEPTT_DEFAULT VARCHAR2(20):=’Human Resource’;

This way you can declare PL/SQL variables and constants in your code for better manipulation of data.

Be First to Comment

Leave a Reply

Your email address will not be published.