ORACLE SQL 行函数

猫猫1

　　第三章:单行函数
　　字符串函数:
　　select lower('Hello WOrld') from dual;
　　select upper('Hello WOrld') from dual;
　　select initcap('Hello WOrld') from dual;
　　select concat('Hello','World') from dual;
　　select length('HeloWorld') from dual;
　　select instr('HelloWorld','o') from dual;
　　select instr('HelloWorld','o',-1,2) from dual; --从右开始第二个“o”所在的位置
　　select substr('HelloWorld',6) from dual;
　　select substr('HelloWorld',6,2) from dual;
　　select substr('HelloWorld',-5) from dual;
　　select lpad(sal,10,'*') from emp;
　　select rpad(sal,10,'*') from emp;
　　select lpad(rpad('good',11,'*'),17,'*')from dual;
　　select replace('he love you','he','I') from dual;
　　select trim('H' from 'HelloWorld') from dual;
　　select ltrim('HelloWorld','H') from dual;
　　select rtrim('HelloWorld','d') from dual;
　　select substr(name,instr(name,'/',-1)+1) from v$datafile;
　　select ascii('刘') from dual;
　　select chr(65) from dual;
　　数字函数:
　　select round(45.926,2) from dual;
　　select round(45.926,0) from dual;
　　select round(45.926) from dual;
　　select round(45.926,-1) from dual;
　　select trunc(45.926,2) from dual;
　　select trunc(45.926,0) from dual;
　　select trunc(45.926) from dual;
　　select trunc(45.926,-1) from dual;
　　select ceil(3.1415927) from dual; --进位取整
　　select abs(100),abs(-100) from dual;
　　select mod(13,4) from dual; --取余数
　　select power(2,3) from dual; --2的3次幂
　　取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
　　select sign(123),sign(-100),sign(0) from dual;
　　日期函数:
　　select round(sysdate+10,'month') from dual;
　　select round(add_months(sysdate,6),'year') from dual;
　　select trunc(sysdate+10,'month') from dual;
　　select trunc(sysdate,'year') from dual;
　　select months_between('20-FEB-81','17-DEC-80') from dual;
　　select add_months(sysdate,2) from dual;
　　select next_day(sysdate,'SAT') from dual;
　　select last_day(sysdate) from dual;
　　转换函数:
　　select to_char(sysdate,'yyyy-mm-dd') from dual;
　　select to_char(sysdate,'fmyyyy-mm-dd') from dual;
　　select to_char(sysdate,'year-month-day') from dual;
　　select to_char(sysdate,'year-mon-dy') from dual;
　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
　　select to_char(sysdate,'dd "of" mm') from dual;
　　select to_char(10,'xx') from dual; --转换为16进制
　　select to_char(sal,'l00,000.00') sal from emp;
　　select to_char(sal,'l99,999.99') sal from emp;
　　select to_char(sal,'l999.99') sal from emp;
　　select to_number('$950.00','l999.99') from dual;
　　select to_number('9c','xx') from dual; --转换为10进制
　　select to_date('2000-05-01','yyyy-mm-dd') from dual;
　　select
　　to_char(sysdate,'yyyy') curr_year,
　　to_char(to_date('05','yy'),'yyyy') yy05,
　　to_char(to_date('95','yy'),'yyyy') yy95,
　　to_char(to_date('05','rr'),'yyyy') rr05,
　　to_char(to_date('95','rr'),'yyyy') rr95
　　from dual;
　　通用函数:
　　nvl : expr1空则expr2，expr1不空则expr1;
　　＊ nvl函数两个形式参数的数据类型要一致
　　select ename,sal,comm,sal+nvl(comm,0) from emp;
　　select ename,nvl(to_char(mgr),'No manager') from emp;
　　nlv2 : expr1空则expr3，expr1不空则expr2
　　select ename,nvl2(comm,sal+comm,sal) from emp;
　　nullif : expr1＝expr2返回空，expr1!＝expr2返回expr1
　　coalesce(exp1,exp2,exp3,.........) : 从左向右做枚举判断返回第一个非空表达式的值
　　条件表达式:
　　SELECT ename, job, sal,
　　CASE job WHEN 'CLERK' THEN 1.10*sal
　　WHEN 'SALESMAN' THEN 1.15*sal
　　WHEN 'ANALYST' THEN 1.20*sal
　　ELSE sal END "REVISED_SALARY"
　　FROM emp;
　　select ename, job, sal,
　　decode (job,
　　'CLERK',1.10*sal,
　　'SALESMAN',1.15*sal,
　　'ANALYST',1.20*sal,
　　sal) "REVISED_SALARY"
　　from emp;
　　SELECT ename, job, sal,
　　CASE WHEN job='CLERK' THEN 1.10*sal
　　WHEN job='SALESMAN' THEN 1.15*sal
　　WHEN job='ANALYST' THEN 1.20*sal
　　WHEN ename='BLAKE' THEN SAL*1.1
　　ELSE sal END "REVISED_SALARY"
　　FROM emp;
　　系统函数:
　　查看当前用户:
　　select user from dual;
　　select SYS_CONTEXT('USERENV','SESSION_USER') from dual;
　　查看系统时间:
　　select sysdate from dual;
　　查看客户端IP地址:
　　SELECT sys_context('USERENV','IP_ADDRESS') FROM DUAL;
　　查看主机名:
　　SELECT sys_context('USERENV','HOST') FROM DUAL;
　　查看当前方案:
　　SELECT sys_context('USERENV','CURRENT_SCHEMA') FROM DUAL;
　　查看当前的安全审核机制:
　　SELECT sys_context('USERENV','AUTHENTICATION_TYPE') FROM DUAL;