Some Samples Of Oracle Function In Order To Relax Java Program.

Some Samples Of Oracle Function In Order To Relax Java Program. 

1.判断一个数是否大于另一个数，大于返回否，小于返回是，相当于java中的三目运算：limit_time>15 ？'否':'是';

create or replace function upLoadOrNot4Baojian(limit_time number) return varchar2 is
    Returns   varchar2(10);  
  begin  
    if  limit_time>15  then
    Returns := '否';
    end if;
   if limit_time<=15 then
    Returns := '是';
    end if;
     return Returns;
    
  end upLoadOrNot4Baojian;

2.返回一个需要15个工作日完成，而剩余多少个工作日的function，参数p_slsj 为业务开始的时间，t_working_date 是关于工作日的表，working_day_flag为1表示是工作日，为0表示不是工作日。

create or replace function getLimitDays4Baojian(p_slsj in varchar2) return varchar2
  IS
    var_limitDays        varchar2(50); 
    v_limitDays          t_shouli.gdblsx%TYPE;
  CURSOR c_limitDays IS
     select 15-to_char(count(*)) as limitDays 
     from  t_working_date tw
     where (tw.ad_date between to_date(p_slsj,'yyyy-mm-dd') and sysdate)
     and tw.working_day_flag<>'0';   
begin
    var_limitDays :=' ';
    open c_limitDays;
    loop
       FETCH c_limitDays INTO v_limitDays;
       EXIT WHEN c_limitDays%NOTFOUND;   
        if  v_limitDays>=0 then
            var_limitDays:='剩余'||v_limitDays||'工作日';
            else
            var_limitDays:='超出'||-v_limitDays||'工作日';
        end if;
    end loop;
  CLOSE c_limitDays;
  return(var_limitDays);
exception
  when others then
    v_limitDays :='';
  return(var_limitDays);
end getLimitDays4Baojian;

3.将取得的几行数据的‘名称’连在一起，用'、'分格开这些名称，返回这个总的名称。

create or replace function getBuildingName(p_buildingId in varchar2) return varchar2
  IS
    var_buildingName        varchar2(2000);
    v_building_name    t_building.building_name%TYPE;
  CURSOR c_building_name IS
      select tb.building_name from t_building tb where
      tb.project_id=(select t.project_id from t_building t where t.id=p_buildingId);   
begin
    var_buildingName :=' ';
    open c_building_name;
    loop
        FETCH c_building_name INTO v_building_name;
        EXIT WHEN c_building_name%NOTFOUND;   
         if  var_buildingName=' ' then
             var_buildingName:=v_building_name;
         else
            var_buildingName:= var_buildingName||'、'||v_building_name;
         end if;
    end loop;
  CLOSE c_building_name;
  return(var_buildingName);
exception
  when others then
    var_buildingName :='';
  return(var_buildingName);
end getBuildingName;