PL/SQL Variables

PL/SQL Variables

These are used to store the values that can change through the PL/SQL Block.

The Syntax to declare a variable is:
variable_name DATA TYPE [NOT NULL := value ];

  • variable_name is the name of the variable.
  • DATA TYPE is a valid PL/SQL data type.
  • NOT NULL is an optional specification on the variable.
  • Value or DEFAULT value is an optional specification, where you can initialize a variable.
  • Each variable declaration is a separate and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.

DECLARE
Salary number (6);

Here “Salary” is a variable of data type NUMBER and of length 6.

When a variable is specified as NOT NULL, you must initialize the variable when it is declared.

For example, the below example declares two variables, one of which is a not null.

DECLARE
Salary NUMBER(4);
Dept VARCHAR2(10) NOT NULL := “HR Dept”;

The value of a variable can change in the execution time. We can assign values to variables in the two ways given below.

1) We can directly assign values to variables.
The General Syntax is:
variable_name := value;

2) We can assign values to variables directly from the database columns by using a SELECT..INTO statement.

The General Syntax is:

SELECT column_name
+++++ INTO variable_name 
+++++ FROM table_name 
+++++ [WHERE condition];

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.

DECLARE 
var_salary number(6); 
var_emp_id number(6) = 1116; 
BEGIN
SELECT salary 
INTO var_salary 
FROM employee 
WHERE emp_id = var_emp_id; 
dbms_output.put_line(var_salary); 
dbms_output.put_line('The emp '  || var_emp_id || ' has  salary  ' || var_salary); 
END;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License