Oracle 数据类型

Oracle数据类型	简要说明
字符类型	char和varchar2，可表达任何字符串
数字类型	number(m,n)，可表达任何数字，m是数字的精度，n是小数点后的位数，如果n为0则表示是一个整数。
日期类型	date，存放日期和时间，包括年（yyyy）、月（mm）、日（dd）、小时（hh24）、分（mi）、秒（ss）
long类型	存放二进制数据，如xxx
clob类型	存放单字节字符串或多字节字符串数据，如文本文件、xml文件
blob类型	存放非结构化的二进制数据，如图片、音频、视频、office文档等
rowid类型 urowid类型	存放表中记录在数据库中的物理地址

不同字符集下的汉字占用字节明细

字符集	每个汉字占用字节
UTF-8	1-3字节
GB2312	2字节
GBK	2字节
GB18030	1、2、4字节

字符类型

字符类型	定长/变长	举例	是否必须指定长度	默认值	字节	unicode字符集
char	定长字符类型（未达到指定长度时，自动在末尾用空格补全）	char(10)，如果存入'freecplus'，在数据库中将存储'freecplus '，在最后补了一个空格。	否	1	最大2000字节	非unicode
nchar	定长字符类型（未达到指定长度时，自动在末尾用空格补全）	同上	否	1	最大1000字节，所有字符都占两个字节	根据unicode
varchar2	变长字符类型（未达到指定长度时，不自动补全空格）	varchar2(10)，如果存入'freecplus'，在数据库中将存储'freecplus'，什么也不会补。	是		最大4000字节，从12c起，可以存储32767字节信息	非unicode
nvarchar2	变长字符类型（未达到指定长度时，不自动补全空格）	同上	是		最大4000字节，从12c起，可以存储32767字节信息	根据unicode

1. 最大xxx字节，字节而非字符个数，如char(1)就连一个汉字都不能存放。

2. unicode字符集是为了解决字符集不兼容的问题而产生的，所有字符都用两个字节表示，即英文字符也用两个字节表示。

3. 如果确定、肯定、一定、保证存入字符串的长度是固定不变的，例如性别、身份证号码、手机号码，用char类型，否则用varchar2类型，例如姓名、学历、地址、兴趣爱好等，char虽然死板，但是效率高。

创建表验证字符类型

create table char_table(
  char_column char,
  nchar_column nchar(2),
  varchar2_column varchar2(4000),
  nvarchar2_column nvarchar2(4)
);

插入数据

insert into char_table values ('1', '太一', '亚古兽进化', '暴龙兽');

查询数据

select * from char_table;

数字类型

oracle中的数字类型主要为三种：number，float，binary_*，其他的类型基本上都是number类型或者float类型的子类型。

数字类型	使用频率	说明
number(p,s) number(p,s）的子类型	常用	略
float float的子类型	不常用	二进制精度，需要进行精度转换
binary_float binary_double	不常用	表示范围更大可用于科学计算，精度没有number类型高（可用于金融数据）

number(p,s)类型

22个字节的存储空间

字母	释义	说明	取值范围	默认值	其它
p	DBA_TAB_COLUMNS表的DATA_PRECISION(精度)	包括小数在内的总共有效位数	[1~38]	若没有指定，默认为38	当整数部分的长度大于p时，报错
s	DBA_TAB_COLUMNS表的DATA_SCALE(小数位)	精确到多少位小数	[-84~127]	若没有指定，默认为0	当小数位数大于s时，并四舍五入 当s取负数时，将小数点左边的s置为0，并四舍五入

官方文档给出的几个例子如下：

输入数据	定义类型	存储结果
1,234,567.89	NUMBER	1234563.89
1,234,567.89	NUMBER(*,1)	1234563.9
1,234,567.89	NUMBER(9)	1234564
1,234,567.89	NUMBER(9,2)	1234563.89
1,234,567.89	NUMBER(9,1)	1234563.9
1,234,567.89	NUMBER(6)	报错，超精度
1,234,567.89	NUMBER(7,-2)	1234600

number类型的子类型

number类型的子类型	简要说明
integer	等同于number(38)
smallint	同于number(38)
decimal	可以使用decimal(p,s),若p，s未指定，等同于number(38)

float类型

字母	释义	说明	取值范围	默认值
float(b)	二进制精度（binary precision	b表示二进制进度	[1,126]	126

b是一个二进制精度（binary precision），而不是我们通常说的十进制精度（decimal precision）

需要进行以下的转换：binary precision＝int(b*0.30103)

举例来说：当b=2，则对应的十进制精度为int(2*0.30103)=0，即小数点后精度为0。

eg：56.2存储到float(2)变为60。计算过程：56.2=5.62*10^1，因为精度为0只能取到整数，5.62四舍五入后为6，最后取值为6*10^1=60

float类型的子类型

float类型的子类型	简要说明
real	等同于float(63)

binary_float和binary_double类型

数据类型	说明	字节长度
binary_float	32位单精度浮点数数据类型	5个字节（4字节+1个长度字节）支持至少6位精度
binary_double	64位双精度浮点数数据类型	9个字节（8字节+1个长度字节）可以支持至少15位精度

创建表验证数字类型

create table num_table(
  number1 number,
  number2 number(38),
  number3 number(9,2),
  int4 int,
  smallint1 smallint,
  decimal1 decimal(5,2),
  float1 float,
  float2 float(2),
  real1 real,
  binary_float1 binary_float,
  binary_double1 binary_double
);

插入数据

insert into num_table values (1.23, 123, 1234567.89, 573, 34, 673.43, 34.1264, 56.2, 23.231, 12.34f, 34.56d);

查询数据

select * from num_table;

日期类型

数据类型	存储说明	字节	取值范围	其它说明
date	世纪，年，月，日，时，分，秒	7个字节	--	每个部分占用一个字节
timestamp(precision)	时间戳，包含小数秒	7字节或11字节的定宽日期/时间数据类型	precision的取值范围为[0,9]，默认为6（微秒），最大为9（纳秒）	秒的小数部分位数超过precision但未超过9时，按精度四舍五入，超过9时报错
timestamp(precision) with time zone	在timestamp(precison)的基础上加入了时区偏移量的值	13字节
timestamp with local time zone	存储时转化为数据库时区进行规范化存储，但不存储时区信息，客户端检索时，按客户端时区的时间数据返回给客户端	7字节或11字节的定宽日期/时间数据类型		在数据的插入和读取时会发生时区转换
interval day(days_precision) to second(seconds_precision)	存储天、小时、分和秒的时间间隔		days_preciosn取值范围为[0~9]，默认是2，seconds_precision取值范围为[0~9]，默认是6
interval year to month	存储一个时段	5字节的定宽数据类型
interval day to second	存储一个时段	11字节的定宽数据类型		将时段存储为天/小时/分钟/秒数，还可以有9位小数秒

创建表验证日期类型

create table date_table (
  date_column date,
  timestamp_column timestamp(6),
  timestamp_time_zone_column timestamp(9) with time zone,
  timestamp_local_time_zone_column timestamp with local time zone,
  interval_year_column interval year(3) to month,
  nterval_day_column interval day(3) to second(6)
);

插入数据

insert into date_table (date_column, timestamp_column, timestamp_time_zone_column, timestamp_local_time_zone_column) values (sysdate, sysdate, sysdate, sysdate);

-- 查询时使用 to_char(),例如select to_char(date_column,'yyyy-mm-dd hh24:mi:ss') from date_table;
insert into date_table (date_column) values (to_date('2024-4-13 16:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into date_table (date_column) values (to_date('20240413161234', 'yyyymmddhh24miss'));

-- 超过精度四舍五入
insert into date_table (timestamp_column) values (to_timestamp('20240413160000.999997623', 'yyyymmddhh24miss.ff'));

insert into date_table (timestamp_time_zone_column) values (to_timestamp_tz('20240413161234.999996623', 'yyyymmddhh24miss.ff'));

-- 超过精度四舍五入
insert into date_table (timestamp_local_time_zone_column) values (to_timestamp_tz('20240413160000.999996623', 'yyyymmddhh24miss.ff'));

insert into date_table (interval_year_column) values (interval '24' year);

-- TODO 待定...
insert into date_table (interval_year_column) values (interval '223-9' year(3) to month);

insert into date_table (interval_day_column) values (interval '12 10:23:01.1234568' day to second);

查询数据

select * from date_table;

long文本类型

数据类型	存储说明	存储大小	是否需要转换字符集	存储限制
long	--	最多达2GB	存储时需要进行字符集转换	列不能作为主键或唯一约束； 一个表中只有一列可以为long或long raw； 不支持分布式事务；限制较多
raw(size)	变长二进制数据类型	size 表示数据长度,取值范围为1~2000B	存储时不需要进行字符集转换	--
long raw	变长二进制数据	最多达2GB	存储时不需要进行字符集转换	列不能作为主键或唯一约束； 一个表中只有一列可以为long或long raw； 不支持分布式事务；限制较多 Oracle 建议使用 blob 类型代替 long raw 类型

blob & clob & nclob大 & bfile对象数据类型

数据类型	存储说明	存储说明	存储大小	其它说明
blob	变长的二进制大型对象	存储保存带格式的非结构化数据，比如：存储图片，音乐，视频等信息，通常将文件转为二进制再存进去	最大大小为4G	支持随机存储，当数据库的字符集发生转换时，blob类型不会受到影响，Oracle数据库不关心存放的是什么内容
clob	变长的字符大型对象	存储单字节字符串或多字节字符串数据，比如：存储文章或较长的文字	最大大小为4G	clob被认为是一个更大的字符串。当数据库的字符集发生转换时，clob类型会受到影响
nclob	--	存储Unicode类型的数据，根据字符集而定的字符类型	最大大小为4G
bfile	二进制文件	存放指向操作系统文件的指针		指向的文件不是数据库的一部分，只能在数据库外维护；只读，数据库将该文件当二进制文件处理。

创建表验证大数据类型

create table bigfile_table (
  long_column long,
  blob_column blob,
  clob_column clob,
  nclob_column nclob,
  bfile_column bfile
);

插入数据

insert into bigfile_table (long_column, blob_column, clob_column, nclob_column) values ('12323232', '2312', '111', '212');

insert into bigfile_table (long_column, blob_column, clob_column, nclob_column) values ('12323232', '231234', '111', '212');

-- 其中bfiledir为文件路径
 insert into bigfile_table (bfile_column) values (bfilename('bfiledir', 'bfile1.txt'));

查询数据

select * from bigfile_table;

rowid & urowid类型

数据类型	存储说明	字节大小	数据说明	注意事项
rowid	十六进制串	10字节	Oracle数据库中每个表的每行记录都有一个存储的物理位置，即表的rowid伪列 采用rowid作为where条件的访问效率最高。	1.rowid存放的是表记录的物理位置，在数据整理、数据备份和迁移的时候，记录的物理位置会发生改变； 2.rowid是Oracle数据库专有的数据类型，与其它的数据库不兼容。
urowid	--	--	支持逻辑的和物理的rowids，是一个通用的rowid，没有固定的rowid的表	--

rowid伪列说明：基于base64编码的18为字符，分为4部分：OOOOOOFFFBBBBBBRRR,用于确定唯一的行地址

分段	分段说明
OOOOOO	六位表示data object id,根据object id可以确定segment
FFF	三位表示相对文件号
BBBBBB	六位表示data block number
RRR	三位表示row number