Oracle学习之数据类型

Oracle数据库的数据类型与其它的数据库系统相比，它的数据类型不多，Oracle在表示数据方面比其他数据库系统来说要省去许多关键字。Oracle只用NUMBER(m,n)就可以表示任何复杂的数字数据。其它如日期类型等也简单得多，只DATE就表示日期和时间。下面以列表形式给出各个版本的Oracle系统数据类型的表示方法。下面给出Oracle旧版本的数据类型的目的是让读者了解Oracle的变化，另外就是你在对旧版本进行升级或数据转换时要注意各个版本的差别。

 

ORACLE5、ORACLE6数据类型

 

数据类型	说明
Char	可变长字符型，≤254
Varchar2	可变长字符型，≤2000
Number(m,n)	数字类型，含整数、小数等
Date	日期型，含时间，缺省格式为mmm-dd-yyyy hh:mi:ss（占7字节）
Long	存储大型可变长字符串，≤2GB
Raw	存储短二进制串，≤2GB
Long  raw	存储长二进制串，≤2GB

                               ORACLE7数据类型

数据类型	说明
Char	定长字符，≤255个字符
Varchar	变长字符，≤2000个字符
Varchar2	变长字符，≤2000个字符
Number(m,n)	数字类型，含整数、浮点、双精度等
Long	存储大型可变长字符串，≤2GB
Raw	存储可变短二进制数，≤2000
Long  raw	存储大型可变长二进制数，≤2GB

 

                      ORACLE8/8i  数据类型

 

数据类型	说明
Char	定长字符，≤2000个字符
Varchar	（同Varchar2）可变字符，≤4000个字符
Varchar2	变长字符，≤4000个字符
Date	固定长度（7字节）的日期型
Number	数字型，可存放实型和整型
Long	可变字符，≤2GB个字符
Raw	可变二进制数据，≤4000字节
Long  raw	可变二进制数据，≤2GB
MLSLABEL	仅Trusted Oracle 用长度在2~5字节间
Blob	大二进制对象，≤4GB
Clob	大字符串对象，≤4GB
Nclob	多字节字符集的Clob，≤4GB
Bfile	外部二进制文件，大小由OS决定

 

 

CHAR(<size>)

定长字符型（在Oracle5 、Oracle6 是变长），字符长度不够自动在右边加空格符号。当字符长度超出2000个则错误。不指定大小缺省为 1。

 

VARCHAR(<size>)

可变字符型，当前与VARCHAR2(<size>)相同。

 

VARCHAR2(<SIZE>)

可变字符型，当前与VARCHAR(<size>)相同。VARCHAR2 类型的字段（列）可存放4000个字符；但是VARCHAR2 变量可以存放32,767 个字符。大小必须指定。

 

NCHAR(<size>)和NVARCHAR2(<size>)

NCHAR 和NVARCHAR2 分别与 CHAR和VARCHAR2 有相同的大小。并用于于存放 National Language Support (NLS)数据，Oracle 允许以本地语言存放数据和查询数据。

如果将列名声明成NCHAR、NVARCHAR2这样的类型，则insert和 select 等语句中的具体值前加N，不能直接按照普通字符类型进行操作。看下面例子：

 

SQL> create table nchar_tst(name nchar(6),addr nvarchar2(16),sal number(9,2));

 

表已创建。

 

SQL> insert into nchar_tst values(N'赵元杰',N'北京市海淀区',9999.99);

 

已创建 1 行。

 

SQL> select * from nchar_tst where name like N'赵%';

 

NAME   ADDR                    SAL

------ ---------------- ----------

赵元杰 北京市海淀区       9999.99

 

SQL> select * from nchar_tst where name like '赵%';

select * from nchar_tst where name like '赵%'

                                        *

ERROR 位于第 1 行:

ORA-12704: 字符集不匹配.

 

提示：虽然Oracle可以使用nchar, nvarchar2 类型来存放字符数据，但建议设计者不要使用NCHAR和NVARCHAR2。因为CHAR和VARCHAR2就能存放汉字。

 

NUMBER(<p>,<s>)

 

 <p>是数据的整数部分，<s>是数据的精度（即小数）部分,注意，<s>部分可以表示负的精度。用<s>可以表示从小数点往右或往左保留多少位。如下表：

 

实际值	数据类型	存储值
1234567.89	Number	1234567.89
1234567.89	Number(8)	1234568
1234567.89	Number(6)	出错
1234567.89	Number(9,1)	1234567.9
1234567.89	Number(9,3)	出错
1234567.89	Number(7,2)	出错
1234567.89	Number(5,-2)	1234600
1234511.89	Number(5,-2)	1234500
1234567.89	Number(5,-4)	1230000
1234567.89	Number(*,1)	1234567.9

 

 

Sal  number(7,2),  --表示5位整数，2位小数.

 

DATE

Oracle 的日期型用7个字节表示，每个日期型包含如下内容：

l       Century (世纪)

l       Year （年）

l       Month（月）

l       Day （天）

l       Hour （小时）

l       Minute （分）

l       Second （秒）

 

日期型字段有下面特点：

 

l       日期型字段的插入和更新可以数据型或字符并带to_date 函数说明即可。

l       缺省的日期格式有NLS_DATE_FORMAT参数控制，它的缺省格式为DD-MON-YY。

l       缺省的时间是夜里 00:00:00 (即0点 0分0秒)。

l       sysdate返回的是服务器的时间，见下面例子。

l       日期格式的显示可以设置，见下面例子。

l       日期型可以运算，见下面例子。见下面例子。

l       世纪用cc 表示；年用yyyy表示，月用mm表示，日用dd表示，小时用hh24表示，分用mi表示，秒用ss表示。

例子：

SQL> create table save_info(per_id varchar2(20),name varchar2(20),tran_date date,

  2  tran_val number(12,2));

 

表已创建。

 

SQL>  insert into save_info values ( '110105540609811','赵元杰',

  2  to_date('2001.06.18','yyyy.mm.dd'),12345.66);

 

已创建 1 行。

 

SQL> select * from save_info;

 

PER_ID               NAME                 TRAN_DATE    TRAN_VAL

-------------------- -------------------- ---------- ----------

110105540609811      赵元杰               18-6月 -01   1234.66

 

SQL> select per_id,name,to_char(tran_date,'yyyy/mm/dd'),tran_val from save_info;

 

PER_ID               NAME                 TO_CHAR(TR   TRAN_VAL

-------------------- -------------------- ---------- ----------

110105540609811      赵元杰               2001/06/18   12345.66

 

 

SQL> show parameter nls_date_format

 

NAME                                 TYPE    VALUE

------------------------------------ ------- ------------------------------

nls_date_format                      string

 

 

SQL> alter session set nls_date_format=

  2  '"公元"yyyy"年"mm"月"dd"日"';

 

会话已更改。

 

SQL> select sysdate from dual;

 

SYSDATE

------------------

公元2001年05月18日

 

 

SQL> select to_char(sysdate,'cc yyyy.mm.dd') from dual;

 

TO_CHAR(SYSDA

-------------

21 2001.05.18

 

关于日期型的使用方法详细请参考《Oracle8i National Language Support Guide》。

 

 

BLOB

大二进制对象，每条记录可存储达4GB的数据，详细见后面章节。

 

CLOB

大字符对象，每条记录可存储达4GB的数据，详细见后面章节。

 

BFILE

外部二进制文件，每条记录可存储达4GB的数据（与OS有关），详细见后面章节。

 

RAW

非结构的二进制数据，这些数据不被数据库系统解释。RAW可以存储达2,000字节。

 

LONGRAW

大的二进制类型数据，LONGRAW是非结构的二进制数据，这些数据不被数据库系统解释。LONGRAW可以存储达2GB字节。LONGRAW不能被索引，而RAW可以被索引。

 

ROWID

ROWID在Oracle数据库中是一个虚的列，即系统用的特殊的列，不是我们建立的列。用于对数据库中的每条记录进行定位。详细见“Rowid的使用”章节。

 

UROWID

UROWID 是Universal ROWID 的意思。即全球ROWID，它支持逻辑和物理ROWID，也作为外部表的（通过getway 访问的非Oracle 表）的ROWID。UROWID类型可以存储所有的ROWID类型的数据。

 

 

%TYPE类型的匹配：

             books_printed         number(6)；

             books_sold             books_printed%TYPE；

             books_sold的数据类型与book_printed的类型一致；

( %TYPE类型 在 PL/SQL中介绍 )。

 

 

空值与字符型、数字型的运算：

        null + <数字>=null            （空值+数字仍是空值）

        null > <数字>=null            （空值与数字比较结果为空值）

        null || '字符串' = 字符串

 

number类型与以下类型具有同等的值域：

       DEC

       Decimal

       Double   PREcision

       Float

       Integer

       Int

       Numeric

       Real

       Smallint

 

提示：虽然Oracle可以使用上面的子数据类型，但建议还是采用NUMBER(n,m)为好。因为如果使用子数据类型定义字段类型不当，可能引起数据在运算方面的问题。

 

Long数据类型的限制：

l       select中可以用long；

l       update中可以用select子句；

l       insert中可以用Valus子句；

l       每个表只能允许一个long列；

l       long列不能列出完整性约束（null、not   null除外）；

l       long列不能被索引；

l       过程或存储函数不能接收long型函数；

l       存储函数不能返回long型值。

 

 

long  目前不能出现在以下情况中：

l       select中的Where，Group  by，order  by，Connect  by，distinct；

l       不能对long列作substr，instr；

l       表达式或条件；

l       子查询或集合中不能用long；

l       Create   table   ...as   select   中不能用long；