ORACLE常⽤数据库类型（转）
oracle常⽤数据类型
1、Char
定长格式字符串，在数据库中存储时不⾜位数填补空格，它的声明⽅式如下CHAR(L)，L为字符串长度，
缺省为1，作为变量最⼤32767个字符，作为数据存储在ORACLE8中最⼤为2000。不建议使⽤，会带来不
必要的⿇烦
a、字符串⽐较的时候，如果不注意（char不⾜位补空格）会带来错误
b、字符串⽐较的时候，如果⽤trim函数，这样该字段上的索引就失效（有时候会带来严重性能问题）
c、浪费存储空间(⽆法精准计算未来存储⼤⼩，只能留有⾜够的空间；字符串的长度就是其所占⽤空间的⼤⼩)
2、Varchar2/varchar
⽬前VARCHAR是VARCHAR2的同义词。⼯业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留
以后这样做的权利。Oracle⾃⼰开发了⼀个数据类型VARCHAR2，这个类型不是⼀个标准的VARCHAR，它将在数据库中
varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能⼒，Oracle建议使⽤VARCHAR2⽽不是VARCHAR。
不定长格式字符串，它的声明⽅式如下VARCHAR2(L)，L为字符串长度，没有缺省值，作为变量最⼤32767个字节，
作为数据存储在ORACLE8中最⼤为4000。在多字节语⾔环境中，实际存储的字符个数可能⼩于L值，例如：当语⾔
环境为中⽂(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)时，⼀个VARCHAR2(200)的数据列可以保存200个英⽂字符或者
100个汉字字符；对于4000字节以内的字符串，建议都⽤该类型
a。VARCHAR2⽐CHAR节省空间，在效率上⽐CHAR会稍微差⼀些，即要想获得效率，就必须牺牲⼀定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。
b。VARCHAR2虽然⽐CHAR节省空间，但是如果⼀个VARCHAR2列经常被修改，⽽且每次被修改的数据的长度不同，这会引起‘⾏迁
移’(Row Migration)现象，⽽这造成多余的I/O，是数据库设计和调整中要尽⼒避免的，在这种情况下⽤CHAR代替VARCHAR2会更好⼀些。不过区别也不是太⼤，如果就是想⽤varchar2，那么在发⽣⾏迁移，可以通过pctfree来调整，然后对表的数据进⾏重组
nchar,nvarchar/nvarchar2国家字符集，与环境变量NLS指定的语⾔集密切相关
只有要⽤到unicode时才会⽤到这些国家字符集，nchar和nvarchar2依据所选的字符集来存储数据，可能⼀个字符占两个或多个字节，防⽌乱码
3、Long/long raw
Oracle已经废弃，只是为了向下兼容保留着，应该全部升级到lob
LONG，在数据库存储中可以⽤来保存⾼达2G的数据，作为变量，可以表⽰⼀个最⼤长度为32760字节的可变字符串
LONG RAW，类似于LONG，作为数据库列最⼤存储2G字节的数据，作为变量最⼤32760字节。
Long类型有很多限制
a、表中只能有⼀列long类型
b、Long类型不⽀持分布式事务
c、太多的查询不能在long上使⽤了
4. ⾏，包括RAW和LONG RAW两种类型。⽤来存储⼆进制数据，不会在字符集间转换
RAW，类似于CHAR，声明⽅式RAW(L)，L为长度，以字节为单位，作为数据库列最⼤2000，作为变量最⼤32767字节。
LONG RAW，类似于LONG，作为数据库列最⼤存储2G字节的数据，作为变量最⼤32760字节。
⾏标识，只有⼀种类型--ROWID，⽤来存储“⾏标识符”，可以利⽤ROWIDTOCHAR函数来将⾏标识转换成为字符。
5、Date
Date类型是⼀个7字节的定长数据类型(从世纪到秒)，绝对没有“千年⾍”问题。
举个例⼦：性能a>b>c
a、Where date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’)
and date_colum< to_date(’02-jan-2007’,’dd-mon-yyyy’)
b、Where trunc(date_colum,’y’)=to_date(’01-jan-2007’,’dd-mon-yyyy’)
c、Where to_char(date_colum,’yyyy’)=’2007’
6、 Timestamp
ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年⽉⽇时分秒的信息，
⽽且包括了⼩数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使⽤CAST 函数
语法Timestamp(n)，n指定秒的⼩数位数，取值范围0～9。缺省是６。
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff8') from dual
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
2010-03-03 20:18:21.31974900
这是因为显⽰格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显⽰。
说明：
1)当你把⼀个表中date类型字段的数据移到另⼀个表的timestamp类型字段中去的时候，可以直接写INSERT SELECT语句， oracle会⾃动为你做转换的。
2) to_char函数⽀持date和timestamp，但是trunc却不⽀持TIMESTAMP数据类型
a。把DATE类型转换成TIMESTAMP类型
SQL> select cast(sysdate as timestamp) from dual;
CAST(SYSDATEASTIMESTAMP)
--------------------------------------------------------------------------------
03-3⽉ -10 08.25.31.000000 下午
正如你看到的，在转换后的时间段尾部有了⼀段“.000000”。这是因为从date转换过来的时候，没有⼩数秒的信息，缺省为0
b. 为了得到系统时间，返回成date数据类型。你可以使⽤sysdate函数。
SQL> select sysdate from dual;
SYSDATE
-----------
2010-3-3 20
c. 为了得到系统时间，返回成timestamp数据类型。你可以使⽤systimpstamp函数。
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
03-3⽉ -10 08.27.41.114314 下午 +08:00
d. 你可以设置初始化参数FIXED_DATE指定sysdate函数返回⼀个固定值。这⽤在测试⽇期和时间敏感的代码。注意，这个参数对于systimestamp函数⽆效。
SQL> alter system set fixed_date ='2006-01-01-10:00:00';
System altered
SQL> select sysdate from dual;
SYSDATE
-----------
2006-1-1 10oracle四舍五入
SQL> select sysdate from dual;
SYSDATE
-----------
2006-1-1 10
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
03-3⽉ -10 08.31.12.665055 下午 +08:00
e. 参数fixed_date恢复默认值
SQL> alter system set fixed_date=none;
System altered
SQL> select sysdate from dual;
SYSDATE
-----------
2010-3-3 20
8. Number
定义Number的⽅法：Number(p,s) ；其中p，s都是可选的：
p代表精度，默认为38
s代表⼩数位数，取值范围-84~127，默认取值要看是否指定了p，如果制定了p，默认s为0，如果没有指定p，默认取最⼤值。⼏个例⼦：
a、 Number(5,0)=Number(5) 取值范围99999～-99999
b、 Number(5,2) 取值范围999.99～-999.99
注意：其中的整数位数只有3位，⼩数位数有2位，按照如下⽅法计算：
整数位数<=p-s
⼩数位数<=s
如果插⼊123.555存储在数据库中变成123.56 (在⼩数的第三位上四舍五⼊)，如果插⼊999.999，数据库就要抛错。
c、 Number(5,-2) 取值范围9999900～-9999900 （整数位数<=p-s，没有⼩数位数）
如果插⼊9999949存储在数据库中变成9999900（在整数的第⼆位上四舍五⼊），如果插⼊9999950，数据库就要抛错。
其他的数值类型都是number的衍⽣，底层都是number，⽐如integer/int完全映射到number(38)
性能相关：number是⼀种软实现的类型，如果需要对number做复杂的运算，建议先⽤cast内置函数转换number为浮点数类型另外需要注意的⼀点是：number是变长类型，在计算表存储空间的时候要切记
9. Lob
Clob/blob实现是⽐较复杂的，主要是⽤来存储⼤量数据的数据库字段，最⼤可以存储4G字节的⾮结构化数据。
Oracle的Blob字段⽐较特殊，他⽐long 字段的性能要好很多，可以⽤来保存例如图⽚之类的⼆进制数据。
写⼊Blob字段和写⼊其它类型字段的⽅式⾮常不同，因为Blob⾃⾝有⼀个cursor，你必须使⽤cursor对
blob进⾏操作，因⽽你在写⼊Blob之前，必须获得cursor才能进⾏写⼊，那么如何获得Blob的cursor呢？这需要你先插⼊⼀个empty的blob，这将创建⼀个blob的cursor，然后你再把这个empty的blob的cursor⽤select查询出来，这样通过两步操作，你就获得了blob的cursor，可以真正的写⼊blob数据了。
这⾥只提提⼏个和性能相关的点，当然能不⽤lob尽量不⽤：
oracle8以后:oralce中有四种类型的lob:clob,blob,nclob,bfile.
clob：内部字符⼤对象;
blob：内部⼆进制⼤对象;
nclob：内部定长多字节⼤对象;
bfile：外部⼆进制⼤⽂件，这个⽂件中的数据只能被只读访问，并且不包含在数据库内;bfile是早期的RDBMS BLOB的直接继承，作为数据库指针存储在数据库内部，指向外部的操作系统⽂件.
a，按存储⽅式分：
内部LOB：存放在DB内部，包括BLOB，CLOB，BCLOB
外部⽂件：存放在DB外⾯，就是BFILE
b.按存储数据的类型分：
①字符类型：
CLOB：存储⼤量单字节字符数据。
NLOB：存储定宽多字节字符数据。
②⼆进制类型：
BLOB：存储较⼤⽆结构的⼆进制数据。
③⼆进制⽂件类型：
BFILE：将⼆进制⽂件存储在数据库外部的操作系统⽂件中。存放⽂件路径。
⼤对象数据的录⼊
1，声明LOB类型列
/
*
conn scott/tiger;
Create TableSpace ts5_21
DataFile 'E:/Oracle/ts5_21.dbf'
Size 5m;
*/
Create Table tLob (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace ts5_21  --指定存储的表空间
Chunk 6k  --指定数据块⼤⼩
Disable Storage In Row
);
内部LOB的存储参数
具体语法可以参见ORACLE⽂档，
LOB ( lob项，...） STORE AS lob_segment_name
CHUNK integer
PCTVERSION integer
CACHE
NOCACHE LOGGING/NOLOGGING
TABLESPACE tablespace_name
STORAGE storage⼦句
INDEX INDEX字句
lob_segment_name：缺省式LOB$n
CHUNK：连续分配在⼀起的BLOCK数⽬，存放连续的LOB数据。这些CHUNK的数据存放在LOB INDEX⾥⾯，使⽤内部LOB标⽰和LOB值作为键。
PCTVERSION：LOB⼀致读需要的系统空间。⼀旦LOB申请超过PCTVERSION的值，ORACLE就会收旧的空间并REUSE之。CACHE：使⽤SGA区的DB BUFFER CACHE处理LOB的READ/WRITE。
NOCACHE LOGGING：不使⽤SGA区的BUFFER，数据的改变纪录到REDO LOG。
存取LOB⽐较频繁时，使⽤CACHE
存取LOB不频繁时，使⽤NOCACHE
NOCACHE NOLOGGING：不使⽤SGA区的BUFFER和REDO LOG
2，插⼊⼤对象列
①先插⼊普通列数据
②遇到⼤对象列时，插⼊空⽩构造函数。
字符型：empty_clob()，empty_nclob()
⼆进制型：empty_blob()
⼆进制⽂件类型：BFileName函数指向外部⽂件。
BFileName函数：
BFileName(‘逻辑⽬录名’，‘⽂件名’);
逻辑⽬录名只能⼤写，因为数据词典是以⼤写⽅式存储。Oracle是区分⼤⼩写的。
在创建时，⽆需将BFileName函数逻辑⽬录指向物理路径，使⽤时才做检查⼆者是否关联。
例⼦：
Insert Into tLob Values(1,'Gene',empty_clob(),empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
③将逻辑⽬录和物理⽬录关联。(如果是⼆进制⽂件类型)
授予 CREATE ANY DIRECTORY 权限
Grant  CREATE ANY DIRECTORY  TO ⽤户名 WITH ADMIN OPTION；
关联逻辑⽬录和物理⽬录
本地
Create Directory  逻辑⽬录名  As  ‘⽂件的物理⽬录’；
⽹络：
Create Directory  逻辑⽬录名  As  ‘//主机名(IP)/共享⽬录’；
例⼦：
Create Directory  MYDIR As 'E:/Oracle';
插⼊例⼦：
insert into tlob values(1,'Gene','CLOB⼤对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
⼤对象数据的读取和操作：DBMS_LOB包
在oracle中有多种⽅法可以对lob数据进⾏操作，但是最常⽤的为dbms_lob包，它主要提供了以下⼏个过程供⽤户对内部lob字段进⾏维护: APPEND（）将源LOB中的内容加到⽬的LOB中
COPY（）从源LOB中复制数据到⽬的LOB
ERASE（）删除LOB中全部或部分内容
TRIM（）将LOB值减少到指定的长度
WRITE（）向LOB 中写⼊数据
COMPARE（）⽐较两个同种数据类型的LOB的部分或全部值是否相同
GETLENGTH（）获取LOB的长度
READ（）从LOB中读出数据
DBMS_LOB包：包含处理⼤对象的过程和函数
/*
insert into tlob values(1,'Gene','CLOB⼤对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
insert into tlob values(2,'Jack','CLOB⼤对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
insert into tlob values(3,'Mary','⼤对象列CLOB',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
*/
1，读取⼤对象数据的过程和函数
①：DBMS_LOB.Read()：从LOB数据中读取指定长度数据到缓冲区的过程。
DBMS_LOB.Read(LOB数据，指定长度，起始位置，存储返回LOB类型值变量)；
例⼦：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。