欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle建表常用数据类型详解

程序员文章站 2022-05-26 13:50:07
...

在Oracle中, varchar已经作为了保留字。如果你使用varchar类型,Oracle也不会报错,但是建表以后你会发现,已经自动变为了varch

前言:
为列指定数据类型并不难,难的是指定合适的数据类型。同样是字符串类型,选择定长还是变长,其中大有文章。所以需要耐心而细致的学习下面的内容。

创建表时,,必须为表的各个列指定数据类型。如果实际的数据与该列的数据类型不相匹配,则数据库会拒绝保存。如为学生指定出生日期为“1980-13-31”。

在Oracle中,常见的数据类型有:
字符串:字符串分为定长类型char和变长类型varchar2。
数字:整数 number(整数位),小数 number(总长度,小数位),只写number,表示无限制。
日期:date类型,可以保存年月日时分秒。

问题:Oracle中为什么字符串类型为varchar2,它与varchar有什么关系?
回答:
在Oracle中, varchar已经作为了保留字。如果你使用varchar类型,Oracle也不会报错,但是建表以后你会发现,已经自动变为了varchar2类型。

问题:用number还是用integer(整数)?
另外Oracle也有integer等其它类型,但是用integer有缺点,就是不知道其长度,甚至可能在不同的操作系统上(如32位CPU和64位CPU)所分配的长度是不同的。而用number,指定多少,就永远是多少,一切掌握在自己手中。

问题:如何为字符串和数字类型指定长度?
char类型可以不指定长度,则默认为1,而varchar2必须指定长度。
char和varchar类型的长度最大长度大约在4000多一点点。
number表示不限整数或小数,它能够保存非常大的数字或者保存小数位非常多的数字。所以为了节约磁盘空间,尽量指定长度。
date:不需要指定长度。

问题:字符串最大长度约为4000。如果要保存更多的内容怎么办?
回答:
解决的方法有两种。第一种是用大对象类型,即CLOB或者BLOB类型,但是编程比较麻烦;第二种是用一对多的父子表实现。

大对象是指大量的数据。如果用char或varchar2,列的最大长度大约在4000多;如果内容更多,其中一个方法就是将列设置为CLOB类型,但是只限制保存字符数据,如小说。如果是二进制数据,如图片、声音、office文档,则需要将列设置BLOB类型。CLOB或BLOB最大能够装4G的内容。如果是电影,则更通常的做法是在表中保存电影的名称、路径等信息,电影直接保存在磁盘上,而不是直接存储在数据库中,也不是用BFile类型。
示例:创建小说表
create table xiao_shuo(
xs_id number(10) primary key, --小说编号
xs_name varchar2(5), --小说名称
xs_contenct clob, --小说内容
xs_fen_mian blob --小说封面(图片)
);
第二种是利用父子表实现,例如:小说表(小说ID,小说标题),内容表(小说ID,行数,行内容),其中行内容为varchar(4000)。这表示每一行最多保存4000个字符。

重点问题:定长类型和变长类型和什么区别?
回答:
两者主要的区别体现在存储上和查询效率上。
首先讲char——定长类型。
如将姓名列指定为char(8)。当保存“张三”时,数据库还会自动保存4个空格;保存“张三丰”时,数据库还会自动保存2个空格,这样每个人的姓名长度都为8,长度是固定的,所以叫做“定长”。明显,在保存信息时,定长会因为保存了很空格而多占用了磁盘空间。
数据库保存这些“多余”的空格有什么作用?
那就是查询时,在取到字段的长度以后,不再需要判断每一个姓名的实际长度,就可以取到数据。这样查询效率大大提高了。

下面再讲varchar2——变长类型。
如将姓名列指定为varchar2(8)。当保存“张三”和保存“张三丰”时,数据库都只保存数据的本身,不会自动添加空格。两个人姓名的长度分别为4和6,长度是变化的,所以叫做“变长”。这样没有多占用任何磁盘空间。
但是在查询时,每个人的姓名的长度都不同,必须先判断后取数据,所以查询效率比char类型要低。

小结:char和varchar的关系就是空间和时间的关系,char是以空间换时间,牺牲了磁盘空间,但羸得了查询时间。

对于Oracle,还有更深层次的区别。
比如对于员工的“备注”信息,如果用varchar(4000)。有的员工备注原来很少,后来有可能加入大量的备注。由于varchar类型是没有在记录之间保存多余的空闲空间的,所以就会引用记录的“行迁移”,造成磁盘碎片,从而降低查询效率。而char类型则用空格已经占用了,不会引用磁盘空间的再分配。不会在项目使用过程中引用碎片问题。比如QQ签名、群简介等信息用户经常修改,用char就比varchar要好。

下面是Oracle中的系统文件中关于数据类型的定义:

subtype FLOAT is NUMBER; -- NUMBER(126)
subtype REAL is FLOAT; -- FLOAT(63)
subtype "DOUBLE PRECISION" is FLOAT;
subtype INTEGER is NUMBER(38,0);
subtype INT is INTEGER;
subtype SMALLINT is NUMBER(38,0);
subtype DECIMAL is NUMBER(38,0);
subtype NUMERIC is DECIMAL;
subtype DEC is DECIMAL;

subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
subtype NATURAL is BINARY_INTEGER range 0..2147483647;
subtype NATURALN is NATURAL not null;
subtype POSITIVE is BINARY_INTEGER range 1..2147483647;
subtype POSITIVEN is POSITIVE not null;
subtype SIGNTYPE is BINARY_INTEGER range '-1'..1; -- for SIGN functions

type VARCHAR2 is NEW CHAR_BASE;

subtype VARCHAR is VARCHAR2;
subtype STRING is VARCHAR2;

subtype LONG is VARCHAR2(32760);

subtype RAW is VARCHAR2;
subtype "LONG RAW" is RAW(32760);

subtype ROWID is VARCHAR2(256);

-- Ansi fixed-length char
-- Define synonyms for CHAR and CHARN.
subtype CHAR is VARCHAR2;
subtype CHARACTER is CHAR;

type MLSLABEL is new CHAR_BASE;