Placeholders are temporary storage area. PL/SQL Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Define PL/SQL Placeholders
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype.
Few of the datatypes used to define placeholders are as given below.
These are placeholders that store the values that can change through the PL/SQL Block.
General Syntax to declare a variable is
variable_name datatype [NOT NULL := value ];
For example, if you want to store the current salary of an employee, you can use a variable.
salary number (6);
* “salary” is a variable of datatype number and of length 6.
When a variable is specified as NOT NULL, you must initialize the variable when it is declared.
For example: The below example declares two variables, one of which is a not null.
dept varchar2(10) NOT NULL := “HR Dept”;
The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.
1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement. The General Syntax is:
Example: The below program will get the salary of an employee with id '1116' and display it on the screen.
var_emp_id number(6) = 1116;
WHERE emp_id = var_emp_id;
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
/NOTE: The backward slash '/' in the above program indicates to execute the above PL/SQL Block.
Scope of PS/SQL Variables
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.
- Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
- Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.
For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.
2> var_num1 number;
3> var_num2 number;
5> var_num1 := 100;
6> var_num2 := 200;
8> var_mult number;
10> var_mult := var_num1 * var_num2;