fun_ora.sql

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

----------计算暂住时间--------------------
create or replace function fun_months(as_lbdry0 string)
 return string is
li_months integer;
begin
  li_months := trunc(months_between(sysdate,to_date(as_lbdry0,'yyyymmdd')));
  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;
/

----------计算来自地区--------------------
create or replace function fun_FROM(as_czhkss string)
 return string is
   ls_fj char(2);       
   ls_city char(2);    
   ls_foreign char(1); 
begin
   ls_fj     :=substr(as_czhkss,1,2);
   ls_city   :=substr(as_czhkss,5,2);
   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;
/

create or replace function fun_zzsj(as_lbdry0 string)
 return string is li_months integer;
begin 
  li_months:=trunc(months_between(sysdate,to_date(as_lbdry0,'yyyymmdd')));
  if li_months >= 0 and li_months <= 6 then
     return '1';
  end if;
  if li_months > 6 and li_months <=12 then
     return '2';
  end if;
  if li_months > 12 then
     return '3';
  end if;
end;
/