DB2 自定义函数（2）

SET SCHEMA TMISUSR;


SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";


CREATE FUNCTION "TMISUSR"."F_DEBTGETTRELIST"
 ("PI_I_YEAR" INTEGER,
  "PI_S_TREAREA" VARCHAR(1024),
  "PI_S_TRELEVEL" CHARACTER(1)
 ) 
  RETURNS VARCHAR(1024)
  SPECIFIC "TMISUSR"."SQL160504163209908"
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  NO EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC
    declare v_s_rst varchar(512) default ' AND 1=1 ';
      --
    if pi_s_TreArea<>'' then
      set v_s_rst =
        case
          when pi_s_TreLevel='1'
            then ' '
          when pi_s_TreLevel='2'
            then ' AND a.s_OfProvTreCodeDebt in ( '||pi_s_TreArea ||' ) '
          when pi_s_TreLevel='3'
            then 
              ' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt and b.s_trecode  in ( '
              ||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS 
              CHAR(4))||' '
          when pi_s_TreLevel='4'
            then 
              ' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and b.s_trecode in ( '
              ||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS 
              CHAR(4))||' '
          else 
            ' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and a.s_oftrecode = b.s_trecode and b.s_trecode in ( '
            ||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS CHAR
            (4))||' '
        end;
      --
    end if;
    --
    return v_s_rst;
  --
  END;