Oracle Datatypes

Oracle Datatypes

1 Numeric Datatypes(数值类型)

TYPE			Precision	Scale(小数位数)
NUMBER(precision, scale)	Fixed and floating-point numbers	$$10^{-130}\le v<10^{126}$$	1~38(默认38, 也可以用*表示38)	-48~127(默认值取决于是否指精度, 如果没有指定精度, 默认最大; 指定精度, 默认为0)
BINARY_FLOAT	32-bit, single-precision floating-point number	5 bytes(including a length byte)	近似数值，没有NUMBER类型精确	常用于科学计算，处理速度快，不适合存储金融信息
BINARY_DOUBLE	64-bit, double-precision floating-point number	9 bytes(including a length byte)

Input Data	Specified As	Stored As
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER(*,1)=NUMBER(38,1)	7456123.9
7,456,123.89	NUMBER(9)=NUMBER(9,0)	7456124
7,456,123.89	NUMBER(9,2)	7456123.89
7,456,123.89	NUMBER(9,1)	7456123.9
7,456,123.89	NUMBER(6)=NUMBER(6,0)	(not accepted, exceeds precision)
7,456,123.89	NUMBER(7,-2)	7456100

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle Database does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to $4.12\ast 10^2$, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive, and s equals 1 if the number is negative.

Zero and positive and negative infinity (only generated on import from Oracle Database, Version 5) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.

2 字符类型

NLS(National Language Support)控制着数据的许多方面。它控制的最重要的两个方面是：

• 文本数据持久存储在磁盘上时如何编码
• 透明地将数据从一个字符集转换到另一个字符集

--查看当前的数据库字符集设定
SELECT *
FROM nls_database_parameters
WHERE parameter='NLS_CHARACTERSET';

TYPE
CHAR(size [BYTE|CHAR])	定长, 用空格填充达到最大长度	最多2000字节	size默认单位为BYTE
VARCHAR2	变长, 不会用空格填充	最多4000字节
NCHAR		最多2000字节	包含UNICODE格式数据的字符串
NVARCHAR2		最多4000字节	包含UNICODE格式数据的字符串

NVARCHAR2和NCHAR与相应的VARCHAR2和CHAR的区别在于：

• 文本采用数据库的国家字符集来存储和管理，而不是默认字符集
• 长度总是字符数，而CHAR/VARCHAR2可能会指定是字节还是字符

3 二进制类型

数据库中以二进制类型存储的二进制数据不会进行字符集转换，而文本类型需要进行字符集转换

TYPE
RAW(size)	最多2000字节二进制数据	变长二进制串，不能存储可直接显示的字符数据
BLOB	大对象类型
LONG RAW	为了支持向后兼容性提供	LONG文本类型：可以存储2GB的文本 LONG RAW类型：可以存储2GB的原始二进制数据

Oracle支持LOB（Large OBject）类型共有4种，分为两类：

• 存储在数据库中的LOB，也称为内部LOB
  • CLOB
  • NCLOB
  • BLOB
• 存储在数据库之外的LOB
  • BFILE

TYPE	TYPE
BLOB	二进制lob类型，	存储二进制数据
CLOB	字符lob类型	存储各种字符数据
NCLOB	国际语言字符lob类型，使用多字节存储各种语言的字符	存储非英文字符
BFILE	二进制file类型	存储指向数据库系统外的文件系统中的文件的指针

与以前的long和long raw类型相比，lob类型具有以下优点：

• 可以存储128TB级的数据，而long类型数据最多只能存储2GB的数据，其中raw类型只能存储4KB的数据
• 一个表中可以有多个lob列，但最多只能有一个long或long raw列
• 可以随机顺序访问，long和long raw只能顺序访问

每一个lob对象都由两部分组成：指针（定位器）和数据内容, 系统根据lob内容大小的不同，采取不同的存储方式

--在创建没有lob数据的表时，在create table语句中可能会有一个storage子句。
--在创建包含lob列的表时，可以为lob数据使用的数据区域指定存储参数
--这时，需要在create table语句中再附加一个lob子句
CREATE TABLE proposal(
proposalID number(10),
recipientName varchar2(20),
proposalName varchar2(20),
description varchar2(200),
proposalText clob,
budget blob,
cover bfile,
constraint proposal_pk primary key(proposalID)
)
tablespace proposals
lob(proposalText, budget) store as
(tablespace proposals_lobs
storage(initial 100K next 100K pctincrease 0)
chunk 16k pctversion 10 nocache nologging);

可以使用多种方法来处理lob值

• 可以使用原来用于varchar2数据的字符串函数来处理lob数据
• 对于比较大的lob值，例如大于100KB，或对于那些复杂的数据操作，应该使用dbms_lob程
  序包来处理lob数据
• 可以使用OCI等程序

对于每一个lob列，Oracle系统将保存一个定位器值，以便找到该记录存储的外部数据。

• 在向包含了lob值的表中插入记录时，可以用函数来告诉Oracle为内部存储的lob列创建一
  个空定位器
• 空定位器值与null值不同。如果一个内部存储的lob列值为null，那么在更新为非null
  值之前，必须将其设置为空定位器。——初始化lob值

可以采用以下方式设置Lob数据的空定位器：

• blob：empty_blob()
• clob：empty_clob()
• bfile：bfilename()

可以使用 bfilename 函数指向一个目录和文件名的组合值，但必须先创建目录对象。注意，在插入数据时， bfile 数据所指向的文件不一定必须存在

update proposal
set proposalText='abcdefg',
	cover=bfilename('proposal_dir', 'office01.doc')
where proposalID=3;

注意，若要更新 lob 型数据，必须以将其初始化为空指针为基础

可以使用字符串函数处理clob值, 在使用substr函数检索数据时，一个中文就是一个字符

可以使用dbms_lob程序包对lob数据进行复杂的处理

read(lob,amount,offset,buffer)

提高使用大对象效率的方法：

• 数据分区技术(把业务数据集分解成多个规模比较小的子数据集单元，通过对这些子数据集单元独立地管理和访问，可以提高整个业务数据集的使用效率)4种数据分区方式: 范围分区、列表分区、散列分区和组合分区
• 压缩数据技术(在压缩数据时，数据库中的冗余数据被删除了，只留下了对这些冗余数据的引用关系，并且这些引用关系都放在数据块的开头位置。通过压缩数据可以大大降低物理磁盘需求的空间。压缩数据最适合用于那些数据很少修改或几乎不修改的环境中)
• 并行处理技术(通过并行处理的方式来提高查询的性能。并行处理适用于大量数据需要处理的情况) 并行处理组件包括并行执行协调器和并行执行服务器。并行执行协调器负责把请求分解到多个处理过程中，每一个处理过程都发送到一个并行执行服务器。最后，并行执行协调器把来自每一个服务器的结果组合在一起，把最终完整的结果提交给请求者。
• 物化视图技术(物化视图具有视图的特征，但是与视图也不完全相同, 可以基于select语句创建物化视图，但是物化视图可以物理地保留和存储数据。使用物化视图，可以执行各种查询操作、可以被
  分区、可以被压缩、可以被并行处理，甚至可以在其上面创建索引)
• RAC技术

4 日期类型

• DATE和TIMESTAMP类型可以存储固定日期/时间
• INTERVAL类型可以存储一个时间量

TIMESTAMP相对于DATE的两个优势：

• 支持小数秒
• 支持时区

DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性，包括：世纪、世
纪中的哪一年、月份、月份中的哪一天、小时、分钟和秒。

5 ROWID/UROWID类型

ROWID是数据库中一行的地址, 可通过ROWID在磁盘上找到行以及标识ROWID所指向的对象。ROWID并不真正存储在行中，而是行的一个推导属性，基于行的物理地址生成。Oracle中除了索引组织表（IOT）外的所有行都有ROWID。ROWID主要用于作为物理地址来减少行更新操作的操作代价，加快访问任何表中某一行的速度。

UROWID是ROWID的“近亲”。它用于比如索引组织表（IOT）和通过异构数据库网关访问的没有固定ROWID的表。UROWID基于行的主键生成，是行主键值的一个表示。但是，UROWID并不作为一个单独的列存在，而只是作为现有列的一个函数。

6 序列

序列就是计数器，属于用户创建的数据库对象，可以被多个用户共享。每使用一次，它的值就
增加1。典型的用途是创建一个主键值，在插入记录时，通过使用序列可以确保为每个插入的列分配新的唯一序号。它可以用来代替应用程序编号

--创建序列（补充）
CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[CYCLE]
--使用序列（补充）
--NEXTVAL
--从NEXTVAL “列”进行选择会导致序列自动增加到下一个序号
SELECT sequence_name.NEXTVAL FROM DUAL
--CURRVAL
SELECT sequence_name.CURRVAL FROM DUAL
--修改序列（补充）
ALTER SEQUENCE sequence_name
[INCREMENT BY n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]