Page 201
Table 10.3 Scalar Data Types (+ as of 7.2; ^ as of 7.3; # as of 8.0.3)
Type | Subtypes | Description | Value Limits | Database Limits |
CHAR(s) | CHARACTER STRING ROWID* NCHAR#** | Fixed length character strings **holds NLS data0_32,767 bytes optional size defaults to 1. | 255 bytes *holds database ROWID string **2,000 bytes | |
VARCHAR2(s) | VARCHAR STRING+ NVARCHAR2#* | Variable length character strings *holds NLS data | 0_32,767 bytes optional size defaults to 1. | 2,000 bytes #4,000 bytes |
NUMBER (p, s) | NUMERIC DEC DECIMAL INT* INTEGER* FLOAT* REAL DOUBLE PRECISION SMALLINT* | Packed decimal values p = precision (total # of digits) s = scale (where rounding occurs) *cannot specify scale | Magnitude range is PL/SQL 1.0E-129 _ 9.99E125. Precision is 1_38 (default size is the maximum value supported by system). Scale is -84 _ 127 (default scale is 0). | Same size as PL/SQL NUMBER |
BINARY_ INTEGER | NATURAL* POSITIVE** NATURALN^- POSITIVEN^- * SIGNTYPE#*** | Signed binary numbers * range starts at 0 ** range starts at 1 - Predefined as NOT NULL; same ranges as above | Magnitude range is -231-1 to 231-1; or ±2,147,483,647 ***limited to (-1, 0, +1) for implementing tri-state logic. | No database type exists (use NUMBER) |
PLS_ INTEGER^ | Signed binary numbers Fastest machine | Magnitude range is -231-1 to 231-1; or ±2,147,483,647. | No database type exists (use NUMBER) |
continues
Page 202
Table 10.3 Continued
Type | Subtypes | Description | Value Limits | Database Limits |
arithmetic, less storage than NUMBER | ||||
DATE | An internal date value | January 1, 4712 BC - December 31, 4712 AD; time in seconds since midnight (default time is 12:00 midnight) | Same as PL/SQL date | |
BOOLEAN | Boolean (logical) values | TRUE, FALSE, NULL (unknown value) | No database type exists | |
LONG | Variable length character strings | 0 - 32,760 bytes (size is not specified) | 0_2,147,483,647 bytes | |
RAW(s) | LONG RAW | Non- interpreted (character set independent) binary data | RAW: 0 - 32,767 bytes; LONGRAW: 0 - 32,760 bytes (same as LONG) (required size must be at least 1) | RAW is 255 bytes; LONGRAW is 2,147,483,647 |
REF CURSOR+ | Cursor variable (pointer to cursor) | No database type exists |
Page 203
Type | Subtypes | Description | Value Limits | Database Limits |
LOB (Large Object) | BFILE* BLOB** CLOB*** NCLOB**** | Locator to large, unstructured, or binary data (images, sound, video, documents) stored in the database or in external operating system files | 232-1 bytes or 0_4GB *external binary files only (read only) **internal/ external binary data (read/write) ***internal/ external single-byte data (read/write) ****internal/ external fixed width multi- byte data (read/write) | Same as PL/SQL LOBs |
Subtypes generally have the same value limits as their main counterparts. Oracle provides them with PL/SQL for compatibility with the column data types found in Oracle and other databases. For example, you can use the alternate NUMBER subtypes when you want something more descriptive. If a type takes an optional size parameter, it is put in parentheses as shown. This size parameter must be specified with integer literals only (for example, CHAR(3)). For strings, a value of length 0 is the NULL value (unknown value).
Character strings are treated as scalars. In order to access individual characters, you use the Oracle-supplied character functions SUBSTR(), INSTR(), and so on. The maximum size of a CHAR is specified in bytes, not characters. This means that multibyte character sets will require more storage. Note the shift in treatment of the STRING subtype from version 7.1 to 7.2.
Oracle performs a conversion from NUMBER (packed decimal) data types into an internal floating point representation in order to accomplish numeric operations. In contrast, the BINARY_INTEGER and PLS_INTEGER data types already have this internal format. Loops coded with indexes using BINARY_INTEGER, while limited in the range of values, can execute faster because this conversion is not necessary. PLS_INTEGER is even faster than BINARY_INTEGER because it uses native machine arithmetic, while BINARY_INTEGER and NUMBER rely on math library routines. However, BINARY_INTEGER values can be assigned to NUMBER variables to avoid overflow exceptions, while using PLS_INTEGER will not avoid the overflow exception. This slight semantic difference should not concern you very much.
Negative scale rounds to the left of the decimal; for example, -3 rounds to the nearest thousands place (1000, 2000, 3000, ), while +3 rounds to the nearest thousandth (0.001, 0.002, 0.003, ).
Page 204
Looking at this table, you may realize that LONG and RAW PL/SQL types have limited usefulness when interacting with database columns of those types. The PL/SQL LONG RAW is actually shorter than a RAW! They should have called it a RAW LONG. However, there is an Oracle-supplied routine to get a database LONG value into PL/SQL LONG variables.
The other thing you may notice from the table is that the limits of a database data type and that of the corresponding PL/SQL data type may be significantly different (specifically, the character types). Please bear this in mind while programming. Some value errors you may experience are related to this discrepancy (when inadvertently trying to stuff a longer string into a shorter variable).
Using PL/SQL Data TypesSome examples of scalar variable declarations are as follows:
item_nameCHAR(32); | May be padded with trailing spaces |
item_category VARCHAR2(32); | These strings are not padded |
price NUMBER(5,2) | Can hold a number between ± 999.99 |
quantity INTEGER(3); | Can hold a number between ± 999 |
discountable BOOLEAN; | Can be TRUE, FALSE, or NULL |
discount REAL(3,3) | Any real number between ± 0.999 |
run_date DATE; | Date fields take up 7 bytes |
Of course, a variable of any data type can hold a NULL value (not just Booleans).
One really slick feature is called "basing" variables. Instead of hard-coding a data type, you use the data type of another variable, database column, or table. You do this with the %TYPE and %ROWTYPE attributes. The %TYPE attribute provides the type and length of the desired database column or variable. The %ROWTYPE attribute allows one to define a record variable whose member variables have the correct type and length for every column in the table or cursor. Each member variable in the record is referenced using dot notation. My convention is to suffix a %ROWTYPE variable with _rec to indicate that it is a record variable. For example:
quantity orders.qty%TYPE; | Based on qty column in table orders |
discount orders.discount%TYPE; | Based on discount column in table orders |
orders_rec orders%ROWTYPE; | A record based on the table orders |
Here are some more examples of using types.
DECLARE -- define user variables MAX_INT CONSTANT INTEGER := +2147483647; MAX_STR VARCHAR2(32767); -- let's use this for typing only notes MAX_STR%TYPE; -- based on another variable's type items_rec items%ROWTYPE; -- based on a table in the database iname items.item_name%TYPE; -- based on a column in a table last_id MAX_INT%TYPE; -- this must be a constant too BEGIN -- executable code iname := `HAMMER'; items_rec.item_name := iname; -- compatible data types last_id := 0; -- Wait! Aagggh! Runtime error!