Oracle PL/SQL Variable Identifiers Tutorial with Examples

What is Identifiers?

Identifiers are nothing but a name that is given to a PL/SQL object. The object could be constant, variable, exception, cursors, procedures, function, package, trigger, object type, reserve word or label.

Properties of Identifiers

• Must start with a letter
• Maximum size is limited to 30 letters
• Cannot contain whitespace characters
• Can contain dollar sign ('$'), underscore ('_') and hash sign ('#')
• Is case-insensitive

Naming Conventions of Identifiers

In a complex program, sometimes we may have to include many identifiers. These identifiers include variables, cursors, etc. So to avoid confusion and to increase the readability of such program we need to follow certain naming conventions.

Following are the commonly used naming conventions in PL/SQL.

• The first letter should be used to specify the declared level of the variable. The below point give the different first letters and their declarative level
• • 'P' – Variable is declared at the parameter level
  • 'L' – Variable is declared at the local block
  • 'G' – Variable is declared at the global level
• The second letter specifies the type of identifier. Below are the commonly used identifier types and their naming code.
  • 'C' – Cursor Identifier
  • 'V' – Varchar and char datatype
  • 'N' – Number datatype
  • 'R' – Record type
  • 'T' – Table type

Below are some of the examples of proper naming conventions

• Lv_name – local level variable of varchar/char datatype
• Pc_num – parameter level cursor identifier
• Gn_user_id – Global level variable of numerical data type

Variables – An Identifier

Variable is the basic identifier which is used more frequently and the most important of all. Variable is nothing but a placeholder where the user can store the value. This variable needs to be associated with some valid PL/SQL datatype before using them. The datatype will define the storage and processing method for these variables.

Declaration of Variables

Variables are mainly used to store data during the data manipulation or data processing. They need to be declared before using them inside the program. This declaration needs to be done in the declarative section of the PL/SQL blocks.

Declaration of variables is a process of assigning the name to the placeholder and associate the same with a valid datatype.

Syntax

<variable name> <datatvpe>;

The above syntax shows how to declare the variable in the declarative section.

Data storing in Variables

Once the variable is declared, they are ready to hold the data of defined type. The values of these variables can be assigned either in execution section or at the time of declaring itself. The value can be either a literal or another variable's value. Once a particular value has been assigned, it will be stored in the allocated memory space for that variable.

Syntax

<variable_name> <datatype> := <default_value>;

The above syntax shows how to declare the variable and assign value in the declarative section.

<Yariable_name> <datatype>;
<variable name> := <value>;

The above syntax shows how to assign the value to an already declared variable.

Example1: In this example, we are going to learn how to declare the variable and how to assign the value to them. We are going to print 'GURU99' in the following program by using the variables.

DECLARE
lv_name VARCHAR2(50);
lv_name_2 VARCHAR2(50) := ‘GURU99';
BEGIN 
lv_name := lv_name_2; 
dbms_output .put_line(lv_name);
END:

Code Explanation:

• Code line 2: Declaring the variable 'lv_name' of VARCHAR2 with size 50.
• Code line 3: Declaring the variable 'lv_name_2' of VARCHAR2 with size 50 and assigned the default value using literal 'GURU99'.
• Code line 5: Value for variable 'lv_name' has been assigned from the variable 'lv_name_2'.
• Code line 6: Printing the stored value of variable 'lv_name'.

When the above code is executed, you will get the following output.

Output:

GURU99

Summary

In this tutorial, we have discussed what is an identifier and their properties. We have also discussed naming conventions for identifiers as well as about declaring and using the variable in programs.

Next