Variables And Constants In Oracle
Introduction
Variables are used to store the result of a query or calculation. Variables must be declared before use. Variable can also be declared, using the row attributes of a table % Row_type and %type.
Declaring variables and constants
Declaration allocates the storage space for a value, specify the data type and name of the storage location, so that the value can be referenced. All statements must end must with a semi colon.
Initial values can also be assigned to a variable at the time of declaration. To assign a value to a variable, the assignment operator ‘:=’ is used.
Constants are declared by specifying the keyword constant before the data type.
Syntax
- <var-name> <type> [:=<value>];
Example
- Emp_name char(20);
- Emp_ID number(20) := 10;
- Dept_no number(20) := 15;
Using Default
The reserved word default can be used, instead of the assignment operator to initialize the variables and constants.
Example
- Dept_no number(20) := 10;
- Pincode constant number(6) := 246761;
Rewritten other types
- Dept_no number(20) Default 10;
- Pincode constant number(6) Default 246761;
This attribute provides a records type, which represents a row in a table. The record can store an entire row of the data, selected from the table or fetched by a cursor.
A %Rowtype declaration cannot include an initialization clause.
Example
- Employee_Row_VAR Employee%RowType;
The attribute %Type is used to avoid the type and size conflict between a variable and the column of a table. The method of defining a variable is whenever the type and size of a column in the table is changed, this is the main advantage of this attribute.
It is automatically reflected in the variable declaration.
Example
- Temp_name Employee.Emp_name%Type;
Thus, we learnt, Variables are used to store the result of a query or a calculation.