OCP认证之Oracle的数据类型

1.四种基本的常用数据类型

• 字符型
• 数值型
• 日期型
• 大对象型

1.1字符型

char 固定长度，最长2000个
varchar2 可变长字符，最长4000个，最小值是1
nchar/nvarchar2 nchar/nvarchar2类型的列使用国家字符集
raw/long raw 固定/可变长度的二进制数据长度，最大2G,可存放多媒体图像声音等（老类型，逐步淘汰）
long 可变长的字符串类型，最长2G,long具有varchar2 列的特性，一个表最多一个long列（老类型，逐步淘汰）

1.2数值型

number(p,s) 实数型，以可变长度的内部格式来存储数字。这个内部格式精度可以高达38位。
int 整数型，number的字类型，范围同上

1.3日期型

date 日期的普通刑事，表示精度只能到秒级
timestamp 日期的扩展形式，表示精度可达秒后小数点9位（10亿分之1秒）
timestamp with timezone 带时区
timestamp with local timezone 时区转换成本地日期

系统安装后，默认日期格式是DD-MON-RR，RR和YY都是表示两位年份，但RR是有世纪认知的，它将制定日期的年份和当前年份比较后确定年份是上个世纪还是本世纪（如下：）

当前年份 指定日期    RR格式   YY格式
-----------------------------------
1995    27-OCT-95   1995    1995
1995    27-OCT-17   2017    1917
2001    27-OCT-17   2017    2017
2013    27-OCT-95   1995    2095

1.4LOB型：

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

• CLOB:用来存储单字节的字符数据，包含在数据库内
• NCLOB:用来存储多字节的字符数据
• BLOB:用于存储二进制数据，包含在数据库内
• BFILE:存储在数据库之外的二进制文件中，这个文件中的数据只能被只读访问

CLOB,NCLOB,BLOB都是内部的LOB类型，没有LONG只能有一列的限制

保存图片或电影使用BLOB最好、如果是小说则使用CLOB最好。
虽然LONG RAW也可以使用，但LONG是Oracle将要废弃的类型，因此建议用LOB。
说将要废弃，但还没有完全废弃，比如Oracle 11g里的重要视图dba_views，对于text（视图定义）仍然沿用了LONG类型。

Oracle 11g重新设计了大对象，推出SecureFile 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 数据的管理。

2.数据类型的转换

隐性类型转换和显性类型转换

2.1 隐性类型转换：

是指Oracle自动完成的类型转换。在一些带有明显意图的字面值上，可以由Oracle自主判段进行数据类型的转换。

--empno本来是数值型的，这里字符‘7788’隐性转成数值7788
SQL> select empno,ename from emp where empno='7788';
EMPNO ENAME
----- ----------
 7788 SCOTT

--将date型隐转成字符型后计算长度
SQL> select length(sysdate) from dual;
LENGTH(SYSDATE)
---------------
              9

--将字符型‘12.5’隐转成数字型再求和
SQL>SELECT '12.5'+11 FROM dual; 
 '12.5'+11
----------
      23.5

--将数字型11隐转成字符与‘12.5’合并，其结果再隐转成数字型与10求和
SQL> SELECT 10+('12.5'||11) FROM dual; 
10+('12.5'||11)
---------------
         22.511

2.2显性类型转换

显性类型转换是强制完成类型转换，转换函数形式有三种：
* to_char
* to_date
* to_number

2.2.1日期转字符

SQL>  select ename, hiredate, to_char(hiredate, 'DD-MON-YY') month_hired from emp
  2  where ename='SCOTT';
ENAME      HIREDATE    MONTH_HIRED
---------- ----------- --------------
SCOTT      1987/4/19   19-4月 -87

--fm 压缩空格或左边的‘0’ 
SQL> select ename, hiredate, to_char(hiredate, 'DD-MON-YY') month_hired from emp
where ename='SCOTT';
ENAME HIREDATE MONTH_HIRED
---------- ------------------- --------------
SCOTT 1987-04-19 00:00:00 19-4 月 -87

其实 DD-MM-YY 是比较糟糕的一种格式，因为当日期中天数小于 12 时，DD-MM-YY 和 MM-DD-
YY 容易造成混乱。

2.2.2数字转字符

9表示数字，L本地化货币字符

SQL> select ename,to_char(sal,'L99,999.99') salary from emp where ename = 'SCOTT';
ENAME      SALARY
---------- --------------------
SCOTT                ￥3,000.00

2.2.3字符转日期

SQL> select to_date('1983-11-12','YYYY-MM-DD') tmp_date from dual;
TMP_DATE
-----------
1983/11/12

2.2.4字符转数字

SQL> select to_number('$123.45','$9999.99') result from dual;
    RESULT
----------
    123.45

使用to_number时如果使用较短的格式掩码转换数字，就会返回错误。不要混淆to_number和to_char转换例如：

SQL> select to_number('$123.46','$9999.9') from dual;
select to_number('$123.46','$9999.9') from dual
ORA-01722: 无效数字

SQL> select to_char(123.46,'999.9') from dual;
TO_CHAR(123.46,'999.9')
-----------------------
 123.5