PL/SQL Acceptable Identifiers, Variable & Naming Conventions

What are PL/SQL Identifiers?

Identifiers in PL/SQL are the names given to a PL/SQL object. The object could be a constant, variable, exception, cursor, procedure, function, package, trigger, object type, reserved word, or label. Identifiers contain letters, numerals, signs, underscores, and so on. They are case-insensitive and limited to 30 characters in size.

Properties of PL/SQL Identifiers

Here are the main properties of PL/SQL identifiers:

• Must start with a letter.
• The maximum size is limited to 30 characters.
• Cannot contain whitespace characters.
• Can contain a dollar sign (‘$’), underscore (‘_’), and hash sign (‘#’).
• Is case-insensitive.

Naming Conventions in PL/SQL

In a complex program, we may have to include many identifiers, such as variables and cursors. To avoid confusion and increase readability, we need to follow certain naming conventions.

The commonly used naming conventions in PL/SQL are as follows.

The first letter specifies the declared level of the variable:

• ‘P’ – variable declared at the parameter level.
• ‘L’ – variable declared at the local block.
• ‘G’ – variable declared at the global level.

The second letter specifies the type of identifier:

• ‘C’ – cursor identifier.
• ‘V’ – VARCHAR and CHAR data type.
• ‘N’ – NUMBER data type.
• ‘R’ – record type.
• ‘T’ – table type.

Below are some examples of proper naming conventions in PL/SQL:

• Lv_name – local level variable of VARCHAR/CHAR data type.
• Pc_num – parameter level cursor identifier.
• Gn_user_id – global level variable of numerical data type.

PL/SQL Variables

Variables in PL/SQL are basic identifiers assigned to a storage area that a program can manipulate. Variables are placeholders where the user can store values. These variables need to be associated with a valid PL/SQL data type before use. Data types define the storage and processing methods for these variables.

PL/SQL Variable Declaration

Variables are mainly used to store data during data manipulation or processing. They must be declared before use, inside the declarative section of the PL/SQL blocks.

Declaring a variable is the process of assigning a name to the placeholder and associating it with a valid data type.

Syntax

<variable_name> <datatype>;

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

Data Storing in PL/SQL Variables

Once a variable is declared, it is ready to hold data of the defined type. The values can be assigned either in the execution section or at the time of declaration. The value can be a literal or another variable’s value. Once assigned, it is stored in the memory space allocated for that variable.

Syntax

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

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

<variable_name> <datatype>;
<variable_name> := <value>;

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

Example 1: In this example, we learn how to declare a variable and assign a value to it. We print ‘GURU99’ in the following program using 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 assigning the default value using the literal ‘GURU99’.
• Code line 5: The value for variable ‘lv_name’ is 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 get the following output.

Output

GURU99

FAQs