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.
10 thoughts on “PL/SQL Variables in Oracle PL/SQL Tutorials”
Nice keep it up
Keep it up computer wali madam ji🤣
Very authentic Information
Very informative article! This article incredibly helpful for programmers. Keep up the good work!!
very good information. keep doing it
Great article! This is surely going to help beginners programmers.
Best site for technical information…..keep sharing