数据类型
官方文档：home ---> SQL Language Reference ---> 随便搜索一种数据类型即可
四种基本的常用数据类型：1、字符型 2、数值型 3、日期型 4、大对象型

字符型

char

The CHAR data type specifies a fixed-length character string. Oracle ensures that all values stored in a CHAR column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, then Oracle returns an error.

The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes.

When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter.

在没有数据之前，还可以改变列的宽度，例如：
SQL> alter table t1 modify al char(4);
如果已经插入值，可以改大，不能改小。

注意：客户端一定要正确地设置 NLS_LANG变量，如果设置不正确，要出问题的，例如，数据库的字符集是AL32UTF8，客户端默认没有设置NLS_LANG变量，则：
SQL> select length('中') from dual; 返回3，正确应该返回1
SQL> select lengthb('中') from dual; 返回9，正确应该返回3

如果数据库使用的AL32UTF8编码，通常说来，一个汉字占3个bytes，如果使用的是ZHS16GBK， 一个汉字占2个bytes。

varchar2

The VARCHAR2 data type specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. You can use the BYTE qualifier, for example VARCHAR2(10 BYTE), to explicitly give the maximum length in bytes. If no explicit qualifier is included in a column or attribute definition when a database object with this column or attribute is created, then the length semantics are determined by the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the object. Independently of the maximum length in characters, the length of VARCHAR2 data cannot exceed 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

可变长字符，最长 4000 bytes，最小值是1，使用时必须指定宽度。

案例记录

文章不存在，或文章是加密、私密文章

varchar

Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.

nchar

The NCHAR data type is a Unicode-only data type. When you create a table with an NCHAR column, you define the column length in characters. You define the national character set when you create your database.

The maximum length of a column is determined by the national character set definition. Width specifications of character data type NCHAR refer to the number of characters. The maximum column size allowed is 2000 bytes.

If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. You cannot insert a CHAR value into an NCHAR column, nor can you insert an NCHAR value into a CHAR column.

The following example compares the translated_description column of the pm.product_descriptions table with a national character set string:

SELECT translated_description
FROM product_descriptions
WHERE translated_name = N'LCD Monitor 11/PM';

下面是为什么可以：
SQL> alter table t1 add (job nchar(4));
SQL> insert into t1 values('cy','我是许望');
SQL> commit;

nvarchar2

The NVARCHAR2 data type is a Unicode-only data type. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.

The maximum length of the column is determined by the national character set definition. Width specifications of character data type NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes.

raw / long raw

老类型，逐步淘汰。

The RAW and LONG RAW data types store data that is not to be explicitly converted by Oracle Database when moving data between different systems. These data types are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns.

RAW is a variable-length data type like VARCHAR2, except that Oracle Net and the Oracle import and export utilities do not perform character conversion when transmitting RAW or LONG RAW data.

使用raw时，A maximum size must be specified: maximum size is 2,000。使用long raw时，最大2G。

long

老类型，逐步淘汰

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer.

Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases.

可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，一个表中最多一个 LONG 列。

Guidelines
•A LONG column is not copied when a table is created using a subquery.
•A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
•Only one LONG column can be used per table.
•No constraints can be defined on a LONG column.
•You might want to use a CLOB column rather than a LONG column.

数值型

number（p,s)

实数类型，以可变长度的内部格式来存储数字。p表示共有多少位数（不包括小数点），s表示保留的小数位数（4舍5入）。

小数点左边最多只能有p-s位有效数字。例如number(3,2)，总位数3位，小数2位，则整数部分只能有1位，可以插入3.2，但是不能插入31.2。

可以没有p和s，即只写number，表示是一个数值，可以插入整数或者小数。也可以只要p，即number(p)，等价于number(p,0)，可以插入整数或者小数，但整数位不能超过p，见下面的详细解释。

s正数，精确到小数点右边s位

SQL> create table number_type(data1 number(5,2));

SQL> insert into number_type values(123.45);    --- 123.45
SQL> insert into number_type values(123.456);    --- 123.46
SQL> insert into number_type values(123.453);    --- 123.45
SQL> insert into number_type values(123.40);    --- 123.4
SQL> insert into number_type values(1234.5);
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> select * from number_type;

     DATA1
----------
    123.45
    123.46
    123.45
     123.4

s是0或者未指定，四舍五入到最近整数

SQL> alter table number_type add (data2 number(3));
SQL> insert into number_type values(null,123);
SQL> insert into number_type values(null,12.3);
SQL> insert into number_type values(null,0.123);
SQL> select * from number_type where data1 is null;

     DATA1    DATA2
---------- ----------
          123
           12

当p小于s时候，表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数

SQL> alter table number_type add (data3 number(4,5));
SQL> insert into number_type values(null,null,1);
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into number_type values(null,null,0.1);
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into number_type values(null,null,0.01);         --- ok, .01
SQL> insert into number_type values(null,null,-0.01);        --- ok, -.01
SQL> insert into number_type values(null,null,0.001);        --- ok, .001
SQL> insert into number_type values(null,null,0.00001);        --- ok, .00001
SQL> insert into number_type values(null,null,0.000001);    --- ok, 0（因为超过了刻度）

s负数，精确到小数点左边s位，并四舍五入。整数位小于 （p+|s|）

SQL> alter table number_type add (data4 number(5,-2));    
SQL> insert into number_type values(null,null,null,12345);    --- 12300
SQL> insert into number_type values(null,null,null,123456);    --- 123500
SQL> insert into number_type values(null,null,null,1234567);    --- 1234600 

SQL> insert into number_type values(null,null,null,12345678);
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into number_type values(null,null,null,1234567.8);    --- 1234600

int

整数型，number的子类型，范围同上。

日期/时间型

Oracle的六种时间类型

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

DATE与TIMESTAMP

date 日期的普通形式，表示精度只能到秒级。
系统安装后，默认日期格式是 DD-MON-RR, RR和YY都是表示两位年份，RR将指定日期的年份和当前年份比较后确定年份是上个世纪还是本世纪。现在基本都是RR格式，YY基本已经不用了。

timestamp 日期的扩展形式，表示精度可达秒后小数点9位（10 亿分之1秒）。
create table dateformat(xstamp timestamp);
insert into dateformat values(systimestamp);

TIMESTAMP是包住DATE的，所以可以将DATE类型的列修改为TIMESTAMP：
SQL> alter table t1 modify d1 timestamp;

timestamp with timezone 带时区
timestamp with local timezone 时区转换成本地日期

CREATE TABLE web_orders 
(order_date TIMESTAMP WITH TIME ZONE, 
delivery_time TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO web_orders values (current_date, current_date);

SQL> select * from web_orders;

ORDER_DATE                                 DELIVERY_TIME
------------------------------------                     ------------------------------------
13-JUN-18 05.42.07.000000 PM +08:00          13-JUN-18 05.42.07.000000 PM

理解 Timezone

Oracle 中有很多特性支持国际化，如字符集、时区等等。

和时区相关的两个时间类型是：
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

1）Database 的 timezone 可以在创建数据库的时候指定，如：
CREATE DATABASE db01
...
SET TIME_ZONE='+08:00';

查看数据库时区信息：

SQL> select dbtimezone from dual;
DBTIME
------
+08:00

2）session 的 timezone 可以简单通过 alter session 语句修改：
ALTER SESSION SET TIME_ZONE='+08:00';

查看 session 时区信息：

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
+08:00

通过范例理解时区
范例：模拟北京、东京、伦敦三地的时区

全球的一个统一的时间应由时区+时刻来指定，比如 2005-4-6 14:00:00.000 并不能说清楚到底这是北京的下午 2 点还是东京的下午两点。两地时差差一个小时。
2005-4-6 14:00:00.000 +8:00 才是北京时间
2005-4-6 14:00:00.000 +9:00 则是东京时间

假设总部伦敦有online meeting system，已经由分部登记了两个网络会议，一个在北京，另一个在东京。

会话1：模拟DB 服务器，在英国（用vbox主控台模拟）
alter session set time_zone ='+0:00'

会话2：一个客户端 c-cn， 在中国（PL/SQL模拟)
alter session set time_zone ='+8:00';
//查看 select sessiontimezone from dual;

会话3：一个客户端 c-jp，在日本（CMD 模拟）
alter session set time_zone ='+9:00';

为了显示时区格式一致，分别在三个 session 下执行下面语句
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd hh24:mi:ss TZR';

假设数据库里有一个会议表（这个表只有三个不同日期类型的字段）：
create table meeting_table (
ctime1 timestamp,
ctime2 timestamp with time zone,
ctime3 timestamp with local time zone);

北京用户登记了第一个会议，要在当地早上 8 点开会
东京用户登记了第二个会议，也要当地早上 8 点开会
两地都插入以下记录：
insert into meeting_table values (
to_timestamp('2005-06-29 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
to_timestamp('2005-06-29 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
to_timestamp('2005-06-29 8:00:00', 'yyyy-mm-dd hh24:mi:ss')
);
commit;

查看北京和日本插入的两条记录在三地的显示结果：

伦敦：

SQL> select * from meeting_table;
CTIME1             CTIME2                       CTIME3
--------------------     ------------------------------             ------------------------- 
2005-06-29 08:00     2005-06-29 08:00:00 +08:00         2005-06-29 00:00
2005-06-29 08:00     2005-06-29 08:00:00 +09:00         2005-06-28 23:00

北京：

SQL> select * from meeting_table;
CTIME1             CTIME2                       CTIME3
--------------------     ------------------------------             ------------------------- 
2005-06-29 08:00     2005-06-29 08:00:00 +08:00         2005-06-29 08:00
2005-06-29 08:00     2005-06-29 08:00:00 +09:00         2005-06-28 07:00

东京：

SQL> select * from meeting_table;
CTIME1             CTIME2                       CTIME3
--------------------     ------------------------------             ------------------------- 
2005-06-29 08:00     2005-06-29 08:00:00 +08:00         2005-06-29 09:00
2005-06-29 08:00     2005-06-29 08:00:00 +09:00         2005-06-28 08:00

这个例子总结几点：
1）timestamp 无法区分哪个是北京的会，哪个是东京的会（都是 8 点）
2）timestamp with time zone 给出了时区，可以识别在哪里开会，但伦敦的管理员要在伦敦本地出席其中的网络会议，需要人工换算时间。
3）timestamp with local time zone，时间自动转换成了本地时间，显示的比较友好。

Session级参数TIME_ZONE用来设置会话级的时区，可以设置为: An absolute offset、Database time zone、OS local time zone、A named region;

ALTER SESSION SET TIME_ZONE = '-05:00';
ALTER SESSION SET TIME_ZONE= 'America/New_York';
ALTER SESSION SET TIME_ZONE = dbtimezone; -- 表示设置为database_properties中的dbtimezone的值
ALTER SESSION SET TIME_ZONE = local; --表示操作系统的时区

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> ALTER SESSION SET TIME_ZONE = '-5:00';

常用日期时间相关函数

SESSIONTIMEZONE（session time zone）

SQL> select SESSIONTIMEZONE from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

该函数返回当前会话所在时区。

DBTIMEZONE（database time zone）

DBTIME
------
+00:00
该函数将显示数据库设置的时区属性。

SYSDATE

SQL> select sysdate from dual;

SYSDATE
--------------------
08-JUN-2018 20:30:05

该函数返回的是操作系统的date命令显示的时间，也当前的时区无关。

SYSTIMESTAMP

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
08-JUN-18 08.34.08.906941 PM +08:00

该函数返回带时区的操作系统精确时间（fractional seconds，小数秒）。

CURRENT_DATE

SQL> select CURRENT_DATE from dual;

CURRENT_DATE
--------------------
08-JUN-2018 07:35:39

该函数返回当前会话的时间，与当前的时区有关，输出格式同SYSDATE函数。

LOCALTIMESTAMP

SQL>  select LOCALTIMESTAMP from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
08-JUN-18 08.03.13.946779 AM

该函数返回时间戳时间，与时区有关，输出格式类似SYSTIMESTAMP但是没有时区，即返回的数据类型为TIMESTAMP。

CURRENT_ TIMESTAMP

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
08-JUN-18 08.04.55.728849 AM -05:00

该函数返回时间戳时间，与时区有关，输出格式同SYSTIMESTAMP，有时区，即返回的数据类型为TIMESTAMP WITH TIME ZONE。

EXTRACT

提取年，月，日等，例如：

select last_name, extract(YEAR from HIRE_DATE) as "YEAR" 
from hr.employees where employee_id=100;

LAST_NAME            YEAR
-------------------------     ----------
King                    2003

可以提取的有：
YEAR | MONTH | DAY | HOUR| MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR

TZ_OFFSET

SQL> SELECT TZ_OFFSET('PRC') from dual;

TZ_OFFS
-------
+08:00

时区的名字可以从V$TIMEZONE_NAMES视图中获得。

除了使用时区名，还可以使用如下几种表达法：

SQL> SELECT TZ_OFFSET('+8:00') from dual;

TZ_OFFS
-------
+08:00

SQL> SELECT TZ_OFFSET(DBTIMEZONE) from dual;

TZ_OFFS
-------
+00:00

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE) from dual;

TZ_OFFS
-------
+08:00

FROM_TZ

Display the TIMESTAMP value '2000-07-12 08:00:00' as a TIMESTAMP WITH TIME ZONE value for the 'Australia/North' time zone region.

SQL> SELECT FROM_TZ(TIMESTAMP '2000-07-12 08:00:00', 'Australia/North')  
as "TIMESTAMP_WITH_TIME_ZONE" FROM DUAL;

TIMESTAMP_WITH_TIME_ZONE
---------------------------------------------------------------------------
12-JUL-00 08.00.00.000000000 AM AUSTRALIA/NORTH

TO_TIMESTAMP

SQL> select to_timestamp('2018-06-14 17:08:00','yyyy-mm-dd hh24:mi:ss') 
as "TIME_FOR_TIMESTAMP" FROM DUAL;

TIME_FOR_TIMESTAMP
---------------------------------------------------------------------------
14-JUN-18 05.08.00.000000000 PM

除了使用TO_TIMESTAMP函数，DATE 类型与 TIMESTAMP 类型，可以通过 CAST 函数互转。

SQL>select sysdate, CAST(sysdate AS TIMESTAMP) "date" from dual;
SYSDATE                 date
-------------------         ---------------------------------------------------------------------------
2013-03-10 07:16:28     2013-03-10 07:16:28.000000

TO_YMINTERVAL

把CHAR、VARCHAR2、NCHAR、NVARCHAR2字符串转换INTERVAL YEAR TO MONTH类型。

SQL> select to_yminterval('15-11') event_time from dual;
EVENT_TIME
---------------------------------------------------------------------------
+000000015-11

注意，月份的位置取值范围是0-11，如果写12会报错。

示例：Display a date that is one year and two months after the hire date

SQL> SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL
FROM employees
WHERE department_id = 20;
TO_DSINTERVAL
Display a date that is 100 days and 10 hours after the hire date for all the employees.

SELECT last_name, TO CHAR(hire date 'mm-dd-yy:hh:mi:ss') hire date TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss ) hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'),'mm-dd-yy:hh:mi:ss') hiredate2 
FROM employees;

numtoyminterval和numtodsinterval

numtoyminterval 用于产生一个指定的日期间隔，可以作为 interval year to month 型的数据插入到表中。

SQL>select sysdate,sysdate+numtoyminterval(1,'month'),sysdate+numtoyminterval(1,'year') from dual;
SYSDATE                 SYSDATE+NUMTOYMINTE         SYSDATE+NUMTOYMINTE
-------------------         -------------------                 -------------------
2012-07-09 08:18:59     2012-08-09 08:18:59             2013-07-09 08:18:59

可以看出 numtoyminterval 产生了一个月的时间间隔和一个年的时间间隔

numtoyminterval 用于产生一个指定的时间间隔，可以作为 day to seconds 型的数据插入到表中。

SQL> select sysdate,sysdate+numtodsinterval(1,'day'),sysdate+numtodsinterval(1,'second') from dual;
SYSDATE                 SYSDATE+NUMTODSINTE         SYSDATE+NUMTODSINTE
-------------------         -------------------                 -------------------
2012-07-09 09:09:06     2012-07-10 09:09:06             2012-07-09 09:09:07

可以看出 numtodsinterval 产生了一天的时间间隔和一秒的时间间隔

Daylight Saving Time (DST)

• Start of Daylight Saving:
– Time jumps from 01:59:59 AM to 03:00:00 AM.
– Values from 02:00:00 AM to 02:59:59 AM are not valid.
• End of Daylight Saving: End of Daylight Saving:
– Time jumps from 02:00:00 AM to 01:00:01 AM.
– Values from 01:00:01 AM to 02:00:00 AM are ambiguous because they are visited twice.

日期/时间间隔类型

从 Oracle 9i 开始，按照 SQL99 标准，增加了时间间隔型数据 INTERVAL YEAR TO MONTH 和INTERVAL DAY TO SECOND。

用 YEAR TO MONTH 表示时间间隔大小时要在年和月之间用一个连字符(-) 连接。
而 DAY TO SECOND 表示时间间隔大小时要在日和时间之间用一个空格连接。
举个例子来说，下面是 2 年 6 个月的时间间隔的表示方法：
INTERVAL "2-6" YEAR TO MONTH
下面的例子表示 3 天 12 个小时 30 分钟 6.7 秒：
INTERVAL "3 12:30:06.7" DAY TO SECOND(1)

时间间隔可以为正，也可以为负。它们可以从各种 TIMESTAMP 数据类型中加上或者减去，从而得到一个新的 TIMESTAMP 数据类型。

因为有精度问题，相对来讲，INTERVAL DAY TO SECOND 比 INTERVAL YEAR TO MONTH 要复杂一些。

INTERVAL YEAR TO MONTH：Stored as an interval of years and months
日期间隔类型。该数据类型常用来表示一段时间差, 注意时间差只精确到年和月。

数据类型：INTERVAL YEAR(3) TO MONTH
插入值：INTERVAL '123-2' YEAR(3) TO MONTH 或者 '+123-02'
含义: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3，为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.

使用举例：
create table dateformat(col1 interval year(3) to month);
insert into dateformat values(interval '123-02' year(3) to month);
insert into dateformat values('+123-02');

再看几个表示值的例子：

INTERVAL '123' YEAR(3) 表示: 123年0个月
INTERVAL '300' MONTH(3) 表示: 300个月.
INTERVAL '4' YEAR 表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH。默认精度是2，4只有一位，所以没有问题。
INTERVAL '50' MONTH 表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH
INTERVAL '123' YEAR 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3)
INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH = INTERVAL '6-11' YEAR TO MONTH 表示: 5年3个月 + 20个月 = 6年11个月

INTERVAL DAY TO SECOND：Stored as an interval of days, hours, minutes, and seconds
时间间隔类型。使用方法与日期间隔类型相似。
INTERVAL ‘3’ DAY : 3天
INTERVAL ‘2’ HOUR : 2小时
INTERVAL ‘25’ MINUTE : 25分钟
INTERVAL ‘45’ SECOND : 时间间隔为45秒
INTERVAL ‘3 2’ DAY TO HOUR : 3天零2小时
INTERVAL ‘3 2:25’ DAY TO MINUTE : 3天零2小时25分
INTERVAL ‘3 2:25:45’ DAY TO SECOND : 3天零2小时25分45秒
INTERVAL ‘123 2:25:45.12’ DAY(3) TO SECOND(2) : 123天零2小时25分45.12秒。
INTERVAL ‘-3 2:25:45’DAY TO SECOND 时间间隔为负数，值为3天零2小时25分45秒
INTERVAL ‘1234 2:25:45’DAY(3) TO SECOND //时间间隔无效，因为天的位数超过了指定的精度 3
INTERVAL ‘123 2:25:45.123’ DAY TO SECOND(2) //时间间隔无效，因为秒的小数部分的位数超过了指定的精度 2

LOB型

大对象是10g引入的，在11g中又重新定义，在一个表的字段里存储大容量数据，所有大对象最大都可能达到4G。

CLOB: 用来存储单字节的字符数据，包含在数据库内。
NCLOB: 用来存储多字节的字符数据。（小说、文章）
BLOB: 用于存储二进制数据，包含在数据库内。（图片或电影）
BFILE:存储在数据库之外的二进制文件中，这个文件中的数据只能被只读访问。

CLOB，NCLOB，BLOB都是内部的LOB类型，没有LONG只能有一列的限制(考点）。
虽然LONG RAW也可以使用，但LONG是Oracle将要废弃的类型，因此建议用LOB。
当然，说将要废弃，但还没有完全废弃，比如Oracle 11g里的视图dba_views的text列仍用了LONG类型。

大对象的增删改查操作不能像普通数据类型的增删改查，需要使用dbms_lob包来实现。当然，少量的数据也可以按照常规做法来做：

SQL> create table lob_t (id int, contents clob);
SQL> insert into lob_t values(1,'xuwang');
SQL> commit;
SQL> select * from lob_t where contents='xuwang';
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> select * from lob_t where contents like '%xuwang%';

    ID        CONTENTS
----------------------------------------------
     1        xuwang

再如：

set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999
select sql_text from dba_hist_sqltext where sql_id='b6rpf3hc89b7c';
select substr(sql_text,1,dbms_lob.getlength(sql_text)) from dba_hist_sqltext where sql_id='b6rpf3hc89b7c';

Oracle 11g 重新设计了大对象，推出SecureFile Lobs的概念（10g是BasicFile Lobs），相关的参数是db_securefile，采用SecureFile Lobs的前提条件是11g以上版本，ASSM管理等，符合这些条件的BasicFile Lobs也可以转换成SecureFile Lobs。较之过去的BasicFile Lobs, SecureFile Lobs有几项改进：1）压缩，2）去重，3）加密。

当create table定义LOB列时，也可以使用LOB_storage_clause指定SecureFile Lobs或BasicFile Lobs，而LOB的数据操作则使用Oracle提供的DBMS_LOB包，通过编写PL/SQL 块完成LOB数据的管理。

-- By 许望（RHCA、OCM、VCP）
最后修改：2025 年 01 月 17 日 06 : 35 PM
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏