oracle 的单行函数
1、字符函数：initcap（注意一下）
SQL> select lower('hellO WORld') 转小写,upper('hellO WORld') 转大写,initcap('hello world') 首字母大写 2 from dual; 转小写 转大写 首字母大写 ---------------------- ---------------------- ---------------------- hello world HELLO WORLD Hello World
SQL> select lower('hellO WORld') 转大写,upper('hellO WORld') 转小写,initcap('hello worlD') 首字母大写 from dual; 转大写 转小写 首字母大写 ---------------------- ---------------------- ---------------------- hello world HELLO WORLD Hello World
2、substr(a,b) 从a中，第b位开始取，取右边所有的字符： 可以取到第b位
SQL> select substr('Hello World',4) from dual; SUBSTR('HELLOWOR ---------------- lo World
SQL> select substr(1111,2) from dual; 对数字也适用 SUBSTR ------ 111
substr(a,b,c) 从a中，第b位开始取，取c位：
SQL> select substr('Hello World',4,4) from dual; 空格也算一位 SUBSTR(' -------- lo W
3、length 字符数 lengthb 字节数：
SQL> select length('Hello World') 字符数, lengthb('Hello World') 字节数 from dual; 字符数 字节数 ---------- ---------- 11 11
SQL> select length('北京') 字符数, lengthb('北京') 字节数 from dual; 字符数 字节数 ---------- ---------- 2 6
4、在母串中查找子串，找到返回下标（从1开始），否则返回0：
SQL> select instr('Hello World','ll') from dual; INSTR('HELLOWORLD','LL') ------------------------ 3
SQL> select instr('Hello World','lle') from dual; INSTR('HELLOWORLD','LLE') ------------------------- 0
5、lpad 左填充 rpad 右填充：以及其他填充
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual; 左 右 -------------------- -------------------- ******abcd abcd******
SQL> select replace('Hello World','l','*') from dual; REPLACE('HELLOWORLD',' ---------------------- He**o Wor*d
6、trim 去掉前后指定的字符：
SQL> select trim('H' from 'Hello WorldH') from dual; TRIM('H'FROM'HELLOWO -------------------- ello World SQL> select trim('l' from 'Hello WorldH') from dual; TRIM('L'FROM'HELLOWORLDH ------------------------ Hello WorldH SQL> select trim('H' from 'Hello WorldHe') from dual; TRIM('H'FROM'HELLOWORLDH ------------------------ ello WorldHe SQL> select trim('H' from 'eHello WorldH') from dual; TRIM('H'FROM'EHELLOWORLD ------------------------ eHello World
7、select replace('Hello World','l','*') from dual;
SQL> select replace('Hello World','l','*') from dual; REPLACE('HELLOWORLD',' ---------------------- He**o Wor*d
8、round （xx，数字）前面的xx进行小数点数字前后四舍五入：
SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual; 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------- 45.93 45.9 46 50 0
9、日期函数 sysdate （系统日期）：
SQL> select sysdate from dual; SYSDATE -------------- 06-9月 -18
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS' -------------------------------------- 2018-09-06 08:31:42
SQL> select systimestamp from dual; 时间戳 SYSTIMESTAMP --------------------------------------------------------------------------- 06-9月 -18 08.32.08.704000 上午 +08:00
SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') from dual; TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DDHH24:MI:SS*FF') ---------------------------------------------------------- 2018-09-06 08:32:55*607000
昨天，今天，明天：
SQL> select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual; 昨天 今天 明天 -------------- -------------- -------------- 08-9月 -18 09-9月 -18 10-9月 -18
员工的工龄
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, 2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 3 from emp; ENAME HIREDATE 天 星期 月 年 -------------------- -------------- ---------- ---------- ---------- ---------- SMITH 17-12月-80 13780.3844 1968.62634 459.346146 37.7544777 ALLEN 20-2月 -81 13715.3844 1959.34063 457.179479 37.5763955 WARD 22-2月 -81 13713.3844 1959.05491 457.112813 37.5709161 JONES 02-4月 -81 13674.3844 1953.48348 455.812813 37.4640668 MARTIN 28-9月 -81 13495.3844 1927.91205 449.846146 36.9736558 BLAKE 01-5月 -81 13645.3844 1949.34063 454.846146 37.3846147 CLARK 09-6月 -81 13606.3844 1943.7692 453.546146 37.2777654 SCOTT 19-4月 -87 11466.3844 1638.05491 382.212813 31.4147517 KING 17-11月-81 13445.3844 1920.7692 448.179479 36.8366695 TURNER 08-9月 -81 13515.3844 1930.7692 450.512813 37.0284503 ADAMS 23-5月 -87 11432.3844 1633.19777 381.079479 31.321601 ENAME HIREDATE 天 星期 月 年 -------------------- -------------- ---------- ---------- ---------- ---------- JAMES 03-12月-81 13429.3844 1918.48348 447.646146 36.7928339 FORD 03-12月-81 13429.3844 1918.48348 447.646146 36.7928339 MILLER 23-1月 -82 13378.3844 1911.19777 445.946146 36.6531079 已选择14行。
日期的最后一天：
SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDA -------------- 30-9月 -18
MONTHS_BETWEEN 计算工龄
SQL> select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 2 from emp; 一 二 ---------- ---------- 459.346217 452.754404 457.179551 450.65763 457.112884 450.593113 455.812884 449.238275 449.846217 443.399565 454.846217 448.270533 453.546217 447 382.212884 376.689888 448.179551 441.754404 450.512884 444.044726 381.079551 375.560855 一 二 ---------- ---------- 447.646217 441.206017 447.646217 441.206017 445.946217 439.560855 已选择14行。
114月后：
SQL> select add_months(sysdate,114) from dual; ADD_MONTHS(SYS -------------- 09-3月 -28
从今天开始算（2018.9.9星期一）,下一个星期一：
SQL> select next_day(sysdate,'星期一') from dual; NEXT_DAY(SYSDA -------------- 10-9月 -18
对日期进行四舍五入：
select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual;
一 二
-------------- --------------
01-9月 -18 01-1月 -19
显式转换：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS"今天是"DAY') -------------------------------------------------------------------------------- 2018-09-09 09:27:35今天是星期日
查询员工的薪水： 货币符号，两位小数，千位符：
SQL> select sal, to_char(sal,'L9,999.99') from emp; SAL TO_CHAR(SAL,'L9,999.99') ---------- -------------------------------------- 800 ￥800.00 1600 ￥1,600.00 1250 ￥1,250.00 2975 ￥2,975.00 1250 ￥1,250.00 2850 ￥2,850.00 2450 ￥2,450.00 3000 ￥3,000.00 5000 ￥5,000.00 1500 ￥1,500.00 1100 ￥1,100.00 SAL TO_CHAR(SAL,'L9,999.99') ---------- -------------------------------------- 950 ￥950.00 3000 ￥3,000.00 1300 ￥1,300.00 已选择14行。
nvl2(a,b,c) 当a=null时，返回c，否则返回b：
SQL> select sal,nvl(comm,0),sal*12+nvl2(comm,comm,0) from emp; SAL NVL(COMM,0) SAL*12+NVL2(COMM,COMM,0) ---------- ----------- ------------------------ 800 0 9600 1600 300 19500 1250 500 15500 2975 0 35700 1250 1400 16400 2850 0 34200 2450 0 29400 3000 0 36000 5000 0 60000 1500 0 18000 1100 0 13200 SAL NVL(COMM,0) SAL*12+NVL2(COMM,COMM,0) ---------- ----------- ------------------------ 950 0 11400 3000 0 36000 1300 0 15600 已选择14行。
nullif(a,b) 当a=b时，返回null，否则返回a：
SQL> select nullif('abc','abc') from dual; NULLIF ------
SQL> select nullif('abc','abdc') from dual; NULLIF ------ abc
COALESCE 从左至右找到第一个不为null的值：
SQL> select comm,sal,COALESCE(comm,sal) from emp; COMM SAL COALESCE(COMM,SAL) ---------- ---------- ------------------ 800 800 300 1600 300 500 1250 500 2975 2975 1400 1250 1400 2850 2850 2450 2450 3000 3000 5000 5000 0 1500 0 1100 1100 COMM SAL COALESCE(COMM,SAL) ---------- ---------- ------------------ 950 950 3000 3000 1300 1300 已选择14行。
根据职位涨工资 总裁1000 经理800 其他400：
SQL> ed 已写入 file afiedt.buf 1 select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000 2 when 'MANAGER' then sal+800 3 else sal+400 4 end 涨后薪水 5* from emp SQL> / ENAME JOB 涨前薪水 涨后薪水 -------------------- ------------------ ---------- ---------- SMITH CLERK 800 1200 ALLEN SALESMAN 1600 2000 WARD SALESMAN 1250 1650 JONES MANAGER 2975 3775 MARTIN SALESMAN 1250 1650 BLAKE MANAGER 2850 3650 CLARK MANAGER 2450 3250 SCOTT ANALYST 3000 3400 KING PRESIDENT 5000 6000 TURNER SALESMAN 1500 1900 ADAMS CLERK 1100 1500 ENAME JOB 涨前薪水 涨后薪水 -------------------- ------------------ ---------- ---------- JAMES CLERK 950 1350 FORD ANALYST 3000 3400 MILLER CLERK 1300 1700 已选择14行。
另外一种方法：
SQL> ed 已写入 file afiedt.buf 1 select ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000, 2 'MANAGER', sal+800, 3 sal+400)涨后薪水 4* from emp SQL> / ENAME JOB 涨前薪水 涨后薪水 -------------------- ------------------ ---------- ---------- SMITH CLERK 800 1200 ALLEN SALESMAN 1600 2000 WARD SALESMAN 1250 1650 JONES MANAGER 2975 3775 MARTIN SALESMAN 1250 1650 BLAKE MANAGER 2850 3650 CLARK MANAGER 2450 3250 SCOTT ANALYST 3000 3400 KING PRESIDENT 5000 6000 TURNER SALESMAN 1500 1900 ADAMS CLERK 1100 1500 ENAME JOB 涨前薪水 涨后薪水 -------------------- ------------------ ---------- ---------- JAMES CLERK 950 1350 FORD ANALYST 3000 3400 MILLER CLERK 1300 1700 已选择14行。
---恢复内容结束---
