Release 2 (9.2)
Part Number A96624-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| PL/SQL User's Guide and Reference Release 2 (9.2) Part Number A96624-01 |
|
Like--but oh how different! --William Wordsworth
Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL/SQL lets you define your own subtypes. This chapter covers the basic types used frequently in PL/SQL programs. Later chapters cover the more specialized types.
This chapter discusses the following topics:
A scalar type has no internal components. A
composite type has internal components that can be
manipulated individually. A reference type holds values,
called pointers, that designate other program items. A
LOB type holds values, called lob locators, that specify the
location of large objects (graphic images for example) stored out-of-line.
Figure 3-1 shows the predefined datatypes available for your use. The scalar types fall into four families, which store number, character, Boolean, and date/time data, respectively.
Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.
You use the BINARY_INTEGER datatype to store signed
integers. Its magnitude range is -2**31 .. 2**31. Like PLS_INTEGER
values, BINARY_INTEGER values require less storage than
NUMBER values. However, most BINARY_INTEGER operations
are slower than PLS_INTEGER operations.
A base type is the datatype from which a
subtype is derived. A subtype associates a base type with
a constraint and so defines a subset of values. For your convenience, PL/SQL
predefines the following BINARY_INTEGER subtypes:
The subtypes NATURAL and POSITIVE let you
restrict an integer variable to non-negative or positive values, respectively.
NATURALN and POSITIVEN prevent the assigning of nulls
to an integer variable. SIGNTYPE lets you restrict an integer
variable to the values -1, 0, and 1, which is useful in programming tri-state
logic.
You use the NUMBER datatype to store fixed-point or
floating-point numbers. Its magnitude range is 1E-130 .. 10E125. If the value of
an expression falls outside this range, you get a numeric
overflow or underflow error. You can specify precision, which is the total number of digits, and scale, which is the number of digits to the right of the
decimal point. The syntax follows:
NUMBER[(precision,scale)]
To declare fixed-point numbers, for which you must specify scale, use the following form:
NUMBER(precision,scale)
To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can "float" to any position, use the following form:
NUMBER
To declare integers, which have no decimal point, use this form:
NUMBER(precision) -- same as NUMBER(precision,0)
You cannot use constants or variables to specify precision and scale; you must use
integer literals. The maximum precision of a NUMBER value is 38
decimal digits. If you do not specify precision, it
defaults to 38 or the maximum supported by your system, whichever is less.
Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of 0 rounds to the nearest whole number. If you do not specify scale, it defaults to 0.
You can use the following NUMBER subtypes for
compatibility with ANSI/ISO and IBM types or when you want a more descriptive
name:
Use the subtypes DEC, DECIMAL, and
NUMERIC to declare fixed-point numbers with a maximum precision of
38 decimal digits.
Use the subtypes DOUBLE PRECISION and
FLOAT to declare floating-point numbers with a maximum precision of
126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the
subtype REAL to declare floating-point numbers with a maximum precision of 63
binary digits, which is roughly equivalent to 18 decimal digits.
Use the subtypes INTEGER, INT, and
SMALLINT to declare integers with a maximum precision of 38 decimal
digits.
You use the PLS_INTEGER datatype to store signed
integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER
values require less storage than NUMBER values. Also,
PLS_INTEGER operations use machine arithmetic, so they are faster
than NUMBER and BINARY_INTEGER operations, which use
library arithmetic. For efficiency, use PLS_INTEGER for all
calculations that fall within its magnitude range.
Although PLS_INTEGER and BINARY_INTEGER
have the same magnitude range, they are not fully compatible. When a
PLS_INTEGER calculation overflows, an exception is raised. However,
when a BINARY_INTEGER calculation overflows, no exception is raised
if the result is assigned to a NUMBER variable.
Because of this small semantic difference, you might want to
continue using BINARY_INTEGER in old applications for
compatibility. In new applications, always use PLS_INTEGER for
better performance.
Character types let you store alphanumeric data, represent words and text, and manipulate character strings.
You use the CHAR datatype to store fixed-length
character data. How the data is represented internally depends on the database
character set. The CHAR datatype takes an optional parameter that
lets you specify a maximum size up to 32767 bytes. You can specify the size in
terms of bytes or characters, where each character contains one or more bytes,
depending on the character set encoding. The syntax follows:
CHAR[(maximum_size [CHAR | BYTE] )]
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.
If you do not specify a maximum size, it defaults to 1. If you
specify the maximum size in bytes rather than characters, a CHAR(n)
variable might be too small to hold n multibyte characters. To
avoid this possibility, use the notation CHAR(n CHAR)so that
the variable can hold n characters in the database character set,
even if some of those characters contain multiple bytes. When you specify the
length in characters, the upper limit is still 32767 bytes. So for double-byte
and multibyte character sets, you can only specify 1/2 or 1/3 as many characters
as with a single-byte character set.
Although PL/SQL character variables can be relatively long, the
maximum width of a CHAR database column is 2000 bytes. So, you
cannot insert CHAR values longer than 2000 bytes into a
CHAR database column.
You can insert any CHAR(n) value into a
LONG database column because the maximum width of a
LONG column is 2**31 bytes or two gigabytes. However, you cannot
retrieve a value longer than 32767 bytes from a LONG column into a
CHAR(n) variable.
When you do not use the CHAR or BYTE
qualifiers, the default is determined by the setting of the
NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL
procedure is compiled, the setting of this parameter is recorded, so that the
same setting is used when the procedure is recompiled after being
invalidated.
Note: Semantic differences between the
CHAR and VARCHAR2 base types are discussed in Appendix B.
The CHAR subtype CHARACTER has the same
range of values as its base type. That is, CHARACTER is just
another name for CHAR. You can use this subtype for compatibility
with ANSI/ISO and IBM types or when you want an identifier more descriptive than
CHAR.
You use the LONG datatype to store variable-length
character strings. The LONG datatype is like the
VARCHAR2 datatype, except that the maximum size of a
LONG value is 32760 bytes.
You use the LONG RAW datatype to store
binary data or byte strings. LONG RAW data is like
LONG data, except that LONG RAW data is
not interpreted by PL/SQL. The maximum size of a LONG
RAW value is 32760 bytes.
Starting in Oracle9i, LOB variables can be
used interchangeably with LONG and LONG RAW variables.
Oracle recommends migrating any LONG data to the CLOB
type, and any LONG RAW data to the BLOB type. See "LOB
Types"
for more details.
You can insert any LONG value into a LONG
database column because the maximum width of a LONG column is 2**31
bytes. However, you cannot retrieve a value longer than 32760 bytes from a
LONG column into a LONG variable.
Likewise, you can insert any LONG RAW
value into a LONG RAW database column because the
maximum width of a LONG RAW column is 2**31 bytes.
However, you cannot retrieve a value longer than 32760 bytes from a
LONG RAW column into a LONG
RAW variable.
LONG columns can store text, arrays of characters, or
even short documents. You can reference LONG columns in
UPDATE, INSERT, and (most) SELECT
statements, but not in expressions, SQL function calls, or
certain SQL clauses such as WHERE, GROUP
BY, and CONNECT BY. For more information,
see Oracle9i
SQL Reference.
Note: In SQL statements, PL/SQL binds
LONG values as VARCHAR2, not as LONG.
However, if the length of the bound VARCHAR2 exceeds the maximum
width of a VARCHAR2 column (4000 bytes), Oracle converts the bind
type to LONG automatically, then issues an error message because
you cannot pass LONG values to a SQL function.
You use the RAW datatype to store binary data or byte
strings. For example, a RAW variable might store a sequence of
graphics characters or a digitized picture. Raw data is like
VARCHAR2 data, except that PL/SQL does not interpret raw data.
Likewise, Oracle Net does no character set conversions when you transmit raw
data from one system to another.
The RAW datatype takes a required parameter that lets
you specify a maximum size up to 32767 bytes. The syntax follows:
RAW(maximum_size)
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.
The maximum width of a RAW database column is 2000
bytes. So, you cannot insert RAW values longer than 2000 bytes into
a RAW column. You can insert any RAW value into a
LONG RAW database column because the maximum width of
a LONG RAW column is 2**31 bytes. However, you cannot
retrieve a value longer than 32767 bytes from a LONG
RAW column into a RAW variable.
Internally, every database table has a ROWID
pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row.
A physical rowid identifies a row in an ordinary
table. A logical rowid identifies a row in an
index-organized table. The ROWID datatype can store only physical
rowids. However, the UROWID (universal rowid) datatype can store
physical, logical, or foreign (non-Oracle) rowids.
Suggestion: Use the ROWID
datatype only for backward compatibility with old applications. For new
applications, use the UROWID datatype.
When you select or fetch a rowid into a ROWID variable,
you can use the built-in function ROWIDTOCHAR, which converts the
binary value into an 18-byte character string. Conversely, the function
CHARTOROWID converts a ROWID character string into a
rowid. If the conversion fails because the character string does not represent a
valid rowid, PL/SQL raises the predefined exception
SYS_INVALID_ROWID. This also applies to implicit conversions.
To convert between UROWID variables and character
strings, use regular assignment statements without any function call. The values
are implicitly converted between UROWID and character types.
Physical rowids provide fast access to particular rows. As long as
the row exists, its physical rowid does not change. Efficient and stable,
physical rowids are useful for selecting a set of rows, operating on the whole
set, and then updating a subset. For example, you can compare a
UROWID variable with the ROWID pseudocolumn in the
WHERE clause of an UPDATE or DELETE
statement to identify the latest row fetched from a cursor. See "Fetching
Across Commits".
A physical rowid can have either of two formats. The 10-byte extended rowid format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byte restricted rowid format is provided for backward compatibility.
Extended rowids use a base-64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query
SQL> SELECT rowid, ename FROM emp WHERE empno = 7788;
might return the following row:
ROWID ENAME ------------------ ---------- AAAAqcAABAAADFNAAH SCOTT
The format, OOOOOOFFFBBBBBBRRR, has four parts:
OOOOOO: The data object
number (AAAAqc in the example above) identifies the database
segment. Schema objects in the same segment, such as a cluster of tables, have
the same data object number.
FFF: The file number
(AAB in the example) identifies the data file that contains the
row. File numbers are unique within a database.
BBBBBB: The block
number (AAADFN in the example) identifies the data block that
contains the row. Block numbers are relative to their data file, not their
tablespace. So, two rows in the same tablespace but in different data files
can have the same block number.
RRR: The row number
(AAH in the example) identifies the row in the block. Logical rowids provide the fastest access to particular rows. Oracle uses them to construct secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.
A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, Oracle uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale and slow down access to rows. To obtain fresh guesses, you can rebuild the secondary index.
You can use the ROWID pseudocolumn to select logical
rowids (which are opaque values) from an index-organized table. Also, you can
insert logical rowids into a column of type UROWID, which has a
maximum size of 4000 bytes.
The ANALYZE statement helps you track the staleness of
guesses. This is useful for applications that store rowids with guesses in a
UROWID column, then use the rowids to fetch rows.
Note: To manipulate rowids, you can use
the supplied package DBMS_ROWID. For more information, see Oracle9i
Supplied PL/SQL Packages and Types Reference.
You use the VARCHAR2 datatype to store variable-length
character data. How the data is represented internally depends on the database
character set. The VARCHAR2 datatype takes a required parameter
that specifies a maximum size up to 32767 bytes. The syntax follows:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.
Small VARCHAR2 variables are optimized for performance,
and larger ones are optimized for efficient memory use. The cutoff point is 2000
bytes. For a VARCHAR2 that is 2000 bytes or longer,
PL/SQL dynamically allocates only enough memory to hold the actual value. For a
VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL
preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable
and to a VARCHAR2(1999 BYTE) variable, the former takes up 500
bytes and the latter takes up 1999 bytes.
If you specify the maximum size in bytes rather than characters, a
VARCHAR2(n) variable might be too small to hold n
multibyte characters. To avoid this possibility, use the notation
VARCHAR2(n CHAR)so that the variable can hold n
characters in the database character set, even if some of those characters
contain multiple bytes. When you specify the length in characters, the upper
limit is still 32767 bytes. So for double-byte and multibyte character sets, you
can only specify 1/2 or 1/3 as many characters as with a single-byte character
set.
Although PL/SQL character variables can be relatively long, the
maximum width of a VARCHAR2 database column is 4000 bytes. So, you
cannot insert VARCHAR2 values longer than 4000 bytes into a
VARCHAR2 database column.
You can insert any VARCHAR2(n) value into a
LONG database column because the maximum width of a
LONG column is 2**31 bytes. However, you cannot retrieve a value
longer than 32767 bytes from a LONG column into a
VARCHAR2(n) variable.
When you do not use the CHAR or BYTE
qualifiers, the default is determined by the setting of the
NLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL
procedure is compiled, the setting of this parameter is recorded, so that the
same setting is used when the procedure is recompiled after being
invalidated.
The VARCHAR2 subtypes below have the same range of
values as their base type. For example, VARCHAR is just another
name for VARCHAR2.
You can use these subtypes for compatibility with ANSI/ISO and IBM types.
Note: Currently, VARCHAR is
synonymous with VARCHAR2. However, in future releases of PL/SQL, to
accommodate emerging SQL standards, VARCHAR might become a separate
datatype with different comparison semantics. So, it is a good idea to use
VARCHAR2 rather than VARCHAR.
The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. These languages require two or three bytes to represent each character. To deal with such languages, Oracle provides globalization support, which lets you process single-byte and multibyte character data and convert between character sets. It also lets your applications run in different language environments.
With globalization support, number and date formats adapt automatically to the language conventions specified for a user session. Thus, users around the world can interact with Oracle in their native languages.
PL/SQL supports two character sets called the database character set, which is used for identifiers and
source code, and the national character set, which is used
for national language data. The datatypes NCHAR and
NVARCHAR2 store character strings formed from the national
character set.
Note: When converting CHAR
or VARCHAR2 data between databases with different character sets,
make sure the data consists of well-formed strings. For more information, see
Oracle9i
Globalization and National Language Support Guide.
The national character set represents data as Unicode, using either
the UTF8 or AL16UTF16 encoding.
Each character in the AL16UTF16 encoding takes up 2
bytes. This makes it simple to calculate string lengths to avoid truncation
errors when mixing different programming languages, but requires extra storage
overhead to store strings made up mostly of ASCII characters.
Each character in the UTF8 encoding takes up 1, 2, or 3
bytes. This lets you fit more characters into a variable or table column, but
only if most characters can be represented in a single byte. It introduces the
possibility of truncation errors when transferring the data to a buffer measured
in bytes.
Oracle Corporation recommends that you use the default
AL16UTF16 encoding wherever practical, for maximum runtime
reliability. If you need to determine how many bytes are required to hold a
Unicode string, use the LENGTHB function rather than
LENGTH.
You use the NCHAR datatype to store fixed-length
(blank-padded if necessary) national character data. How the data is represented
internally depends on the national character set specified when the database was
created, which might use a variable-width encoding (UTF8) or a
fixed-width encoding (AL16UTF16). Because this type can always
accommodate multibyte characters, you can use it to hold any Unicode character
data.
The NCHAR datatype takes an optional parameter that
lets you specify a maximum size in characters. The syntax follows:
NCHAR[(maximum_size)]
Because the physical limit is 32767 bytes, the maximum value you can
specify for the length is 32767/2 in the AL16UTF16 encoding, and
32767/3 in the UTF8 encoding.
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.
If you do not specify a maximum size, it defaults to 1. The value
always represents the number of characters, unlike CHAR which can
be specified in either characters or bytes.
my_string NCHAR(100); -- maximum size is 100 characters
The maximum width of an NCHAR database column is 2000
bytes. So, you cannot insert NCHAR values longer than 2000 bytes
into an NCHAR column.
If the NCHAR value is shorter than the defined width of
the NCHAR column, Oracle blank-pads the value to the defined
width.
You can interchange CHAR and NCHAR values
in statements and expressions. It is always safe to turn a CHAR
value into an NCHAR value, but turning an NCHAR value
into a CHAR value might cause data loss if the character set for
the CHAR value cannot represent all the characters in the
NCHAR value. Such data loss can result in characters that usually
look like question marks (?).
You use the NVARCHAR2 datatype to store variable-length
Unicode character data. How the data is represented internally depends on the
national character set specified when the database was created, which might use
a variable-width encoding (UTF8) or a fixed-width encoding
(AL16UTF16). Because this type can always accommodate multibyte
characters, you can use it to hold any Unicode character data.
The NVARCHAR2 datatype takes a required parameter that
specifies a maximum size in characters. The syntax follows:
NVARCHAR2(maximum_size)
Because the physical limit is 32767 bytes, the maximum value you can
specify for the length is 32767/2 in the AL16UTF16 encoding, and
32767/3 in the UTF8 encoding.
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.
The maximum size always represents the number of characters, unlike
VARCHAR2 which can be specified in either characters or bytes.
my_string NVARCHAR2(200); -- maximum size is 200 characters
The maximum width of a NVARCHAR2 database column is
4000 bytes. Therefore, you cannot insert NVARCHAR2 values longer
than 4000 bytes into a NVARCHAR2 column.
You can interchange VARCHAR2 and NVARCHAR2
values in statements and expressions. It is always safe to turn a
VARCHAR2 value into an NVARCHAR2 value, but turning an
NVARCHAR2 value into a VARCHAR2 value might cause data
loss if the character set for the VARCHAR2 value cannot represent
all the characters in the NVARCHAR2 value. Such data loss can
result in characters that usually look like question marks (?).
The LOB (large object) datatypes BFILE,
BLOB, CLOB, and NCLOB let you store
blocks of unstructured data (such as text, graphic images, video clips, and
sound waveforms) up to four gigabytes in size. And, they allow efficient,
random, piece-wise access to the data.
The LOB types differ from the LONG and
LONG RAW types in several ways. For example,
LOBs (except NCLOB) can be attributes of an object
type, but LONGs cannot. The maximum size of a LOB is
four gigabytes, but the maximum size of a LONG is two gigabytes.
Also, LOBs support random access to data, but LONGs
support only sequential access.
LOB types store lob locators,
which point to large objects stored in an external file, in-line (inside the row) or out-of-line
(outside the row). Database columns of type BLOB,
CLOB, NCLOB, or BFILE store the locators.
BLOB, CLOB, and NCLOB data is stored in
the database, in or outside the row. BFILE data is stored in
operating system files outside the database.
PL/SQL operates on LOBs through the locators. For
example, when you select a BLOB column value, only a locator is
returned. If you got it during a transaction, the LOB locator
includes a transaction ID, so you cannot use it to update that LOB
in another transaction. Likewise, you cannot save a LOB locator
during one session, then use it in another session.
Starting in Oracle9i, you can also convert
CLOBs to CHAR and VARCHAR2 types and vice
versa, or BLOBs to RAW and vice versa, which lets you
use LOB types in most SQL and PL/SQL statements and functions. To
read, write, and do piecewise operations on LOBs, you can use the
supplied package DBMS_LOB. For more information, see Oracle9i
Application Developer's Guide - Large Objects (LOBs).
You use the BFILE datatype to store large binary
objects in operating system files outside the database. Every BFILE
variable stores a file locator, which points to a large binary file on the
server. The locator includes a directory alias, which specifies a full path name
(logical path names are not supported).
BFILEs are read-only, so you cannot modify them. The
size of a BFILE is system dependent but cannot exceed four
gigabytes (2**32 - 1 bytes). Your DBA makes sure that a given BFILE
exists and that Oracle has read permissions on it. The underlying operating
system maintains file integrity.
BFILEs do not participate in transactions, are not
recoverable, and cannot be replicated. The maximum number of open
BFILEs is set by the Oracle initialization parameter
SESSION_MAX_OPEN_FILES, which is system dependent.
You use the BLOB datatype to store large binary objects
in the database, in-line or out-of-line. Every BLOB variable stores
a locator, which points to a large binary object. The size of a
BLOB cannot exceed four gigabytes.
BLOBs participate fully in transactions, are
recoverable, and can be replicated. Changes made by package
DBMS_LOB can be committed or rolled back. BLOB
locators can span transactions (for reads only), but they cannot span
sessions.
You use the CLOB datatype to store large blocks of
character data in the database, in-line or out-of-line. Both fixed-width and
variable-width character sets are supported. Every CLOB variable
stores a locator, which points to a large block of character data. The size of a
CLOB cannot exceed four gigabytes.
CLOBs participate fully in transactions, are
recoverable, and can be replicated. Changes made by package
DBMS_LOB can be committed or rolled back. CLOB
locators can span transactions (for reads only), but they cannot span
sessions.
You use the NCLOB datatype to store large blocks of
NCHAR data in the database, in-line or out-of-line. Both
fixed-width and variable-width character sets are supported. Every
NCLOB variable stores a locator, which points to a large block of
NCHAR data. The size of an NCLOB cannot exceed four
gigabytes.
NCLOBs participate fully in transactions, are
recoverable, and can be replicated. Changes made by package
DBMS_LOB can be committed or rolled back. NCLOB
locators can span transactions (for reads only), but they cannot span
sessions.
You use the BOOLEAN datatype to store the logical
values TRUE, FALSE, and NULL (which
stands for a missing, unknown, or inapplicable value). Only logic operations are
allowed on BOOLEAN variables.
The BOOLEAN datatype takes no parameters. Only the
values TRUE, FALSE, and NULL can be
assigned to a BOOLEAN variable. You cannot insert the values
TRUE and FALSE into a database column. Also, you
cannot select or fetch column values into a BOOLEAN variable.
The datatypes in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has a date/time datatype holds values called datetimes; a variable that has an interval datatype holds values called intervals. A datetime or interval consists of fields, which determine its value. The following list shows the valid values for each field:
Except for TIMESTAMP WITH LOCAL TIMEZONE, these types
are all part of the SQL92 standard. For information about datetime and interval
format models, literals, time-zone names, and SQL functions, see Oracle9i
SQL Reference.
You use the DATE datatype to store fixed-length
datetimes, which include the time of day in seconds since midnight. The date
portion defaults to the first day of the current month; the time portion
defaults to midnight. The date function SYSDATE returns the current
date and time.
Tip: To compare dates for equality,
regardless of the time portion of each date, use the function result
TRUNC(date_variable) in
comparisons, GROUP BY operations, and so on.
Valid dates range from January 1, 4712 BC to December 31, 9999 AD. A
Julian date is the number of days since January 1, 4712 BC. Julian dates allow
continuous dating from a common reference. You can use the date format model
'J' with the date functions TO_DATE and
TO_CHAR to convert between DATE values and their
Julian equivalents.
In date expressions, PL/SQL automatically converts character values
in the default date format to DATE values. The default date format
is set by the Oracle initialization parameter NLS_DATE_FORMAT. For
example, the default might be 'DD-MON-YY', which includes a
two-digit number for the day of the month, an abbreviation of the month name,
and the last two digits of the year.
You can add and subtract dates. For example, the following statement returns the number of days since an employee was hired:
SELECT SYSDATE - hiredate INTO days_worked FROM emp WHERE empno = 7499;
In arithmetic expressions, PL/SQL interprets integer literals as
days. For instance, SYSDATE + 1 is tomorrow.
The datatype TIMESTAMP, which extends the datatype
DATE, stores the year, month, day, hour, minute, and second. The
syntax is:
TIMESTAMP[(precision)]
where the optional parameter precision specifies the
number of digits in the fractional part of the seconds field. You cannot use a
symbolic constant or variable to specify the precision; you must use an integer
literal in the range 0 .. 9. The default is 6.
The default timestamp format is set by the Oracle initialization
parameter NLS_TIMESTAMP_FORMAT.
In the following example, you declare a variable of type
TIMESTAMP, then assign a literal value to it:
DECLARE checkout TIMESTAMP(3); BEGIN checkout := '1999-06-22 07:48:53.275'; ... END;
In this example, the fractional part of the seconds field is
0.275.
The datatype TIMESTAMP WITH
TIME ZONE, which extends the datatype
TIMESTAMP, includes a time-zone
displacement. The time-zone displacement is the difference (in hours
and minutes) between local time and Coordinated Universal Time (UTC)--formerly
Greenwich Mean Time. The syntax is:
TIMESTAMP[(precision)] WITH TIME ZONE
where the optional parameter precision specifies the
number of digits in the fractional part of the seconds field. You cannot use a
symbolic constant or variable to specify the precision; you must use an integer
literal in the range 0 .. 9. The default is 6.
The default timestamp with time zone format is set by the Oracle
initialization parameter NLS_TIMESTAMP_TZ_FORMAT.
In the following example, you declare a variable of type
TIMESTAMP WITH TIME ZONE,
then assign a literal value to it:
DECLARE logoff TIMESTAMP(3) WITH TIME ZONE; BEGIN logoff := '1999-10-31 09:42:37.114 +02:00'; ... END;
In this example, the time-zone displacement is
+02:00.
You can also specify the time zone by using a symbolic name. The
specification can include a long form such as 'US/Pacific', an
abbreviation such as 'PDT', or a combination. For example, the
following literals all represent the same time. The third form is most reliable
because it specifies the rules to follow at the point when switching to daylight
savings time.
TIMESTAMP '1999-04-15 8:00:00 -8:00' TIMESTAMP '1999-04-15 8:00:00 US/Pacific' TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'
You can find the available names for time zones in the
TIMEZONE_REGION and TIMEZONE_ABBR columns of the
V$TIMEZONE_NAMES data dictionary view.
Two TIMESTAMP WITH TIME
ZONE values are considered identical if they represent the same
instant in UTC, regardless of their time-zone displacements. For example, the
following two values are considered identical because, in UTC, 8:00 AM Pacific
Standard Time is the same as 11:00 AM Eastern Standard Time:
'1999-08-29 08:00:00 -8:00' '1999-08-29 11:00:00 -5:00'
The datatype TIMESTAMP WITH
LOCAL TIME ZONE, which extends the
datatype TIMESTAMP, includes a time-zone
displacement. The time-zone displacement is the difference (in hours
and minutes) between local time and Coordinated Universal Time (UTC)--formerly
Greenwich Mean Time. You can also use named time zones, as with TIMESTAMP
WITH TIME ZONE.
The syntax is
TIMESTAMP[(precision)] WITH LOCAL TIME ZONE
where the optional parameter precision specifies the
number of digits in the fractional part of the seconds field. You cannot use a
symbolic constant or variable to specify the precision; you must use an integer
literal in the range 0 .. 9. The default is 6.
This datatype differs from TIMESTAMP WITH
TIME ZONE in that when you insert a value into a
database column, the value is normalized to the database time zone, and the
time-zone displacement is not stored in the column. When you retrieve the value,
Oracle returns it in your local session time zone.
In the following example, you declare a variable of type
TIMESTAMP WITH LOCAL TIME
ZONE:
DECLARE logoff TIMESTAMP(3) WITH LOCAL TIME ZONE; BEGIN ... END;
You cannot assign literal values to a variable of this type.
You use the datatype INTERVAL YEAR
TO MONTH to store and manipulate intervals of years
and months. The syntax is:
INTERVAL YEAR[(precision)] TO MONTH
where precision specifies the number of digits
in the years field. You cannot use a symbolic constant or variable to specify
the precision; you must use an integer literal in the range 0 .. 4. The default
is 2.
In the following example, you declare a variable of type
INTERVAL YEAR TO MONTH, then
assign a value of 101 years and 3 months to it:
DECLARE lifetime INTERVAL YEAR(3) TO MONTH; BEGIN lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal lifetime := '101-3'; -- implicit conversion from character type lifetime := INTERVAL '101' YEAR; -- Can specify just the years lifetime := INTERVAL '3' MONTH; -- Can specify just the months ... END;
You use the datatype INTERVAL DAY
TO SECOND to store and manipulate intervals of days,
hours, minutes, and seconds. The syntax is:
INTERVAL DAY[(leading_precision)] TO SECOND[(fractional_seconds_precision)]
where leading_precision and
fractional_seconds_precision specify the number of digits
in the days field and seconds field, respectively. In both cases, you cannot use
a symbolic constant or variable to specify the precision; you must use an
integer literal in the range 0 .. 9. The defaults are 2 and 6,
respectively.
In the following example, you declare a variable of type
INTERVAL DAY TO SECOND:
DECLARE lag_time INTERVAL DAY(3) TO SECOND(3); BEGIN IF lag_time > INTERVAL '6' DAY THEN ... ... END;
PL/SQL lets you construct datetime and interval expressions. The following list shows the operators that you can use in such expressions:
You can also manipulate datetime value using various functions, such
as EXTRACT. For a list of such functions, see Figure 2-3,
"Built-In Functions".
For further information and examples of datetime arithmetic, see Oracle9i SQL Reference and Oracle9i Application Developer's Guide - Fundamentals.
The default precisions for some of the date and time types are less
than the maximum precision. For example, the default for DAY TO
SECOND is DAY(2) TO SECOND(6), while the highest precision
is DAY(9) TO SECOND(9). To avoid truncation when assigning
variables and passing procedure parameters of these types, you can declare
variables and procedure parameters of the following subtypes, which use the
maximum values for precision:
TIMESTAMP_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
YMINTERVAL_UNCONSTRAINED
DSINTERVAL_UNCONSTRAINED
Each PL/SQL base type specifies a set of values and a set of operations applicable to items of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.
Subtypes can increase reliability, provide compatibility with
ANSI/ISO types, and improve readability by indicating the intended use of
constants and variables. PL/SQL predefines several subtypes in package
STANDARD. For example, PL/SQL predefines the subtypes
CHARACTER and INTEGER as follows:
SUBTYPE CHARACTER IS CHAR; SUBTYPE INTEGER IS NUMBER(38,0); -- allows only whole numbers
The subtype CHARACTER specifies the same set of values
as its base type CHAR, so CHARACTER is an unconstrained subtype. But, the subtype INTEGER
specifies only a subset of the values of its base type NUMBER, so
INTEGER is a constrained subtype.
You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax
SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];
where subtype_name is a type specifier used in
subsequent declarations, base_type is any scalar or user-defined
PL/SQL datatype, and constraint applies only to base types that can
specify precision and scale or a maximum size.
Some examples follow:
DECLARE SUBTYPE BirthDate IS DATE NOT NULL; -- based on DATE type SUBTYPE Counter IS NATURAL; -- based on NATURAL subtype TYPE NameList IS TABLE OF VARCHAR2(10); SUBTYPE DutyRoster IS NameList; -- based on TABLE type TYPE TimeRec IS RECORD (minutes INTEGER, hours INTEGER); SUBTYPE FinishTime IS TimeRec; -- based on RECORD type SUBTYPE ID_Num IS emp.empno%TYPE; -- based on column type
You can use %TYPE or %ROWTYPE to specify
the base type. When %TYPE provides the datatype of a database
column, the subtype inherits the size constraint (if any) of the column.
However, the subtype does not inherit other kinds of
constraints such as NOT NULL.
Once you define a subtype, you can declare items of that type. In
the example below, you declare a variable of type Counter. Notice
how the subtype name indicates the intended use of the variable.
DECLARE SUBTYPE Counter IS NATURAL; rows Counter;
The following example shows that you can constrain a user-defined subtype when declaring variables of that type:
DECLARE SUBTYPE Accumulator IS NUMBER; total Accumulator(7,2);
Subtypes can increase reliability by detecting out-of-range values.
In the example below, you restrict the subtype Numeral to storing
integers in the range -9 .. 9. If your program tries to store a number outside
that range in a Numeral variable, PL/SQL raises an exception.
DECLARE SUBTYPE Numeral IS NUMBER(1,0); x_axis Numeral; -- magnitude range is -9 .. 9 y_axis Numeral; BEGIN x_axis := 10; -- raises VALUE_ERROR ... END;
An unconstrained subtype is interchangeable with its base type. For
example, given the following declarations, the value of amount can
be assigned to total without conversion:
DECLARE SUBTYPE Accumulator IS NUMBER; amount NUMBER(7,2); total Accumulator; BEGIN ... total := amount; ... END;
Different subtypes are interchangeable if they have the same base
type. For instance, given the following declarations, the value of
finished can be assigned to debugging:
DECLARE SUBTYPE Sentinel IS BOOLEAN; SUBTYPE Switch IS BOOLEAN; finished Sentinel; debugging Switch; BEGIN ... debugging := finished; ... END;
Different subtypes are also interchangeable if their base types are
in the same datatype family. For example, given the following declarations, the
value of verb can be assigned to sentence:
DECLARE SUBTYPE Word IS CHAR(15); SUBTYPE Text IS VARCHAR2(1500); verb Word; sentence Text(150); BEGIN ... sentence := verb; ... END;
Sometimes it is necessary to convert a value from one datatype to another. For example, if you want to examine a rowid, you must convert it to a character string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.
To convert values from one datatype to another, you use built-in
functions. For example, to convert a CHAR value to a
DATE or NUMBER value, you use the function
TO_DATE or TO_NUMBER, respectively. Conversely, to
convert a DATE or NUMBER value to a CHAR
value, you use the function TO_CHAR. For more information about
these functions, see Oracle9i
SQL Reference.
When it makes sense, PL/SQL can convert the datatype of a value
implicitly. This lets you use literals, variables, and parameters of one type
where another type is expected. In the example below, the CHAR
variables start_time and finish_time hold string
values representing the number of seconds past midnight. The difference between
those values must be assigned to the NUMBER variable
elapsed_time. So, PL/SQL converts the CHAR values to
NUMBER values automatically.
DECLARE start_time CHAR(5); finish_time CHAR(5); elapsed_time NUMBER(5); BEGIN /* Get system time as seconds past midnight. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual; -- do something /* Get system time again. */ SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual; /* Compute elapsed time in seconds. */ elapsed_time := finish_time - start_time; INSERT INTO results VALUES (elapsed_time, ...); END;
Before assigning a selected column value to a variable, PL/SQL will,
if necessary, convert the value from the datatype of the source column to the
datatype of the variable. This happens, for example, when you select a
DATE column value into a VARCHAR2 variable.
Likewise, before assigning the value of a variable to a database column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column. If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. Table 3-1 shows which implicit conversions PL/SQL can do.
Notes:
CLOB and NCLOB, CHAR and
NCHAR, and VARCHAR and NVARCHAR2, can
be substituted for each other.
CLOB
and NCLOB, you must use the conversion functions
TO_CLOB and TO_NCLOB.
TIMESTAMP,
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME
ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO
MONTH can all be converted using the same rules as the
DATE type. However, because of their different internal
representations, these types cannot always be converted to each other. See Oracle9i
SQL Reference for details on implicit conversions between different
date and time types. It is your responsibility to ensure that values are convertible. For
instance, PL/SQL can convert the CHAR value
'02-JUN-92' to a DATE value but cannot convert the
CHAR value 'YESTERDAY' to a DATE value.
Similarly, PL/SQL cannot convert a VARCHAR2 value containing
alphabetic characters to a NUMBER value.
Generally, to rely on implicit datatype conversions is a poor programming practice because they can hamper performance and might change from one software release to the next. Also, implicit conversions are context sensitive and therefore not always predictable. Instead, use datatype conversion functions. That way, your applications will be more reliable and easier to maintain.
When you select a DATE column value into a
CHAR or VARCHAR2 variable, PL/SQL must convert the
internal binary value to a character value. So, PL/SQL calls the function
TO_CHAR, which returns a character string in the default date
format. To get other information such as the time or Julian date, you must call
TO_CHAR with a format mask.
A conversion is also necessary when you insert a CHAR
or VARCHAR2 value into a DATE column. So, PL/SQL calls
the function TO_DATE, which expects the default date format. To
insert dates in other formats, you must call TO_DATE with a format
mask.
When you select a RAW or LONG
RAW column value into a CHAR or VARCHAR2
variable, PL/SQL must convert the internal binary value to a character value. In
this case, PL/SQL returns each binary byte of RAW or
LONG RAW data as a pair of characters. Each character
represents the hexadecimal equivalent of a nibble (half a byte). For example,
PL/SQL returns the binary byte 11111111 as the pair of characters
'FF'. The function RAWTOHEX does the same
conversion.
A conversion is also necessary when you insert a CHAR
or VARCHAR2 value into a RAW or LONG
RAW column. Each pair of characters in the variable must represent
the hexadecimal equivalent of a binary byte. If either character does not
represent the hexadecimal equivalent of a nibble, PL/SQL raises an
exception.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|