PL/SQL Datatypes  «Prev 

PL/SQL Datatypes


The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of
respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware.
When PLSQL_CODE_ TYPE='INTERPRETED', the performance improvement is smaller.

Any NUMBER or DATE will be converted into a CHAR. As a consequence, any string function can be used on a NUMBER or DATE column. Literal NUMBERs do not have to be enclosed in single quotation marks when used in a string function; literal DATES do. For example,

A CHAR string will be converted to a NUMBER if it contains only numbers, a decimal point, or a minus sign on the left. There must be no embedded spaces or other characters.

A CHAR string will be converted to a DATE only if it is in the format DD-MON-YY, such as 16-JAN-73. This is true for all functions except GREATEST and LEAST, which will treat it as a string, and true for BETWEEN only if the column to the left of the word BETWEEN is a DATE. Otherwise, TO_DATE must be used with a proper format.