此时情绪此时天，无事小神仙
好好生活，平平淡淡每一天

编辑

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
posted @ 2024-04-10 18:34  踏步  阅读(28)  评论(0)  编辑  收藏  举报