判斷使用者是否定購某sp業務——預存程序

標籤：

create or replace procedure pro_check_sp(v_servnumber in varchar2,
                                         v_spid       in varchar2,
                                         v_spbizid    in varchar2,
                                         v_result     out varchar2
                                       
                                         ) is
  --判斷使用者是否定購某sp業務，已訂購返回yes,否則為no
  n_region  number(5);
  n_subsid  number(18);
  int_count number(10);
  my_region_err exception;
  my_subsid_err exception;
begin
  v_result  := ‘‘;
  int_count := 0;
  ---取region;
  SELECT DISTINCT REGION
    INTO n_region
    FROM TBCS.REC_SERVNUMBER_REGION
   WHERE BEGINNUM <= v_servnumber
     AND ENDNUM >= v_servnumber;
  if (n_region is null) then
    --拋出異常後過程會自動結束
    raise my_region_err;
  end if;
  --取subsid
  select count(*)
    into int_count
    from (select subsid
             from tbcs.subscriber
            where servnumber = v_servnumber
              and region = n_region
              and active = 1
           union
           select subsid
             from tbcs.subscriber
            where servnumber = v_servnumber
              and region = n_region
              and active = 1);
  if int_count != 1 then
    raise my_subsid_err;
  end if;
  select subsid
    into n_subsid
    from (select subsid
             from tbcs.subscriber
            where servnumber = v_servnumber
              and region = n_region
              and active = 1
           union
           select subsid
             from [email protected]_B.HEBEI.MOBILE.COM
            where servnumber = v_servnumber
              and region = n_region
              and active = 1);
  --取定購關係
  int_count := 0;
  select count(*)
    into int_count
    from (select t.*
             from tbcs.subs_spservice t
            where t.subsid = n_subsid
              and t.region = n_region
              and t.spid = v_spid
              and t.spbizid = v_spbizid
              and (t.enddate >= sysdate or t.enddate is null)
           union
           select t.*
             from [email protected]_B.HEBEI.MOBILE.COM t
            where t.subsid = n_subsid
              and t.region = n_region
              and t.spid = v_spid
              and t.spbizid = v_spbizid
              and (t.enddate >= sysdate or t.enddate is null));
  if int_count >= 1 then
    v_result := ‘yes‘;
  else
    v_result := ‘no‘;
  end if;
exception
  when my_region_err then
    v_result := ‘region_err‘;
  when my_subsid_err then
    v_result := ‘subsid_err‘;
 
end pro_check_sp;

判斷使用者是否定購某sp業務——預存程序