oracle内置数据类型有：
- CHAR,VARCHAR2,NCHAR,NVARCHAR2
- NUMBER
- RAW
- LONG,LONG RAW
- DATE
- TIMESTAMP
- INTERVAL
- CLOB,BLOB,NCLOB,BFILE
- ROWID,UROWID
每列的数据类型并不存储在数据块中，而是存储在数据字典col$.type#。
下面总结一下常用的几种数据类型：
字符类型
- char and varchar2(stroed in the database (single byte) character set）
- char：字符长度上限为2000 characters，internal code=96
- varchar2：字符长度上限为4000 characters，internal code=1
- nchar and nvarchar2(stroed in a national (multi byte) character set）
- nchar：字符长度上限为2000 characters，internal code=96
- nvarchar2：字符长度上限为4000 characters，internal code=1
Single-byte character sets can only encode 255 distinct characters at most. Several character sets have more than 255 characters, so need multiple bytes to represent them all. Unicode now encodes over 109000 characters across different scripts. http://en.wikipedia.org/wiki/Unicode http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch6unicode.htm
字符类型按照acsii码进行转换，参考http://www.asciitable.com/
数值类型number
- number类型存储在一个可变长的数组中
- 存储格式为：length byte，sign bit/exponent，dight1，digit2，…，digit20
- internal code=2
- 首尾的0将被剥离
- 0存储为0x80(128）
Both NUMBER and FLOAT data types share the same internal implementation. 20 digit bytes maximum + 1 exponent byte = 21 bytes. Yet numbers have a max length of 22. Why? It allows the length byte to be included with the number as an argument passed internally in code for backward compatibility with older code that hasn’t been touched. Internal API prefers numbers passed as two arguments (input_length, input_value)
number类型内部存储主要分为2部分：
- first bit：sign/offset/exponent
|
1 2 3 |
- sign bit：正数为128，负数为0 - offset：等于65 - exponent：范围在-65 到62之间，底数为100 |
- digit bit(最多20 byte)
|
1 2 3 |
-每个字节value取值范围在0到99之间 -如果是正数，则存储value+1；如果是负数，则存储101-value -如果是负数，末尾字节以0x66(102)结尾 |
正数数值转换：
This number is positive because the first byte is greater than 128 (0x80).
- Step 1 Exponent = first byte – (128 + 65 offset or 193)
- Step 2 Subtract 1 from every other digit represented in decimal.
- Step 3 Convert from base 100 to base 10.
|
1 2 3 |
Multiply each resulting digit by 100^(EXP - N), where: -EXP is the exponent value from step 1 -N is the digit’s placement order (for most significant digit N = 0) |
- Step 4 Add all the values.
|
1 2 3 4 5 6 7 8 9 10 |
For Number 1200: Its Dump shows Typ=2 Len=2: 194,13 1st byte = 194 decimal --> 11000010 binary Number is positive as bit 8 (1 1000010 ) is set to 1. The exponent value is 1000010 binary which is 66 decimal. Since the offset is 65, deduct 65 from 66 giving 1. => Exponent is 1 and positive. 2nd byte = 13 decimal As the sign is positive deduct 1 from 13 (as it is stored with one added) to get 12 The exponent is 1, the data bytes are in base 100, so the number is 12 * (100 ^ 1) =12 * 100 = 1200 |
负数数值转换：
This number is negative because the first byte is less than 128 (0x80).
- Step 1 Exponent = 62 – first byte
- Step 2 Subtract every other digit from 101.
- Step 3 Convert from base 100 to base 10.
|
1 2 3 |
Multiply each resulting digit by 100^(EXP - N), where: -EXP is the exponent value from step 1 -N is the digit’s placement order (N starts at 0 for the most significant digit) |
- Step 4 Make sure to ignore the trailing byte if it is 0x66 (102).
- Step 5 Add all the values.
If a negative number requires 21 bytes for its full representation, the trailing sort byte (0x66=102) is no longer required. Because the Oracle server does not represent any number using 22 bytes, the sorting conflict that forced the introduction of the trailing byte does not occur. In this case, all the bytes will be considered.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
For Number -1200 Its Dump shows Typ=2 Len=3: 61,89,102 1st byte = 61 decimal --> 00111101 binary Number is negative as the high order bit is not set. Take the 1's complement of the remaining bits 0111101 which is 1000010 or 66 decimal As there is an offset of 65, subtract 65 from 66 giving 1. Exponent is positive and is 1. 2nd byte = 89 decimal Since number is negative take 101 – 89 = 12 3th byte = 102 decimal (0x66). This is the trailing byte for negative numbers so throw it away. The calculation is 12 * (100 ^ 1) = 12* 100 =1200 and as the sign bit is negative, the answer is -1200. |
Hexidecimal to Number Conversion. (文档 ID 119698.1)
时间类型date：
- date数据类型存储的信息有世纪、年、月、日、时、分、秒，不包括分秒以及time zone，internal code=12
- date数据类型存储占用7字节
- 前两个字节存储世纪和年(减100），接下来两个字节存储月和日，最后三个字节存储时分秒（减1）
|
1 2 3 4 5 6 7 8 9 10 11 |
for example： Col1 is date column and have value : 01-Apr-2011 09:46:22 A dump of this column is as follows : Typ=12 Len=7: 120,111,4,1,10,47,23 This is how the information is stored: Century: 120-100 = 20 Year: 111-100 = 11 Month: 4 Day: 1 Hours: 10-1 = 9 Minutes: 47-1 = 46 Seconds: 23-1 = 22 |