PL/SQL Acceptable Identifiers, Variable & Naming Conventions

What are PL/SQL Identifiers?

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

Properties of PL/SQL Identifiers

Here are the main properties of PL/SQL 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 in PL/SQL

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 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

PL/SQL Variables

Variables in PL/SQL are basic identifiers assigned to a storage area that a program can manipulate. Variables are nothing but placeholders where the user can store values. These variables need to be associated with some valid PL/SQL datatypes before using them. Datatypes define the storage and processing methods for these 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.

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 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

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

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

<variable_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.

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

  • 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.