Section 1.3. Program Data

1.3. 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 downin noncomputer termswhat 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 variables defined in packages have a "g_" prefix. All record types should have a suffix of "_rt" and so on. You can download a comprehensive set of naming conventions from O'Reilly's Oracle page at http://oracle.oreilly.com. Click on "Oracle PL/SQL Best Practices," then "Examples." The download contains a standards document for your use. (Currently, the direct URL is http://examples.oreilly.com/orbestprac/.)

1.3.1. Types of PL/SQL Datatypes

Whenever you declare a variable or a constant, you must assign it a datatype. (PL/SQL is, with very few exceptions, a strongly typed language.) PL/SQL offers a comprehensive set of predefined scalar and composite datatypes , and you can create your own user-defined types (also known as abstract datatypes ).

Virtually all of these predefined datatypes are defined in the PL/SQL STANDARD package. Here, for example, are the statements that define the Boolean datatype and two of the numeric datatypes:

     CREATE OR REPLACE PACKAGE STANDARD     IS      type BOOLEAN is (FALSE, TRUE);       type NUMBER is NUMBER_BASE;       subtype INTEGER is NUMBER(38,);

When it comes to datatypes, PL/SQL supports the usual suspects and a whole lot more. This section provides only a quick overview of the various predefined datatypes.

1.3.1.1. Character data

PL/SQL supports both fixed- and variable-length strings as both traditional character and Unicode character data. CHAR and NCHAR are fixed-length datatypes; VARCHAR2 and NVARCHAR2 are variable-length datatypes. Here is a declaration of a variable-length string that can hold up to 2,000 characters:

     DECLARE        l_accident_description VARCHAR2(2000);

Oracle also supports very large character strings, known as LONGs and LOBs. These datatypes allow you to store and manipulate very large amounts of dataa LOB can hold up to 128 terabytes of information in Oracle Database 10g. (Use LONGs only for compatibility with existing code. The future lies with LOBs.) The character LOB datatypes are CLOB and NCLOB (multi-byte format). CLOB stands for character large object, and NCLOB for National Language Support character large object.

1.3.1.2. Numbers

PL/SQL supports an increasing variety of numeric datatypes. NUMBER has long been the workhorse of the numeric datatypes, and you can use it for decimal fixed- and floating-point values, and for integers . The following are examples of some possible NUMBER declarations:

     DECLARE        salary NUMBER(9,2); --fixed-point, seven to the left, two to the right        raise_factor NUMBER; --decimal floating-point        weeks_to_pay NUMBER(2); --integer     BEGIN        salary := 1234567.89;        raise_factor := 0.05;        weeks_to_pay := 52;     END;

Because of its true decimal nature, NUMBER is particularly useful when working with monetary amounts. You won't incur any rounding error as a result of binary representation. For example, when you store 0.95, you won't come back later to find only 0.949999968.

Until Oracle Database 10g came along, NUMBER was the only one of PL/SQL's numeric datatypes to correspond directly to a database datatype. This is one reason you'll find NUMBER so widely used. Oracle Database 10g introduced two binary floating-point types: BINARY_FLOAT and BINARY_DOUBLE. As is the case with NUMBER, these new datatypes are also supported in both PL/SQL and the database. Given the right type of application, their use can lead to tremendous performance gains, as arithmetic involving the new types is performed in hardware (whenever the underlying platform allows).

PL/SQL supports several numeric types and subtypes that do not correspond to database datatypes but are, nevertheless, quite useful. Notable here is PLS_INTEGER, an integer type with its arithmetic implemented in hardware. FOR loop counters are implemented as PLS_INTEGERs.

1.3.1.3. Dates, timestamps, and intervals

Prior to Oracle9i Database, the Oracle world of dates was limited to the DATE datatype , which stores both dates and times (down to the nearest second). Oracle9i Database introduced two sets of new, related datatypes: INTERVALs and TIMESTAMPs. These datatypes greatly expand the capability of PL/SQL developers to write programs that manipulate and store dates and times with very high granularity, and also compute and store intervals of time.

Here is an example of a function that computes the age of a person:

     CREATE OR REPLACE FUNCTION age (dob_in IN DATE)        RETURN INTERVAL YEAR TO MONTH     IS        retval INTERVAL YEAR TO MONTH;     BEGIN        RETURN (SYSDATE - dob_in) YEAR TO MONTH;     END;

1.3.1.4. Booleans

PL/SQL supports a true Boolean datatype . A variable of this type can have one of only three values: TRUE, FALSE, and NULL.

Booleans help us write very readable code, especially involving complex logical expressions. Here's an example of a Boolean declaration, along with an assignment of a default value to that variable:

     DECLARE        l_eligible_for_discount BOOLEAN :=           customer_in.balance > min_balance AND           customer_in.pref_type = 'MOST FAVORED' AND           customer_in.disc_eligibility;

1.3.1.5. Binary data

Oracle supports several forms of binary data (unstructured data that is not interpreted or processed by Oracle), including RAW, LONG RAW, BFILE, and BLOB. The BFILE datatype stores unstructured binary data in operating system files outside the database. RAW is a variable-length datatype like the VARCHAR2 character datatype, except that Oracle utilities do not perform character conversion when transmitting RAW data.

1.3.1.6. ROWIDs

Oracle provides two proprietary datatypes, ROWID and UROWID, used to represent the address of a row in a table. ROWID represents the unique address of a row in its table; UROWID represents the logical position of a row in an index-organized table (IOT) . ROWID is also a SQL pseudonym that can be included in SQL statements.

1.3.1.7. REF CURSOR datatype

The REF CURSOR datatype allows you to declare cursor variables, which can be used with static and dynamic SQL statements to implement very flexible requirements. This datatype supports two forms: the strong REF CURSOR and the weak REF CURSOR. The latter is one of the very few weakly typed datatypes you can declare.

Here is an example of a strong REF CURSOR declaration (I associate the cursor variable with a specific record structure with %ROWTYPE):

     DECLARE        TYPE  book_data_t IS REF CURSOR RETURN book%ROWTYPE;        book_curs_var book_data_t;

And here are two weak REF CURSOR declarations in which I do not associate any particular structure with the resulting variable. The fourth line showcases SYS_REFCURSOR, a predefined weak REF CURSOR type.

     DECLARE        TYPE  book_data_t IS REF CURSOR;        book_curs_var book_data_t;        book_curs_var2 SYS_REFCURSOR

1.3.1.8. Internet datatypes

From Oracle9i Database onward, you'll find native support for several Internet-related technologies and types of data, specifically Extensible Markup Language (XML) and Universal Resource Identifiers (URIs) . Oracle provides datatypes used to handle XML and URI data, as well as a class of URIs called DBUri-REFs used to access data stored within the database itself. Oracle also provides a new set of types to store and access both external and internal URIs from within the database.

The XMLType allows you to query and store XML data in the database using functions like SYS_XMLGEN and the DBMS_XMLGEN package . It also allows you to use native operators in the SQL language to perform searching with the XPath language.

The URI-related types, including URIType and HttpURIType, are all part of an object type inheritance hierarchy and can be used to store URLs to external web pages and files, as well as to refer to data within the database.

1.3.1.9. "Any" datatypes

Most of the time, our programming tasks are fairly straightforward and very specific to the requirements at hand. At other times, however, we write more generic kinds of code. For those situations, the "Any" datatypes might come in very handy.

The "Any" types were introduced in Oracle9i Database Release 1 and are very different from any other kind of datatype available in Oracle. They let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. You can use these types (and the methods defined for them, as they are object types) to do things like determine the type of data stored in a particular nested tablewithout having access to the actual declaration of that table type!

The "Any" datatypes include AnyType, AnyData, and AnyDataSet.

1.3.2. Declaring Program Data

As I mentioned, before you can make a reference to a variable or a constant, you must declare it. (The only exception to this rule is for the index variables of FOR loops .) All declarations must be made in the declaration section of your anonymous block, procedure, function, trigger, object type body, or package body. You can declare many types of data and data structures in PL/SQL, including variables, constants, TYPEs (such as a type of collection or a type of record), and exceptions. This section focuses on the declarations of variables and constants.

1.3.2.1. Declaring a variable

When you declare a variable, PL/SQL allocates memory for the variable's value and names the storage location so that the value can be retrieved and changed. The declaration also specifies the datatype of the variable; this datatype is then used to validate values assigned to the variable.

The basic syntax for a declaration is:

     name datatype [NOT NULL] [default_assignment];

where name is the name of the variable or constant to be declared, and datatypeis the datatype or subtype that determines the type of data that can be assigned to the variable. You can include a NOT NULL clause , which means that if your code assigns a NULL to this variable, Oracle will raise an exception. The [default_assignment] clause allows you to initialize the variable with a value; this is optional for all declarations except those of constants.

The following examples illustrate declarations of variables of different datatypes:

     DECLARE        -- Simple declaration of numeric variable        l_total_count NUMBER;        -- Declaration of number that rounds to nearest hundredth (cent):        l_dollar_amount NUMBER (10,2);        -- A single date value, assigned a default value of "right now"        -- and it can never be NULL        l_right_now DATE NOT NULL  DEFAULT SYSDATE;        -- Using the assignment operator for the default value specification        l_favorite_flavor VARCHAR2(100) := 'Anything with chocolate, actually';        -- Two-step declaration process for associative array.        -- First, the type of table:        TYPE list_of_books_t IS TABLE OF book%ROWTYPE INDEX BY BINARY_INTEGER;        -- And now the specific list to be manipulated in this block:        oreilly_oracle_books list_of_books_t;

The DEFAULT and assignment operator syntax for assigning a default value are equivalent and can be interchanged. So which should you use? I like to use the assignment operator (:=) to set default values for constants, and the DEFAULT syntax for variables. In the case of the constant, the assigned value is not really a default but an initial (and unchanging) value, so the DEFAULT syntax feels misleading to me.

1.3.2.2. Declaring constants

There are just two differences between declaring a variable and declaring a constant: for a constant, you include the CONSTANT keyword, and you must supply a default value (which isn't really a default at all, but rather is the only value). So the syntax for the declaration of a constant is:

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

The value of a constant is set upon declaration and may not change thereafter.

Here are some examples of declarations of constants:

     DECLARE        -- The current year number; it's not going to change during my session.        l_curr_year CONSTANT PLS_INTEGER :=           TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));        -- Using the DEFAULT keyword         l_author CONSTANT VARCHAR2(100) DEFAULT 'Bill Pribyl';        -- Declare an object type as a constant        -- this isn't just for scalars!        l_steven CONSTANT  person_ot :=          person_ot ('HUMAN', 'Steven Feuerstein', 175, '09-23-1958');

Unless otherwise stated, the information provided in the rest of this chapter for variables also applies to constants.

1.3.2.3. Anchored declarations

When you anchor a datatype, you tell PL/SQL to set the datatype of your variable based on the datatype of an already defined data structureanother PL/SQL variable, a predefined TYPE or SUBTYPE, a database table, or a specific column in a table. PL/SQL offers two kinds of anchoring :

Scalar anchoring

Use the %TYPE attribute to define your variable based on a table's column or some other PL/SQL scalar variable.

Record anchoring

Use the %ROWTYPE attribute to define your record structure based on a table or a predefined PL/SQL explicit cursor.

The syntax for an anchored datatype is:

     variable_name_type attribute%TYPE [optional default value assignment];     variable_name table_name | cursor_name%ROWTYPE [optional default value assignment];

where variable_name is the name of the variable you are declaring, and type_attribute is either a previously declared PL/SQL variable name or a table column specification in the format table.column.

Here is an example of anchoring a variable to a database column:

     l_company_id company.company_id%TYPE;

Here is an example of anchoring a record to a cursor:

     DECLARE        CURSOR book_cur IS           SELECT author, title FROM book;        l_book book_cur%ROWTYPE;

This anchoring reference is resolved at the time the code is compiled; there is no runtime overhead to anchoring. The anchor also establishes a dependency between the code and the anchored element (the table, cursor, or package containing the variable referenced). This means that if those elements are changed, the code in which the anchoring takes place is marked INVALID. When it is recompiled, the anchor will again be resolved, thereby keeping the code current with the anchored element.