十四、在SQL*Plus 中使用函数
1 、字符串函数,字符串函数是oracle 使用最广泛的一种函数。常用字符串处理函数如下:
LOWER: 小写
UPPER :大写
INITCAP :首字母大写
CONCAT :连接
SUBSTR :截取 ( 参数,开始,数目)
LENGTH :返回字符串的长度
INSTR :(参数,字母) 返回字母出现的位置
LPAD :(参数, 长度,在前补齐参数字母)
RPAD :(参数,长度, 在后补齐参数字母)
REPLACE :(参数, 参数[, 参数] ):第一个参数操作数, 第二是要查找的字符, 第三个是替换的字符,如果没有第三个就删除查找的字符。
//查询姓名为scott的员工信息 (不区分大小写)
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where lower(ename)='scott';
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where upper(ename)='SCOTT';
//查询员工信息,将员工名称与工作连接在一起
SQL>select empno,concat(ename,job),mgr,hiredate,sal,comm,deptno from emp;
//查询员工信息,把员工名称与工作连接在一起
SQL> select empno,concat(ename||'is work:',job),mgr,hiredate,sal,comm,deptno from emp;
//查询员工名称中含有O字符的位置
SQL> select empno,instr(ename,'O'),job,mgr,hiredate,sal,comm,deptno from emp;
//查询员工名称中含有O字符的位置并且求出了员工名称字符长度
SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp;
//在查询上面结果中过滤出员工工作从第三字母开始为ERK员工信息
SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3)='ERK';
备注:当含有两个参数的时候,从开始位置直接到参数结束的结束为止
//在效果同上的同时指明了截取个数为3
SQL> select empno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp where substr(job,3,3)='ERK';
//查询员工信息 薪资是10位位数不够在左部分补*填充
SQL> select empno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptno from emp;
//查询员工信息 薪资是10位位数不够在右部分补*填充
SQL> select empno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptno from emp;
//查询员工信息 把员工名称中含有S字符去除掉
SQL> select empno,TRIM('S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
等效于:
SQL> select empno,TRIM( both 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
//查询员工信息 把员工名称中前面有S字符去除掉
SQL> select empno,TRIM( Leading 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
//等效于
SQL> select empno,LTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
//查询员工信息 把员工名称中后面有S字符去除掉
SQL> select empno,TRIM( trailing 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
等效于:
SQL> select empno,RTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
2 、数字函数,当检索的数据为数字数据类型时,可以使用数字函数进行数学计算。在Oracle 系统中,PL/SQL 语言支持以下数字函数。
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNC: 截取
TRUNC(45.926, 2) 45.92
MOD: 求余弦
MOD(1600, 300) 100
ABS :求绝对值
CEIL :返回大于或等于value 的最小整数
FLOOR :返回小于或等于value 的最大整数
SQRT : 返回value 的平方根 负数无意义。
//四舍五入 结果为46
SQL> select round(45.56) from dual;
//绝对值 结果为45.56
SQL> select abs(-45.56) from dual;
//大于等于最小整数 结果为-45
SQL> select ceil(-45.56) from dual;
//小于等于最大整数 结果为-46
SQL> select floor(-45.56) from dual;
//求余数 结果为300
SQL> select mod(1800,500) from dual
//截取的数的操作数是正数的情况下:只操作小数位 结果为1800.11
SQL> select trunc(1800.11111,2) from dual;
//截取的数的操作数是负数的情况下:操作的是小数点之前的位,把操作位小数点之前的位数全部改写成0. 结果就是1000
SQL> select trunc(1899.11111,-3) from dual;