ORACLE 常用SQL函数

oracle中函数分为：字符类函数、数值类函数、日期类函数、空值处理函数、转换类函数、其他常用函数

这次主要整理几个ORACLE中常用到的SQL函数 如下：

lower（）函数 内容转换小写
SQL> SELECT LOWER(T_NAME) FROM test_partitioning2;
LOWER(T_NAME)
-------------
a
b
c
d
e
e
e
7 rows selected

upper（）函数 内容抓换大写
SQL> SELECT UPPER(T_NAME) FROM test_partitioning2;
UPPER(T_NAME)
-------------
A
B
C
D
E
E
E
7 rows selected

ltrim（）去除左右空格（前后各两空格）

SQL> select length(ltrim('  oracle  ')),length(ltrim('  oracle  ',' ')) from dual;
LENGTH(LTRIM('ORACLE')) LENGTH(LTRIM('ORACLE',''))
----------------------- --------------------------
                      8                          8
--ltrim中第二个参数没有指定参数，默认删除' '空格的字符，即与ltrim('  oracle  ',' ')效果相同；

ltrim删除字符串中指定字符
SQL> select ltrim('oracle','roc'),ltrim('lllearner','ale') from dual;
LTRIM('ORACLE','ROC') LTRIM('LLLEARNER','ALE')
--------------------- ------------------------
acle                  rner                   
--ltrim函数第二个参数不是以‘roc’作为整体删除的，而是以单个字符删除。碰到连续的字符将连续删除。      

rtrim（）去除左右空格（前后各两空格） 

SQL> select length(rtrim('  oracle  ')),length(rtrim('  oracle  ',' ')) from dual;
LENGTH(RTRIM('ORACLE')) LENGTH(RTRIM('ORACLE',''))
----------------------- --------------------------
                      8                          8
SQL> select rtrim('oracle','e'),rtrim('learner','rle') from dual;
RTRIM('ORACLE','E') RTRIM('LEARNER','RLE')
------------------- ----------------------
oracl               learn
--作用同ltrim函数一样，只不过这是从右边往左边检索删除

trim（）函数包括了ltrim（）、rtrim（）函数功能      
 语法：TRIM([LEADING | TRAILING| BOTH] [trim_char_from] 列明或表达式)
LEADING：表示从左边删除
TRAILING:右边删除
BOTH：两边同时删除（默认）
例子：
SQL> SELECT TRIM(LEADING '*' FROM '**oracle*') c1,TRIM(TRAILING '*' FROM '**oracle*') c2,TRIM(BOTH '*' FROM '**oracle*') c3,
  2  TRIM('*' FROM '**oracle*') c4,LENGTH(TRIM(' oracle')) c5 from dual
  3  ;
C1      C2       C3     C4             C5
------- -------- ------ ------ ----------
oracle* **oracle oracle oracle          6

length（）与lengthb（） 函数
两者都是返回长度，前者返回字符串长度，后者返回字节长度
SQL> select length('数据库'),lengthb('数据库') from dual;
   LENGTH('数据库')    LENGTHB('数据库')
---------------- -----------------
               3                 9

substr（）与substrb（） 函数

两个都是返回字符串的字串，后者按照字符返回


SQL> select substr('oracle',1,3) c1,substr('oracle',1,3) c2,substr('学习数据库',1,3) c3,substrb('学习数据库',1,3) from dual;
C1  C2  C3        SUBSTRB('学习数据库',1,3)
--- --- --------- -------------------------
ora ora 学习数    学

round（）函数

功能将列明所表示的数值进行四舍五入，语法格式
ROUND(列明，X) X表示保留几个小数，不写默认为0，小数点后面四舍五入
例子:
SQL> select round(123.456) from dual;
--------------
           123
SQL>  select round(123.456) from dual;
ROUND(123.456)
--------------
           123
--round()函数还可以对日期类型数据进行处理

TRUNC（）函数

功能：将列名所表示的数值进行截取，语法如下：
TRUNC(列名,x) x表示截取到小数点的第几位（不进行四舍五入），默认为0，如果x为负数，则表示从小数点左边第x位截取
SQL> select trunc(123456.78) from dual;   
TRUNC(123456.78)
----------------
          123456                                      
SQL> select trunc(123456.78,1) from dual;
TRUNC(123456.78,1)
------------------
          123456.7
                   
--trunc第二个参数为负数，表示从小数点左边截断，截断几位自动补0。 如下：          
SQL> select trunc(1234.567,-1) A,trunc(123456.789,-2) B,trunc(3456332.1,-4) C from dual;
         A          B          C
---------- ---------- ----------
      1230     123400    3450000  
--trunc函数也还可以对日期类型数据进行处理         
--trunc与round不同，round是小数点后面进行四舍五入，而trunc直接截断，这是两函数区别。

last_day（）函数

功能：获取当前日期所在月的最后一天
SQL> select sysdate,last_day(sysdate) last_day from dual;
SYSDATE     LAST_DAY
----------- -----------
2013/10/20  2013/10/31

NVL（）函数

功能：实现空值转换，根据一个表达式的值是否为空来判断返回相应列明或表达式。主要用于对空值进行处理，语法如下：
NVL(列名或表达式，列名或表达式)
如果第一个参数值返回空值，则返回第二个参数值，否则返回第一个参数值，第一第二参数值都可为任意类型值，但两个参数类型必须相同。

NVL2（）函数NVL（）函数扩展函数

功能也是对一个空值转换函数，如果第一个参数值不为空，则返回第二个参数值，否则返回第三个参数值。第一个参数可以为任意类型，

第二第三参数除LONG类型以外的任何类型。语法如下：
NVL2(列名或表达式，列名或表达式，列名或表达式）

TO_CHAR（）函数

功能：将非字符数据转换为字符型数据，并设置字符的输出格式，语法如下：
TO_CHAR(列名或表达式，[格式],[NLS参数])
第一个参数：要指定转换列名
第二个参数：转换列名指定转换格式
第三个参数：显示格式国家语言支持参数，如不设置则使用默认NLS参数指定
SQL> SELECT TO_CHAR(1234567,'9,999,999') C1,TO_CHAR(123.456,'9999,9999') C2,TO_CHAR(12.466666,'9.99') C5 from dual;
C1         C2         C5
---------- ---------- -----
 1,234,567        123 #####
 
 #####：表示当整数部分的长度大于格式字符串中指定的长度时，则返回一个由#组成字符串
 
SQL> select to_char(systimestamp,'YYYY-MM-DD hh24:MI:SS') d1, to_char(systimestamp,'WW') d2,to_char(sysdate,'W') d3 from dual;
D1                  D2 D3
------------------- -- --
2013-10-20 20:56:58 42 3

TO_DATE（函数

功能：将字符型数据转换成日期型数据，并设置日期的输出格式，语法如下：
TO_CHAR(列名或表达式，[格式]),[NLS参数]
第一个参数：转换列名
第二个参数：指定转换后显示格式
第三个参数：显示格式国家语言支持参数，如不设置则使用默认NLS参数指定
SQL> select to_char(to_date('2013-10-20','yyyy-mm-dd'),'DAY') from dual;
TO_CHAR(TO_DATE('2013-10-20','
------------------------------
星期日

DECODE（） 函数
功能：类似与高级语言中IF ELSE的分支语句
SELECT T_ID,T_NAME,T_RESEARCH,DECODE(T_TITLEID,1，'教授',2,'副教授',3,'其他') from dual;