Oracle 基础知识：数据库支持的数据类型信息和检索

原创盖国强 2023-02-14

407

在 Oracle 数据库中，支持的数据类型，可以从数据字典中直接得到。

例如，通过创建 col 视图的数据字典，可以清晰的获取类型列表。如下 SQL 中显示的 252 号 Boolean 布尔数据类型，是 Oracle Database 23c 中支持的，所以位列最后。

以下代码引用自 cdcore_str.sql 脚本：

create or replace view col
  (tname, colno, cname, coltype, width, scale, precision, nulls, defaultval,
   character_set_name) as
  select t.name, c.col#, c.name,
         decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                         2, decode(c.scale, null,
                                   decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                  'NUMBER'),
                         8, 'LONG',
                         9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                         12, 'DATE',
                         23, 'RAW', 24, 'LONG RAW',
                         69, 'ROWID',
                         96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                         100, 'BINARY_FLOAT',
                         101, 'BINARY_DOUBLE',
                         105, 'MLSLABEL',
                         106, 'MLSLABEL',
                         111, 'REF '||'"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                         113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                         121, '"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         122, '"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         123, '"'||ut.name||'"'||'.'||'"'||ot.name||'"',
                         178, 'TIME(' ||c.scale|| ')',
                         179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                         180, 'TIMESTAMP(' ||c.scale|| ')',
                         181, 'TIMESTAMP(' ||c.scale|| ')'||' WITH TIME ZONE',
                         231, 'TIMESTAMP(' ||c.scale|| ')'||' WITH LOCAL TIME ZONE',
                         182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                         183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                               c.scale || ')',
                         208, 'UROWID',
                         252, 'BOOLEAN',
                         'UNDEFINED'),
         c.length, c.scale, c.precision#,
         decode(sign(c.null$),-1,'NOT NULL - DISABLED', 0, 'NULL',
        'NOT NULL'), c.default$,
         decode(c.charsetform, 1, 'CHAR_CS',
                               2, 'NCHAR_CS',
                               3, NLS_CHARSET_NAME(c.charsetid),
                               4, 'ARG:'||c.charsetid)
  from  sys.col$ c, sys."_CURRENT_EDITION_OBJ" t, sys.coltype$ ac,
        sys.obj$ ot, sys."_BASE_USER" ut
  where t.obj# = c.obj#
  and   t.type# in (2, 3, 4)
  and   t.owner# = userenv('SCHEMAID')
  and   bitand(c.property, 32) = 0 /* not hidden column */
  and   c.obj# = ac.obj#(+)
  and   c.intcol# = ac.intcol#(+)
  and   ac.toid = ot.oid$(+)
  and   ot.owner# = ut.user#(+)
/

官方文档中描述的数据类型和编码如下表所示：

Code	Data Type	Description
1	`VARCHAR2`(`size` [`BYTE` \| `CHAR`])	Variable-length character string having maximum length `size` bytes or characters. You must specify `size` for `VARCHAR2`. Minimum `size` is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if `MAX_STRING_SIZE` `=` `EXTENDED` 4000 bytes or characters if `MAX_STRING_SIZE` `=` `STANDARD` Refer to Extended Data Types for more information on the `MAX_STRING_SIZE` initialization parameter. `BYTE` indicates that the column will have byte length semantics. `CHAR` indicates that the column will have character semantics.
1	`NVARCHAR2`(`size`)	Variable-length Unicode character string having maximum length `size` characters. You must specify `size` for `NVARCHAR2`. The number of bytes can be up to two times `size` for `AL16UTF16` encoding and three times `size` for `UTF8` encoding. Maximum `size` is determined by the national character set definition, with an upper limit of: 32767 bytes if `MAX_STRING_SIZE` `=` `EXTENDED` 4000 bytes if `MAX_STRING_SIZE` `=` `STANDARD` Refer to Extended Data Types for more information on the `MAX_STRING_SIZE` initialization parameter.
2	`NUMBER` [ (`p` [, `s`]) ]	Number having precision `p` and scale `s`. The precision `p` can range from 1 to 38. The scale `s` can range from -84 to 127. Both precision and scale are in decimal digits. A `NUMBER` value requires from 1 to 22 bytes.
2	`FLOAT` [(`p`)]	A subtype of the `NUMBER` data type having precision `p`. A `FLOAT` value is represented internally as `NUMBER`. The precision `p` can range from 1 to 126 binary digits. A `FLOAT` value requires from 1 to 22 bytes.
8	`LONG`	Character data of variable length up to 2 gigabytes, or 2³¹ -1 bytes. Provided for backward compatibility.
12	`DATE`	Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the `NLS_DATE_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The size is fixed at 7 bytes. This data type contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, and `SECOND`. It does not have fractional seconds or a time zone.
100	`BINARY_FLOAT`	32-bit floating point number. This data type requires 4 bytes.
101	`BINARY_DOUBLE`	64-bit floating point number. This data type requires 8 bytes.
180	`TIMESTAMP` [(`fractional_seconds_precision`)]	Year, month, and day values of date, as well as hour, minute, and second values of time, where `fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values of `fractional_seconds_precision` are 0 to 9. The default is 6. The default format is determined explicitly by the `NLS_TIMESTAMP_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The size is 7 or 11 bytes, depending on the precision. This data type contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, and `SECOND`. It contains fractional seconds but does not have a time zone.
181	`TIMESTAMP` [(`fractional_seconds_precision`)] `WITH` `TIME` `ZONE`	All values of `TIMESTAMP` as well as time zone displacement value, where `fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values are 0 to 9. The default is 6. The default date format for the `TIMESTAMP WITH TIME ZONE` data type is determined by the `NLS_TIMESTAMP_TZ_FORMAT` initialization parameter. The size is fixed at 13 bytes. This data type contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `TIMEZONE_HOUR`, and `TIMEZONE_MINUTE`. It has fractional seconds and an explicit time zone.
231	`TIMESTAMP` [(`fractional_seconds_precision`)] `WITH` `LOCAL` `TIME` `ZONE`	All values of `TIMESTAMP` `WITH` `TIME` `ZONE`, with the following exceptions: Data is normalized to the database time zone when it is stored in the database. When the data is retrieved, users see the data in the session time zone. The default format is determined explicitly by the `NLS_TIMESTAMP_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The size is 7 or 11 bytes, depending on the precision.
182	`INTERVAL` `YEAR` [(`year_precision`)] `TO` `MONTH`	Stores a period of time in years and months, where `year_precision` is the number of digits in the `YEAR` datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183	`INTERVAL` `DAY` [(`day_precision`)] `TO` `SECOND` [(`fractional_seconds_precision`)]	Stores a period of time in days, hours, minutes, and seconds, where `day_precision` is the maximum number of digits in the `DAY` datetime field. Accepted values are 0 to 9. The default is 2. `fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.
23	`RAW`(`size`)	Raw binary data of length `size` bytes. You must specify `size` for a `RAW` value. Maximum `size` is: 32767 bytes if `MAX_STRING_SIZE` `=` `EXTENDED` 2000 bytes if `MAX_STRING_SIZE` `=` `STANDARD` Refer to Extended Data Types for more information on the `MAX_STRING_SIZE` initialization parameter.
24	`LONG RAW`	Raw binary data of variable length up to 2 gigabytes.
69	`ROWID`	Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the `ROWID` pseudocolumn.
208	`UROWID` [(`size`)]	Base 64 string representing the logical address of a row of an index-organized table. The optional `size` is the size of a column of type `UROWID`. The maximum size and default is 4000 bytes.
96	`CHAR` [(`size` [`BYTE` \| `CHAR`])]	Fixed-length character data of length `size` bytes or characters. Maximum `size` is 2000 bytes or characters. Default and minimum `size` is 1 byte. `BYTE` and `CHAR` have the same semantics as for `VARCHAR2`.
96	`NCHAR`[(`size`)]	Fixed-length character data of length `size` characters. The number of bytes can be up to two times `size` for `AL16UTF16` encoding and three times `size` for `UTF8` encoding. Maximum `size` is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum `size` is 1 character.
112	`CLOB`	A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
112	`NCLOB`	A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
113	`BLOB`	A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
114	`BFILE`	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
119	`JSON`	Maximum size is 32 megabytes.

参考文献

1. Oracle Data Types

col datatype

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 基础知识：数据库支持的数据类型信息和检索

评论