fun_loc.sql

公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统

drop function  dayaftermonths;
CREATE FUNCTION dayaftermonths ( yqdate char(8),yqafter char(1),qfdate char(8),qfafter char(1)) --延期日期,延期期限，签发日期,有效期限
RETURNS CHAR(8)
BEGIN
   DECLARE ls_ansday CHAR(8) ;
   declare ls_after char(2);

   if TRIM(yqdate) = '' OR IFNULL( yqdate,qfdate ) = qfdate  then --如果没有延期数据,计算签发日期
      set ls_ansday = qfdate ;
      set ls_after  = qfafter;
   else
      set ls_ansday = yqdate;
      set ls_after = yqafter;
   end if ;
   SET ls_ansday =  MONTHS ( date( ls_ansday ), cast(ls_after as integer)  ) ;      

   RETURN DATEFORMAT(ls_ansday,'yyyymmdd') ;
END;


----------计算来自地区--------------------
DROP FUNCTION fun_FROM;
create function fun_FROM(as_czhkss CHAR(6))
 returns CHAR(1)
begin
   DECLARE ls_fj char(2);       
   DECLARE ls_city char(2);    
   DECLARE ls_foreign char(1); 
   
   set ls_fj     =substr(as_czhkss,1,2);
   set ls_city   =substr(as_czhkss,5,2);
   set ls_foreign=substr(as_czhkss,1,1);
  if ls_foreign = '7' then
     return '5';
  end if;
  if ls_foreign = '9' then
     return '6';
  end if;
  if ls_fj = '35' and (ls_city < '19' or ls_city > '80') then
     return '1';
  end if;
  if ls_fj = '35' and ls_city > '19' and ls_city < '80' then
     return '2';
  end if;
  if ls_fj <> '35' and (ls_city < '19' or ls_city > '80') then
     return '3';
  end if;
  if ls_fj <> '35' and ls_city > '19' and ls_city < '80' then
     return '4';
  end if;
end;


----------计算暂住时间--------------------
drop function fun_months;
create function fun_months(as_lbdrq0 char(8))
 returns char(1) 
begin
  declare li_months integer;

  set li_months = months(date(as_lbdrq0),today());
  if li_months = 0 then
     return '1';
  end if;
  if li_months > 0 and li_months <=12 then
     return '2';
  end if;
  if li_months > 12 then
     return '3';
  end if;
end;


----------计算暂住时间--------------------
drop function fun_match;
create function fun_match(as_source char(7),as_dest char(7),as_rs char(8))
 returns numeric
begin
  if as_source = as_dest then
     return as_rs;
  else
     return NULL;
  end if;
end;