oracle function

1.查询字符串business_scope逗号分隔后的字符串数组中是否包含字符串checkStr逗号分隔后的字符串数组的所有元素

eg: F_MUTISTR('AB,BC,CD','AB,CD ') 返回 1

create or replace function F_MUTISTR(businessScope  varchar2, checkStr  varchar2) return integer
as
splitStr varchar2(50);
remainStr varchar2(50);
spilitIndex integer;
begin
  remainStr:=checkStr;
  while instr(remainStr,',')>0 loop
    spilitIndex:=instr(remainStr,',');    
    splitStr:=substr(remainStr,0,spilitIndex-1);
    if(instr(businessScope,splitStr)<=0) then
      return 0;
    end if;
    remainStr:=substr(remainStr,spilitIndex+1,length(checkStr)-1);
  end loop;
  if(instr(businessScope,remainStr)<=0) then
    return 0;
  else
    return 1;
   end if;
end;

2.传入年月返回上个月的年份

eg:f_lastmonth_year(2022,1) 返回 2021

create or replace function f_lastmonth_year(year  integer, month  integer) return integer
as
 return_year integer;
begin
 select  floor((year*12+month-1)/12)  into return_year  from dual;
 return return_year;
end;

3.传入年月返回上个月的月份

eg:f_lastmonth_month(2022,1) 返回11

create or replace function f_lastmonth_month(year  integer, month  integer) return integer
as
 return_month integer;
begin
  with month1 as(
     select   (mod((year*12+month)-1,12))  m  from dual
    )
    select decode (m.m,0,12,m.m)  into return_month from month1 m;
    return return_month;
end;

eg:获取2022年1月上个月的年月为 

select f_lastmonth_year(2022,1)*100+f_lastmonth_month(2022,1) from dual