Declarations

PL/SQL  program stores values in variables and constants.You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package.

Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it.
As the program executes, the values of variables can change, but the values of constants cannot.

Declaring Variables

To declare simple variable, the format is:

VARIABLE_NAME DATATYPE;

Some examples follow:

DECLARE
v_emp_name VARCHAR2(10);
v_emp_number NUMBER(10);
v_emp_bdate DATE;
BEGIN
–some executable statments using these variables

END;

Assigning values to Variables

To declare some variables and assign values we use := (the assignment operator)

The format is

VARIABLE_NAME DATATYPE := VARIABLE_VALUE;

Some examples follow:

DECLARE
v_emp_name VARCHAR2(20) := ‘PETER’;
v_emp_number NUMBER(10) := 1899891;
BEGIN
–some executable statments using these variables
END;

When the program executes, the values of variables may change.In the below example value of v_emp_number 0 in Declaration,As the program executes the value of v_emp_number change to a value which stored in the database associated with the employee name ‘PETER’

DECLARE
v_emp_name VARCHAR2(20) := ‘PETER’;
v_emp_number NUMBER(10) := 0;
BEGIN
– This Query find employee number associated with the name ‘PETER’ and stores it into variable v_emp_number
SELECT emp_number into v_emp_number
FROM employees
WHERE emp_name=’PETER’;

–This prints the output to the user screen
DBMS_OUTPUT.PUT_LINE(v_emp_number);
END;

OUTPUT
————-
1899891

Declaring constant Variables

A constant variable is a variable that is assigned a value during the declaration using the CONSTANT keyword. A constant variable maintains the same value throughout the life of the procedure. You cannot change the value of a constant outside the declaration. If you try to change the value of a constant outside the declaration section, you will get an error.

To declare a constant,Format is:

VARIABLE_NAME CONSTANT DATATYPE := VARIABLE_VALUE;

Some examples follow:

DECLARE
v_message CONSTANT VARCHAR2(10) := ‘Hello World’;
v_emp_number CONSTANT NUMBER := 123;
BEGIN
–some executable statments that use these variables
END;