Oracle数据类型及函数

一、数字类型 ：NUMBER 

          定义形式：NUMBER(P,S)，P 代表数字的总位数（整数位 + 精度位），S 代表精度（可以不写。不写表示整数）

          NUMBER(P)表示整数， P 表示数字的总位数，取值为 1-38

          NUMBER(P,S) 表示浮点数， P包含了 S 。

          例如： NUMBER(6，2), 那么整数位最大位是 4 位，小数位最大位是 2位

       数值函数

• ROUND(n, m) --- 用于四舍五入

               （0） n可以是任何数

               （1） m必须是整数

               （2） m取正数则四舍五入到小数点后第 m 位

               （3） m取 0 值则四舍五入到整数位

               （4） m取负数，则四舍五入到小数点前 m 位

               （5） m缺省，默认值是 0

          例如：SELECT ROUND('25.6612',2) FROM DUAL

• TRUNC(n, m) --- 用于截取小数点后的 m 位，但不进行四舍五入

          例如：SELECT TRUNC('25.6612',1) FROM DUAL

• MOD(m, n) --- 取模函数，注意：在数据库中 n 可以为零，返回原值

          例如：SELECT MOD(25,0) FROM DUAL

• CEIL(N) --- 取大于或等于 n 的最小整数值

          例如：SELECT CEIL(4.2) FROM DUAL

• FLOOR(N) --- 取小于或等于 n 的最大整数值

          例如：SELECT FLOOR(4.5) FROM DUAL

---

二、字符串类型：

• 默认单位是字节，可指定为字符：

     a)    CHAR(10),等价于CHAR(10 byte)

               b)    指定单位为字符 --- CHAR(10 CHAR), 20个字节

               c)    VARCHAR2(10),等价于VARCHAR2(10 BYTE)

• CHAR ：表示固定长度的字符类型

          定义形式：CHAR(N)，N表示占用的字节数，最大长度 2000 字节。

          注意：-无论实际数据多长，都会将数据补充若干空格，达到该字段实际指定的长度。

                    - CHAR 可以不指定长度，默认长度为 1 。

          优点：查询效率好

          缺点：浪费磁盘空间

• VARCHAR2 ： Oracle 特有类型，表示变长的字符类型
   定义形式：VARCHAR2(N)，N 表示占用的字节数，最大长度 4000 字节，数据多长就保存多长，用多少占多少。
   优点：磁盘空间利用好
   缺点：查询效率低。
• LONG ： VARCHAR2加长版，存储变长字符串，最多达到 2GB 的字符串数据，

          限制：每个表只能有一个 LONG 类型列；不能作为主键；不能建立索引；不能出现在查询条件中。          

• CLOB ：存储定长或变长字符串，最多达到 4GB 的字符串数据，建议使用 CLOB 代替LONG 。

        字符串函数

• CONCATE(char1,char), 返回两个字符串连接后的结果，一般使用“ || ”来代替

          例如：SELECT CONCAT(ename,':') || job FROM emp_rs

• LENGTH(char), 用于返回这个字符串的长度（字符量）

          例如： SELECT ename, LENGTH(ename) FROM emp_rs

• UPPER(char) ，用于将字符转换为大写形式 
• LOWER(char) ，用于将字符转换为小写形式

• INITCAP(char) ，用于首字母大写，其它字母小写（其中可以使用空格分隔多个单词，那么每个单词首字母都会大写）

          例如：SELECT UPPER('helloworld'),LOWER('HELLOWORLD'),INITCAP('HELLO WORLD') FROM DUAL

• TRIM(c2 from c1) --- 去除给定字符串中两边重复的给定字符

          例如：SELECT TRIM('e' FROM 'eeeeeliteeeee') FROM DUAL

          LTRIM(c1[,c2]) --- 可以单独去除左面的字符

          例如：SELECT LTRIM('eeeeeliteeeee','e') FROM DUAL

          RTRIM(c1[,c2]) ---可以单独去除右面的字符

          例如：SELECT RTRIM('eeeeeliteeeee','e') FROM DUAL

• LPAD(char1, n, char2) --- 补位函数，在左边补充

          例如：SELECT LPAD(sal,10,' ') FROM emp_rs

          RPAD(char1, n, char2) --- 补位函数，在右边补充

          例如：SELECT RPAD(sal,10,' ') FROM emp_rs 

• SUBSTR(char, offset, n) --- 用于获取字符串的子串，返回 char 中从m 位开始取 n 个字符。Offset 为负数时，就是从字符串最后一位开始， n 不能为负数。若截取的字符数量超过实际可以截取的字符数量时，则截取到字符串末尾。数据库中下标都是从 1 开始的

          例如：SELECT SUBSTR('Thinking in java', 10, 2) FROM DUAL

• INSTR(char1, char2[,n,m]) --- 返回子串 char2 在源字符串char1 中的位置，

              n : 从char1 的第几个字符开始查找（不写则为 1 ）

              m : 查找第几次出现（不写则为 1 ）

          例如：SELECT INSTR('Thinking in java', 'in', 4, 2) FROM DUAL

---

三、日期类型：

• 日期类型的数据是可以进行运算的，两个日期类型的值相减，差为相差的天数；给一个日期的值加上一个数字，相当于累加了多少天

          例如：SELECT SYSDATE - TO_DATE('1993-07-05','YYYY-MM-DD') FROM dual

• DATE --- 最多精确到秒

                         表示定义日期时间的数据，长度 7 个字节，7 个字节分别存储 世纪、年、月、日

                         默认格式： DD-MON-RR( 日月年) ，但是秒以下的精度保存不了

               -RR：可以自动判断世纪

               

                Sys：表示系统目前的年份， user 表示用户输入的年份。

                例如： RR 是03 ，那么在 sys 中是0-49 这个区间，在 user 中是在0-49 这个区间，它们的交集就是此 RR 表示的世纪——本世纪。

• TIMESAMP --- 最多精确到纳秒

        日期关键字：

• SYSDATE --- 本质是一个 Oracle 的内部函数，返回当前的系统时间，精度为秒，默认显示格式是 DD-MON-RR.
• SYSTIMESTAMP --- 返回当前系统日期和时间，精确到毫秒

        日期转换函数

• TO_DATE --- 按照字符串规定的格式转换为日期类型

         常用的日期格式：

YY	2 位数字的年份
YYYY	4 位数字的年份
MM	2 位数字的月份
MON	简拼的月份
MONTH	全拼的月份
DD	2 位数的天
DY	周几的缩写
DAY	周几的全拼
HH24	24 小时制的小时
HH12	12 小时制的小时
MI	显示分钟
SS	显示秒

     例如：SELECT TO_DATE('2015年12月17日','YYYY"年"MM"月"DD"日"') FROM dual

  

• TO_CHAR --- 将其它类型的数据转换为字符类型

     例如： SELECT TO_CHAR(TO_DATE('88-02-22','RR-MM-DD'),'YYYY-MM-DD') FROM dual

                SELECT TO_CHAR(SYSDATE,'YYYY"年"-MM"月"-DD"日"') FROM dual

       日期常用的函数：

• LAST_DAY(date) --- 返回日期date 所在月的最后一天

          例如：SELECT LAST_DAY(SYSDATE) FROM dual

• ADD_MONTHS(date, i) --- 返回日期 date 加上i 个月后的日期值

          - 参数 i可以是任何数，大部分时候去正值整数

          - 如果 i是小数，将会被截取整数后再参与运算

          - 如果 i是负数，则获得的是减去 i 个月后的日期值

          例如：

• MONTHS_BETWEEN (date1, date2) --- 计算 date1和 date2 两个日期值之间间隔了多少个月，实际运算是 date1-date2, 越晚，date 就越大。除非两个月间隔的是整数月，否则会出现小数

          例如： SELECT ename, ADD_MONTHS(hiredate, 20*12) as "纪念日" FROM emp_rs

• NEXT_DAY(date, char) --- 返回date日期数据的下一个周几，周几是由参数char来决定。返回的是离现在的日期还没到的最近的周几。

          注意：数字1-7表示周日-周六

          例如：SELECT NEXT_DAY(SYSDATE,1) FROM dual

• LEAST(expr1[,expr2,expr3…]) --- 返回最小的值

          例如：SELECT LEAST(SYSDATE, TO_DATE('2008-03-02','YYYY-MM-DD')) FROM dual

• GREATEST(expr1[,expr2,expr3…]) --- 返回最大的值

          注意：参数类型必须一致，不过若第 2 个以及后面的参数能够转换成前一个类型，那么可以比较，否则会报错。

          例如：SELECT GREATEST(SYSDATE, TO_DATE('2008-03-02','YYYY-MM-DD')) FROM dual

• EXTRACT(date FROM datetime) --- 从参数 datetime中提取参数 date 指定的数据。

          例如：SELECT ename, hiredate FROM emp_rs WHERE EXTRACT(YEAR FROM hiredate) = 1981

---

四、空值类型：NULL

        NULL 条件查询：

• 当判断一个字段的值是否为 NULL 时，不能使用“ =”判断，而是使用 IS NULL 或者IS NOT NULL 。

     NULL 的运算：

• NULL 与字符串连接，等于什么都没干
• NULL 与数字运算，结果还是 NULL

     空值函数：

• NVL(expr1,exp2) --- 将 NULL转换为非 NULL 值。若expr1 为 null，则函数返回 exp2, 否则就返回expr1 的值。

          例如： SELECT ename, sal, comm,sal+NVL(comm,0) FROM emp_rs

• NVL2(exp1,exp2,exp3) --- 用来判断 exp1 是否为NULL ，如果不是，返回 exp2 ，如果是NULL ，返回 exp3。

          例如：SELECT ename, NVL2(comm,'有奖金','没奖金') as "奖金情况" FROM emp_rs

---

五、零碎知识

DUAL( 伪表) ，为了满足 SELECT 语法要求使用，当我们查询的数据不来自任何一张表时，可以使用伪表代替 FROM 字句中的内容，伪表仅会查询出一条记录。