PL/SQL Variables
PL/SQL Variables
A variable is a meaningful name which facilitates a programmer to store data temporarily during the execution of code. It helps you to manipulate data in PL/SQL programs. It is nothing except a name given to a storage area. Each variable in the PL/SQL has a specific data type which defines the size and layout of the variable's memory.
A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs, numerals, underscore etc.
It needs to declare the variable first in the declaration section of a PL/SQL block before using it.
By default, variable names are not case sensitive. A reserved PL/SQL keyword cannot be used as a variable name.
How to declare variable in PL/SQL
You must declare the PL/SQL variable in the declaration section or in a package as a global variable. After the declaration, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name.
Syntax for declaring variable:
Following is the syntax for declaring variable:
Here, variable_name is a valid identifier in PL/SQL and datatype must be valid PL/SQL data type. A data type with size, scale or precision limit is called a constrained declaration. The constrained declaration needs less memory than unconstrained declaration.
Example:
Radius Number := 5;
Date_of_birth date;
Declaration Restrictions:
In PL/SQL while declaring the variable some restrictions hold.
- Forward references are not allowed i.e. you must declare a constant or variable before referencing it in another statement even if it is a declarative statement.
val number := Total - 200;
Total number := 1000;
The first declaration is illegal because the TOTAL variable must be declared before using it in an assignment expression. - Variables belonging to the same datatype cannot be declared in the same statement.
N1, N2, N3 Number;
It is an illegal declaration.
Naming rules for PL/SQL variables
The variable in PL/SQL must follow some naming rules like other programming languages.
- The variable_name should not exceed 30 characters.
- Variable name should not be the same as the table table's column of that block.
- The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so it could be either lowercase or uppercase. For example: v_data and V_DATA refer to the same variables.
- You should make your variable easy to read and understand, after the first character, it may be any number, underscore (_) or dollar sign ($).
- NOT NULL is an optional specification on the variable.
Initializing Variables in PL/SQL
Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a variable with other value than NULL value, you can do so during the declaration, by using any one of the following methods.
- The DEFAULT keyword
- The assignment operator
You can also specify NOT NULL constraint to avoid NULL value. If you specify the NOT NULL constraint, you must assign an initial value for that variable.
You must have a good programming skill to initialize variable properly otherwise, sometimes program would produce unexpected result.
Example of initilizing variable
Let's take a simple example to explain it well:
After the execution, this will produce the following result:
Value of c: 70 Value of f: 33.333333333333333333 PL/SQL procedure successfully completed.
Variable Scope in PL/SQL:
PL/SQL allows nesting of blocks. A program block can contain another inner block. If you declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope:
- Local Variable: Local variables are the inner block variables which are not accessible to outer blocks.
- Global Variable: Global variables are declared in outermost block.
Example of Local and Global variables
Let's take an example to show the usage of Local and Global variables in its simple form:
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
After the execution, this will produce the following result:
Outer Variable num1: 95 Outer Variable num2: 85 Inner Variable num1: 195 Inner Variable num2: 185 PL/SQL procedure successfully completed.
Variable Attributes:
When you declare a PL/SQL variable to hold the column values, it must be of correct data types and precision, otherwise error will occur on execution. Rather than hard coding the data type and precision of a variable. PL/SQL provides the facility to declare a variable without having to specify a particular data type using %TYPE and %ROWTYPE attributes. These two attributes allow us to specify a variable and have that variable data type be defined by a table/view column or a PL/SQL package variable.
A % sign servers as the attribute indicator. This method of declaring variables has an advantage as the user is not concerned with writing and maintaining code.
Following are the types of Variable Attributes in PL/SQL.
- %TYPE:
The %TYPE attribute is used to declare variables according to the already declared variable or database column. It is used when you are declaring an individual variable, not a record. The data type and precision of the variable declared using %TYPE attribute is the same as that of the column that is referred from a given table. This is particularly useful when declaring variables that will hold database values. When using the %TYPE keyword, the name of the columns and the table to which the variable will correspond must be known to the user. These are then prefixed with the variable name. If some previously declared variable is referred then prefix that variable name to the %TYPE attribute.
The syntax for declaring a variable with %TYPE is:
- .%TYPE;
Where is the column defined in the .
Consider a declaration.
SALARY EMP.SAL % TYPE;
This declaration will declare a variable SALARY that has the same data type as column SAL of the EMP table.
Example:
DECLARE
SALARY EMP.SAL % TYPE;
ECODE EMP.empno % TYPE;
BEGIN
Ecode :=&Ecode;
Select SAL into SALARY from EMP where EMPNO = ECODE;
dbms_output.put_line('Salary of ' || ECODE || 'is = || salary');
END;
After the execution, this will produce the following result:
Enter value for ecode: 7499 Salary of 7499 is = 1600 PL/SQL procedure successfully completed.
- %ROWTYPE:
The %ROWTYPE attribute is used to declare a record type that represents a row in a table. The record can store an entire row or some specific data selected from the table. A column in a row and corresponding fields in a record have the same name and data types.
The syntax for declaring a variable with %ROWTYPE is:
- .ROW%TYPE;
Where is the variable defined in the .
Consider a declaration.
EMPLOYEE EMP. % ROW TYPE;
This declaration will declare a record named EMPLOYEE having fields with the same name and data types as that of columns in the EMP table. You can access the elements of EMPLOYEE record as
EMPLOYEE.SAL := 10000;
EMPLOYEE.ENAME := ‘KIRAN’;
Example:
DECLARE
EMPLOYEE EMP. % ROW TYPE;
BEGIN
EMPLOYEE.EMPNO := 2092;
5 EMPLOYEE.ENAME := 'Sanju';
Insert into EMP where (EMPNO, ENAME) Values (employee.empno, employee.ename);
dbms_output.put_line('Row Inserted');
END;
After the execution, this will produce the following result:
Row Inserted PL/SQL procedure successfully completed.
Advantages:
- If you don’t know the data type at the time of declaration. The data type assigned to the associated variables will be determined dynamically at run time.
- If the data type of the variable you are referencing changes the %TYPE or %ROWTYPE variable changes at run time without having to rewrite variable declarations. For example: if the ENAME column of an EMP table is changed from a VARCHAR2(10) to VRACHAR2(15) then you don’t need to modify the PL/SQL code.
