7.2.2 Numbers

PL/SQL supports both real and integer datatypes. The base datatype for all variations is NUMBER. INTEGER supports only whole numbers, whilePOSITIVE and NATURAL are variations of INTEGER. Here is an example ofdeclaring a numeric datatype that can have only one of three values (1, -1, and NULL):

DECLARE

l_direction SIGNTYPE;

Chapter 9 explores the rules for numeric data, provides many examples, and explains the built-in functions provided to manipulate numbers in PL/ SQL.

7.2.3 Dates, Timestamps, and Intervals

Prior to Oracle9i, the Oracle world of dates was limited to the DATE datatype, which stored both dates and times (down to the nearest second). Oracle9i introduces 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;

Chapter 10 explores the rules for date-related data, provides many examples, and explains the built-in functions provided to manipulate dates, timestamps, and intervals in PL/SQL.

7.2.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;

Chapter 12 explores the rules for Boolean data and provides examples of usage.

7.2.5 Binary Data

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

Chapter 12 explores the rules for binary data, provides many examples, and explains the built-in functions and DBMS_LOB package provided to manipulate BFILEs and other binary data in PL/SQL.

7.2.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.

Chapter 12 explores the rules for working with the ROWID and UROWID datatypes.

7.2.7 Ref Cursors

The REF CURSOR datatype allows developers 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 second line showcases SYS_REFCURSOR, a predefined weak REF CURSOR type.

DECLARE

TYPE book_data_t IS REF CURSOR;

book_curs_var book_data_t;

Chapter 14 explores REF CURSORs and cursor variables in much more detail.