Oracle 数据类型及存储方式(免费)
Oracle 数据类型及存储方式
袁光东原创
概述
通过实例，全面而深入的分析oralce的基本数据类型及它们的存储方式。以ORACLE 10G为基础，介绍oralce 10g引入的新的数据类型。让你对oracle数据类型有一个全新的认识。揭示一些不为人知的秘密和被忽略的盲点。从实用和优化的角度出发，讨论每种数据类型的特点。从这里开始oracle之旅！
第一部份字符类型
§1.1char
定长字符串，会用空格来填充来达到其最大长度,最长2000个字节。
1．新建一个测试表test_char.,只有一个char类型的列。长度为10
SQL> create table test_char(colA char(10));
T able created
2．向这个表中插入一些数据。
SQL> insert into test_char values('a');
1 row inserted
SQL> insert into test_char values('aa');
1 row inserted
SQL> insert into test_char values('aaa');
1 row inserted
SQL> insert into test_char values('aaaa');
1 row inserted
SQL> insert into test_char values('aaaaaaaaaa');
1 row inserted
注意：最多只能插入10个字节。否是就报错。
SQL> insert into test_char values('aaaaaaaaaaa');
insert into test_char values('aaaaaaaaaaa')
ORA-12899: value too large for column "PUB_TEST"."TEST_CHAR"."COLA" (actual: 11, maximum: 10)
3．使用dump函数可以查看每一行的内部存数结构。
SQL> select colA, dump(colA) from test_char;
COLA DUMP(COLA)
---------- --------------------------------------------------------------------------------
a Typ=96 Len=10: 97,32,32,32,32,32,32,32,32,32
aa Typ=96 Len=10: 97,97,32,32,32,32,32,32,32,32
aaa Typ=96 Len=10: 97,97,97,32,32,32,32,32,32,32
aaaa Typ=96 Len=10: 97,97,97,97,32,32,32,32,32,32
aaaaaaaaaa Typ=96 Len=10: 97,97,97,97,97,97,97,97,97,97
注意：Typ=96 表示数据类型的ID。Oracle为每一种数据类型都进行了编号。说明char类型的编号是96. Len =10 表示所在的内部存储的长度(用字节表示)。虽然第一例只存了一个字符’a’，但是它还是占用了10个字节的空间。
97,32,32,32,32,32,32,32,32,32 表示内部存储方式。可见oracle的内部存储是以数据库字符集进行存储的。
97正好是字符a的ASCII码。
可以使用chr函数把ASCII码转成字符。
SQL> select chr(97) from dual;
CHR(97)
-------
a
要想知道一个字符的ASCII码，可以使用函数ascii
SQL> select ascii('a') from dual;
ASCII('A')
----------
97
32正好是空格的ascii码值。
Char类型是定长类型。它总会以空格来填充以达到一个固定宽度。
使用char类型会浪费存储空间。
Oracle的数据类型的长度单位是字节。
SQL> select dump('汉') from dual;
DUMP('汉')
---------------------
Typ=96 Len=2: 186,186
可见一个汉字在oracle中是占用了两个字节的。
英文字母或符号只占用一个字节。
Char(10)最多可存放5个汉字。
§1.2varchar2
是一种变长的字符类型。最多可占用4000字节的存储空间。
1. 创建一个表,只有一列，类型为varchar2,长度为10
SQL> create table test_varchar( col varchar2(10));
T able created
2. 插入一些数据
SQL> insert into test_varchar values('a');
1 row inserted
SQL> insert into test_varchar values('aa');
1 row inserted
SQL> insert into test_varchar values('aaa');
1 row inserted
SQL> insert into test_varchar values('aaaaaaaaaa');
1 row inserted
SQL> insert into test_varchar values('aaaaaaaaaaa');
2. 用dump函数查看每一行的内部存储结构。
SQL> select col, dump(col) from test_varchar;
COL DUMP(COL)
---------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
aa Typ=1 Len=2: 97,97
aaa Typ=1 Len=3: 97,97,97
aaaaaaaaaa Typ=1 Len=10: 97,97,97,97,97,97,97,97,97,97
Typ=1,说明varchar2类型在oracle中的类型编号为1
Len代表了每一行数据所占用的字节数。
后面是具体的存储值。
由此可见，varchar2是存多少就占用多少空间。比较节省空间的。不会像char那样用空格填充。
§1.3byte 和char
在10g中，字符类型的宽度定义时，可以指定单位。
Byte就是字节。
Char就是字符。
Varchar2(10 byte) 长度为10个字节。
Varchar2(10 char) 长度为10个字符所占的长度。
Char(10 byte)长度为10个字节。
Char(10 char) 长度为10个字符所占的长度。
一个字符占用多少个字节，是由当前系统采用的字符集来决定的。
如一个汉字占用两个字节。
查看当前系统采用的字符集
SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------
--------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
如果在定义类型时，不指定单位。默认是按byte，即以字节为单位的。
采用char为单位的好处是，使用多字节的字符集。
比如，在ZHS16GBK字符集中，一个汉字占用两个字节。
把数据表的某一列长度定义为可存放10个汉字，通过下面的定义就可以了。
Create table test_varchar(col_char varchar2(10 char));
这样相对简单一些。在数据库表设计时需要注意。
继续实验，新建一个表，包含两列。一列采用byte为单位，一列采用char为单位
SQL> create table test_varchar2 (col_char varchar2(10 char),col_byte varchar2(10 byte)); T able created
Col_char列，定义为可存放10个字符。
Col_byte 列，定义为可存放10个字节的字符。
当前的系统采用字符集为ZHS16GBK.所以一个字符占两个字节。
试着在表中插入一些数据
SQL> insert into test_varchar2 values('a','a');
1 row inserted
SQL> insert into test_varchar2 values('袁','a');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁'); insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁')
ORA-12899: value too large for column "PUB_TEST"."TEST_VARCHAR2"."COL_BYTE" (actual: 20, maximum: 10)
第一次，在两列中都插入字符a
第二次，在col_char列插入字符’袁’,在col_byte插入字符a
第三次，在col_char列中插入10个中文字符’袁’,在col_byte插入10个字符a
第四次，在两列中都插入中文字符’袁’时，报错了。第二列长度不够。
再看看每一行的存储结构
SQL> select col_char, dump(col_char) from test_varchar2;
COL_CHAR DUMP(COL_CHAR)
-------------------- --------------------------------------------------------------------------------
a Typ=1 Len=1: 97
袁Typ=1 Len=2: 212,172
袁袁袁袁袁袁袁袁袁袁Typ=1 Len=20: 212,172,212,172,212,172,212,172,212,172,212,172,212,172,212,172,21
当我们在col_char列插入10个汉字时，它的长度为20.
尽管我们在定义的时候是采用varchar2(10,char).
由此可见,oracle是根据当前数据库采用的字符集，每个字符的所占字节数X 字段长度来决定了该字段所占的字节数。
在本例中，varchar2(10,char)相当于varchar2(20).
不信，我们可以试试看。
SQL> desc test_varchar2;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
COL_CHAR VARCHAR2(20) Y
COL_BYTE VARCHAR2(10) Y
当采用多字节的字符集时，定义字段长度还是采用char为单位指定为佳。因为可以避免字段长度的问题。
当不知道当前数据库采用的字符集，一个字符占用多少字节时，可以使用lengthb函数。
SQL> select lengthb('袁') from dual;
LENGTHB('袁')
-------------
2
§1.4char还是varchar
1. 新建一个表,一列为char类型，一列为varchar2类型
SQL> create table test_char_varchar(char_col char(20),varchar_col varchar2(20));
T able created
2. 向该表中的两列都插入相关的数据
SQL> insert into test_char_varchar values('Hello World','Hello World');
1 row inserted
SQL> select * from test_char_varchar;
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
3. 以char_col列为条件查询
SQL> select * from test_char_varchar where char_col ='Hello World';
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
4. 以varchar_col列为条件查询
SQL> select * from test_char_varchar where varchar_col ='Hello World';
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
5.似乎char 和varchar类型没有什么两样。再看看下面的语句。
SQL> select * from test_char_varchar where varchar_col =char_col;
CHAR_COL VARCHAR_COL
-------------------- --------------------
这已经看出他们并不一样，这涉及到字符串比较的问题。
因为已经发生了隐式转换,在与char列char_col进行比较时，char_col列的内容已经转换成了char(20).在Hello World后面以空格进行填充了。而varchar_col列并没有发生这种转换。
如果要让char_col列与varchar_col列相等。有两种方法。
第一种是：使用trim把char_col列的空格去掉。
第二种是：使遥rpad把varchar_col列用空格进行填充长度为20的字符。
SQL> select * from test_char_varchar where trim(char_col) = varchar_col;
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
SQL> select * from test_char_varchar where char_col = rpad(varchar_col,20);
CHAR_COL VARCHAR_COL
-------------------- --------------------
Hello World Hello World
如果使用trim函数，如果char_col列上有索引，那么索引将不可用了。
此外还会在绑定变量时出现问题。
§1.5NCHAR和NVARCHAR2
如果系统需要集中管理和存储多种字符集，就需要使用这两种字符类型。在使用NCAHR和NVARCHAR2时，文本内容采用国家字符集来存储和管理。而不是默认字符集。
这两种类型的长度指的是字符数，而不是字节数。
NLS国家语言支持(National Language Support)
在oracle 9i及以后的版本，数据库的国家字符集可以是：utf-8和AL16UTF-16两种。
Oracle 9i是utf -8, Oralce 10g是AL16UTF-16.
1.新建一个表,有两列，类型分别为：nchar和nvarchar
2.长度都为10
SQL> create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10));
T able created
2.插入一些数据
SQL> insert into test_nvarchar values('袁','袁光东');
1 row inserted
SQL> insert into test_nvarchar values(N'袁',N'袁光东');
1 row inserted
(在9i之前的版本，插入时加上N时，在处理时跟普通方式有不同的方式。但是在10g的时候已经有了改变，加不加N都是一样,这里只是为了测试)
SQL> insert into test_nvarchar values('a','b');
1 row inserted
插入一行英文字母
3. 查看每行的col_nchar列的存储方式。
SQL> select col_nchar, dump(col_nchar) from test_nvarchar;
COL_NCHAR DUMP(COL_NCHAR)
-------------------- --------------------------------------------------------------------------------
袁Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
a Typ=96 Len=20: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
袁Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
Typ=96 与char的类型编码一样。
Len=20 每一行的长度都是20字节。这一点跟char一样。都是定长的，会以空格填充。
需要注意的是：统统以两位来表示一个字符。
136,129 表示’袁’
0,97 表示’a’
0,32 表示空格。
4. nvarchar2的储存
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar;
COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁光东Typ=1 Len=6: 136,129,81,73,78,28
b Typ=1 Len=2: 0,98
袁光东Typ=1 Len=6: 136,129,81,73,78,28
Typ=1 与varchar2一样。
每一行的len值都不样同。不会使用空格进行填充。
每一个字符都占有两个字节两进行存储。
b 存储为：0, 98
袁存储为：136,129
5.nchar和nvarchar2的数据定义。
SQL> desc test_nvarchar;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
COL_NCHAR NCHAR(20) Y
COL_NVARCHAR2 NVARCHAR2(20) Y
虽然在定义nchar和nvarchar2时，指定的长度是指字符数。但是表结构的定义中，仍然是存储着它的字节数。
在定义时nchar(10)表示可以最大存储10个字符。
在查看数据表结构时，显示该列最大占用的字节数。
需要注意的是：在char和nchar中对汉字的实际存储值是不一样的。因为采用了不同的字符集，就有了不同的字符编码。
SQL> insert into test_varchar values('袁');
1 row inserted
SQL> select col, dump(col) from test_varchar where col='袁';
COL DUMP(COL)
---------- --------------------------------------------------------------------------------
袁Typ=1 Len=2: 212,172
这时采用的字符集系统默认字符集ZHS16GBK。
这里很容易的把它转换成ascii码。
高位* 256(2的8次方) + 低位.
212 * 256 + 172 = 54444
SQL> select chr(54444) from dual;
CHR(54444)
----------
袁
而在Nchar 和Nvarchar中，采用的是UTF-8或UTF-16的字符集。
SQL> insert into test_nvarchar values('袁','袁');
1 row inserted
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2='袁';
COL_NVARCHAR2 DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁Typ=1 Len=2: 136,129
‘袁’存储的值为：136,129
Oracle 10以上对nchar和nvarchar都采用utf-16字符集了。它的好处就是对字符采用固定长度的字节存储(2字节),支持多国字符，在操作效率上会更高。但是它却无法兼容于ascii码。
§1.6RAW
RAW与CHAR和VARCHAR2相比。RAW属于二进制数据，更可以把它称为二进制串。在对CHAR和VARCHAR2类型进行存储时，会进行字符集转换。而对二进制数据进行存储则不会进行字符集转换。SQL> create table test_raw (col_chr varchar2(10), col_raw raw(10));
T able created
SQL> insert into test_raw values('aa','aa');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_raw;
COL_CHR COL_RAW
---------- --------------------
aa AA
SQL> select col_chr,dump(col_chr) from test_raw;
COL_CHR DUMP(COL_CHR)
---------- --------------------------------------------------------------------------------
aa Typ=1 Len=2: 97,97
SQL> select col_raw,dump(col_raw) from test_raw;
COL_RAW DUMP(COL_RAW)
-------------------- --------------------------------------------------------------------------------
AA Typ=23 Len=1: 170
通过上面的分析，虽然我们通过select查询得到的结果，raw列显示为插入的字符。但是我们通过dump 函数得知到raw并不是以字符的方式存储。它是把插入的字符认为是16进制的值。
比如本例，我们向raw列插入aa,但是它占用的空间为1个字节。值为170.
170转为16进制正好是aa
向raw列插入数据时会发生一个隐式转换HEXTORAW
从raw列读取数据时会发生一个隐式转换RAWTOHEX
如果向raw列插入值不是有效的十六进制值时，会报错的。
SQL> insert into test_raw values('h','h');
insert into test_raw values('h','h')
ORA-01465: invalid hex number
第二部分数值类型
§ 2.1number
Number类型是oralce的数值类型，存储的数值的精度可以达到38位。Number是一种变长类型,长度为0-22字节。取值范围为:10e-130 – 10e 126(不包括)
Number(p,s)
P和s都是可选的。
P指精度(precision)，即总位数。默认情况下精度为38。精度的取值范围为1~38.
S指小数位(scale).小数点右边的位数。小数点位数的合法值为-48~127。小数位的默认值由精度来决定。如果没有指定精度，小数位默认为最大的取值区间.如果指定了精度，没有指定小数位。小数位默认为0(即没有小数位).
精度和小数位不会影响数据如何存储，只会影响允许哪些数值及数值如何舍入。
1.新建一个表
SQL> create table test_number(col_number number(6,2));
T able created
2.插入一些不同的数据
SQL> insert into test_number values(-1);
1 row inserted
SQL> insert into test_number values(0);
1 row inserted
SQL> insert into test_number values(1);
1 row inserted
SQL> insert into test_number values(2);
1 row inserted
SQL> insert into test_number values(11.00);
1 row inserted
SQL> insert into test_number values(11.11);
1 row inserted
SQL> insert into test_number values(1234.12);
1 row inserted
SQL> insert into test_number values(-0.1);
1 row inserted
SQL> insert into test_number values(-11.11);
1 row inserted
SQL> insert into test_number values(-1234.12);
1 row inserted
SQL> commit;
Commit complete
3.查看结果
SQL> select * from test_number;
COL_NUMBER
----------
-1.00
0.00
1.00
2.00
11.00
11.11
1234.12
-0.10
-11.11
-1234.12
10 rows selected
5. 查看存储结构
SQL> select col_number, dump(col_number) from test_number;
COL_NUMBER DUMP(COL_NUMBER)
---------- -------------------------------------------------------------------------------- -1.00 Typ=2 Len=3: 62,100,102
0.00 Typ=2 Len=1: 128
1.00 Typ=2 Len=2: 193,2
2.00 Typ=2 Len=2: 193,3
11.00 Typ=2 Len=2: 193,12
11.11 Typ=2 Len=3: 193,12,12
1234.12 Typ=2 Len=4: 194,13,35,13
-0.10 Typ=2 Len=3: 63,91,102
-11.11 Typ=2 Len=4: 62,90,90,102
-1234.12 Typ=2 Len=5: 61,89,67,89,102
10 rows selected
由此可见：
Number类型的内部编码为：2
根据每一行的len值可以看出，number是一个变长类型。不同的数值占用不同的空间。如果指定了精度，显示结果与精度相关。
就像我插入语句写为
insert into test_number values(0);
但是显示结果为：0.00
如果数值是负数，在最后一位上填充一个补码102.即表示该数值为负数。
0是一个特殊的值，它在oracle中存储为128.
第一位为标志位。以128为比较。如果数值大于128,则它大于0。如果小于128小于0。
-1的内部存储为：
-1.00 Typ=2 Len=3: 62,100,102
最后一位是102，是一个负数。
第一位小于128，所以小于10.
除了第一位标志位外，其它的都是数值为了。
如果该值是一个正数。每一位的存储值减1为每一位的实际值。
1.0的存储结构为：
1.00 typ=2 Len=2: 193,2
实值上1.00的存储结果与1相同。
第一位193为标志位，大于128，大于0.
第二位为数值为，因为是正数，实际值为存储值减1。2-1 = 1。
如是该值是一个负数，每一位的实际值为101 减去存储的值。
-1.00的存储结构为：
-1.00 Typ=2 Len=3: 62,100,102
最后一位102为补位。
第一位62为标志位，小于128。实际值小于0.
第二位为数值为，因为是负数。实际值为:101 – 100 =1.
§2.2 小数位在哪里？
从上面的存储结果看，对小数存储时，它并没有一个小数的标志位。但是它实际上是由第一位标志位，和数值位(第二位)来决定的。
当存储的数是一个正数,该数值的前几位为：第一位* power(100 , (标志位- 193));
当存储的数是一个负数，该数值的前几位为：第一位* power(100,(62 –标志位));
11.11的存储结果为：
11.11 Typ=2 Len=3: 193,12,12
第一位数值位为：12 实际数值为11
标志位为：193
12 * power(100, (193- 193);
100的零次方为1.
12 乘1 等于12.
所以这个数的前几位为：12。从这后面就是小数了。
1234.12的存储结构为：
1234.12 Typ=2 Len=4: 194,13,35,13
第一位数值位为：13,实际值为12
标志位为：193
13 * power(100,(194-193)) = 1300
所以前四位为整数位，后面的为小数位。
-0.10的存储结构为：
-0.10 Typ=2 Len=3: 63,91,102
标志位为：63
第一位数值为：91 ，实际值为：10
91 * (100,(62-63)) =-9100.
所以小数位在91之前。
-1234.12的存储结构为：
-1234.12 Typ=2 Len=5: 61,89,67,89,102
标志位为：61
第一位数值为：89
89*(100,(62-61)) =8900
所以小数位在67之后。
§2.3 number的精度和小数位
Number类型的精度最多可是38位。小数位-84--127位。
SQL> create table test_number1(col_number number(39));
create table test_number1(col_number number(39))
ORA-01727: numeric precision specifier is out of range (1 to 38)
指定小数位时，精度只能是1-38。不能是0
SQL> create table test_number1(col_number number(0,127));
create table test_number1(col_number number(0,127))
ORA-01727: numeric precision specifier is out of range (1 to 38)
SQL> create table test_number1(col_number number(1,128));
create table test_number1(col_number number(1,128))
ORA-01728: numeric scale specifier is out of range (-84 to 127)
精度与小数位的关系。精度并不是小数位加整数位之和。
我们先看看小数位为0的情况。
SQL> create table test_number1(col_char varchar2(200), col_num number(10));
T able created
Number(10).只定义了精度，小数位为0.
看看它可以存放的数据。
SQL> insert into test_number1 values('9999999999',9999999999);
1 row inserted
插入了10个9，没有问题，再插入多一位看看
SQL> insert into test_number1 values('99999999991',99999999991);
insert into test_number1 values('99999999991',99999999991)
ORA-01438: value larger than specified precision allowed for this column
报错了，精度不够。
再看看能不能再插入小数？
SQL> insert into test_number1 values('0.9',0.9);
1 row inserted
SQL> select * from test_number1;
Col_char COL_NUM
-------------------- --------------
9999999999 9999999999
0.9 1
注意插入数值0.9后，存储为1.这就是小数位的作用。在哪里进行舍入。
带小数位和精度的情况。
SQL> create table test_number2(col_char varchar(20),col_num number(1,3));
T able created
精度是1，小数位是3.
可见，精度不是小数位加整数位了。但是精度和小数位倒底什么关系呢？
SQL> insert into test_number2 values('0.111',0.111);
insert into test_number2 values('0.111',0.111)
ORA-01438: value larger than specified precision allowed for this column 插入3位小数,0.111竟然报错了，说精度不够。
SQL> insert into test_number2 values('0.001',0.001);
1 row inserted
插入0.001时，成功了。
SQL> insert into test_number2 values('0.001',0.0015);
1 row inserted
插入0.0015也成功了。
看看插入的值。
SQL> select * from test_number2;
COL_CHAR COL_NUM
-------------------- -------
0.001 0.001
0.0015 0.002
需要注意的是0.0015被舍入为0.002
精度大于小数位
SQL> create table test_number3 (col_char varchar(20), col_number number(5,3));
T able created
SQL> insert into test_number3 values('99.899',99.899);
1 row inserted
SQL> insert into test_number3 values('99.999',99.999);
1 row inserted
SQL> insert into test_number3 values('99.9999',99.9999);
insert into test_number3 values('99.9999',99.9999)
ORA-01438: value larger than specified precision allowed for this column
注意，当插入99.9999时，系统报错。因为小数位为3位。第四位小数位是9,于是往前入。最终变成100.000.就已经超过了精度。
Number(5,3)可存储的数值最大为99.999.
现在终于有点明白小数位与精度的关系了。
number(38,127)
可以存储的最大小数为：127位小数，最后38为9.
即：0.00000000000000000000000000000000000000000000000000000000000000000000000000000 00000000000099999999999999999999999999999999999999
小数位为负数。
我们从前面知道，小数位的取值为-48 ~127
为什么小数位会为负数？这有点怪异了。像上面的number(5,3)将值舍入为最接近0.001
Number(5,-3)就是将值舍入为最接近的1000
SQL> create table test_number5 (col_char varchar(20), col_num number(5,-3));
T able created
插入值10999
SQL> insert into test_number5 values('10999',10999);
1 row inserted
查看一下结果
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
10999 11000
存储的结果为：11000
当小数部分为负数时，是对小数部分进行舍入。
那么精度在这时起到什么作用呢？与小数位又有什么关系？
SQL> insert into test_number5 values('111111111',111111111);
insert into test_number5 values('111111111',111111111)
ORA-01438: value larger than specified precision allowed for this column
插入9个1时，报错精度不够。
SQL> insert into test_number5 values('11111111',11111111);
1 row inserted
插入8个1时，正确插入。
我们看看它的结果，看它是怎么舍入的。
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
11111111 11111000
结果是1111100而不是1111100
无限接近1000，就是从百位开始进行四舍五入，后面的值全部为0。
所以看出number(5,-3)可存储的最大值为：99999000
SQL> insert into test_number5 values('99999499.999999',99999499.999999);
1 row inserted
SQL> select * from test_number5;
COL_CHAR COL_NUM
-------------------- -------
99999999 99999000
99999499.999999 99999000
现在应该明白了精度和小数位的关系了吧。
小数位告诉系统保留多少位小数，从哪里开始舍入。
精度舍入后，从舍入的位置开始，数值中允许有多少位。
§2.4binary_float 和binary_double
这两种类型是oracle 10g新引进的数值类型。在oracle 10g之前是没有这两种类型的。
Number类型是由oracle软件支持的类型。而浮点数用于近似数值。但是它浮点数允许由在硬盘上(CPU,芯片)上执行运行。而不是在oracel进程中运算。如果希望在一个科学计算中执行实数处理，依赖于硬件的算术运算速度要快得多。但是它的精度却很小。如果希望用来存储金融数值，则必须用number. BINARY_FLOAT是一种IEEE固有的单精度浮点数。可存储6位精度，取值范围在~±1038.25的数值。BINARY_DOUBLE是一种IEEE固有的双精度浮点数。可存储12位精度。取值范围在~±10308.25的数值SQL> create table test_floatdouble(col_number number, col_float binary_float, col_double binary_double);
T able created
SQL> insert into test_floatdouble values(9876543210.0123456789,9876543210.0123456789,9876543210.0123456789);
1 row inserted
2 SQL> select to_char(col_number), to_char(col_float), to_char(col_double) from test_floatdouble; 3
4
TO_CHAR(COL_NUMBER) TO_CHAR(COL_FLOAT) TO_CHAR(COL_DO UBLE)
5 ---------------------------------------- ---------------------------------------- ----------------------------------------
6
9876543210.0123456789 9.87654349E+009 9.8765432100123463E+ 009
由此可见，binary_float无法表示这个数。Binary_float和binary_double无法用于对精度要求高的数据。
SQL> select dump(col_float)from test_floatdouble;
DUMP(COL_FLOAT)
--------------------------------------------------------------------------------
Typ=100 Len=4: 208,19,44,6
BINARY_FLOAT 类型编码为100
Len=4 占用4个字节。它是采用固定字节进行存储的。
SQL> select dump(col_double)from test_floatdouble;
DUMP(COL_DOUBLE)
--------------------------------------------------------------------------------
Typ=101 Len=8: 194,2,101,128,183,80,25,73
BINARY_DOUBLE 类型编码为101
Leng= 8 占用8个字节。也是采用固定字节进行存储。
注意：number 类型使用的CPU时间是浮点数类型的50倍。浮点数是数值的一个近似值，精度在6-12位之间。从Number类型得到的结果要比从浮点数得到的结果更精确。但在对科学数据进行数据挖掘和进行复杂数值分析时，精度的损失是可以接受的，还会带来显著的性能提升。
这时需要使用内置CAST函数，对NUMBER类型执行一种实时的转换，在执行复杂数学运算之前先将其转换为一种浮点数类型。CPU使用时间就与固有浮点类型使用的CPU时间非常接近了。
Select ln(cast(number_col as binary_double)) from test_number.
§2.5 Oracle在语法上还支持的数值数据类型
NUMERIC(p,s):完全映射到NUMBER(p,s)。如果p未指定，则默认为38.
DECIMAL(p,s)或DEC(p,s):同NUMERIC(p,s).
INTEGER或int:完全映射至NUMBER(38)
SMALLINT:完全映射至NUMBER(38)
FLOAT(b):映射至NUMBER
DOUBLE PRECISION:映射到NUMBER
REAL:映射到NUMBER.
第三部分日期时间类型
§3.1DATE
Date类型Oralce用于表示日期和时间的数据类型。固定占用7个字节。
包括七个属性：
世纪
世纪中的年份
月份
月份中的哪一天
小时
分
秒
SQL> create table test_date(col_date date);
T able created
SQL> insert into test_date values(to_date('2008-06-27 10:35:00','yyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> select to_char(col_date,'yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;
TO_CHAR(COL_DATE,'YYYY-MM-DDHH DUMP(COL_DATE)
------------------------------
--------------------------------------------------------------------------------
2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1
Date类型的内部编码为12
长度:占用7个字节
数据存储的每一位到第七位分别为：世纪，年，月，日，时，分，秒。
世纪：采用”加100”表示法来存储。即世纪+100来存储。120 – 100 = 20
年:跟世纪一样采用”加100”表示法来存储。108 – 100 = 08(采用两位表示)
月:自然存储.6
日:自然存储，不做修改，27
时：（时，分，秒都采用“加1”法存储）11 -1= 10
分:36 -1 = 35
秒:1 -1 = 0
为什么世纪和年份要用加100法存储呢？是为了支持BC和AD日期。
BC即为公元前。
AD即为公元。
如果世纪– 100为一个负数，那么就是一个BC日期。
插入一个公元前日期
SQL> insert into test_date values(to_date('-4712-01-01','syyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> select to_char(col_date,'bc yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date;
TO_CHAR(COL_DATE,'BCYYYY-MM-DD DUMP(COL_DATE)
------------------------------
--------------------------------------------------------------------------------
公元2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1
公元前4712-01-01 00:00:00 Typ=12 Len=7: 53,88,1,1,1,1,1
我们已经了解了日期的存储结构。当要对日期进行截取时，比如去掉时，分，秒。只需要把最后的三个字节设为：12 12 1就可以了。
SQL> create table test_date1 (col_char varchar2(12), col_date date);
oracle数据类型及其隐式转换
备注：转换时要根据给定的string设定正确的格式掩码，否则 Ora_01840:input value is not long enough for date format. Ora_01862:the numeric value does not match the length of the format item. 3） to_number 字符值->数字值 语法：to_number (string,[format mask],[nls_parameters]) 参数：string 待转换的字符值 format mask:可选参数 格式掩码同to_char转换为number时相同。 备注：如果使用较短的格式掩码就会返回错误。 例如: to_number(123.56,’999.9’)返回错误。 在oracle中，如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则对数据进行隐式转换 1) 对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。假如id列的数据类型为number update t set id='1'; -> 相当于update t set id=to_number('1'); insert into t(id) values('1') -> insert into t values(to_number('1')); 2) 对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型。 如假设id列的数据类型为varchar2 select * from t where id=1; -> select * from t where to_number(id)=1; 但如果id列的数据类型为number，则 select * from t where id='1'; -> select * from t where id=to_number('1');(参考下文) 3) 当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。 如假设id列的数据类型为number select * from t where id='1'; -> select * from t where id=to_number('1');
Oracle数据库的空间数据类型
Oracle数据库中空间数据类型随着GIS、CAD/CAM的广泛应用，对数据库系统提出了更高的要求，不仅要存储大量空间几何数据，且以事物的空间关系作为查询或处理的主要内容。Oracle数据库从9i开始对空间数据提供了较为完备的支持，增加了空间数据类型和相关的操作，以及提供了空间索引功能。 Oracle的空间数据库提供了一组关于如何存储，修改和查询空间数据集的SQL schema与函数。通过MDSYS schema规定了所支持的地理数据类型的存储、语法和语义，提供了R-tree空间数据索引机制，定义了关于空间的相交查询、联合查询和其他分析操作的操作符、函数和过程，并提供了处理点，边和面的拓扑数据模型及表现网络的点线的网络数据模型。 Oracle中各种关于空间数据库功能主要是通过Spatial组件来实现。从9i版本开始，Oracle Spatial空间数据库组件对存储和管理空间数据提供了较为完备的支持。其主要通过元数据表、空间数据字段（即SDO_GEOMETRY字段）和空间索引来管理空间数据，并在此基础上提供一系列空间查询和空间分析的函数，让用户进行更深层次的GIS应用开发。Oracle Spatial使用空间字段SDO_GEOMETRY存储空间数据，用元数据表来管理具有SDO_GEOMETRY字段的空间数据表，并采用R树索引和四叉树索引技术来提高空间查询和空间分析的速度。 1、元数据表说明。 Oracle Spatial的元数据表存储了有空间数据的数据表名称、空间字段名称、空间数据的坐标范围、坐标参考信息以及坐标维数说明等信息。用户必须通过元数据表才能知道ORACLE数据库中是否有Oracle Spatial的空间数据信息。一般可以通过元数据视图（USER_SDO_GEOM_METADATA）访问元数据表。元数据视图的基本定义为： ( TABLE_NAME V ARCHAR2(32), COLUMN_NAME V ARCHAR2(32), DIMINFO MDSYS.SDO_DIM_ARRAY, SRID NUMBER
oracle数据库浅析number类型的值
number数据内部存储时，以变长的数组来存放，数组里的每个元素占一个字节，最多20个元素。内部代码为2。number数据的存放格式为：<[length]>,sign bit/exponent,digit1,digit2,…,digit20 sign bit/exponent这部分叫做exponent byte。 exponent byte包括三部分： ?sign bit：这表示高位bit，也就是128。并且我们有： 1. 如果小于128，则数值为负数。 2. 如果大于等于128，则数值为正数或0。 ?offset，始终为65 ?exponent：其范围从-65到62。该部分的值是基于100而 进行的科学计数法。 为0时比较特殊，就只有sign bit而没有offset和exponent，也就是128。比如： SQL> select dump(0) from dual; DUMP(0) ---------------- Typ=2 Len=1: 128 来看一个非0的值： SQL> select dump(25,16) from dual; DUMP(25,16) ------------------ Typ=2 Len=2: c1,1a 则exponent byte为c1，也就是 SQL> select to_number('c1','xx'),to_number('1a','xx') from dual; TO_NUMBER('C1','XX') TO_NUMBER('1A','XX') -------------------- -------------------- 193 26 而193＝128＋65＋0，也就是sign bit为128，offset为65，exponent为0。 同时，oracle存储时，用1表示0，2表示1，依此类推。也就是说用显示的值减1就是实际的值。如下所示： SQL> select dump(1,16) from dual;
Oracle基础知识大全,
课程目标： 1. 了解数据库系统在项目开发中的地位 2. 了解目前主流数据库系统 3. 熟练掌握查询命令 4. 熟练掌握数据操作方式 5. 熟练使用数据库内置函数 6. 掌握pl/sql程序设计语法结构 7. 掌握视图 8. 掌握存储过程 9. 掌握用户自定义函数 10. 掌握触发器 了解： 1.数据库组件： 1.1 实施应用集群 1.2 Oracle OLAP 1.3 数据挖掘 1.4 应用程序接口 1.5 网络组件 1.6 SQL Plus 1.7 企业管理器OEM
2. 几个版本间的差异： 2.1 增加了服务器参数文件SPFILE 2.2 使用了OMF免去了手工管理数据库文件的繁琐 2.3 支持多种数据库块大小 2.4 会话可以自动调整PGA的大小 2.5 引入了列表分区 课程内容： 1. 操作数据之前的准备工作： 1.1. 要保证有一个数据库： 1.2. 要保证创建一个表空间 create tablespace hbjr datafile 'E:\dbs\hbjr.dbf' size 100M; 1.3. 要创建一个用户，用户默认的表空间就是你创建的表空间 create user hbjr identified by orcl default tablespace hbjr; //授权限： grant dba to hbjr; 1.4. 用户/密码登录：
2. 表名/列字段名起名规范： 2.1 使用常规字母和数字 2.2 长度不要超过30 2.3 表名/列名不能重复 2.4 不能使用oracle的保留字 3. 数据类型 3.1 char:定长，存储单位--字节(bite) 3.2 varchar2:变长，存储单位--字节 3.3 nchar:定长，存储单位--字符 3.4 nvarchar2:变长，存储单位--字符 3.5 date:日期类型，7个字节，存储着--世纪，年，月，日，时，分，秒 3.6 timestamp:日期类型，11个字节，存储着--世纪，年，月，日，时，分，秒，毫秒 3.7 number:数据类型(数值类型) 3.8 clob:大字段类型，适合存储纯文本信息(例如：*.txt 文件)
Oracle数据库基本数据类型
oracle基本数据类型
oracle 数据库中讨论char ,varchar ,varchar2 数据类型! 这3种字符串数据类型是我们使用最多的，我们在数据库设计时到底该怎么使用了？ 首先我们先来分析3个数据类型的说明： 1。char CHAR的长度是固定的,最长2000个字符。 2。varchar 和 varchar2 可以肯定的是oracle中目前是可以使用varchar数据类型的，但他们的区别我查了不少资料也没有找到一个准确的答案最常见的说话是说oracle中的varchar只是varchar2的别名也就是同义词。 其次也有人认为varchar是最大长度为2000的可变字符串（和sql server中的varchar一致），而varchar2最大长度为4000。 知道了他们的特点我们就来讨论下使用区别 1.char和varchar、varchar2 由于varchar和varchar2用途类似，我们先来讨论char和他们的使用区别： varchar和varchar2比char节省空间，在效率上比char会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的
‘以空间换效率’。 varchar和varchar2虽然比char节省空间，但是如果一个varchar和varchar2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用char代替varchar和varchar2会更好一些。 当然还有一种情况就是象身份证这种长度几乎不变的字段可以考虑使用char，以获得更高的效率。 2。varchar和varchar2 这里就进入重点了，前面的区别我们在sql server中我们已经基本了解了，大家可能都知道，最多也就是复习下，但oracle增加了一个varchar2类型，是大家以前所没有用到过的。 因为oracle保证在任何版本中该数据类型向上和向下兼容，但不保证varchar，还有其他数据中一般都有varchar这个数据类型。 所以我得出了以下结论： 如果想在oracle新版本的数据库兼容就不要用varchar,如果想和oracle 之外其他数据库兼容就不要用varchar2。 ORACLE中的数据类型分类 ORACLE中的数据类型不可谓不多，下面把我这两天来的学习体会写一下吧！ 1、字符数据类型：包括我CHAR，VARCHAR2，LONG。 CHAR型可以存储字母数字值，这种数据类型的 列长度可以是1到2000个字节。如果未指明，则默认其占用一个字节，如果用户输入的值小于指定的长度，数据库则用空格填充至固定长度。 VARCHAR2型其实就是VARCHAR，只不过后面多了一个数字2，VARCHAR2就是VARCHAR的同义词，也称别名。数据类型大小在1至4000个字节，但是和CHAR不同的一点是：当你定义了VARCHAR2长度为30，但是你只输入了10个字符，这时VARCHAR2不会像CHAR一样填充，在数据库中只有10具字节。
Oracle与DB2数据类型的分类对应说明
Oracle与DB2数据类型的分类对应说明首先，通过下表介绍与DB2/400数据类型之间的对应关系，是一对多的关系，具体采用哪种对应关系，应具体问题具体分析。 注意事项: Oracle中的DATE含有年、月、日、时、分、秒，它和DB2/400中的DATE不相互对应，DB2/400中的DATE只有年、月、日，TIME类型含有时、分、秒，因此日期和时间类型要进行转换，
NUMBER Oracle中的NUMBER类型可以对应DB2/400中的很多类型，这种对应关系要依赖于Oracle中number将用于保存何种类型的数据，是整型还是带有小数位的实型数据，另外还要考虑类型所占用的存储空间，例如:SAMLLINT占2 byte, INTEGER占4 byte; BIGINT占8 byte;Oracle 中的NUMBER类型所占用的存储空间要根据它的定义而定，例如缺省精度下的NUMBER有38个数字长，占用20 byte的空间。具体的对应关系请参照上表。 ROW and LOB类型 DB2/400提供VARCHAR和CLOB与ORACLE中的RAW和LONG RAW相对应。ORACLE也支持大对象:BLOB、CLOB、CLOB和NCLOB, ORACLE中的BLOB和CLOB可以提供4GB的空间，而DB2/400中的BLOB和CLOB只能存放2GB的数据;DB2/400中的DBCLOB与ORACLE中的NCLOB 2GB相对应。Oracle 中的BFILE数据类型用于管理数据库以外的二进制数据，数据库中的表将指向数据库外部的存放的BFILE文件，DB2/400也提供一个类似的数据类型DATALINK相对应。 ROWID Oracle ROWIND虚拟列用于对表中的某一列进行唯一标示，DB2/400中也有这样的数据类型ROWID，它与ORACLE中的ROWID的功能相似。DB2/400中的ROWID可以存放40 byte的数据用来唯一标示表中的每一行，它没有ccsid属性，这些信息中没有关于datafile、 block 或row的信息。 例如: CREATE TABLE LIBRARYNAME/ORDERS2 (ORDERNO ROWID, SHIPPED_TO VARCHAR (36) , ORDER_DATE DATE) ORDERNO的数据类型为ROWID，用于存放订单号，每当插入一行时，系统自动生成一个值，存放进这个字段。可以用OPERATION NAVIGATOR查看它的内容。
浅谈Oracle中的CHAR与NCHAR数据类型 .
CHAR与NCHAR在Oracle数据库是常用的类型，不过两种数据类型是互相不兼容的，这里大概简要谈一下这2种数据类型吧。 在Oracle中，CHAR类型是一种固定长度的字符串类型，最大长度是2000个byte，在这里要注意了，是2000个byte，并不是2000个字符的意思。1个字符在Oracle中可能是1个byte到4个byte不等，这个要看数据库的设置了。这个是数据库中有一个参数 NLS_LENGTH_SEMANTICS来决定的。CHAR类型有一个特点，就是字符长度总是固定的，也就是说，如果你声明了一个COLUMN类型为CHAR(10)，如果你只插入了1个字符，那么系统也会自动在这个字符的右边补上9个空格，Oracle的CHAR类型中，补空格是在右侧补的。但是如果你插入了11个字符，那么就会系统出错，而不会自动截成10个字符。 NCHAR数据类型可能不是经常见到，不过在许多数据库设计中也有了。NCHAR数据类型是专为UNICODE字符而设计的，而且只能插入UNICODE字符串，NCHAR也是最大长度为2000个byte，不过每个字符占多少个byte，是由数据库的国际字符集决定的。同样的，如果插入字符不足定义的长度，比如NCHAR(10)类型中只插入了1个字符，系统会自动在右侧补充9个空格。 NCHAR和CHAR之间是不能直接互相兼容的，而是要通过Oracle的函数或者语法进行转换，比如说从NCHAR转成CHAR是要用TO_CHAR函数进行转换，而CHAR转换为NCHAR，比如说'ABCDE'这个CHAR字符串，则要通过N'ABCDE'来进行表示，也就是说前面要有一个N这个特殊字符，表示是NCHAR类型。
mysql与oracle数据类型对照
MySQL Data Type Oracle Data Type Java BIGINT NUMBER(19, 0)https://www.wendangku.net/doc/4e1696622.html,ng.Long BIT RAW byte[] BLOB BLOB, RAW byte[] CHAR CHAR https://www.wendangku.net/doc/4e1696622.html,ng.String DATE DATE java.sql.Date DATETIME DATE java.sql.Timestamp DECIMAL FLOAT (24)java.math.BigDecimal DOUBLE FLOAT (24)https://www.wendangku.net/doc/4e1696622.html,ng.Double DOUBLE PRECISION FLOAT (24)https://www.wendangku.net/doc/4e1696622.html,ng.Double ENUM https://www.wendangku.net/doc/4e1696622.html,ng.String FLOAT FLOAT https://www.wendangku.net/doc/4e1696622.html,ng.Float INT NUMBER(10, 0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer INTEGER NUMBER(10, 0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer LONGBLOB BLOB, RAW byte[] LONGTEXT CLOB, RAW https://www.wendangku.net/doc/4e1696622.html,ng.String MEDIUMBLOB BLOB, RAW byte[] MEDIUMINT NUMBER(7, 0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer MEDIUMTEXT CLOB, RAW https://www.wendangku.net/doc/4e1696622.html,ng.String NUMERIC NUMBER REAL FLOAT (24) SET VARCHAR2 https://www.wendangku.net/doc/4e1696622.html,ng.String
SMALLINT NUMBER(5, 0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer TEXT VARCHAR2, CLOB https://www.wendangku.net/doc/4e1696622.html,ng.String TIME DATE java.sql.Time TIMESTAMP DATE java.sql.Timestamp TINYBLOB RAW byte[] TINYINT TINYINT https://www.wendangku.net/doc/4e1696622.html,ng.Boolean TINYTEXT https://www.wendangku.net/doc/4e1696622.html,ng.String VARCHAR VARCHAR2, CLOB https://www.wendangku.net/doc/4e1696622.html,ng.String YEAR YEAR java.sql.Date（日期 设为2月1日点）
Oracle与DB2数据类型的分类对应说明
Oracle 与 DB2 数据类型的分类对应说明
首先，通过下表介绍与 DB2/400 数据类型之间的对应关系，是一对多的关系，具体采用 哪种对应关系，应具体问题具体分析。 注意事项: Oracle 中的 DATE 含有年、 日、 分、 它和 DB2/400 中的 DATE 不相互对应， 月、 时、 秒， DB2/400 中的 DATE 只有年、月、日，TIME 类型含有时、分、秒，因此日期和时间类型要进行转换， 请参照下表。
Oracle
DB2/400 注意事项 如果只使用 MM/DD/YYY,那 么使用 DATE 类型。 l 如果只使用 HH:MM:SS, 那么使用 TIME 类型。 l 如果要使用日期和时间， 则使用时间戳类型 （TIMESTAMP） l 可以使用 Oracle 中的 TO_CHAR()函数来取 DATE 的字串来分别与 DB2/400 的 DATE、TIME 相匹配。 若 n<=32766,则使用 DB2/400 中的 CHAR 类型、 VARCHAR 若 n<=32K,则使用 DB2/400 中的 CHAR 类型、VARCHAR。 l 若 32K=< n <=2GB，则使 用 CLOB。
Oracle 数据 注意事 DB2 UDB 数据类型 类型 项
DATE
DATE TIME TIMESTAMP l
CHAR(n) VARCHAR2(n) n<=4000 VARCHAR(n) l
LONG
n<=2GB
VARCHAR(n) CLOB(n) l
ROW& LONG ROW
若 n<=32K, 则使用 CHAR(n) FOR BIT DATA CHAR(n) FOR BIT DATA 或 n<=255 VARCHAR(n) FOR BIT DATA VARCHAR(n) FOR BIT DATA BLOB(n) l l 若 n<=2GB, 则使用 BLOB(n) n<=4GB BLOB(n) n<=4GB CLOB(n) 若 n<=2GB, 则使用 BLOB(n) 若 n<=2GB, 则使用 CLOB(n)
BLOB CLOB
浅谈oracle复合数据类型
浅谈oracle复合数据类型 --本文档可以直接拷贝运行。 /* oracle复合数据类型 PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。*/ /* 一、记录类型 类似C语言中的结构，有两种定义方式：显式定义和隐式定义。 */ create table test ( id varchar2(20), mc varchar2(60) ); insert into test values('111','11111'); insert into test values('222','22222'); insert into test values('333','33333'); insert into test values('444','44444'); insert into test values('555','55555'); insert into test values('666','66666'); commit; /* 1.1、显示定义记录 */ set serverout on declare type t_record is record ( id test.id%type, mc test.mc%type ); var_record t_record; counter number default 0;
begin for row_test in (select id,mc from test) loop counter := counter + 1; var_record.id := row_test.id; var_record.mc := row_test.mc; dbms_output.put_line('var_record:'||var_record.id||'---'||var_record.mc); dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc); dbms_output.put_line('================loop '||counter||' times.'); end loop; exception when others then dbms_output.put_line(sqlcode||sqlerrm); end; / /*有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的ld 和:new记录。*/ /* 1.2、隐式定义记录 */ 隐式定义记录中，我们不用描述记录的每一个域，在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录。 declare t_record1 test%rowtype; cursor cur_test(v_id in varchar2) is select id,mc from test where id <= v_id; t_record2 cur_test%rowtype; begin for row_test in cur_test('333') loop t_record1.id := row_test.id; t_record1.mc := row_test.mc; t_record2.id := row_test.id; t_record2.mc := row_test.id; dbms_output.put_line('t_record1:'||t_record1.id||'---'||t_record1.mc); dbms_output.put_line('t_record2:'||t_record2.id||'---'||t_record2.mc); dbms_output.put_line('row_test:'||row_test.id||'---'||row_test.mc); dbms_output.put_line('================loop '||cur_test%rowcount||' times.'); end loop;
oracle数据库的5种约束类型
oracle数据库的5种约束类型 oracle 数据库数据表的5个约束类型： 1.主键约束 2.外键约束 3.唯一约束 4.检查约束 5.非空约束F 主键约束：用来唯一标示表中的一个列，一个表中的主键约束只能有一个，但是可以在一个主键约束中包含多个列，也称为联合约束。外键约束：用来约束两个表中列之间的关系。唯一约束：用来唯一标示表中的列。与主键约束不同的是，在一个数据表中可以有多个唯一约束。检查约束：用来约束表中列的输入值得范围，比如在输入性别时，要求数据库中只能输入男或者女，就可以使用检查约束来约束该列。非空约束：约束该列一定要输入值。 --------------------------------------------------------------------------------------- 创建一个带检查约束的表：使用PL/SQL语句创建检查约束的语法如下所示：CONSTRAINT constraint_name CHECK(condition) [语法说明：] CONSTRAINT:关键词constraint_name:约束名称condition:约束条件列如:创建BOOKINFO表时，给图书价格加上一个检查约束，要求图书价格在10元到100元之间。CREATE TABLE BOOKINFO ( BOOKID INT, BOOKNAME CAHR, PUBLISH
VARCHAR2(20), PUBDATE VARCHAR2(20), PRICE DECIMAL, AUTHOR CHAR, STORE VARCHAR2(1), READER INT, REMARKS VARCHAR2(50), CONSTRAINT CK_PRICE CHECK(PRICE>=10 AND PRICE ); --------------------------------------------------------------------------------------- 创建一个带非空约束的表：举例:在创建BOOKINFO表时，给图书名称加上一个非空约束。CREATE TABLE BOOKINFO ( BOOKID INT NOT NULL, BOOKNAME CHAR NOT NULL, PUBLISH VARCHAR2(20), PUBDATE VARCHAR2(20), PRICE DECIMAL, AUTHOR CHAR, STORE VARCHAR2(1), READER INT, REMAERKS VARCHAR2(50) ); ---------------------------------------------------------------------------------------- 创建一个带唯一约束的表语法格式：CONSTRAINT constraint_name UNIQUE(column_name) 【语法说明】UNIQUE:唯一约束的关键词 column_name:唯一约束的名称。举例:创建BOOKINFO 表时，为图书名称（BOOKNAME）列添加唯一约束create table bookinfo ( bookid int, bookname char, publish varchar2(20), pubdate varchar2(20),
oracle支持的数据类型
常用的数据库字段类型如下： 字段类型中文说明限制条件其它说明 CHAR 固定长度字符串最大长度2000 bytes VARCHAR2 可变长度的字符串最大长度4000 bytes 可做索引的最大长度749 NCHAR 根据字符集而定的固定长度字符串最大长度2000 bytes NVARCHAR2 根据字符集而定的可变长度字符串最大长度4000 bytes DATE 日期（日-月-年）DD-MM-YY（HH-MI-SS）经过严格测试，无千虫问题 LONG 超长字符串最大长度2G（231-1）足够存储大部头著作 RAW 固定长度的二进制数据最大长度2000 bytes 可存放多媒体图象声音等 LONG RAW 可变长度的二进制数据最大长度2G 同上 BLOB 二进制数据最大长度4G CLOB 字符数据最大长度4G NCLOB 根据字符集而定的字符数据最大长度4G BFILE 存放在数据库外的二进制数据最大长度4G ROWID 数据表中记录的唯一行号10 bytes ********.****.****格式，*为0或1 NROWID 二进制数据表中记录的唯一行号最大长度4000 bytes NUMBER(P,S) 数字类型P为整数位，S为小数位 DECIMAL(P,S) 数字类型P为整数位，S为小数位 INTEGER 整数类型小的整数 FLOAT 浮点数类型NUMBER(38)，双精度 REAL 实数类型NUMBER(63)，精度更高 数据类型参数描述 char(n) n=1 to 2000字节定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）
varchar2(n) n=1 to 4000字节可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。 如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。 number(m,n) m=1 to 38 n=-84 to 127 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。 如：number(5,2)，则这个字段的最大值是99,999，如果数值超出了位数限制就会被截取多余的位数。 如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。 如：number(3,0)，输入575.316，真正保存的数据是575。 date 无从公元前4712年1月1日到公元4712年12月31日的所有合法日期，Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。 缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。 long 无可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 raw(n) n=1 to 2000 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。 raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 long raw 无可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。 在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。 blob /clob /nclob 无三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。 LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。 可以执行读取、存储、写入等特殊操作。 bfile 无在数据库外部保存的大型二进制对象文件，最大长度是4GB。 这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。
Oracle数据库基本数据类型
本文由ｈｏｒｏｓｃｏｐｅｃ贡献 ｄｏｃ１。 ｏｒａｃｌｅ 基本数据类型 数据类型 ｃｈａｒ（ｎ） 参数 ｎ＝１ ｔｏ ２０００ 字节 描述 定长字符串，ｎ 字节长，如果不指定长度，缺省 为 １ 个字节长（一个汉字为 ２ 字节） 可变长的字符串，具体定义时指明最大长度 ｎ， 这种数据类型可以放数字、字母以及 ＡＳＣＩＩ 码字 符集（或者 ＥＢＣＤＩＣ 等数据库系统接受的字符集标 准）中的所有符号。 ｖａｒｃｈａｒ２（ｎ） ｎ＝１ ｔｏ ４０００ 字节 如果数据长度没有达到最大值 ｎ，Ｏｒａｃｌｅ ８ｉ 会 根据数据大小自动调节字段长度， 如果你的数据前后有空格，Ｏｒａｃｌｅ ８ｉ 会自动将 其删去。ＶＡＲＣＨＡＲ２ 是最常用的数据类型。 可做索引的最大长度 ３２０９。 可变长的数值列，允许 ０、正值及负值，ｍ 是所 有有效数字的位数，ｎ 是小数点以后的位数。 如：ｎｕｍｂｅｒ（５，２），则这个字段的最大值是 ９９，９９９，如果数值超出了位数限制就会被截取多 ｎｕｍｂｅｒ（ｍ，ｎ） ｍ＝１ ｔｏ ３８ ｎ＝－８４ ｔｏ １２７ 余的位数。 如：ｎｕｍｂｅｒ（５，２），但在一行数据中的这个字段 输入 ５７５．３１６，则真正保存到字段中的数值是 ５７５．３２。 如：ｎｕｍｂｅｒ（３，０），输入 ５７５．３１６，真正保存的数 据是 ５７５。 从公元前 ４７１２ 年 １ 月 １ 日到公元 ４７１２ 年 １２ 月 ｄａｔｅ 无 ３１ 日的所有合法日期， Ｏｒａｃｌｅ ８ｉ 其实在内部是按 ７ 个字节来保存日期 数据，在定义中还包括小时、分、秒。 缺省格式为 ＤＤ－ＭＯＮ－ＹＹ，如 ０７－１１ 月－００ 表示 ２０００ 年 １１ 月 ７ 日。 可变长字符列，最大长度限制是 ２ＧＢ，用于不需 要作字符串搜索的长串数据，如果要进行字符搜 ｌｏｎｇ 无 索就要用 ｖａｒｃｈａｒ２ 类型。 ｌｏｎｇ 是一种较老的数据类型，将来会逐渐被 ＢＬＯＢ、ＣＬＯＢ、ＮＣＬＯＢ 等大的对象数据类型所取代。 可变长二进制数据，在具体定义字段的时候必须 指明最大长度 ｎ，Ｏｒａｃｌｅ ８ｉ 用这种格式来保存 ｒａｗ（ｎ） ｎ＝１ ｔｏ ２０００ 较小的图形文件或带格式的文本文件，如 Ｍｉｃｅｏｓｏｆｔ Ｗｏｒｄ 文档。 ｒａｗ 是一种较老的数据类型， 将来会逐渐被 ＢＬＯＢ、 ＣＬＯＢ、ＮＣＬＯＢ 等大的对象数据类型所取代。 可变长二进制数据，最大长度是 ２ＧＢ。Ｏｒａｃｌｅ ８ｉ 用这种格式来保存较大的图形文件或带格式的 文本文件，如 Ｍｉｃｅｏｓｏｆｔ Ｗｏｒｄ 文档，以及音频、 ｌｏｎｇ ｒａｗ 无 视频等非文本文件。 在同一张表中不能同时有 ｌｏｎｇ 类型和 ｌｏｎｇ ｒａｗ 类型，ｌｏｎｇ ｒａｗ 也是一种较老的数据类型，将来 会逐渐被 ＢＬＯＢ、ＣＬＯＢ、ＮＣＬＯＢ 等大的对象数据类 型所取代。 三种大型对象（ＬＯＢ），用来保存较大的图形文件 或带格式的文本文件，如 Ｍｉｃｅｏｓｏｆｔ Ｗｏｒｄ 文档， ｂｌｏｂ ｃｌｏｂ ｎｃｌｏｂ 无 以及音频、视频等非文本文件，最大长度是 ４ＧＢ。 ＬＯＢ 有几种类型，取决于你使用的字节的类型， Ｏｒａｃｌｅ ８ｉ 实实在在地将这些数据存储在数据库 内部保存。 可以执行读取、存储、写入等特殊操作。 ｂｆｉｌｅ 无 在数据库外部保存的大型二进制对象文件，最大 长度是 ４ＧＢ。 这种外部的 ＬＯＢ 类型， 通过数据库记录变化情况， 但是数据的具体保存是在数据库外部进行的。 Ｏｒａｃｌｅ ８ｉ 可以读取、查询 ＢＦＩＬＥ，但是不能写 入。 大小由操作系统决定。 ｏｒａｃｌｅ 数据库中讨论 ｃｈａｒ ，ｖａｒｃｈａｒ ，ｖａｒｃｈａｒ２ 数据类型！ 这 ３ 种字符串数据类型是我们使用最多的， 我们在数据库设计时到底该怎么使用 了？ 首先我们先来分析 ３ 个数据类型的说明： １。ｃｈａｒ ＣＨＡＲ 的长度是固定的，最长 ２０００ 个字符。 ２。ｖａｒｃｈａｒ 和 ｖａｒｃｈａｒ２ 可以肯定的是 ｏｒａｃｌｅ 中目前是可以使用 ｖａｒｃｈａｒ 数据类型的， 但他们 的区别我查了不少资料也没有找到一个准确的答案 ｏｒａｃｌｅ 中的 ｖａｒｃｈａｒ 只是 ｖａｒｃｈａｒ２ 的别名也就是同义词。 其次也有人认为 ｖａｒｃｈａｒ 是最大长度为 ２０００ 的可变字符串（和 ｓｑｌ ｓｅｒｖｅｒ 中的 ｖａｒｃｈａｒ 一致），而 ｖａｒｃｈａｒ２ 最大长度为 ４０００。 知道了他们的特点我们就来讨论下使用区别 １．ｃｈａｒ 和 ｖａｒｃｈａｒ、ｖａｒｃｈａｒ２ 由于 ｖａｒｃｈａｒ 和 ｖａｒｃｈａｒ２ 用途类似，我们先来讨论 ｃｈａｒ 和他们的使 用区别： ｖａｒｃｈａｒ 和 ｖａｒｃｈａｒ２ 比 ｃｈａｒ 节省空间，在效率上比 ｃｈａｒ 会稍微差一些， 即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的 最常见的说话是说 ‘以空间换效率’。 ｖａｒｃｈａｒ 和 ｖａｒｃｈａｒ２ 虽然比 ｃｈａｒ 节省空间，但是如果一个 ｖａｒｃｈａｒ 和 ｖａｒｃｈａｒ２ 列经常被修改，而且每次被修改的数据的长度不同，这会
oracle数据类型和对应的JDBC类型
oracle数据类型和对应的java类型默认的Java类型之间的映射SQL类型表
Mysql Oracle Java 数据类型对照 Mysql Oracle Java BIGINT NUMBER(19,0) https://www.wendangku.net/doc/4e1696622.html,ng.Long BIT RAW byte[] BLOB BLOB
DOUBLE PRECISION FLOAT (24) https://www.wendangku.net/doc/4e1696622.html,ng.Double ENUM VARCHAR2 https://www.wendangku.net/doc/4e1696622.html,ng.String FLOAT FLOAT https://www.wendangku.net/doc/4e1696622.html,ng.Float INT NUMBER(10,0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer INTEGER NUMBER(10,0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer LONGBLOB BLOB RAW byte[] LONGTEXT CLOB RAW https://www.wendangku.net/doc/4e1696622.html,ng.String MEDIUMBLOB BLOB RAW byte[] MEDIUMINT NUMBER(7,0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer MEDIUMTEXT CLOB RAW https://www.wendangku.net/doc/4e1696622.html,ng.String NUMERIC NUMBER REAL FLOAT (24) SET VARCHAR2 https://www.wendangku.net/doc/4e1696622.html,ng.String SMALLINT NUMBER(5,0) https://www.wendangku.net/doc/4e1696622.html,ng.Integer TEXT VARCHAR2 CLOB https://www.wendangku.net/doc/4e1696622.html,ng.String TIME DATE java.sql.Time TIMESTAMP DATE java.sql.Timestamp TINYBLOB RAW byte[] TINYINT NUMBER(3,0) https://www.wendangku.net/doc/4e1696622.html,ng.Boolean TINYTEXT VARCHAR2 https://www.wendangku.net/doc/4e1696622.html,ng.String VARCHAR VARCHAR2 CLOB https://www.wendangku.net/doc/4e1696622.html,ng.String YEAR NUMBER java.sql.Date
oracle数据类型格式化函数
数据类型格式化函数 PostgreSQL 格式化函数提供一套有效的工具用于把各种数据类型（日期/时间，integer，floating point，numeric）转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。Table 9-20列出了这些函数。这些函数都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是一个定义输出或输出格式的模板。 to_timestamp 函数也能接受一个 double precision 参数，把它从 Unix 纪元转换成 timestamp with time zone。（Integer Unix 纪元隐含地转换成了double precision。） Table 9-20. 格式化函数
在输出模板字串里（对 to_char 而言），该函数族可以识别一些特定的模式，并且把待格式化的数值正确地格式化成相应的数据。任何不属于模板模式的文本都简单地照字拷贝。同样，在一个输入模板字串里（对除 to_char 外的任何东西），模板模式标识要查看的输入数据字串，并且将在该位置寻找数值。 Table 9-21显示了可以用于格式化日期和时间值的模版。 Table 9-21. 用于日期/时间格式化的模式
有一些修饰词可以应用于模板来修改它们的行为。比如， FMMonth 就是带着 FM 前缀的 Month 模式。Table 9-22显示了用于日期/时间格式化的修饰词模式。 Table 9-22. 日期/时间格式化的模板模式修饰词
日期/时间格式化的用法须知： ?FM 抑制前导的零或尾随的空白，如果没有使用它的话，会在输出中增加这些填充最终把输出变成固定宽度的模式。 ?如果没有使用 FX 选项， to_timestamp 和 to_date 在转换字串的时候忽略多个空白。 FX 必须做为模板里的第一个项声明。比如 to_timestamp('2000 JUN', 'YYYY MON') 是正确的， to_timestamp('2000 JUN', 'FXYYYY MON') 会返回一个错误，因为 to_timestamp 只预料会有一个空白。 ?在 to_char 模板里可以有普通文本，并且它们会被照字输出。你可以把一个字串放到双引号里强迫它解释成一个文本，即使它里面包含模式 关键字也如此。比如，在 '"Hello Year "YYYY'， YYYY 将被年份数据代替，但是Year里单独的 Y 不会。 ?如果你想在输出里有双引号，那么你必须在它们前面放双反斜杠，比如'\\"YYYY Month\\"'. （需要两个反斜杠是因为反斜杠在字串常量里已经有特殊含义了。） ?如果你使用的年份长于 4 位字符，那么用 YYYY 从字串向timestamp或者date 做转换时要受到限制。你必须在 YYYY 后面使用一些非数字字 符或者模板，否则年份总是解释为 4 位数字。比如（对于 20000 年）：to_date('200001131', 'YYYYMMDD') 将会被解释成一个 4 位数字的年 份，最好在年后面使用一个非数字的分隔符，象 to_date('20000-1131', 'YYYY-MMDD') 或 to_date('20000Nov31', 'YYYYMonDD')。 ?在从字串向 timestamp 或 date 转换的时候，如果有 YYY，YYYY 或者Y,YYY 字段，那么 CC 字段会被忽略。如果 CC 和 YY 或 Y 一起使用，那么年份时用公式 (CC-1)*100+YY 计算的。 ?将字串转化为timestamp时，毫秒（MS）和微秒（US）都是用字串的小数点后面的部分转换的。比如 to_timestamp('12:3', 'SS:MS') 不是 3 毫秒, 而是 300，因为转换把它看做 12 + 0.3 秒。这意味着对于格式 'SS:MS'而言，输入值为 12:3 或 12:30或12:300 声明了相同数目的毫秒。对于三毫秒，你必须使用 12:003，那么转换会把它看做 12 + 0.003 = 12.003 秒。 下面是一个更复杂的例子∶ to_timestamp('15:12:02.020.001230', 'HH:MI:https://www.wendangku.net/doc/4e1696622.html,') 是 15 小时，12 分钟，和 2 秒 + 20 毫秒 + 1230 微秒 = 2.021230 秒。 ?to_char 的星期几的编号（参阅 'D' 格式化模式）与 extract 的不同。
- Oracle与DB2数据类型的分类对应说明
- oracle支持的数据类型
- oracle数据类型格式化函数
- Oracle数据库的空间数据类型
- Oracle数据库常用的知识点汇总
- Oracle数据库基本数据类型
- mysql与oracle数据类型对照
- Oracle数据库基本数据类型
- Oracle数据类型
- oracle数据库浅析number类型的值
- Oracle数据字典管理与数据类型
- oracle数据库的5种约束类型
- Oracle基本数据类型
- oracle数据类型及其隐式转换
- mysql与oracle数据类型对应关系
- oracle常用数据类型
- oracle数据类型函数以及相互转换
- SQL语言的四种类型及oracle数据类型
- oracle数据库试题
- Oracle数据库的空间数据类型