PL/SQL Variables and Constants

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

PL/SQL Variables

Variables can be any SQL data type such as CHAR, DATE, or NUMBER, or a type of date PL/SQL such as BOOLEAN or PLS_INTEGER.

These are placeholders that store the values that can change through the PL/SQL Block.
--------------------------------------------------------------------------------
Naming rules for Variables in PL SQL:
--------------------------------------------------------------------------------
The variable name must be less than 31 characters.

The starting of a variable must be an ASCII letter. It can be either lowercase or uppercase.

A variable name can contain numbers, underscore, and dollar sign characters followed by the first character.

Make them meaningful to understand to make it easier to maintain in the future.
---------------------------------------------------------------------------------

PL/SQL Variable Declaration


The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ];

variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT valueis also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.

1. Declaring Variables in PL/SQL

DECLARE
part_no NUMBER (6);
part_name VARCHAR2 (20);
in_stock BOOLEAN;
part_price NUMBER (6.2);
part_desc VARCHAR2 (50);

2. Assigning variables using the := operator

DECLARE
hours_worked NUMBER: = 40;
hourly_salary NUMBER: = 22.50;
bonus NUMBER: = 150;
country VARCHAR2 (128);
counter NUMBER: = 0;
done BOOLEAN;
emp_rec1 employees% ROWTYPE;
emp_rec2 employees% ROWTYPE;
BEGIN
wages: = (hours_worked * hourly_salary) + bonus;
country: = ' Italy ';
country: = UPPER (' Spain ');
done: = (counter > 100);
emp_rec1.: first_name = ' John ';
emp_rec1. last_name: = ' Davis ';
emp_rec1: = emp_rec2;
END;

3. Assigning values to a variable using Select Into

DECLARE
bonus NUMBER (8,2);
emp_id NUMBER (6): = 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

4. Assigning Value to a Variable as a parameter of a subroutine

DECLARE
new_sal NUMBER (8,2);
emp_id NUMBER (6): = 126;
PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
emp_job VARCHAR2 (10);
avg_sal NUMBER (8,2);
BEGIN
SELECT job_id INTO emp_job FROM employees WHERE emp_id = employee_id;
SELECT AVG (salary) INTO avg_sal FROM employees WHERE job_id = emp_job;
DBMS_OUTPUT.PUT_LINE (' The average salary for ' emp_job || || ' employees: ' || TO_CHAR (avg_sal));
sal: = (sal + avg_sal)/2; --adjust sal value which is returned
END;
BEGIN
SELECT AVG (salary) INTO new_sal FROM employees;
DBMS_OUTPUT.PUT_LINE (' The average salary for all employees: ' || TO_CHAR (new_sal));
adjust_salary (emp_id, new_sal); --assigns a new value to new_sal
DBMS_OUTPUT.PUT_LINE (' The adjusted salary for employee ' || TO_CHAR (emp_id) || ' is ' || TO_CHAR (new_sal)); — sal has new value
END;

Constant
constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

The General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE
Salary_increase CONSTANT number (5) := 20;