oracle 内置数据类型

oracle内置数据类型

编码数据类型描述

1

VARCHAR2(size [BYTE |CHAR])

变长字符串，. 最大长度为4000byte或者4000个字符（char）, 最小单位为1. 必须指定size长度

使用BYTE指定这一列最多可以包含size个字节的字符串.而 CHAR 则指定本列可以包含size个字符。

1

NVARCHAR2(size)

同varchar2相同，只是nvarchar2采用国家字符集存储，而varchar2采用的是数据库默认字符集

存储。最大尺寸由国家字符集界定。

2

NUMBER [ (p [,s]) ]

number含两个参数 精度：p 小数位：s。p s都是可选的。

精度：默认情况下精度为38位

小数位：小数点右边的位数。默认情况取决于是否指定了精度。1、若没有指定精度，则精度默认为38，小数位数默认有最大取值区间0~37，如果指定了精度，而没有指定s，则默认小数位为0.

2

FLOAT [(p)]

A subtype of the NUMBER data type having precision p. AFLOAT value is represented internally asNUMBER. The precisionp can range from 1 to 126 binary digits. AFLOAT value requires from 1 to 22 bytes.--此数据类型会映射到number类型

8

LONG

文本类型，能存储2GB的文本。long类型在表的使用中会有很多限制，这点需要注意

12

DATE

7字节的定宽日期/时间类型，包含7个属性：世纪，世纪中哪一年，月份，月份中哪一天，小时、分、秒。2013-07-27 23:06:17。不包含小数秒和时区

21

BINARY_FLOAT

32位浮点数，需要4byte

22

BINARY_DOUBLE

64位浮点数，需要8byte

180

TIMESTAMP [(fractional_seconds_precision)]

通常又叫时间戳，与date类型相比，最大的区别是包含小数秒，但是不包含时区。小数秒最大长度为9位。默认为6位。默认格式由NLS_TIMESTAMP_FORMAT或NLS_TERRITORY参数指定。7或者11byte。

181

TIMESTAMP [(fractional_seconds)]WITHTIMEZONE

与timestamp相类似，只是多了时区信息。

231

TIMESTAMP [(fractional_seconds)]WITHLOCALTIME ZONE

所有的特性和TIMESTAMP WITHTIMEZONE 相同，只是在两点：

• 数据在存储到数据库时，会统一转换成数据库本地时区.

• 当用户从数据库获取数据时，看到的是用户所在的时区

同样格式由NLS_TIMESTAMP_FORMAT或NLS_TERRITORY参数指定。7或者11byte。

182

INTERVAL YEAR [(year_precision)]TOMONTH

存储一个包含年和月的时间段，,year_precision取值范围0-9 默认值为2.

183

INTERVAL DAY [(day_precision)]TOSECOND [(fractional_seconds)]

存储一个按天、小时、分、秒指定的时间段。, where

• 取值范围0-9，默认值为2.

• 取值范围0-9，默认值为6.

23

RAW(size)

二进制数据类型，最大长度为2000 byte ，必须为raw指定精度size

24

LONG RAW

长二进制类型，可以存储2GB的二进制数据

69

ROWID

Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by theROWID pseudocolumn.

208

UROWID [(size)]

Base 64 string representing the logical address of a row of an index-organized table. The optionalsize is the size of a column of typeUROWID. The maximum size and default is 4000 bytes.

96

CHAR [(size [BYTE |CHAR])]

定长字符串。最大长度为2000 byte 或者2000个字符（char）.默认最小单位 为1byte

在存储的字符串长度小于size的时候，会在字符串后用添加空格的方式来达到size规定的长度

96

NCHAR[(size)]

与char属性相同，区别在于NCHAR使用国家字符集存储数据。最大尺寸由国家字符集界定。

112

CLOB

大对象或多字节类型。支持定长和变长两种特性,。使用数据库字符集存储， 最大尺寸= (4 G- 1) * (database block size).

112

NCLOB

和clob属性类似，只是NCLOB采用国家字符集存储数据

113

BLOB

二进制大对象类型. Maximum size is (4 gigabytes - 1) * (database block size).

114

BFILE

二进制lob，数据库中存储的仅仅是某个文件的指针，而该文件并没有存在于数据库内，文件在数据库外维护。Bfile文件内容read only。

使用LONG类型时的一些限制

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.

• You cannot create an object type with a LONG attribute.

• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear inNULL andNOT NULL constraints).

• LONG columns cannot be indexed.

• LONG data cannot be specified in regular expressions.

• A stored function cannot return a LONG value.

• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.

• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both theLONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either theLONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, orCONNECTBY clauses or with the DISTINCT operator inSELECT statements

• The UNIQUE operator of a SELECT statement

• The column list of a CREATE CLUSTER statement

• The CLUSTER clause of a CREATE MATERIALIZEDVIEW statement

• SQL built-in functions, expressions, or conditions

• SELECT lists of queries containing GROUP BY clauses

• SELECT lists of subqueries or queries combined by the UNION,INTERSECT, orMINUS set operators

• SELECT lists of CREATE TABLE ... AS SELECT statements

• ALTER TABLE ... MOVE statements

• SELECT lists in subqueries in INSERT statements

Triggers can use the LONG data type in the following manner:

• A SQL statement within a trigger can insert data into a LONG column.

• If data from a LONG column can be converted to a constrained data type (such asCHAR andVARCHAR2), then a LONG column can be referenced in a SQL statement within a trigger.

• Variables in triggers cannot be declared using the LONG data type.

• :NEW and :OLD cannot be used with LONG columns.

date、timestamp和interval类型

向date增加或减去时间

常用3种方法：

• 向date增加一个number(或者‘number’，比如‘10’)，+1就代表增加一天，-1就代表减去天。
• 用interval类型来增加时间段的方式。支持两种粒度：年和月、日 /小时/分/秒 的两种时间间隔。
• 用add_months函数增加月

 N秒 date +n/24/60/60date +n/86400date +numtodsinterval(n,'second') N分钟date +n/24/60date +n/1400date +numtodsinterval(n,'minute') N小时date +n/24date +numtodsinterval(n,'hour') N天date +nN周date +7*nN月add_months(date,n)date +numtoyminterval(n,'month')N年add_months(date,12*n)date +numtoyminterval(n,'year')

建议：

1、用numtodsinterval内置函数来增加小时、分、秒

2、date+n来增加天

3、使用add_months来增加年和月

在使用numtoyminterval来增加年和月的时候，在涉及到处理月末日期的时候，会出现问题。而add_months不会。

两个日期相减，得到一个number类型的数字，表示两个日期建间隔的天数。也可以使用month_between。

两个oracle 内置函数numtoyminterval和numtodsinterval

通过这两个内置函数，可以构建一个interval类型的年和月、日/小时/分/秒的时间间隔

numtoyminterval :

numtodsinterval:

timestamp类型

向timestamp类型加上/减去一个时间

用法与date用法相同，只不过注意，在做加减运算之后，小数秒和时区没有了。

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
28-JUL-13 03.25.30.625547 AM -04:00

--减去三天

SQL> select systimestamp -3 from dual;

SYSTIMESTAMP-3
-------------------
2013-07-25 03:25:36

使用interval可以避免这样的情况

SQL> select systimestamp - numtodsinterval(3,'DAY') from dual;

SYSTIMESTAMP-NUMTODSINTERVAL(3,'DAY')
---------------------------------------------------------------------------
25-JUL-13 03.28.56.782975000 AM -04:00

--时区和小数秒都在

interval类型

interval year（n） to month

表示一个时间间隔 例如：3年零6各月的这样一个时间间隔

构建方式：

1、SQL> select numtoyminterval(3,'year')+numtoyminterval(6,'month') from dual;
2、SQL> select numtoyminterval(3*12+6,'month') from dual;   --利用一年12各月这个事实

3、SQL> select to_yminterval('3-6') from dual;    ---利用 to_yminterval（） 函数

4、SQL> select interval '5-2' year to month from dual;        --直接利用interval   year to month

interval day to second

表示一个天/小时/分/秒的一个时间间隔：构建一个 2天4小时30分20秒这样一个时间间隔

构建方式

1、select numtodsinterval(2,'day') + numtodsinterval(4,'hour') + numtodsinterval(30,'minute')+ numtodsinterval(20,'second') from dual同上

2、select numtodsinterval(2*86400+4*3600+30*60+20,'second') from dual;
3、select to_dsinterval('2 4:30:20') from dual;

4、select interval '2 4:30:20' day to second from dual;

摘自官网interval的一些用法示例

Examples of the various forms of INTERVAL DAY TO SECOND literals follow, including some abbreviated versions:

Form of Interval LiteralInterpretationINTERVAL '4 5:12:10.222' DAY TO SECOND(3)4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.INTERVAL '4 5:12' DAY TO MINUTE4 days, 5 hours and 12 minutes.INTERVAL '400 5' DAY(3) TO HOUR400 days 5 hours.INTERVAL '400' DAY(3)400 days.INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)11 hours, 12 minutes, and 10.2222222 seconds.INTERVAL '11:20' HOUR TO MINUTE11 hours and 20 minutes.INTERVAL '10' HOUR10 hours.INTERVAL '10:22' MINUTE TO SECOND10 minutes 22 seconds.INTERVAL '10' MINUTE10 minutes.INTERVAL '4' DAY4 days.INTERVAL '25' HOUR25 hours.INTERVAL '40' MINUTE40 minutes.INTERVAL '120' HOUR(3)120 hours.INTERVAL '30.12345' SECOND(2,4)30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4.