关于列的数据类型有很多 不同的数据类型存储不同的数据
类型分类
ORACLE类型分四大类
Oracle_built_in_datatypes ORACLE内置类型 varchar2 number date
ANSI_supported_datatypes 美国国际标准化组织支持的通用数据类型 int Nchar Nvarchar2
User_defined_types 自定义类型 PL/SQL
Oracle-Supplied Types 前面3种支持不了的情况下补充支持的类型
其中最常用的是ORACLE内置类型、自定义类型.
ORACLE内置类型 内置类型分了六大类
char varchar2
字符类型
number
数值类型
long和raw
二进制类型
date
日期类型
LOB
大对象类型
ROWID
行地址类型
第一类内置类型：字符类型
char(个数 byte|char)
char是一个固定长度的字符类型,不足的字符将用空格填充。
由于一个块通常是2K/4K/8K,所以对列类型有一定的长度限制
char至少1个byte长,最多2000 bytes,存储null其实也可以是0长度
其个数有两种属性,分别是byte或者char
对于单字节 1 char = 1 bytes
对于多字节字符集 1 char = n bytes
对于双字节中文字符集 1 char = 2 bytes
SQL> create table t1(a char(5));
Table created.
SQL>
SQL> insert into t1 values(null);
1 row created.
SQL> insert into t1 values('1A');
1 row created.
SQL> insert into t1 values('1A ');
1 row created.
SQL> commit;
SQL> select length(a) from t1;
LENGTH(A)
----------
5
5
SQL>
第一条插入的数据时null 所以没长度 也就是0长度
第二条和第三条 是一样的 虽然第三条插入时带了空格
但因为char是固定长度的 不足长度会自动补空格
第二条记录补了3个空格 第三条记录补了2个空格
所以这两条记录是一样的.
SQL> create table t1(a char(5));
Table created.
SQL> create table t2(a char(5 byte));
Table created.
SQL> create table t3(a char(5 char));
Table created.
SQL>
SQL> truncate table t1;
Table truncated.
SQL> truncate table t2;
Table truncated.
SQL> truncate table t3;
Table truncated.
SQL> insert into t1 values('a');
1 row created.
SQL> insert into t2 values('a');
1 row created.
SQL> insert into t3 values('a');
1 row created.
SQL> select dump(a) from t1;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
SQL> select dump(a) from t2;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
SQL> select dump(a) from t3;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
SQL> select length(a) from t3;
LENGTH(A)
----------
5
SQL> select lengthb(a) from t3;
LENGTHB(A)
----------
5
SQL> insert into t1 values('飞');
1 row created.
SQL> insert into t2 values('飞');
1 row created.
SQL> insert into t3 values('飞');
1 row created.
SQL> select dump(a) from t1;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
Typ=96 Len=5: 233,163,158,32,32
SQL> select dump(a) from t2;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
Typ=96 Len=5: 233,163,158,32,32
SQL> select dump(a) from t3;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=5: 97,32,32,32,32
Typ=96 Len=7: 233,163,158,32,32,32,32
SQL>
SQL> select lengthb(a) from t2;
LENGTHB(A)
----------
5
5
SQL> select lengthb(a) from t3;
LENGTHB(A)
----------
5
7
SQL>
varchar2(个数 byte|char)
变长度的字符类型,不足部分不进行填充
至少1个byte长,最多4000 bytes
同样可以指定byte|char
SQL> create table t4(a varchar2(4000));
Table created.
SQL> insert into t4 values('1A');
1 row created.
SQL> insert into t4 values('1A ');
1 row created.
SQL> select length(a) from t4;
LENGTH(A)
----------
2
3
SQL>
Nchar(个数 byte|char) 国家字符集
对于一个跨国网站可能有中文和英文或更多国家语言环境页面
由于不同字符集使用同一个字符(单词)所需要的字节长度不一样,空间浪费程度不一样,
而且选择出的数据是需要转换的 转换所消耗的资源也不一样
所以我们选择一种最合适的字符集作为内置的数据库字符集
另外设置一份辅助的字符集来解决所有语言字符支持，它就是UNICODE,我们称之为国家字符集
类型名头的N就是国家字符集的意思。N是National的简写
Nvarchar2(个数 byte|char)
同样是国家字符集 是varchar2的版本
SQL> create table t6(a Nvarchar2(5));
Table created.
SQL>
SQL> create table t7(a Nchar(5));
Table created.
SQL>
SQL> insert into t6 values('飞');
1 row created.
SQL>
SQL> insert into t7 values('飞');
1 row created.
SQL>
SQL> select dump(a) from t6;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=1 Len=2: 152,222
SQL>
SQL> select dump(a) from t7;
DUMP(A)
-----------------------------------------------------------------------------------------------
Typ=96 Len=10: 152,222,0,32,0,32,0,32,0,32
SQL>
varchar(个数 byte|char)
varchar是varchar2的同义词,是为了将来的版本做扩充用,其实就是向前兼容的意思
ORACLE建议用varchar2
数值类型
整型数据分两大类
固点数值类型
固点数值类型就是把NUMBER的所有有效数字个数(包括小数位)和小数位个数给设置了一个固定的阀值。
浮点数值类型
有效数字个数和小数位不固定
固点数值类型
NUMBER(p,s)
precision --> p --精度
scale --> s --小数位范围
p NUMBER的所有有效数字个数(包括小数位) 1~38个数字,
最大就是9999.....9 ＝＝> 38个9
s -84 ~ 127
正数 ＝＝> 小数点后数字个数
负数 ＝＝> 正数部分被四舍五入的位置
0 ＝＝> 此时NUMBER表示整数
溢出
P 益出将报错
S 溢出将四舍五入
没整数情况下是 40位小数 多的就四舍五入
有整数情况下 最多38位小数
删除之前实验表
select 'drop table '||tname||' purge;' from tab where tname like 'T%';
SQL> create table t1 (id number(6,3));
Table created.
SQL> insert into t1 values(1234.89);
insert into t1 values(1234.89)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into t1 values(123.89);
1 row created.
SQL> select * from t1;
ID
----------
123.89
SQL> insert into t1 values(123.789);
1 row created.
SQL> select * from t1;
ID
----------
123.89
123.789
SQL> insert into t1 values(123.6789);
1 row created.
SQL> select * from t1;
ID
----------
123.89
123.789
123.679
SQL>
SQL> create table t2 (id number(2,7));
Table created.
SQL> insert into t2 values(0.0000012);
1 row created.
SQL> insert into t2 values(0.00000123);
1 row created.
SQL>
SQL> select * from t2;
ID
----------
.0000012
.0000012
SQL>
SQL> 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
SQL>
平时我们也可以不指明精度 number就可以存小数
SQL> create table t4(id number);
Table created.
SQL> insert into t4 values(99.99);
1 row created.
SQL> select * from t4;
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
SQL> create table t6(r raw(10));
Table created.
SQL>
SQL> insert into t6 values(hextoraw('ff'));
1 row created.
SQL>
SQL> insert into t6 values(hextoraw('ff'));
1 row created.
SQL> select * from t6;
R
--------------------
FF
SQL>
SQL> insert into t6 values('0123456789abcdef');
1 row created.
SQL> insert into t6 values('g');
insert into t6 values('g')
*
ERROR at line 1:
ORA-01465: invalid hex number
SQL>
日期类型
常见的类型如下：
DATE类型
只能将粒度精确到秒
范围 公元前4712 - 1月 ~ 9999 - 12月精确到秒
取值范围
YEAR 公元前4712 ~ 9999
MONTH 1~12
DAY 01 ~ 31
HOUR 00~23
MINUTE 00 ~ 59
SECEND 00 ~ 59
删除之前的实验表
select 'drop table '||tname||' purge;' from tab where tname like 'T%';
SQL> create table t1 (t date);
Table created.
SQL> insert into t1 values(sysdate);
1 row created.
SQL> select * from t1;
T
------------------
28-FEB-11
SQL> select to_char(t,'YYYY-MM-DD HH24:MI:SS') from t1;
TO_CHAR(T,'YYYY-MM-
-------------------
2011-02-28 14:00:25
SQL>
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select * from t1;
T
-------------------
2011-02-28 14:00:25from
SQL>
NLS_DATE_FORMAT
可以设置在数据库的参数文件中(以后会学到)
如果你不想改变数据库参数 又不想每次修改或转换 可以设置系统变量
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
timestamp(n)
date类型的扩展 区别在于精度
比如拿数据库存一下刘翔的百米记录时间 date只能精确到秒 显然不合适
timestamp精确分秒的粒度
n默认=6 范围是0~9 n表示小数秒的位数
CURRENT_TIMESTAMP 返回精确时间(绝对时间全球化时间戳)
LOCALTIMESTAMP 本地化时间戳
SQL> create table t4(t timestamp(9));
Table created.
SQL>
SQL> insert into t4 values(current_timestamp);
1 row created.
SQL> select to_char(t,'YYYY-MM-DD HH24:MI:SS.FF') from t4;
TO_CHAR(T,'YYYY-MM-DDHH24:MI:
-----------------------------
2011-02-28 14:16:42.325329000
SQL>
SQL> insert into t4 values(to_timestamp('2010-05-10 18:19:20.123456789','YYYY-MM-DD HH24:MI:SS.FF'));
1 row created.
SQL>
SQL> SELECT * FROM T4;
T
---------------------------------------------------------------------------
28-FEB-11 02.16.42.325329000 PM
10-MAY-10 06.19.20.123456789 PM
SQL>
SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
Session altered.
SQL> select * from t4;
T
---------------------------------------------------------------------------
2011-02-28 14:16:42.325329000
2010-05-10 18:19:20.123456789
SQL>
TIMESTAMP WITH TIME ZONE
相比timestamp多了时区
SQL> create table t5(t timestamp with time zone);
Table created.
SQL>
SQL> insert into t5 values(current_timestamp);
1 row created.
SQL> select * from t5;
T
---------------------------------------------------------------------------
28-FEB-11 02.22.30.700433 PM +08:00
SQL>
查看数据库中支持的时区
SQL> select * from v$timezone_names
SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';
Session altered.
SQL>
查看数据库当前时区
col SESSIONTIMEZONE for a20
col CURRENT_TIMESTAMP for a50
SQL> SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL;
SESSIONTIMEZONE
CURRENT_TIMESTAMP
------------------- ----------------------------------------------------
+08:00
28-FEB-11 02.34.28.007190 PM +08:00
SQL>
修改当前会话的时区:
SQL> SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- --------------------------------------------------
+08:00
28-FEB-11 02.36.08.783077 PM +08:00
SQL> ALTER SESSION SET TIME_ZONE = '-2:0';
Session altered.
SQL> SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- --------------------------------------------------
-02:00
28-FEB-11 04.36.33.280415 AM -02:00
SQL> ALTER SESSION SET TIME_ZONE = '8:0';
Session altered.
SQL> SELECT SESSIONTIMEZONE,CURRENT_timestamp FROM DUAL;
SESSIONTIMEZONE CURRENT_TIMESTAMP
-------------------- --------------------------------------------------
+08:00
28-FEB-11 02.36.36.435547 PM +08:00
SQL>
返回创建数据库时的时区选项:
SELECT DBTIMEZONE FROM DUAL;
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Interval year(precision) to month
precision是这个时限的年部分所要求的最大位数。 默认为2，范围为0~9
Interval day(d_precision) to second(s_precision)
d_precision是这个时限的天部分所要求的最大位数,默认为2，范围也是0~9
s_precision是这个时限的秒部分所要求的小数点右边的位数，默认为6，范围是0~9
求精确的时间间隔
把一段间隔时间明细化得一种方法
例如 问你活了多少年了? 我们都能答出来
问你活了多少天了? 我们算起来很麻烦 中间又有闰年什么的.
而这种类型就能给取出非常准确的时间间隔
例如计算SCOTT至今为止在公司多久了.
year to month 返回23年10个月
day to second 返回8716天14小时....
SQL> SELECT (SYSdate - hiredate) YEAR TO MONTH FROM emp where ename='SCOTT';
(SYSDATE-HIREDATE)YEARTOMONTH
---------------------------------------------------------------------------
+23-10
SQL>
SQL> SELECT (SYSdate - hiredate) DAY(9) TO SECOND FROM emp where ename='SCOTT';
(SYSDATE-HIREDATE)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000008716 14:49:34.000000
SQL>
大对象类型
用于支持一个块无法存下某一行甚至一个字段的情况下，用一个非结构化的数据类型来存储
LOB是为大文本，图像视频，音频和空间数据而设计的
非结构化数据
最大4G
LOB列上设置有指针，指向在线LOB值或者离线LOB值
dbms_lob来进行操作
可以定义多个LOB列
随机访问
CLOB 存储变长字符数据
NCLOB 用国家字符集存储变长字符数据。
BLOB 在数据库里面存储变长二进制数据。
BFILE 在数据库外部存储变长二进制数据
SQL> create table t6(c clob);
Table created.
SQL>
SQL> insert into t6 values(rpad('X',4000,'Y'));
1 row created.
SQL> select length(c) from t6;
LENGTH(C)
----------
4000
SQL> insert into t6 values(rpad('X',6000,'Y'));
1 row created.
SQL> select length(c) from t6;
LENGTH(C)
----------
4000
4000
SQL> 因为rpad受限于varchar2(4000)
SQL> update t6 set c=(select c||'XXXXXXXXXXXXXXXXXXXXXXXX' from t6 where rownum<2);
2 rows updated.
SQL>
SQL> select length(c) from t6;
LENGTH(C)
----------
4024
4024
SQL> 因为clob能存超越4000字节的长度 所以利用更新在原数据上追加几个XXX即可看到效果
暂做了解 以后PLSQL中 我们再试验BLOB 和 BFILE
行地址类型
每一行在数据库中有一个地址称为ROWID
查看的方法是使用伪列的形式：ROWID
用户在查看表的结构时不会看到ROWID字段
但和其他字段一样，可以在执行SELECT操作时直接引用
在引用ROWID时通常可以使用DBMS_ROWID包将其转变为可读取的内容。
ROWID 又分为受限制的ROWID和扩展的ROWID两类
受限ROWID已经在8I时被拓展ROWID取代
取代的原因是使用这个ROWID导致整个数据库只能有1022个数据文件
拓展ROWID 又引入了对象号 受限ROWID中的文件号 也变成了相对文件号
这就是原来的数据库的1022个数据文件转变成了一个表空间内1022个数据文件
这里我们只做拓展ROWID介绍
SQL> select ename,rowid from emp where ename='SCOTT';
ENAME
ROWID
---------- ------------------
SCOTT
AAAMfMAAEAAAAAgAAH
SQL>
AAAMfMAAEAAAAAgAAH 即使soctt这条记录的ROWID
由18位64进制数组成 把它6363分组
AAAMfM AAE AAAAAg AAH
对应上图OFBR
OOOOOO FFF BBBBBB RRR
64进制组成符号
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
a b c d e f g h i j k l m n o p q r s t u v w x y z
0 1 2 3 4 5 6 7 8 9
+ /
A-Z 对应 0-25
a-z 对应 26-51
0-9 对应 52-61
+ / 对应 62 63
AAAMfM 转换对应值 000 12 31 12
AAE 转换对应值 00 4
AAAAAg 转换对应值 00000 32
AAH 转换对应值 00 7
SQL> select 12*power(64,2) + 31*64 + 12 as "object#" from dual;
object#
----------
51148
SQL> select object_id from all_objects where object_name='EMP';
OBJECT_ID
----------
51148
SQL>
SQL> select 4 as "file#" from dual;
file#
----------
4
SQL>
SQL> select 32 as "block#" from dual;
block#
----------
32
SQL>conn / as sysdba
SQL> select SEGMENT_NAME,FILE_ID,BLOCK_ID,blocks from dba_extents where SEGMENT_NAME='EMP';
SEGMENT_NAME
FILE_ID BLOCK_ID BLOCKS
-------------------- ---------- ---------- ----------
EMP
4
25
8
SQL> select 25+8 from dual;
25+8
----------
33
SQL> 32# 就在25-33之间 一个文件创建时预分配8个块
SQL> select 7 as "row#" from dual;
row#
----------
7
SQL>
oracle为了演算ROWID提供了一个包 简化了我们的操作
SQL> select dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num
from scott.emp where ename='SCOTT';
OBJECT_ID FILE_ID
BLOCK_ID
NUM
---------- ---------- ---------- ----------
51148
4
32
7
SQL>
通时也提供ROWID的逆算
SQL> desc dbms_rowid
FUNCTION ROWID_CREATE RETURNS ROWID
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE
NUMBER
IN
OBJECT_NUMBER
NUMBER
IN
RELATIVE_FNO
NUMBER
IN
BLOCK_NUMBER
NUMBER
IN
ROW_NUMBER
NUMBER
IN
SQL> select dbms_rowid.ROWID_CREATE(1,51148,4,32,7) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAMfMAAEAAAAAgAAH
SQL>
SQL> create table t7 (r rowid);
Table created.
SQL>
SQL> insert into t7 values('AAAMfMAAEAAAAAgAAH');
1 row created.
SQL> insert into t7 values('AAA');
insert into t7 values('AAA')
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL> SELECT * FROM T7;
R
------------------
AAAMfMAAEAAAAAgAAH
SQL>
urowid
支持IOT(索引组织表)和外部表
这些对象每一行上不一定有地址,或者行上地址不是物理地址
ANSI_supported_datatypes
比如 int 数值整型
类型int 只接收整数 存浮点数会被四舍五入
SQL> create table t5(id int);
Table created.
SQL> insert into t5 values(99.99);
1 row created.
SQL> select * from t5;
ID
----------
100
SQL>
User-Defined Types
自定义类型
通过内置类型或者其它自定义类型设置的自定义类型来完成特定的应用需要
Oracle-Supplied Types
以上3类不支持的类型
补充的类型 PLSQL高级编程内容
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
