PL/SQL Program Data: Naming, Declaring, and Best Practices

When handling data in programming, we need variables or constants to store and manipulate it. In PL/SQL, variables, constants, scalar types, composite types, and containerized types are collectively referred to as program data.

Program data in PL/SQL is defined within a block and exists only for the duration of the PL/SQL session; it is not stored in the database. When working with program data, it’s important to declare its data structure clearly.

In this blog, I’ll discuss the fundamentals of program data and some best practices for managing it in PL/SQL.

1. Naming Program Data
2. Double Quotes in Naming
3. Naming Conventions
4. Declaring Program Data
5. Anchored Declarations
6. Programmer-Defined Subtypes

Naming Program Data
When naming program data, there are a few rules and recommendations:

• Length: Names can be up to 30 characters long.
• First Character: Names must start with a letter.
• Subsequent Characters: After the first letter, names can contain letters, numerals, $, #, and _.
• Case Sensitivity: All names are case-insensitive.

Double Quotes in Naming
One interesting thing about PL/SQL’s naming rules is that when using double quotes, all the rules, except the 30-character limit, become flexible.

For example, these names are considered acceptable:
“123@”
“ ”(three spaces)

However, double quotes should generally be avoided for naming program data. They are usually used for table names with lowercase extensions. Using double quotes in variable or constant names can make the code harder to read and maintain.

Naming Conventions
Using prefixes or suffixes can improve the readability and understanding of your variables. Here are some commonly used conventions:

l_ for local variables
g_ for global variables
_rt for record types

Declaring Program Data

Declaring Variables
To declare a variable in PL/SQL, use the following structure:

_name_ _datatype_ [NOT NULL] [_default_assignment_];

[ ] is optional

name: The variable name, following the rules mentioned above.
datatype: Specifies the datatype for the variable, which allocates memory for it.
NOT NULL: If this is used, an exception will be raised if NULL is assigned to the variable.
default_assignment: Optionally assign a default value either by using an assignment operator (:=) or the DEFAULT keyword:

Example

name VARCHAR2(10) := ‘John';
-- or
namd VARCHAR2(10) DEFAULT ‘John';

Declaring Constants
To declare a constant, use the following syntax:

name CONSTANT datatype [NOT NULL] := | DEFAULT default_value;

CONSTANT: The keyword indicating that the variable cannot be reassigned once it’s set.

Constants should be initialized with a value during declaration. They cannot be changed after that.

Anchored Declarations
In PL/SQL, variables can be declared using types that are “anchored” to other sources, such as table columns or other PL/SQL variables. This is done using anchored declarations.

Scalar Anchoring (%TYPE)
You can anchor a variable to a column or another PL/SQL variable’s type using the %TYPE attribute:

variable_name attribute%TYPE [default_value];

Here, attribute can be a column in a database table or a scalar variable in PL/SQL.

Record Anchoring (%ROWTYPE)
You can also anchor a variable to a table row using %ROWTYPE:

variable_name table_name | cursor_name%ROWTYPE [default_value];

In this case, the variable will inherit the structure of a row from the specified table.

Advantages of Anchored Declarations
Hardcoding the datatype when declaring a variable can lead to issues if the data structure changes over time. For example, if you initially declare a variable as NUMBER(5, 2) and later the data grows to $1,000,000, the variable will not work correctly anymore.

By using %TYPE, PL/SQL can automatically adjust to changes in the underlying data type without breaking your code.

NOT NULL with Anchoring
When declaring a variable using %TYPE, you should ensure it is initialized with a default value.

However, if the source of the %TYPE is a database column that already has a NOT NULL constraint, you do not need to specify a default value because the database already guarantees that a value will exist.

Programmer-Defined Subtypes
PL/SQL allows you to create your own subtypes or aliases based on predefined datatypes using the SUBTYPE keyword.

Example

SUBTYPE long_string IS VARCHAR2(10000);

There are two types of subtypes:

Constrained Subtype
A constrained subtype applies specific restrictions to the base type, such as value ranges.

Example

SUBTYPE age_limit IS NUMBER(3) RANGE 0 TO 150;

This restricts the value of age_limit to a number between 0 and 150.

Unconstrained Subtype
An unconstrained subtype does not impose any restrictions on the base type. It is typically used when you simply want to give a more meaningful name to an existing datatype.

Example

SUBTYPE long_string IS VARCHAR2(10000);

In summary, handling program data in PL/SQL requires understanding how to declare variables, constants, and subtypes, and how to use naming conventions and anchored declarations effectively. By following these best practices, we can write more maintainable PL/SQL code.