PL/SQL variable is a relevant name which provide a storage capability during the execution of code. Every PL/SQL variables has a specific data type which defines the size and physical location of the variable memory. Moreover you can specifies initial value of the variable at the time of variable declaration.
You must have to declare the PL/SQL variable in the declaration section. And variable name should not more than 31 characters. Variable name must be begin with ASCII letter. It can be either in uppercase or lowercase.
Followed by the first character are numerals, underscore, and dollar sign characters allowed.
PL/SQL is case-insensitive. It means variable name
length refer as the same variable.
Syntax for declaring variable
The general syntax to declaring variable:
variable_name Datatype[Size] [NOT NULL] := [ value ];
- variable_name is the predefined name of the variable.
- Data type is a valid PL/SQL data type.
- Size is an optional specification of data type size to hold the maximum size value.
- NOT NULL is an optional specification of the variable value can't be accept NULL.
- value is also an optional specification, where you can initialize the initial value of variable.
- Each variable declaration is terminated by a semicolon.
Example of Variable Declaration
In this example variable defining employee number (eno) is NOT NULL(compulsory), employee name and initializing initial value to a variable,
DECLARE eno number(5) NOT NULL := 2 -- NOT NULL (value can't be blank), Assign initial value ename varchar2(15) := 'Branson Devs'; -- intialize value at the time of declaration BEGIN dbms_output.put_line('Declared Value:'); dbms_output.put_line(' Employee Number: ' || eno || ' Employee Name: ' || ename); END; /
Backward slash '/' indicates to execute the above PL/SQL Program.
Employee Number: 2 Employee Name: Branson Devs
Placeholders are any of variables, constants or records to store temporary in storage area. Later you can use it to manipulate data during the execution of a PL/SQL block. You can define placeholders with the name and data type. Here are some data types to define placeholders.
NUMBER(p,s), NUMBER(n), CHAR, VARCHAR2, DATE, LONG, LONG RAW, BLOB, CBLOB, BFILE
PL/SQL variable scope is identified the region range which you can reference the variable. PL/SQL have two type scopes local scope and global scope,
Local variables - Variables declared in inner block and can't be referenced by the outside blocks.
Global variables - Where as variables declared in a outer block and can be referencing by itself in inner blocks.
Variable Scope Example
In this example declaration two variables (num1 and num2) are in the outer block (Global variable) and another one third variable declared (num_addition) into the inner block (local variable). Variable 'num_addition' declared inner block so can't access in the outer block. But num1 and num2 can be accessed anywhere in the block.
DECLARE num1 number := 10; num2 number := 20; BEGIN DECLARE num_addition number; BEGIN num_addition := num1 + num2; dbms_output.put_line('Addition is: ' || num_addition); END; -- End of access num_addition variable END; /
Variable Scope Identifier (OUTER keyword)
This example is also showing a difference between inner block and outer block variable scope. You can use OUTER keyword to access outer block variable inside the inner block. It's called global qualified name space.
DECLARE num number := 10; BEGIN DECLARE num number := 10; BEGIN IF num = OUTER.num THEN DBMS_OUTPUT.PUT_LINE('Both are same value'); ELSE DBMS_OUTPUT.PUT_LINE('Different value'); END IF; END; -- End of scope to access num variable END; /