Description
Variables are used to manipulate and store the data in the database. All PLSQL Variables contains a specific datatype that describes the layout and size of the variable. The spectrum of esteems that is stored inside the collections and performs certain operations on those variables.
The PLSQL Variables consisting of optional letters succeeded by dollar signs, numerals, letters, number signs and underscores doesn't exceed more than 30 characters. These naming variables are not case sensitive by default. And, various type of variables can be defined in programming language of PL/SQL that contains collection of records, time and date datatype.
The initialization and declaration of PLSQL Variables will be declared inside the block declarations in PL/SQL block. The variable that is declared in PL/SQL will also be utilized in procedural and SQL statements.
- Stored values manipulation
- Reliability
- Storage of information on temporary basis
- Handling variables in PL/SQL contain declaration and executable section.
- In declaration section, variables will be declared and initialized.
- In executable section, it utilizes and assigns new values.
- In PL/SQL, variables can be passed as parameters to the sub-programs of PL/SQL and the declaration of variables holds the output of the sub-program.
Description
Each and every variable in PL/SQL should be declared in the declaration section. The key function of the declaration section is to store a value in the storage space determining the name and data type. In the declaration section, the variable can be declared under any sub-program, package or PL/SQL block.
Syntax
identifier
[CONSTANT] datatype [NOT NULL]
[:=expr | DEFAULT expr];
Identifier => Name of the variable
Constant => Variable value must be initialized
Datatype => May be composite, LOB and scaler data type
Not Null => Is a constrain that contains values
Expr => Any expression in pl/sql
Examples
The below example illustrates how to declare and initialize variables[c]SQL> DECLARE
2 v_emp_hiredate DATE;
3 v_emp_deptno NUMBER(2) NOT NULL := 10;
4 v_location VARCHAR2(13):= 'Atlanta';
5 c_comm CONSTANT NUMBER : =1500;
6 v_population INTEGER;
7 v_firstname VARCHAR2(20):= 'RAJIV';
8 v_lastname VARCHAR2(20):= 'KUMAR';[/c]
Description
All variables will be declared as null by default. One can insert a variable value without assigning a null value in the declaration section using the default keyword and assignment operator.
A variable that shouldn't contain null values can be specified by performing NOT NULL constraint, where assigning values and initialization of values can be done explicitly on any variable. If the variable initialization is not done properly, then it will give unexpected output.
Examples
The following example illustrates initializing aPL/SQL variable.
[c]SQL> Declare
2 a integer :=10;
3 b integer :=20;
4 c integer;
5 f real;
6 BEGIN
7 c:=a+b;
8 dbms_output.put_line('value of c:'||c);
9 f:=80.0/3.0;
10 dbms_output.put_line('value of f:'||f);
11 END;
12 /
PL/SQL procedure successfully completed.
value of c : 30
value of f : 23.3333[/c]
Description
Nesting of blocks will be allowed under PL/SQL i.e, all the PL/SQL program blocks will contain another inner block. Suppose, in the inner block a variable is declared, it cannot be accessed by the outer block. If any variable is accessed and declared in the outer block, it can be operated in every nested inner block. And, variable scope is of 2 types-local and global variables.
Local variables cannot be accessed in the outer block and can be accessed in the inner block.
Global variables will be declared in the outer most package.
Key Points
- PLSQL Variables - Is a temporary storage location in the PL/SQL.
- Declaration and initialization variables - All the variables must be declared in the declaration block.
- Initializing of variable - Initializing the variable other than null values.
- Variable scope - Defines the scope of a variable in inner and outer blocks.