Declaring Variables in SQL

Declaring Variables in SQL

We can declare a variable of any data type either native to the ORACLE or native to PL/SQL. Variables are declared in the DECLARE section of the PL/SQL block. Declaration involves the name of the variable followed by its data type. All statement must end with a semicolon (;) which is the delimiter in PL/SQL. To assign a value to the variable the assignment operator (:=) is used.
The general syntax is <Variable name><type>  [:=<value];
Example:   pay    NUMBER(6,2);
in_stack  BOOLEAN;
name    VARCHAR2 (30);
room     CHAR(2);
date_of_purchase    DATE;

Assigning a value to a variable

A value can be assigned to the variable in any one of the following two ways.

  • Using the assignment operator :=
Ex: tax:= price*tax_rate
Pay := basic +da.
  • Selecting or fetching table data values in to variables.
Ex:  SELECT sal INTO pay
FROM Employee
WHERE emp_name=’SMITH’;

 Declaring a constant

Declaring a constant is similar to declaring a variable except that you have to add the key word CONSTANT and immediately assign a value to it. Thereafter, no further assignment to the constant is possible.
Example: pf_percent CONSTANT   NUMBER (3,2):=8.33;

An Identifier in PL/SQL block

The name of any ORACLE object (variable, memory variable, constant, record, cursor etc) is known as an Identifier. The following laws have to be followed while working with identifiers.

  • An identifier cannot be declared twice in the same block
  • The same identifier can be declared in two different blocks
In the second law, the two identifiers are unique and any change in one does not affect the other.

Leave a Reply

Your email address will not be published.