数据类型.docx

1、数据类型1、数据类型关于列的数据类型有很多 不同的数据类型存储不同的数据-规范化数据与非规范化数据数据类型分类ORACLE类型分四大类 Oracle_built_in_datatypes ORACLE内置类型 varchar2 number dateANSI_supported_datatypes 美国国际标准化组织支持的通用数据类型 int Nchar Nvarchar2User_defined_types 自定义类型 PL/SQLOracle-Supplied Types 前面3种支持不了的情况下补充支持的类型其中最常用的是ORACLE内置类型、自定义类型.ORACLE内置类型 内置类型分

2、了六大类char 是定长的 varchar2 是变长的char varchar2 字符类型number 数值类型date 日期类型long和raw 二进制类型LOB 大对象类型ROWID 行地址类型第一类内置类型：字符类型char(个数 byte|char)char是一个固定长度的字符类型,不足的字符将用空格填充。由于一个块通常是2K/4K/8K,所以对列类型有一定的长度限制char至少1个byte长,最多2000 bytes,存储null其实也可以是0长度其个数有两种属性,分别是byte或者charchar为字符，byte为字节 在不同的字符集中，同样的char实际存储时，所占用的byte位

3、数是不同的。对于中文，可能是占两个字节，也可能是占三个字节。对于单字节 1 char = 1 bytes对于多字节字符集 1 char = n bytes对于双字节中文字符集 1 char = 2 bytesSCOTTora11g create table t_t (a char(5);Table created.SCOTTora11g insert into t_t values (null);1 row created.SCOTTora11g insert into t_t values (1a);1 row created.SCOTTora11g insert into t_t valu

4、es (1a );1 row created.SCOTTora11g commit;Commit complete.SCOTTora11g select length(a) from t_t;LENGTH(A)-55第一条插入的数据为null 所以没长度 也就是0长度第二条和第三条 是一样的 虽然第三条插入时带了空格但因为char是固定长度的 不足长度会自动补空格第二条记录补了3个空格 第三条记录补了2个空格所以这两条记录是一样的.SCOTTora11g create table t1(a char(5);SCOTTora11g create table t2(a char(5 byte);S

5、COTTora11g create table t3(a char(5 char);SCOTTora11g insert into t1 values(a);SCOTTora11g insert into t2 values(a);SCOTTora11g insert into t3 values(a);SCOTTora11g select dump(a) from t1 union allselect dump(a) from t2 union allselect dump(a) from t3;DUMP(A)-Typ=96 Len=5: 97,32,32,32,32Typ=96 Len=5

6、: 97,32,32,32,32Typ=96 Len=5: 97,32,32,32,32-注释：-typ=type 表示输入的值的类型 96表示char类型-len=length 输入的值的长度-97,32,32,32,32 实际存储的时候，会把这些值全部转换为对应的ascii码。SCOTTora11g select dump(a) from dual;DUMP(A)-Typ=96 Len=1: 97-dump函数（详见扩展）SCOTTora11g select length(a) from t3;LENGTH(A)-5SCOTTora11g select lengthb(a) from t3

7、;LENGTHB(A)-5-length与lengthb函数区别：-前者返回字符长度，后者返回字节长度。varchar2(个数 byte|char)变长度的字符类型,不足部分不进行填充至少1个byte长,最多4000 bytes同样可以指定byte|charSCOTTora11g create table t4 (a varchar2(4000);Table created.SCOTTora11g insert into t4 values (1A);1 row created.SCOTTora11g insert into t4 values (1A );1 row created.SCOT

8、Tora11g select length(a) from t4;LENGTH(A)-23Nchar(个数 byte|char) 国家字符集对于一个跨国网站可能有中文和英文或更多国家语言环境页面由于不同字符集使用同一个字符(单词)所需要的字节长度不一样,空间浪费程度不一样,而且选择出的数据是需要转换的 转换所消耗的资源也不一样所以我们选择一种最合适的字符集作为内置的数据库字符集另外设置一份辅助的字符集来解决所有语言字符支持，它就是UNICODE,我们称之为国家字符集类型名头的N就是国家字符集的意思。N是National的简写Nvarchar2(个数 byte|char)同样是国家字符集 是va

9、rchar2的版本SCOTTora11g create table t6 (a Nvarchar2(5);Table created.SCOTTora11g create table t7 (a Nchar(5);Table created.SCOTTora11g insert into t6 values (a);1 row created.SCOTTora11g insert into t7 values (a);1 row created.SCOTTora11g select dump(a) from t6 union all select dump(a) from t7;DUMP(A)

10、-Typ=1 Len=2: 0,97Typ=96 Len=10: 0,97,0,32,0,32,0,32,0,32varchar(个数 byte|char)varchar是varchar2的同义词,是为了将来的版本做扩充用,其实就是向前兼容的意思ORACLE建议用varchar2数值类型整型数据分两大类固点数值类型固点数值类型就是把NUMBER的所有有效数字个数(包括小数位)和小数位个数给设置了一个固定的阀值。浮点数值类型有效数字个数和小数位不固定固点数值类型NUMBER(p,s)precision - p -精度scale - s -小数位范围p NUMBER的所有有效数字个数(包括小数位)

11、 138个数字,最大就是9999.9 38个9 s -84 127正数 小数点后数字个数负数 正数部分被四舍五入的位置0 此时NUMBER表示整数溢出P 益出将报错S 溢出将四舍五入没整数情况下是 40位小数 多的就四舍五入有整数情况下 最多38位小数SCOTTora11g select drop table |tname| purge; from tab where tname like T%;DROPTABLE|TNAME|PURGE;-drop table T_T purge;drop table T_EMP purge;drop table TAB_TEST purge;drop ta

12、ble T7 purge;drop table T6 purge;drop table T4 purge;drop table T3 purge;drop table T2 purge;drop table T1 purge;9 rows selected.执行上述语句即可清除前面创建的测试表。SCOTTora11g create table t1 (id number(6,3);Table created.SCOTTora11g insert into t1 values(1234.89);insert into t1 values(1234.89)*ERROR at line 1:ORA-

13、01438: value larger than specified precision allowed for this columnSCOTTora11g insert into t1 values(123.89);1 row created.SCOTTora11g select * from t1;ID-123.89SCOTTora11g insert into t1 values(123.789);1 row created.SCOTTora11g select * from t1;ID-123.89123.789SCOTTora11g insert into t1 values(12

14、3.6789);1 row created.SCOTTora11g select * from t1;ID-123.89123.789123.679SCOTTora11g create table t2 (id number(2,7);Table created.SCOTTora11g insert into t2 values(0.0000012);1 row created.SCOTTora11g insert into t2 values(0.00000123);1 row created.SCOTTora11g select * from t2;ID-.0000012.0000012S

15、COTTora11g insert into t2 values(1.00000123);insert into t2 values(1.00000123)*ERROR at line 1:ORA-01438: value larger than specified precision allowed for this column平时我们也可以不指明精度 number就可以存小数SCOTTora11g create table t4(id number);Table created.SCOTTora11g insert into t4 values (99.99);1 row created

16、.SCOTTora11g select * from t4;ID-99.99浮点数值类型没有固定精度刻度的,它有个10的次数或者2的次数最大的范围就是3.4 * 10的-38次方 3.4 * 10的38次方或者2的-126次方 2的126次方floatfloat(n)二进制类型 longrawlong长二进制类型，最大是2G数据每个表最多1个long字段已经淘汰rawSCOTTora11g create table t6 (r raw(10);Table created.SCOTTora11g insert into t6 values(hextoraw(ff);1 row created.S

17、COTTora11g select * from t6;R-FFSCOTTora11g insert into t6 values (g);insert into t6 values (g)*ERROR at line 1:ORA-01465: invalid hex numberSCOTTora11g insert into t6 values(0123456789abcdefadbcd);insert into t6 values(0123456789abcdefadbcd)*ERROR at line 1:ORA-12899: value too large for column SCO

18、TT.T6.R (actual: 11, maximum:10)-为何上句显示actual: 11SCOTTora11g select dump (hextoraw(0123456789abcdefadbcd) from dual;DUMP(HEXTORAW(0123456789ABCDEFADBCD)-Typ=23 Len=11: 0,18,52,86,120,154,188,222,250,219,205日期类型常见的类型如下：DATE类型只能将粒度精确到秒范围 公元前4712 - 1月 9999 - 12月精确到秒取值范围YEAR 公元前4712 9999MONTH 112DAY 01

19、31HOUR 0023MINUTE 00 59SECEND 00 59SCOTTora11g select drop table |tname| purge; from tab where tname like T%;DROPTABLE|TNAME|PURGE;-drop table T6 purge;drop table T4 purge;drop table T2 purge;drop table T1 purge;执行上述语句清除测试表。SCOTTora11g create table t1 (t date);Table created.SCOTTora11g insert into t

20、1 values(sysdate);1 row created.SCOTTora11g select * from t1;T-18-MAR-15SCOTTora11g select to_char(t,YYYY-MM-DD HH24:MI:SS) from t1;TO_CHAR(T,YYYY-MM-2015-03-18 15:24:33SCOTTora11g alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;Session altered.SCOTTora11g select * from t1;T-2015-03-18 15:24

21、:33NLS_DATE_FORMAT可以设置在数据库的参数文件中(以后会学到) 如果你不想改变数据库参数 又不想每次修改或转换 可以设置系统环境变量export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS;timestamp(n) 时间戳date类型的扩展 区别在于精度比如拿数据库存一下刘翔的百米记录时间 date只能精确到秒 显然不合适timestamp精确分秒的粒度n默认=6 范围是09 n表示小数秒的位数CURRENT_TIMESTAMP 返回精确时间(绝对时间全球化时间戳)LOCALTIMESTAMP 本地化时间戳SCOTTora11g create t

22、able t4(t timestamp(9);Table created.SCOTTora11g insert into t4 values(current_timestamp);1 row created.SCOTTora11g select to_char(t,YYYY-MM-DD HH24:MI:SS.FF) from t4;TO_CHAR(T,YYYY-MM-DDHH24:MI:-2015-03-18 15:31:11.866163000SCOTTora11g insert into t4 values(to_timestamp(2010-05-10 18:19:20.12345678

23、9,YYYY-MM-DD HH24:MI:SS.FF);1 row created.SCOTTora11g select * from t4;T-18-MAR-15 03.31.11.866163000 PM10-MAY-10 06.19.20.123456789 PMSCOTTora11g alter session set nls_timestamp_format=YYYY-MM-DD HH24:MI:SS.FF;Session altered.SCOTTora11g select * from t4;T-2015-03-18 15:31:11.8661630002010-05-10 18

24、:19:20.123456789TIMESTAMP WITH TIME ZONE相比timestamp多了时区SCOTTora11g create table t5(t timestamp with time zone);Table created.SCOTTora11g insert into t5 values(current_timestamp);1 row created.SCOTTora11g select * from t5;T-18-MAR-15 03.36.23.189113 PM +08:00查看数据库中支持的时区sqlselect * from v$timezone_nam

25、es;sqlalter session set nls_timestamp_tz_format=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM;Session altered.查看数据库当前时区SCOTTora11g col SESSIONTIMEZONE for a20SCOTTora11g col CURRENT_TIMESTAMP for a50SCOTTora11g SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP- -+08:00 2015-03-18 15:42:56.413480 +08:00修改当前会话的时区:SCOTTora11g ALTER SESSION SET TIME_ZONE = -2:0;Session altered.SCOTTora11g SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL;SESSIONTIMEZONE CURRENT_TIMESTAMP- -02:00 2015-03-18 05:44:03.427582 -02:00SCOT