Properties of PL/SQL Identifiers Naming Conventions in PL/SQL PL/SQL Variables PL/SQL Variable Declaration Data Storing in PL/SQL Variables

PL/SQL Variable Declaration

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.

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

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 in PL/SQL:

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

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

Syntax:

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

Data Storing in PL/SQL 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:

The above syntax shows how to declare the variable and assign value in the declarative section. 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.

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

Identifiers in PL/SQL are nothing but names given to a PL/SQL object. In a complex program, sometimes we may have to include many identifiers. To avoid confusion and to increase the readability of such program, we need to follow certain naming conventions. Variables in PL/SQL are basic identifiers assigned to a storage area that a program can manipulate. Declaration of variables is a process of assigning the name to the placeholder and associate the same with a valid datatype. Once a particular value has been assigned, it will be stored in the allocated memory space for that variable.