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