正文
Oracle数据库的函数
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
一、字符函数upper和lower(1)、upper和lower
upper把小写的字符转换成大小的字符 ,lower把大写字符变成小写字符 .
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes
项目中使用:
<if test="null!=batch and batch !=''">
and upper(t2.batch) LIKE '%' || upper(#{batch}) || '%'
</if>
二、数值函数round、trunc和mod(1)、round:
四舍五入函数,默认情况下 ROUND 四舍五入取整,可以自己指定保留的位数。函数后面的参数表示保留的位数,1表示保留一位小数,二表示保留两位小数,-1表示小数点前面进行1位的四舍五入,56就会变成60,-2表示小数点前面进行2为的四舍五入,56就会变成100.
select round(56.166, 1) from dual;--56.2
select round(56.166, 2) from dual;--56.17
select round(56.166, -1) from dual;--60
select round(56.166, -2) from dual;--100
项目中使用:
SELECT A.supplierName,ROUND(A.receiveWt/1000,2) as receiveWt,ROUND(IFNULL(B.qualifiedWt,0)/1000,2) AS qualifiedWt FROM 。。。
(2)、trunc:截断,是truncation的缩写
直接截取,不在看后面位数的数字是否大于5
select trunc(56.166, 1) from dual;--56.1
select trunc(56.166, 2) from dual;--56.16
select trunc(56.166, -1) from dual;--50
select trunc(56.166, -2) from dual;--0
(3)、mod:求余,是modulo的缩写
select mod(10, 6) from dual;
三、日期函数
Oracle 中提供了很多和日期相关的函数,包括日期的加减,在日期加减时有一些规律:
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字
(1)、SYSDATE函数
SYSDATE函数可以得到目前系统的时间,如:select sysdate from dual;
----查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;
----算出明天此刻
select sysdate+1 from dual;
----查询出emp表中所有员工入职距离现在几周。
select round((sysdate-e.hiredate)/7) from emp e;
(2)、MONTHS_BETWEEN() :获得两个时间段中的月数;
----查询出emp表中所有员工入职距离现在几月。
select months_between(sysdate,e.hiredate) from emp e;
----查询出emp表中所有员工入职距离现在几年。
select months_between(sysdate,e.hiredate)/12 from emp e;
(3)、add_months():加多少个月
select sysdate,add_months(sysdate,12) from dual; --加1年
项目中使用:
insert into SYS_DATA_SET values(seqId, 'audit_type', '审核状态类型', '0', null, null, sysdate, null, sysdate);
<insert id="saveMobileMenu">
insert into sys_role_menu_mobile (role_id, menu_id, del_flag, create_by, create_time) VALUES
<foreach collection="list" item="item" separator=",">
(#{roleId}, #{item},0,#{userId}, sysdate())
</foreach>
</insert>
<update id="softDeleteByKey" parameterType="com.jawasoft.pts.entity.IntelDrugStore">
update t_intel_drugstore
set del_flag = '2',
update_by = #{updateBy},
update_time = sysdate
where id = #{id}
</update>
四、转换函数(1)、TO_DATE:将字符串的数据转换成日期类型
to_date()用法如下:
TO_DATE('2020-11-05 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
前者为字符串,后者为转换日期格式。前后两者要以一对应。
项目中使用:
<if test="null!=startDate and startDate !=''">
and t1.upload_time >= to_date('${startDate}','yyyy-mm-dd')
</if>
<if test="null!=endDate and endDate !=''">
and t1.upload_time <= to_date('${endDate}','yyyy-mm-dd')
</if>
(2)、TO_CHAR:将日期按一定格式转换成字符类型
to_char()用法如下:
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;
在结果中,如1981-02-20, 10 以下的月前面被被补了前导零,可以使用 fm 去掉前导零 to_char(hiredate,'fmyyyy-mm-dd'),改完之后1981-2-20
五、通用函数(1)、空值处理nvl:null value
范例:查询所有的雇员的年薪
我们发现很多员工的年薪是空的,原因是很多员工的奖金是 null,null和任何数值计算都是null,这时我们可以使用 nvl来处理。
select ename,nvl(comm,0),sal*12+nvl(comm,0) from emo;
(2)、Decode函数
该函数类似 if....else if...esle ,语法:DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式;Search1,search2...:用于比较的条件 ;Result1, result2...:返回值
如果 col/expression 和 Searchi匹配就返回 resulti,否则返回 default 的默认值
范例:查询出所有雇员的职位的中文名 :字段为job,如果job为clerk,则返回业务员,以此类推,如果都未匹配上,则返回无业
(3)、case when
语法如下:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
示例:
select t.empno, t.ename,case when t.job = 'CLERK' then
'业务员'
when t.job = 'MANAGER' then
'经理'
when t.job = 'ANALYST' then
'分析员'
when t.job = 'PRESIDENT' then
'总裁'
when t.job = 'SALESMAN' then
'销售'
else
'无业'
end from emp t
在项目中的应用:
select update_time,create_time, case when (update_time is null or update_time = '') then create_time else update_time end from 表名;
<select id="selectTsList" resultType="java.util.Map">
select tts.ID "id",product_Name "productName", DECODE(tts.line_type, '1', tts.production_batch, tts.import_Batch) "batch",
localtion "localtion", d.name as "productUnit" , tts.uom as "uom",
(case when tts.LINE_TYPE='1' then tts.complete_Qty else tts.import_num end ) as "batchNum",
material_Code "materialCode", '1' "materialType"
from T_TRACEABILITY_SLICE tts
left join sys_department d on tts.workshop_code=d.code
<include refid="tsListWhere"></include>
order by tts.create_time desc
</select>
六、聚合函数(又叫分组函数)(1)、统计记录数 count()
不建议使用 count(*),可以使用一个具体的列以免影响性能。
(2)、最小值查询 min() ,最大值查询 max() ,查询平均值 avg()
(3)、.求和函数 sum()
七、分组统计
分组统计需要使用 GROUP BY来分组
语法:SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列 名 1 ASC|DESC,列名 2...ASC|DESC
注意: 1. 如果使用分组函数,SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来,不能有其 他字段。
注意:2. 如果使用分组函数,不使用 GROUP BY 只可以查询出来分组函数的值
范例:查询出部门人数大于 5 人的部门 分析:需要给 count(ename)加条件,此时在本查询中不能使用 where,可以使用HAVING
项目中使用group by
<select id="selectOriginalBySv" resultType="java.util.Map">
SELECT
t2.id AS originalId,
t2.original_name AS originalName
FROM
t_supplier_variety sv
INNER JOIN t_supplier_variety_original svo ON svo.supplier_variety_id = sv.id AND svo.`is_historical` = 0
INNER JOIN t_supplier s ON s.`id` = sv.`supplier_id`
INNER JOIN t_variety_original t1 ON sv.`variety_id` = t1.`variety_id`
INNER JOIN t_original t2 ON FIND_IN_SET(t2.id, svo.original)
WHERE 1=1
AND t1.del_flag = 0
AND t2.del_flag = 0
<if test="supplierId!=null">
AND s.`id` = #{supplierId}
</if>
<if test="supId!=null">
AND s.`sup_id` = #{supId}
</if> <if test="prcId!=null">
AND s.`prc_id` = #{prcId}
</if> <if test="varietyId!=null">
AND t1.variety_id = #{varietyId}
</if>
GROUP BY t2.id, t2.original_name
</select>