Identifiers are nothing but a name that is given for a PL/SQL object. The object could be constant, variable, exception, cursors, procedures, function, package, trigger, object type, reserve word or label.
In this tutorial, you will learn-
- Must start with a letter
- Maximum size is limited to 30 letters
- Cannot contain white space characters
- Can contain dollar sign ('$'), underscore ('_') and hash sign ('#')
- Is case-insensitive
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 for 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
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.
Variables are mainly used to store data during the data manipulation or data processing. They needs 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.
- The above syntax shows how to declare the variable in the declarative section.
Once the variable is declared, they are ready to hold the data of defined type. The values to 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.
- 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 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.
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.