Oracle Database ：玩转Oracle学习笔记之（27）：Oracle函数

1、字符函数：


我们在这Oracle函数测试的时候，如果没有测试的表，我们可以用dual表；

SQL> select mod(10 , 2) from dual;

lower（char）：将字符串转化为小写格式；
upper（char） ：将字符串转化为大写格式；
length（char）：返回字符串长度：
substr（chat ，m ， n）：去字符串子串；（从地m个开始取，总共取n个字符）；

SQL> select lower(ename) , sal from emp;
SQL> select ename from emp where length(ename)=5;
SQL> select upper(substr(ename , 1,1))||lower(substr(ename,2,length(ename))) from emp;

字符串替换；
replace(char1 , search_string , replace_string);
instr(chat1 , char2 , [,n[,m]]):

SQL> select replace(ename ,'A' , 'I am A a') from emp;

2、数学函数：
数学函数的输入参数和返回值的数据类型都是数字类型的，数学函数包括abs(n) ,cos(n),cosh(n),exp(n) , ln(n),log(n),sin(n),sin(n),sqrt(n),tan(n),tanh(n),acos(n),asin(n),atan(n),round,我们常常用的油如下函数：
round(n,[m]);该函数用于执行四舍五入，如果省掉m，就四舍五入到整数，如果m是正数，则四舍五入到小数点后m位，如果是负数，就保留到小数点前m位；
trunc(n,[m]);该函数用于截取数字，如果省略掉m，就截去小数部分，如果m是正数，就截取到小数点后m位，如果m是负数，就截取到小数点前m位；
mod(m,n);
floor(m);返回小于或是等于m的最大整数；
ceil(m);返回大于或是等于m的最小正数；
exp(n):返回e的n此幂；
log（m，n）：返回对数；
power（m，n）：返回m的n次幂；

3、日期函数：
处理date数据类型的函数：
sysdate：返回系统时间；
add_months（d，n）：给指定的日期d加上指定的月数n后的时间；
last_day（d）：返回指定日期所在月份的最后一天；

SQL> select * from emp where sysdate>add_months(hiredate , 8);
SQL> select sysdate from dual;
select hiredate,ename from emp where hiredate=last_day(hiredate) -2;

4、转换函数：用于将数据类型从一种转换为另一种的函数，在某些情况下，oracle server 允许值的数据类型和实际的不一样。这时候oracle  server就会隐含的转化数据类型；

比如：SQL> create table tableOne ( id int);SQL> insert into tableOne values('100');这是后，oracle就会将‘100’转化为100；

虽然如此，但是Oracle并不能转化所有类型，因此为了提高可读性，我们应该使用转换函数进行转换；比如：SQL> insert into tableOne values('adfasd');就会报错：类型不匹配；

to_char:转化字符串；

SQL> select ename , to_char(hiredate , 'yyyy/mm/dd  hh24/mi/ss') from emp;

ENAME      TO_CHAR(HIREDATE,'YY
---------- --------------------
ok         1988/07/12  00/00/00
ok         1988/07/12  00/00/00
SMITH      1980/12/17  00/00/00
ALLEN      1981/02/20  00/00/00
WARD       1981/02/22  00/00/00
JONES      1981/04/02  00/00/00
MARTIN     1981/09/28  00/00/00
BLAKE      1981/05/01  00/00/00
CLARK      1981/06/09  00/00/00
SCOTT      1987/04/19  00/00/00
KING       1981/11/17  00/00/00

ENAME      TO_CHAR(HIREDATE,'YY
---------- --------------------
TURNER     1981/09/08  00/00/00
ADAMS      1987/05/23  00/00/00
JAMES      1981/12/03  00/00/00
FORD       1981/12/03  00/00/00
MILLER     1982/01/23  00/00/00

16 rows selected.

在date中有如下形式：
yy：显示两位数年份： 2004---04;
yyyy:显示四位数年份：2004年；
mm：两位数字的月份：8月-----08；
dd：两位数字的天：30号-----30；
hh24:24小时制的小时显示方式：下午8点------20；
hh12:12小时制显示小时方式：上午8点---------08；
mi：显示分钟；
ss：显示秒钟；


9：显示数字并忽略后面的0；
0：显示数字，如果尾数不足，就用0补充；
.:在指定位置显示小数点；
，：在指定位置显示逗号；
$:在数字前假美元；
L:在数字前面加上本地货币符号；
C:在数字前面加上国际货币符号；
G:在指定位置显示组分隔符；
D:在指定位置显示小数点符号（.);

SQL> select to_char(sal ,'L999999.999') from emp where deptno=20;

TO_CHAR(SAL,'L999999.
---------------------
             $800.000
            $2975.000
            $3000.000
            $1100.000
            $3000.000

SQL> select to_char(sal ,'L99,999.99') from emp where deptno=20;

TO_CHAR(SAL,'L99,999
--------------------
             $800.00
           $2,975.00
           $3,000.00
           $1,100.00
           $3,000.00


SQL> select hiredate from emp where to_char(hiredate,'yyyy')=1988;

5、系统函数：
sys_context：
1)、terminal:当前会话客户所对应的终端的标识符；
2)、language：语言；
3)、db_name：当前数据库名称；
4)、nls_date_fromat:当前会话客户所对应的日期格式；
5)、session_user:当前会话客户所对应的数据库名称；
6)、current_schema:当前会话客户所对应的默认方案名（一个用户对应一个方案，方案名和用户名相同，当一个用户创建成功后，oracle就会为该用户创建一个名称和该用户名名称相同的方案，Oracle是以方案的方式来管理和组织你的数据对象的；方案里面有很多很多的数据对象，比如：表，视图，触发器，存储过程，角色，表空间等等）；
7)、host:返回数据库所在主机的名称；
通过该函数可以查询一些重要的信息；

SQL> select sys_context('userenv' , 'db_name') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
-------------------------------------------------------------
orcl


SQL> select sys_context('userenv' , 'language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

其中userenv是user environment的缩写，不能够改变；

SQL> select sys_context('userenv' , 'current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
------------------------------------------------------------------
SCOTT