In this article, we will discuss PL/SQL Variables in Oracle PL/SQL tutorial, and you will learn about PL/SQL variables and how to use them effectively.
If you missed the previous article of data types in PL/SQL, then please click here.
Let’s start the tour…
A variable is a title assigned to a storage area that a program can use. Each variable in PL/SQL has a specific data type, which determines the size and layout of the variable’s memory.
NOTE: A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs, numerals, underscore etc.
How to declare variables in Oracle PL/SQL
The variables must be declared in the declaration section or in a package as a global variable. After declaring the variables, PL/SQL allocates memory for the variable’s value, and the storage location identifies by the variable name.
The syntax for a PL/SQL Variables declaration is :
variable_name datatype [NOT NULL] [:= Default initial_values];
Here, First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g.,
2_total_sales.Second, choose an appropriate data type for the variable, depending on the kind of value which you want to store, for example, number, character, Boolean, and DateTime. PL/SQL allows us to set a default value for a variable at the declaration time. To assign a default value to a variable, we use the assignment operator (
:=) or the DEFAULT keyword. or you can also initiate the value of the variable.
Naming rules for PL/SQL variables
Like every programming language, the variable must follow some naming rules. So, here in PL/SQL variables, some rules are there:
- The variable_name should not exceed 30 characters.
- The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so, it can be either lowercase or uppercase. For example, v_data and V_DATA both refer to the same variables.
- We should make sure that our variables are easy to read and understand, after the first character, it may be any number, underscore (_) or a dollar sign ($).
- The NOT NULL is an optional specification on the variable.
Initializing Variables in PL/SQL
Whenever we declare a variable, PL/SQL assigns it a default value of NULL. If we want to initialize a variable with a value other than the NULL value, then we can do so during the declaration of a variable, using either of the following −
- The DEFAULT keyword
- The assignment operator
For example −
counter binary_integer := 0; greetings varchar2(20) DEFAULT 'Hello Lucine';
Here, we can also specify NOT NULL constraint to avoid NULL value. If we specify the NOT NULL constraint, you must assign an initial value for that variable.
It is a good programming practice to initializing the variables properly; otherwise, sometimes programs would produce unexpected results.
Example of initializing variable:
DECLARE var_salary number(8); var_emp_id number(6) = 226; BEGIN SELECT salary INTO var_salary FROM employee WHERE emp_id = var_emp_id; dbms_output.put_line(var_salary); dbms_output.put_line('The employee ' || var_emp_id || ' has salary ' || var_salary); END; /
NOTE: backward slash ‘/’ in the above record indicates to execute the above PL/SQL Block.
Variable Scope in PL/SQL
PL/SQL allows the nesting of blocks. A program block could contain another inner block. If we declare a variable within an inner block, it is not accessible to an outer block. There are two types of variable scope in PL/SQL:
- Local variables – These are declared in an inner block and cannot be referenced by outside Blocks.
- Global variables – These are declared in an outer block and can be referenced by its itself and by its inner blocks.
Example of Local and Global variables:
DECLARE -- Global variables vnum1 number := 95; vnum2 number := 75; BEGIN dbms_output.put_line('The outer Variable num1: ' || vnum1); dbms_output.put_line('The outer Variable num2: ' || vnum2); 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 vnum1: 95 Outer Variable vnum2: 75 Inner Variable num1: 200 Inner Variable num2: 195 PL/SQL procedure successfully completed.
Answer: A data type with size, scale or precision limit is called a constrained declaration. The constrained declaration needs less memory than an unconstrained declaration.
Answer: A BLOB data type is a varying length binary string that is used to store two gigabytes of memory. Length should be specified in Bytes for BLOB
Answer: the NULL value represents missing or unknown data. This uses as a place holder or represented, it in as default entry to indicate that there is no actual data present.
Answer: The WITH CHECK option clause specifies the verify level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the subquery.
This article is all about the variables their uses and etc. I hope this article is as much as easy for anyone. In the future article we will discuss the constant, Literals, operators and many more for that, please stay with us.