Oracle PL/SQL for DBAs

Program Data

Almost every PL/SQL block you write will define and manipulate program data. Program data consists of data structures that exist only within your PL/SQL session (physically, within the Process Global Area, or PGA, for your session); they are not stored in the database. This section shows how to declare program data, covers the rules governing the format of the names you give them, and offers a quick reference to the different types of data supported in PL/SQL.

Before you can work with a variable or a constant, you must first declare it, and when you declare it, you give it a name and a datatype.

Here are two key recommendations for naming your variables, constants, and types:

Make sure each name accurately reflects its usage and is understandable at a glance

You might even take a moment to write down—in noncomputer terms—what a variable represents. You can then easily extract an appropriate name from that statement. For example, if a variable represents the “total number of calls made about lukewarm coffee,” a good name for that variable might be total_calls_on_cold_coffee, or tot_cold_calls, if you are allergic to five-word variable names. A bad name for that variable would be totcoffee, or t_#_calls_lwcoff, both too cryptic to get the point across.

Establish consistent, sensible naming conventions

Such conventions usually involve the use of prefixes and/or suffixes to indicate type and usage. For example, all local variables should be prefixed with “l_” while global ...