数据类型.docx
《数据类型.docx》由会员分享，可在线阅读，更多相关《数据类型.docx（18页珍藏版）》请在冰豆网上搜索。
数据类型
1、数据类型
关于列的数据类型有很多不同的数据类型存储不同的数据
--规范化数据与非规范化数据
数据类型分类
ORACLE类型分四大类
Oracle_built_in_datatypesORACLE内置类型varchar2numberdate
ANSI_supported_datatypes美国国际标准化组织支持的通用数据类型intNcharNvarchar2
User_defined_types自定义类型PL/SQL
Oracle-SuppliedTypes前面3种支持不了的情况下补充支持的类型
其中最常用的是ORACLE内置类型、自定义类型.
ORACLE内置类型内置类型分了六大类
char是定长的
varchar2是变长的
charvarchar2字符类型
number数值类型
date日期类型
long和raw二进制类型
LOB大对象类型
ROWID行地址类型
第一类内置类型：
字符类型
char（个数byte|char）
char是一个固定长度的字符类型,不足的字符将用空格填充。
由于一个块通常是2K/4K/8K,所以对列类型有一定的长度限制
char至少1个byte长,最多2000bytes,存储null其实也可以是0长度
其个数有两种属性,分别是byte或者char
char为字符，byte为字节
在不同的字符集中，同样的char实际存储时，所占用的byte位数是不同的。
对于中文，可能是占两个字节，也可能是占三个字节。
对于单字节1char=1bytes
对于多字节字符集1char=nbytes
对于双字节中文字符集1char=2bytes
SCOTT@ora11g>createtablet_t（achar（5））;
Tablecreated.
SCOTT@ora11g>insertintot_tvalues（null）;
1rowcreated.
SCOTT@ora11g>insertintot_tvalues（'1a'）;
1rowcreated.
SCOTT@ora11g>insertintot_tvalues（'1a'）;
1rowcreated.
SCOTT@ora11g>commit;
Commitcomplete.
SCOTT@ora11g>selectlength（a）fromt_t;
LENGTH（A）
----------
5
5
第一条插入的数据为null所以没长度也就是0长度
第二条和第三条是一样的虽然第三条插入时带了空格
但因为char是固定长度的不足长度会自动补空格
第二条记录补了3个空格第三条记录补了2个空格
所以这两条记录是一样的.
SCOTT@ora11g>createtablet1（achar（5））;
SCOTT@ora11g>createtablet2（achar（5byte））;
SCOTT@ora11g>createtablet3（achar（5char））;
SCOTT@ora11g>insertintot1values（'a'）;
SCOTT@ora11g>insertintot2values（'a'）;
SCOTT@ora11g>insertintot3values（'a'）;
SCOTT@ora11g>selectdump（a）fromt1
unionall
selectdump（a）fromt2
unionall
selectdump（a）fromt3;
DUMP（A）
--------------------------------------------------------------------------------
Typ=96Len=5:
97,32,32,32,32
Typ=96Len=5:
97,32,32,32,32
Typ=96Len=5:
97,32,32,32,32
--注释：
--typ=type表示输入的值的类型96表示char类型
--len=length输入的值的长度
--97,32,32,32,32实际存储的时候，会把这些值全部转换为对应的ascii码。
SCOTT@ora11g>selectdump（'a'）fromdual;
DUMP（'A'）
----------------
Typ=96Len=1:
97
--dump函数（详见扩展）
SCOTT@ora11g>selectlength（a）fromt3;
LENGTH（A）
----------
5
SCOTT@ora11g>selectlengthb（a）fromt3;
LENGTHB（A）
----------
5
--length与lengthb函数区别：
--前者返回字符长度，后者返回字节长度。
varchar2（个数byte|char）
变长度的字符类型,不足部分不进行填充
至少1个byte长,最多4000bytes
同样可以指定byte|char
SCOTT@ora11g>createtablet4（avarchar2（4000））;
Tablecreated.
SCOTT@ora11g>insertintot4values（'1A'）;
1rowcreated.
SCOTT@ora11g>insertintot4values（'1A'）;
1rowcreated.
SCOTT@ora11g>selectlength（a）fromt4;
LENGTH（A）
----------
2
3
Nchar（个数byte|char）国家字符集
对于一个跨国网站可能有中文和英文或更多国家语言环境页面
由于不同字符集使用同一个字符（单词）所需要的字节长度不一样,空间浪费程度不一样,
而且选择出的数据是需要转换的转换所消耗的资源也不一样
所以我们选择一种最合适的字符集作为内置的数据库字符集
另外设置一份辅助的字符集来解决所有语言字符支持，它就是UNICODE,我们称之为国家字符集
类型名头的N就是国家字符集的意思。
N是National的简写
Nvarchar2（个数byte|char）
同样是国家字符集是varchar2的版本
SCOTT@ora11g>createtablet6（aNvarchar2（5））;
Tablecreated.
SCOTT@ora11g>createtablet7（aNchar（5））;
Tablecreated.
SCOTT@ora11g>insertintot6values（'a'）;
1rowcreated.
SCOTT@ora11g>insertintot7values（'a'）;
1rowcreated.
SCOTT@ora11g>selectdump（a）fromt6
unionall
selectdump（a）fromt7;
DUMP（A）
--------------------------------------------------------------------------------
Typ=1Len=2:
0,97
Typ=96Len=10:
0,97,0,32,0,32,0,32,0,32
varchar（个数byte|char）
varchar是varchar2的同义词,是为了将来的版本做扩充用,其实就是向前兼容的意思
ORACLE建议用varchar2
数值类型
整型数据分两大类
固点数值类型
固点数值类型就是把NUMBER的所有有效数字个数（包括小数位）和小数位个数给设置了一个固定
的阀值。
浮点数值类型
有效数字个数和小数位不固定
固点数值类型
NUMBER（p,s）
precision-->p--精度
scale-->s--小数位范围
pNUMBER的所有有效数字个数（包括小数位）1~38个数字,
最大就是9999.....9＝＝>38个9
s-84~127
正数＝＝>小数点后数字个数
负数＝＝> 正数部分被四舍五入的位置
0＝＝> 此时NUMBER表示整数
溢出
P益出将报错
S溢出将四舍五入
没整数情况下是40位小数多的就四舍五入
有整数情况下最多38位小数
SCOTT@ora11g>select'droptable'||tname||'purge;'
fromtab
wheretnamelike'T%';
'DROPTABLE'||TNAME||'PURGE;'
------------------------------------------------
droptableT_Tpurge;
droptableT_EMPpurge;
droptableTAB_TESTpurge;
droptableT7purge;
droptableT6purge;
droptableT4purge;
droptableT3purge;
droptableT2purge;
droptableT1purge;
9rowsselected.
执行上述语句即可清除前面创建的测试表。
SCOTT@ora11g>createtablet1（idnumber（6,3））;
Tablecreated.
SCOTT@ora11g>insertintot1values（1234.89）;
insertintot1values（1234.89）
*
ERRORatline1:
ORA-01438:
valuelargerthanspecifiedprecisionallowedforthiscolumn
SCOTT@ora11g>insertintot1values（123.89）;
1rowcreated.
SCOTT@ora11g>select*fromt1;
ID
----------
123.89
SCOTT@ora11g>insertintot1values（123.789）;
1rowcreated.
SCOTT@ora11g>select*fromt1;
ID
----------
123.89
123.789
SCOTT@ora11g>insertintot1values（123.6789）;
1rowcreated.
SCOTT@ora11g>select*fromt1;
ID
----------
123.89
123.789
123.679
SCOTT@ora11g>createtablet2（idnumber（2,7））;
Tablecreated.
SCOTT@ora11g>insertintot2values（0.0000012）;
1rowcreated.
SCOTT@ora11g>insertintot2values（0.00000123）;
1rowcreated.
SCOTT@ora11g>select*fromt2;
ID
----------
.0000012
.0000012
SCOTT@ora11g>insertintot2values（1.00000123）;
insertintot2values（1.00000123）
*
ERRORatline1:
ORA-01438:
valuelargerthanspecifiedprecisionallowedforthiscolumn
平时我们也可以不指明精度number就可以存小数
SCOTT@ora11g>createtablet4（idnumber）;
Tablecreated.
SCOTT@ora11g>insertintot4values（99.99）;
1rowcreated.
SCOTT@ora11g>select*fromt4;
ID
----------
99.99
浮点数值类型
没有固定精度刻度的,它有个10的次数或者2的次数
最大的范围就是3.4*10的-38次方~3.4*10的38次方
或者2的-126次方~2的126次方
float
float（n）
二进制类型
long
raw
long
长二进制类型，最大是2G数据
每个表最多1个long字段
已经淘汰
raw
SCOTT@ora11g>createtablet6（rraw（10））;
Tablecreated.
SCOTT@ora11g>insertintot6values（hextoraw（'ff'））;
1rowcreated.
SCOTT@ora11g>select*fromt6;
R
--------------------
FF
SCOTT@ora11g>insertintot6values（'g'）;
insertintot6values（'g'）
*
ERRORatline1:
ORA-01465:
invalidhexnumber
SCOTT@ora11g>insertintot6values（'0123456789abcdefadbcd'）;
insertintot6values（'0123456789abcdefadbcd'）
*
ERRORatline1:
ORA-12899:
valuetoolargeforcolumn"SCOTT"."T6"."R"（actual:
11,maximum:
10）
--为何上句显示actual:
11
SCOTT@ora11g>selectdump（hextoraw（'0123456789abcdefadbcd'））fromdual;
DUMP（HEXTORAW（'0123456789ABCDEFADBCD'））
-----------------------------------------------------
Typ=23Len=11:
0,18,52,86,120,154,188,222,250,219,205
日期类型
常见的类型如下：
DATE类型
只能将粒度精确到秒
范围公元前4712-1月~9999-12月精确到秒
取值范围
YEAR公元前4712~9999
MONTH1~12
DAY01~31
HOUR00~23
MINUTE00~59
SECEND00~59
SCOTT@ora11g>select'droptable'||tname||'purge;'fromtabwheretnamelike'T%';
'DROPTABLE'||TNAME||'PURGE;'
------------------------------------------------
droptableT6purge;
droptableT4purge;
droptableT2purge;
droptableT1purge;
执行上述语句清除测试表。
SCOTT@ora11g>createtablet1（tdate）;
Tablecreated.
SCOTT@ora11g>insertintot1values（sysdate）;
1rowcreated.
SCOTT@ora11g>select*fromt1;
T
---------
18-MAR-15
SCOTT@ora11g>selectto_char（t,'YYYY-MM-DDHH24:
MI:
SS'）fromt1;
TO_CHAR（T,'YYYY-MM-
-------------------
2015-03-1815:
24:
33
SCOTT@ora11g>altersessionsetnls_date_format='YYYY-MM-DDHH24:
MI:
SS';
Sessionaltered.
SCOTT@ora11g>select*fromt1;
T
-------------------
2015-03-1815:
24:
33
NLS_DATE_FORMAT
可以设置在数据库的参数文件中（以后会学到）
如果你不想改变数据库参数又不想每次修改或转换可以设置系统环境变量
exportNLS_DATE_FORMAT='YYYY-MM-DDHH24:
MI:
SS';
timestamp（n）时间戳
date类型的扩展区别在于精度
比如拿数据库存一下刘翔的百米记录时间date只能精确到秒显然不合适
timestamp精确分秒的粒度
n默认=6范围是0~9n表示小数秒的位数
CURRENT_TIMESTAMP返回精确时间（绝对时间全球化时间戳）
LOCALTIMESTAMP本地化时间戳
SCOTT@ora11g>createtablet4（ttimestamp（9））;
Tablecreated.
SCOTT@ora11g>insertintot4values（current_timestamp）;
1rowcreated.
SCOTT@ora11g>selectto_char（t,'YYYY-MM-DDHH24:
MI:
SS.FF'）fromt4;
TO_CHAR（T,'YYYY-MM-DDHH24:
MI:
-----------------------------
2015-03-1815:
31:
11.866163000
SCOTT@ora11g>insertintot4values（to_timestamp（'2010-05-1018:
19:
20.123456789','YYYY-MM-DDHH24:
MI:
SS.FF'））;
1rowcreated.
SCOTT@ora11g>select*fromt4;
T
---------------------------------------------------------------------------
18-MAR-1503.31.11.866163000PM
10-MAY-1006.19.20.123456789PM
SCOTT@ora11g>altersessionsetnls_timestamp_format='YYYY-MM-DDHH24:
MI:
SS.FF';
Sessionaltered.
SCOTT@ora11g>select*fromt4;
T
---------------------------------------------------------------------------
2015-03-1815:
31:
11.866163000
2010-05-1018:
19:
20.123456789
TIMESTAMPWITHTIMEZONE
相比timestamp多了时区
SCOTT@ora11g>createtablet5（ttimestampwithtimezone）;
Tablecreated.
SCOTT@ora11g>insertintot5values（current_timestamp）;
1rowcreated.
SCOTT@ora11g>select*fromt5;
T
---------------------------------------------------------------------------
18-MAR-1503.36.23.189113PM+08:
00
查看数据库中支持的时区
sql>select*fromv$timezone_names;
sql>altersessionsetnls_timestamp_tz_format='YYYY-MM-DDHH24:
MI:
SS.FFTZH:
TZM';
Sessionaltered.
查看数据库当前时区
SCOTT@ora11g>colSESSIONTIMEZONEfora20
SCOTT@ora11g>colCURRENT_TIMESTAMPfora50
SCOTT@ora11g>SELECTSESSIONTIMEZONE,CURRENT_timestampFROMDUAL;
SESSIONTIMEZONECURRENT_TIMESTAMP
----------------------------------------------------------------------
+08:
002015-03-1815:
42:
56.413480+08:
00
修改当前会话的时区:
SCOTT@ora11g>ALTERSESSIONSETTIME_ZONE='-2:
0';
Sessionaltered.
SCOTT@ora11g>SELECTSESSIONTIMEZONE,CURRENT_timestampFROMDUAL;
SESSIONTIMEZONECURRENT_TIMESTAMP
----------------------------------------------------------------------
-02:
002015-03-1805:
44:
03.427582-02:
00
SCOT
升级会员 