oracle的函数操作（一）

1、extract函数取日期的年月日 时分秒
Select Extract(month from sysdate)as year from dual ;
–取得当前的月份
–取得当前的月份 可以相应的通过month day year取得月份 日子和年份
select extract(month from timestamp’2011-11-12 12:12:12’) month from dual;
–取得某个表中的某个字段的月份
select extract(month from to_timestamp(tt.AUDIT_DATE,‘yyyy-mm-dd hh24:mi:ss’)) month from sys_user tt ;
–可以相应的变为minute hour
select extract(second from timestamp’2011-11-12 12:12:12’) seconde from dual;
–获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp(‘2011-02-04 15:07:00’,‘yyyy-mm-dd hh24:mi:ss’) dt1
,to_timestamp(‘2011-05-17 19:08:46’,‘yyyy-mm-dd hh24:mi:ss’) dt2
from dual)
/
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
102 4 1 46
2、oracle 分组后取每组第一条数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,
test1.*
FROM test1)
WHERE rn = 1 ;
例如：
select * from (
SELECT t.id,
t.attribute_03,
t.weight_version,
ROW_NUMBER() OVER(PARTITION BY t.attribute_03 ORDER BY t.weight_version desc) rn
FROM BIBSC_JX_KPI_ASSESSMENT_PLAN t
where t.period_code is not null)
where rn = 1
3、oracle 的round函数： 查询时，确定对数据的四舍五入；
select round(number,index) from dual;
number：需要处理的数据；
index：需要保留的小数位数
select round(123.3423423,5) from dual;
4、oracel函数decode用法：
decode(value,if1,then1,if2,then2,if3,then3,…,else);
如果value==if1，decode函数结果为then1,…,如果不等于任何一个值，则返回else
示例1如下：
select decode(1,1,‘a’,‘b’) as a from dual;
如果1=1（第一个参数等于第二个参数），则decode函数取值为a；
如果1!=1（第一个参数不等于第二个参数），则decode函数取值为b；
示例2如下：
select decode(1,2,‘a’,decode(1,2,‘b’,‘c’)) as a from dual;
如果1=2，decode函数取值为a；
如果1!=2，decode函数取值为decode(1,2,‘b’,‘c’);递归函数中接着进行判断，如果1=2，函数取值b，如果1!=2，函数取值c；
示例3如下：分析同上
select decode(1,2,‘a’,decode(1,2,‘b’,decode(1,2,‘c’,‘d’))) as a from dual;
select decode(‘a’,‘1’,‘true’,‘false’) as flag from dual;
5、trunc函数
select sysdate from dual; --2018/7/17 20:05:00
select trunc(sysdate) from dual; --2018/7/17
6、oracle创建一个函数，返回2个参数
CREATE OR REPLACE FUNCTION F_getPeriodStr(F_id IN VARCHAR2) RETURN VARCHAR2 IS
begin_str VARCHAR2(400);
end_str VARCHAR2(400);
var_return VARCHAR2(400);
BEGIN

	  select attribute_01,attribute_02 into begin_str,end_str  from risk_zl_period where id = F_id;
	  WHILE begin_str <= end_str LOOP
	        if var_return is null then 
	          var_return :=  begin_str;
	           begin_str := begin_str + 1;
	        else
	           var_return :=  var_return ||','|| begin_str;
	           begin_str := begin_str + 1;
	        end if;
	        
	      END LOOP;
	  RETURN var_return;
	END F_getPeriodStr;

7、 round(number1,number2); 保留指定的小数位数
注意：round函数中，第一个参数是要查询的数据，第二个参数为查询的这个数据保留几位小数；
select round(12.3567,2) from dual; 查询结果为12.35
8、nvl(param1, param2)
表示，如果第一个值param1为空，则取第二个值param2
select round(nvl(t.RISK_VALUE, t.EVAL_VALUE),2) as RISK_VALUE
from pt6.RISK_FRAME_CASE_REL_TJ_V t
where t.risk_data_id = ‘ff808081611d1ec701611d451e4f07d2’ ;

9、sql：行转列 wm_concat(列名);
函数可以把列值以","号分隔起来，并显示成一行
例如：
select wm_concat(dept_id) as dept_id from (
select t.attribute_02 as dept_id from bibsc_jx_kpi_condition t where t.attribute_03 in (
select t.id from bibsc_jx_kpi t where t.parent_id = ‘8a58c6745e7ffd6e015e803a89ae07c7’
)
union
select t1.kpi_dept_range as dept_id from bibsc_jx_kpi_rule t1 where t1.bibsc_jx_kpi_parent_id in (
select t.id from bibsc_jx_kpi t where t.parent_id = ‘8a58c6745e7ffd6e015e803a89ae07c7’
)
) a
10、case when then else end 语法用法
select t1.id,
t1.parent_id,
t1.period_id,
t1.zl_kpi_id,
t1.attribute_02,
m.attribute_03 as kpi_Sskj,
case
when t1.attribute_02 = ‘data_type’ then
t1.zl_kpi_id
else
rz.kpi_name
end as kpiName
from RISK_ZL_KPI_YUCE t1
left join risk_zl_kpi_monitor_data m
on t1.zl_kpi_id = m.id
left join RISK_ZL_KPI_LIBS_TREE rt
on m.risk_zl_kpi_monitor_id = rt.id
left join RISK_ZL_KPI_LIBS rz
on rt.kpi_lib_id = rz.id
WHERE t1.RISK_STATE like ‘%’ || ‘1’ || ‘%’
order by t1.creation_date desc
===============================================================================================
注意case when then else end 语法用法
case when t1.attribute_02 = ‘data_type’ —当attribute_02等于data_type时，取t1.zl_kpi_id作为kpiName的值，
then t1.zl_kpi_id ----否则的话，rz.kpi_name作为kpiName的值；
else rz.kpi_name
end as kpiName
============================================================================================
select a.*,
case
when risk_value >= 12 then
‘red’
when risk_value <= 6 then
‘green’
else ‘yellow’
end as risk_leva
from a
============================================================================
case when a=1 then ‘1’
when a=2 then ‘2’
when a=3 then ‘3’
else ‘4’
end as result_flag
from dual;

	============================================================================================
	with a as(
	select r.*,t.first_name,t.secod_name from RISK_EVENT_RESULT_LEVEL r
	left join RISK_EVENT_FRAME_EFFICT_TYPE t on r.effect_type_id = t.id
	) select a.*,
	  case when first_name = '运营'
	    then (first_name ||'-'|| secod_name)
	  when first_name = '财务'
	    then (first_name ||'-'|| secod_name)
	  else first_name
	  end as effect_Type_name
	 from a;

11、pivot的用法
–使用前数据集形式：
jx_dept jx_period jx_value
测试部门C 2017年4月 58
测试部门D 2017年4月 44
测试部门E 2017年4月 84
测试部门F 2017年4月 54

	--使用后数据集形式：
	jx_dept		2017年4月
	测试部门E	84
	测试部门C	58
	测试部门F	54
	测试部门D	44

sql如下：
with a as
(select t1.jx_dept, t1.jx_period, t1.jx_value
from bibsc_center_jxtj t1
WHERE t1.JX_ORDER_TYPE = ‘DEPT’
and t1.JX_DEPT_TYPE = ‘职能部门’
and t1.JX_PERIOD in (‘2017年4月’))
select * from a pivot(sum(jx_value) for jx_period in(‘2017年4月’))
12、 instr(a,b)
select * from a where instr(a,b)>0;用于实现B字段是A字段中的某一部分的时候，要论顺序或者要相邻的字符。
如果想要不论顺序或者不相邻的字符时，定义函数可以实现：
select * from a where instr(a,b)>0;这个只能实现B字段是A字段中的某一部分的时候。
如果想要不论顺序或者不相邻的字符时，定义函数可以实现
create or replace function checks(v_a varchar2,v_b varchar)
return number
as
num number;
cou number;
begin
num := -1;
cou:=0;
for i in 1…length(v_b) loop
if instr(v_a,substr(v_b,i,1))>0 then
cou:=cou+1;
end if;
end loop;
if cou=length(v_b) then
return cou;
end if;
dbms_output.put_line(cou||’ '||length(v_b));
return num;
end;

		结果：
		SQL> select * from a;
		A          B
		---------- ----------
		asdf       sd
		asdf       ad
		asdf       df
		asdf       asdf
		
		SQL> select * from a where checks(a,b)>0;
		A          B
		---------- ----------
		asdf       sd
		asdf       ad
		asdf       df
		asdf       asdf

13、oracle中，查询多列中的最大值和最小值
greatest和least函数
select id , chinese , math , english , greatest( chinese ,math ,english) as max , least( chinese , math ,english) as min from table;
例如:
select greatest(1,2,3,4) as max , least( 5,6,7,8) as min from dual;

14、oracle函数中有不足位数补空格的函数
LPAD和RPAD分别是左补位和右补位
repalce(lpad(字段名，共几位)，‘ ’，‘0’)
如运行
select replace(lpad(‘123’,5),’ ',‘0’) from dual
结果是00123
即补足五位
select lpad(‘123’,5,‘0’) from dual 这样也可以