Oracle数据类型

-- Oracle Data TypesEach column has a data type, which is associated with a specific storage format, constraints, and valid range of values. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle Database to treat values of one data type differently from values of another. For example, you can multiply values of the NUMBER data type, but not values of the RAW data type.每列都有一个数据类型，包含着特定存储格式，约束，有效范围的值域。例如，你可以把NUMBER 数据类型的值相乘，但不能把RAW数据类型的值相乘。When you create a table, you must specify a data type for each of its columns. Each value subsequently inserted in a column assumes the column data type.当你创建一个表，你必须对它每一列指定一个数据类型。随后插入的每一个值都默认是那个数据类型。Oracle Database provides several built-in data types. The most commonly used data types fall into the following categories:Oracle 数据库提供了几种内建的数据类型。Character Data TypesNumeric Data TypesDatetime Data TypesRowid Data TypesFormat Models and Data TypesOther important categories of built-in types include raw, large objects (LOBs), and collections. PL/SQL has data types for constants and variables, which include BOOLEAN, reference types, composite types (records), and user-defined types.其余重要的内建类型还包括RAW,LOBs,collectionsCharacter Data Types:Character data types store character (alphanumeric) data in strings. The most commonly used character data type is VARCHAR2, which is the most efficient option for storing character data.The byte values correspond to the character encoding scheme, generally called a character set or code page. The database character set is established at database creation. Examples of character sets are 7-bit ASCII, EBCDIC, and Unicode UTF-8.The length semantics of character data types can be measured in bytes or characters. Byte semantics treat strings as a sequence of bytes. This is the default for character data types. Character semantics treat strings as a sequence of characters. A character is technically a code point of the database character set.字符数据类型的长度语义可以以字节或字符为单位。默认是字节。VARCHAR2 and CHAR Data TypesThe VARCHAR2 data type stores variable-length character literals. The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'LILA', 'St. George Island', and '101' are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks so that the database can distinguish them from schema object names.For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the maximum length, in which case the database returns an error. For example, in a single-byte character set, if you enter 10 characters for the last_name column value in a row, then the column in the row piece stores only 10 characters (10 bytes), not 25. Using VARCHAR2 reduces space consumption.In contrast to VARCHAR2, CHAR stores fixed-length character strings. When you create a table with a CHAR column, the column requires a string length. The default is 1 byte. The database uses blanks to pad the value to the specified length.Oracle Database compares VARCHAR2 values using nonpadded comparison semantics and compares CHAR values using blank-padded comparison semanticsOracle不填充未满的varchar，而使用空白填充charEODA@PROD1> create table tmp(a varchar2(20), b char(20));Table created.EODA@PROD1> insert into tmp values('a', 'b');1 row created.EODA@PROD1> commit;Commit complete.EODA@PROD1> select * from tmp;A     B-------------------- --------------------a     bEODA@PROD1> select length(a) a, length(b) b from tmp;   --空白填充了char A    B---------- ---------- 1   20 NCHAR and NVARCHAR2 Data TypesThe NCHAR and NVARCHAR2 data types store Unicode character data. Unicode is a universal encoded character set that can store information in any language using a single character set. NCHAR stores fixed-length character strings that correspond to the national character set, whereas NVARCHAR2 stores variable length character strings.You specify a national character set when creating a database. The character set of NCHAR and NVARCHAR2 data types must be either AL16UTF16 or UTF8. Both character sets use Unicode encoding.When you create a table with an NCHAR or NVARCHAR2 column, the maximum size is always in character length semantics. Character length semantics is the default and only length semantics for NCHAR or NVARCHAR2.当创建数据库指定国家字符集时，NCHAR 和 NVARCHAR2 数据类型的字符集必须是 AL16UTF16 或 UTF8。这两种字符集都使用 Unicode 编码。Datetime Data TypesThe datetime data types are DATE and TIMESTAMP. Oracle Database provides comprehensive time zone support for time stamps.日期时间数据类型包括DATE和 TIMESTAMP。DATE Data TypeThe DATE data type stores date and time. Although datetimes can be represented in character or number data types, DATE has special associated properties. The hire_date column in Example 2-1 has a DATE data type.The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second.数据库在内部将日期存储为数字。日期被存储为固定长度的域，共7 个字节，分别对应世纪、 年、 月、 日、 小时、分、和秒。  1* select to_char(sysdate,'MON DD YYYY') from dualEODA@PROD1> /TO_CHAR(SYSDATE,'MON--------------------OCT 07 2016Table 3-15 Datetime Format Elements http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51077TIMESTAMP Data TypeThe TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. The TIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.TIMESTAMP 数据类型是日期数据类型的扩展。除了存储在 DATE 数据类型的信息，它同时还存储秒的小数部分。TIMESTAMP 数据类型对于存储精确时间的值很有用，比如那些必须跟踪事件顺序的应用程序。Rowid Data TypesOracle Database uses rowids internally for the construction of indexes. A B-tree index, which is the most common type, contains an ordered list of keys divided into ranges. Each key is associated with a rowid that points to the associated row's address for fast access. End users and application developers can also use rowids for several important functions:Oracle 数据库在内部使用 rowids用于构造索引，最常见的 B 树索引，包含一个被划分成多个键范围的排序列表。每个键都与一个指向关联的行地址的 rowid 相关联，用于快速访问。Rowids are the fastest means of accessing particular rows. --访问特定行的最快方式Rowids provide the ability to see how a table is organized. --查看表的组织方式Rowids are unique identifiers for rows in a given table.  --行的唯一标识符You can also create tables with columns defined using the ROWID data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on.ROWID为该表行的唯一标识，是一个伪列，可以用在SELECT中，但不可以用INSERT, UPDATE来修改该值。EODA@PROD1> select rowid from dual;ROWID------------------AAAAB0AABAAAAOhAAAAn extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:OOOOOO  --obj#The data object number identifies the segment . A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.FFF  --rfile#The tablespace-relative data file number identifies the data file that contains the row .BBBBBB  --block#The data block number identifies the block that contains the row . Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.RRR  --row#The row number identifies the row in the block .