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.
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
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.
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.
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.
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);
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.