oracle:自定义函数

 

CREATE OR REPLACE FUNCTION GETDAY_HLastDAY (D_Stand_Date date)
 return Date as
 D_Return Date; /*返回的日期*/
 begin
   D_Return := D_Stand_Date;


   /*如果当天是工作日,下一天也是工作日*/
   if (isholiday('XSHE',D_Stand_Date)=0 AND isholiday('XSHE',getday_hday('CN',D_Stand_Date,1, 'D'))=0) then
     return D_Return;
   end if;

   while (true) loop
       D_Return := D_Return + 1;
       /*如果是节假日的最后一天*/
       if (isholiday('XSHE',D_Return)=1 AND isholiday('XSHE',getday_hday('CN',D_Return,1, 'D'))=0) then
         exit; /*退出循环*/
      end if;  -- if then 表示开始, end if;表示结束,要成对出现
      
   end loop;
   return D_Return;

  -- 异常处理,捕捉异常
  exception when others then D_Return := D_Stand_Date;
    return D_Return;
    
 end;  -- 结束的end要加上分号

 

 oracle中使用if  then  elsif  then end if

CREATE OR REPLACE FUNCTION GETFJKMBYMAP
( var_len     IN NUMBER,     -- 长度
  var_fjjdm   IN VARCHAR2,   -- 组合代码
  var_Km_Code IN VARCHAR2    -- 4.5父级科目代码
) return VARCHAR2 is
  R_FJ_KM VARCHAR2(50);  -- 3.0父级科目
  R_KM VARCHAR2(50);
begin
  begin

  select facctcode into R_KM from md_km_map a where a.fjjdm = var_fjjdm and substr(c_km_code,0,var_len) = var_Km_Code
       and trim(facctcode) is not null and rownum = 1;

  exception when no_data_found
       then R_FJ_KM := var_Km_Code;
  end;


begin
    if (var_len = 4) then
      R_FJ_KM := substr(R_KM,0,4);  -- 1级
    elsif (var_len = 7) then       -- 2级
      R_FJ_KM := substr(R_KM,0,6);
    elsif (var_len = 10) then     -- 3级
      R_FJ_KM := substr(R_KM,0,8);
    elsif (var_len = 13) then       -- 4级
      R_FJ_KM := substr(R_KM,0,10);
    elsif (var_len = 17) then       -- 4级
      R_FJ_KM := substr(R_KM,0,13);
    else
      R_FJ_KM := var_Km_Code;
    end if;
    
    exception when no_data_found
       then R_FJ_KM := var_Km_Code;
end;

  return R_FJ_KM;
  
end GETFJKMBYMAP;