oracle内置数据类型
编码 | 数据类型 | 描述 |
---|---|---|
1 |
| 变长字符串,. 最大长度为4000byte或者4000个字符(char), 最小单位为1. 必须指定size长度
|
1 |
| 同varchar2相同,只是nvarchar2采用国家字符集存储,而varchar2采用的是数据库默认字符集 存储。最大尺寸由国家字符集界定。 |
2 |
| number含两个参数 精度:p 小数位:s。p s都是可选的。 精度:默认情况下精度为38位 小数位:小数点右边的位数。默认情况取决于是否指定了精度。1、若没有指定精度,则精度默认为38,小数位数默认有最大取值区间0~37,如果指定了精度,而没有指定s,则默认小数位为0. |
2 |
| A subtype of the |
8 |
| 文本类型,能存储2GB的文本。long类型在表的使用中会有很多限制,这点需要注意 |
12 |
| 7字节的定宽日期/时间类型,包含7个属性:世纪,世纪中哪一年,月份,月份中哪一天,小时、分、秒。2013-07-27 23:06:17。不包含小数秒和时区 |
21 |
| 32位浮点数,需要4byte |
22 |
| 64位浮点数,需要8byte |
180 |
| 通常又叫时间戳,与date类型相比,最大的区别是包含小数秒,但是不包含时区。小数秒最大长度为9位。默认为6位。默认格式由NLS_TIMESTAMP_FORMAT 或NLS_TERRITORY 参数指定。7或者11byte。 |
181 |
| 与timestamp相类似,只是多了时区信息。 |
231 |
| 所有的特性和
同样格式由 |
182 |
| 存储一个包含年和月的时间段,, |
183 |
| 存储一个按天、小时、分、秒指定的时间段。, where
|
23 |
| 二进制数据类型,最大长度为2000 byte ,必须为raw指定精度size |
24 |
| 长二进制类型,可以存储2GB的二进制数据 |
69 |
| Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the |
208 |
| Base 64 string representing the logical address of a row of an index-organized table. The optional |
96 |
| 定长字符串。最大长度为2000 byte 或者2000个字符(char).默认最小单位 为1byte 在存储的字符串长度小于size的时候,会在字符串后用添加空格的方式来达到size规定的长度 |
96 |
| 与char属性相同,区别在于NCHAR使用国家字符集存储数据。最大尺寸由国家字符集界定。 |
112 |
| 大对象或多字节类型。支持定长和变长两种特性,。使用数据库字符集存储, 最大尺寸= (4 G- 1) * (database block size). |
112 |
| 和clob属性类似,只是NCLOB采用国家字符集存储数据 |
113 |
| 二进制大对象类型. Maximum size is (4 gigabytes - 1) * (database block size). |
114 |
| 二进制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 inWHERE
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
andLONG
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, orCONNECT
BY
clauses or with theDISTINCT
operator inSELECT
statements -
The
UNIQUE
operator of aSELECT
statement -
The column list of a
CREATE
CLUSTER
statement -
The
CLUSTER
clause of aCREATE
MATERIALIZED
VIEW
statement -
SQL built-in functions, expressions, or conditions
-
SELECT
lists of queries containingGROUP
BY
clauses -
SELECT
lists of subqueries or queries combined by theUNION
,INTERSECT
, orMINUS
set operators -
SELECT
lists ofCREATE
TABLE
...AS
SELECT
statements -
ALTER
TABLE
...MOVE
statements -
SELECT
lists in subqueries inINSERT
statements
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 aLONG
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 withLONG
columns.
date、timestamp和interval类型
向date增加或减去时间
常用3种方法:
- 向date增加一个number(或者‘number’,比如‘10’),+1就代表增加一天,-1就代表减去天。
- 用interval类型来增加时间段的方式。支持两种粒度:年和月、日 /小时/分/秒 的两种时间间隔。
- 用add_months函数增加月
N秒 | date +n/24/60/60 |
date +n/86400 | |
date +numtodsinterval(n,'second') | |
N分钟 | date +n/24/60 |
date +n/1400 | |
date +numtodsinterval(n,'minute') | |
N小时 | date +n/24 |
date +numtodsinterval(n,'hour') | |
N天 | date +n |
N周 | date +7*n |
N月 | 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 Literal | Interpretation |
---|---|
INTERVAL '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 MINUTE | 4 days, 5 hours and 12 minutes. |
INTERVAL '400 5' DAY(3) TO HOUR | 400 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 MINUTE | 11 hours and 20 minutes. |
INTERVAL '10' HOUR | 10 hours. |
INTERVAL '10:22' MINUTE TO SECOND | 10 minutes 22 seconds. |
INTERVAL '10' MINUTE | 10 minutes. |
INTERVAL '4' DAY | 4 days. |
INTERVAL '25' HOUR | 25 hours. |
INTERVAL '40' MINUTE | 40 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. |