.
	Enterprise Systems

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;