专注做网管的电脑公司
  • 微信公众号
  • 联系我们
  • 客服热线:400-0355-812
  • oracle单行函数和多行函数

    2020-10-08 14:06:31 19

    单行函数和多行函数示意图:

    爱土壤电脑

     

    单行函数分为五种类型:字符函数、数值函数、日期函数、转换函数、通用函数

    爱土壤电脑

    单行函数:

    1. --大小写控制函数  

    2. select lower('Hello World') 转小写, upper('Hello World') 转大写 from dual;  

    3. --initcap: 首字母大写  

    4. select initcap('hello world') 首字符大写 from dual;  

    5.   

    6. --字符控制函数  

    7. -- concat: 字符连接函数, 等同于  ||  

    8. select concat('Hello',' World'from dual;  

    9. --substr:求母串中的某个子串  

    10. select substr('Hello World',3) from dual;  

    11. select substr('Hello World',3,4) from dual;  

    12. --length和lengthb: 字符数和字节数  

    13. select length('China') 字符数, lengthb('China') 字节数  from dual;  

    14. --instr:在母串中,查找子串的位置  

    15. select instr('Hello World','ll'from dual;  

    16. --lpad,rpad: 左右填充,将abcd用*填充到10位  

    17. select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;  

    18. --trim: 去掉字符串前后指定的字符  

    19. select trim('H' from 'Hello WorldH'from dual;  

    20. --replace:字符串替换函数  

    21. select replace('Hello Wordl','l','*'from dual;  

    22.   

    23. --数字函数  

    24. select round(45.926,2) 四舍五入, trunc(45.926,2)  截断 ,mod(1600,300) 求于 from dual;  

    25. --ROUND函数  

    26. select round(45.923,0) 整数位, round(45.923,-1) 十位,round(45.923,-2) 百位 from dual;  

    27.   

    28. --日期函数  

    29. --显示当前日期  

    30. select sysdate from dual;  

    31. --显示时间部分  

    32. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'from dual;  

    33. --显示昨天,今天和明天,加减数字仍未日期  

    34. select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;  

    35. --两个日期相减,结果为相差的天数,查询员工信息,显示员工工龄。两个日期不能相加  

    36. select empno,ename, sysdate-hiredate 天 from emp;  

    37. --查询员工信息,显示员工工龄,分别按照天,星期,月显示  

    38. select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from emp;  

    39. --months_between:两个日期相差的月数  

    40. select (sysdate-hiredate)/30 方式一, months_between(sysdate,hiredate) 方式二 from emp;  

    41. --add_months:在指定日期上加上若干个月  

    42. select add_months(sysdate,1)  下个月, add_months(sysdate,123) "123个月后" from dual  

    43. --last_day: 某个日期当月的最后一天  

    44. select last_day(sysdate) from dual;  

    45. --next_day:下周六  

    46. select next_day(sysdate,'星期五'from dual;  

    47. --对日期进行四舍五入  

    48. select round(sysdate,'MONTH')  月,round(sysdate,'YEAR'from dual;     

    49. --对日期进行截断  

    50. select trunc(sysdate,'MONTH')  月,trunc(sysdate,'YEAR'from dual;  

    51. --日期格式  

    52. select * from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');  

    53. -- 查询当前日期:显示:  2011-09-17 15:12:15今天是星期六  

    54. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day'from dual;  

    55. --查询员工信息,显示员工的编号,姓名,月薪,要求有货币代码(L),千位符(,),小数点(.),  

    56. select empno,ename,to_char(sal,'L9,999.99'from emp;  

    1. --通用函数  

    2. --nvl(exp1,exp2):当exp1为空时,返回exp2  

    3. --nvl2(exp1,exp2,exp3):当exp1为空时,返回exp3;否则返回exp2  

    4. select ename,sal*12+nvl2(comm,comm,0) 年收入 from emp;  

    5. --NULLIF (expr1, expr2),如果expr1=expr2,返回null;否则,返回expr1  

    6. select nullif('abc','abc'from dual;  

    7. select nullif('abc','abcaa'from dual;  

    8. --COALESCE :找到参数列表中,第一个不为空的值  

    9. select ename,comm,sal,COALESCE(comm,sal) from emp;  

    10. --给员工涨工资,根据职位涨,总裁涨1000,经理涨600 其他人员涨400  

    11. select ename,job,sal 涨前工资, case job when 'PRESIDENT' then sal+1000  

    12.                                          when 'MANAGER'   then sal+600  

    13.                                          else sal+400  

    14.                                  end 涨后工资  

    15. from emp;  

    16.   

    17. select ename,job,sal 涨前工资, decode(job,'PRESIDENT',sal+1000,  

    18.                                             'MANAGER',sal+600,  

    19.                                              sal+400) 涨后工资  

    20. from emp; 

    (五).转换函数(Conversion Functions)
    转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。

    1、TO_CHAR() 本函数又可以分三小类,分别是
     转换字符->字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型;
    例如:SELECT TO_CHAR('AABBCC') FROM DUAL;

     转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型;
    例如:SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

     转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
    例如:SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

    2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。
    例如:
    SELECT TO_DATE(2454336, 'J') FROM DUAL;
    SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

    为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句:
    SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;

    3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
    例如:SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;

    (六).其它辅助函数(Miscellaneous Single-Row Functions)

    1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else的功能。
    exp做为初始参数。s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。
    毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。

    例如:select decode('a2','a1','true1','a2','true2','default') from dual;

    2、GREATEST(n1,n2,...n) 返回序列中的最大值
    例如:SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;

    3、LEAST(n1,n2....n) 返回序列中的最小值
    例如:SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

    4、NULLIF(c1,c2)
    Nullif也是个很有意思的函数。逻辑等价于:CASE WHEN c1 = c2 THEN NULL ELSE c1 END
    例如:SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

    5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同,则oracle会自动将c2转换为c1的类型。
    例如:SELECT NVL(null, '12') FROM DUAL;

    6、NVL2(c1,c2,c3) 大家可能都用到nvl,但你用过nvl2吗?如果c1非空则返回c2,如果c1为空则返回c3
    例如:select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;

    7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。

     

    多行函数

    和单行函数相比,oracle提供了丰富的基于组的,多行的函数。这些函数能在select或select的having子句中使用,当用于select子串时常常都和GROUP BY一起使用。多行函数分为接收多个输入,返回一个输出。

    组函数:

    1. --求员工的工资总和  

    2. select sum(sal) from emp;  

    3. --求个数  

    4. select count(*) from emp;  

    5. --求平均工资  

    6. select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;  

    7. --关于空值:组函数会自动滤空  

    8. select count(*), count(comm) from emp;  

    9. --max和min:求最高工资和最低工资  

    10. select max(sal) 最高工资,min(sal) 最低工资 from emp;  

    11. --分组数据:求各个部门的平均工资  

    12. select deptno,avg(sal) from emp group by deptno;  

    13. --group by作用于多列: 按部门,不同的工种,统计平均工资  

    14. --group by作用于多列:先按照第一列分组;如果相同,再按照第二列分组  

    15. select deptno,job,avg(sal) from emp group by deptno,job;  

    16. --:求部门的平均工资大于2000的部门  

    17. select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;  

    18. --group by的增强  

    19. select deptno,job,sum(sal) from emp group by rollup(deptno,job);  

    20. --不同的deptno空两行/取消设置  

    21. break on deptno skip 2/break on null  


    联系我们