Variable Use Standards
Now that some basic standards are defined let us look at how some of these standards are used in practice.
Cursor Declarations
Cursors are usually named after the table or a view that is being processed. Use the word cur as the prefix for the variable. You would still specify the scope of the variable as usual. What happens if you pass the cursor in as a parameter? Although this is unusual, it works fine.
| Scope | Type | Descriptive Name | Usage | Example |
|---|---|---|---|---|
| Local | cur | spriden_new | lcur_spriden_new | - |
| Parameter | cur | spriden_old | IN | pcur_spriden_old_i |
Record Based on Table or Cursor
These records are defined from the structure of a table or cursor.
| Scope | Type | Descriptive Name | Usage | Example |
|---|---|---|---|---|
| Local | Rec | spraddr | lrec_spraddr | - |
| Parameter | Rec | spraddr | IN | prec_spraddr_i |
| Global | Rec | spraddr | grec_spraddr | - |
If you have more than one record declared for a single cursor, preface the record name with a word that describes it, such as lrec_new_spraddr and lrec_old_spraddr.
FOR Loop Index
There are two kinds of FOR loops, numeric and cursor, each with a corresponding numeric or record loop index. In a numeric loop you should use the suffix "idx". This is important since the index is not a declared variable.
FOR lv_year_idx IN 1 .. 12
In a cursor loop, the name of the record, which serves, as a loop index should follow the convention described above for records.
FOR lrec_spriden IN lcur_spriden
PL/SQL Table TYPE
In PL/SQL you can create PL/SQL tables, which are similar to one-dimensional arrays. In order to create a PL/SQL table, you must first execute a TYPE declaration to create a table datatype with the right structure.
| Scope | Type | Descriptive Name | Usage | Example |
|---|---|---|---|---|
| Local | typ | pay_class | table | ltyp_pay_class_table |
| Global | typ | grade_code | table | gtyp_grade_code_table |
TYPE ltyp_pay_class_table IS TABLE OF ...;
TYPE gtyp_grade_code_table IS TABLE OF ...;
Programmer Defined Subtype
In PL/SQL you can define subtypes from base datatypes.
| Scope | Type | Descriptive Name | Example |
|---|---|---|---|
| Local | stp | primary_key | lstp_primary_key |
| Global | stp | large_string | gstp_large_string |
SUBTYPE lstp_primary_key IS BINARY_INTEGER;
SUBTYPE gstp_large_string IS VARCHAR2;
PL/SQL Record TYPE
In PL/SQL, you can create records with a structure you specify (rather than from a table or cursor). To do this, you must declare a type of record, which determines the structure number, and types of columns). Use a typ type and record usage in the name of the TYPE declaration as follows:
| Scope | Type | Primary Identifier | Usage | Example |
|---|---|---|---|---|
| Local | typ | new_account | record | ltyp_new_account_record |
| Global | typ | old_account | record | gtyp_old_account_record |
TYPE ltyp_new_account_record IS RECORD ... ;
Programmer-defined record instance
Once you have defined a record type, you can declare actual records with that structure. Now you can drop the type part of the prefix; the naming convention for these programmer-defined records is the same as that for records based on tables and cursors:
| Scope | Type | Primary Identifier | Example |
|---|---|---|---|
| Local | rec | new_account | lrec_new_account |
| Global | rec | old_account | grec_old_account |
lrec_new_acct ltyp_new_account_record;
grec_old_account gtyp_old_account_record;
lrec_new_acctltyp_new_account_record;grec_old_account gtyp_old_account_record;