FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');


The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing 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;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.