oracle 函数的应用

--返回日期型

create or replace function getplandate(v_date in date,v_cnt in number)--传入当前时间，期数
    return date
    as
        y number;
        m number;
        d number;
        last_cnt_date date;
        last_cnt_end_date date;
        last_cnt_end_day number;
    begin
     
      y := to_char(v_date,'yyyy');--获取当前时间的年份
      m := to_char(v_date,'mm');--获取当前时间的月份
      d := to_char(v_date,'dd');--获取当前时间的日期
      last_cnt_date := add_months(sysdate,v_cnt);--当前时间追加v_cnt个月的时间格式为 日-月-年
      last_cnt_end_date := LAST_DAY(last_cnt_date);--last_cnt_date时间的最后一天,时间格式为 日-月-年
      last_cnt_end_day := to_char(last_cnt_end_date,'dd');--获取last_cnt_end_date 的日
       if (to_char(LAST_DAY(v_date),'yyyy-mm-dd')=to_char(v_date,'yyyy-mm-dd')) then--当前时间是否为当前月份的最后一期
         return last_cnt_end_date;
       else
         if (last_cnt_end_day <= d) then --判断last_cnt_end_date 的日小于当前时间
           return last_cnt_end_date;
         else
           return to_date(to_char(last_cnt_date,'yyyy-mm')||'-'||d,'yyyy-mm-dd');
         end if;
       end if;
    end;

--返回字符串型

create or replace function nTime(p_applyid                  in number,
                                        p_endstatus                in varchar2,
                                        p_endattachedstatus        in varchar2,
                                        p_credit_endattachedstatus in varchar2,
                                        p_nodecode                 in varchar2,
                                        p_times in number)


return varchar2 --  表示函数的返回类型为varchar2类型
is -- 表示函数体部分
showResult varchar2(100);--  声明零时变量，这是可有可无的，这里声明返回变量为varchar2类型的showResult变量。
begin
  if p_endstatus is not null and p_endattachedstatus is null and p_nodecode is not null  then
        SELECT   id||','||to_char(time,'yyyy/MM/dd HH24:mi:ss') into showResult
         from (
          SELECT ROWNUM RECNO, n.* from
            (select a.*
            from re_approvalflow a where  nodecode = p_nodecode
            and endstatus = p_endstatus
            and applyid = p_applyid order by a.time) n)
           WHERE applyid = p_applyid  and RECNO = p_times;


  elsif p_endstatus is null and p_endattachedstatus is not null and p_nodecode is not null   then
        SELECT id||','||to_char(time,'yyyy/MM/dd HH24:mi:ss') into showResult
         from (
          SELECT ROWNUM RECNO, n.* from
            (select a.*
            from re_approvalflow a where   nodecode = p_nodecode
             and endattachedstatus = p_endattachedstatus
            and applyid = p_applyid order by a.time) n)
           WHERE applyid = p_applyid  and RECNO = p_times;
  elsif p_endstatus is null and p_endattachedstatus is null and
        p_credit_endattachedstatus is not null and p_nodecode is not null then
         SELECT id||','||to_char(time,'yyyy/MM/dd HH24:mi:ss') into showResult
         from (
          SELECT ROWNUM RECNO, n.* from
            (select a.*
            from re_approvalflow a where   nodecode = p_nodecode
            and credit_endattachedstatus = p_credit_endattachedstatus
            and applyid = p_applyid order by a.time) n)
           WHERE applyid = p_applyid  and RECNO = p_times;


  else
     SELECT id||','||to_char(time,'yyyy/MM/dd HH24:mi:ss') into showResult
         from (
          SELECT ROWNUM RECNO, n.* from
            (select a.*
            from re_approvalflow a where   nodecode = p_nodecode
            and endstatus = p_endstatus
            and applyid = p_applyid order by a.time) n)
           WHERE applyid = p_applyid  and RECNO = p_times;
  end if;
  return showResult;
end nTime;

--for循环更新sql，无返回值

create or replace function updatebank return varchar2 as
  r re_contract_payinfo%rowtype;
begin
  for r in (select bank, apply.applyId
              from re_contract_payinfo payinfo
             inner join re_apply apply on apply.applyId = payinfo.applyid
             where apply.status in ('010608','010609','010610','010601','010602','010603','010604','010605','010606','010607','010701','010702','010703','010801','010802','010611','010612','010901')) loop
    if r.bank = '301' then--301
      update re_contract_payinfo set bank = '35' where applyId = r.applyid;
    elsif r.bank = '305' then
      update re_contract_payinfo set bank = '1' where applyId = r.applyid;
    elsif r.bank = '303' then
      update re_contract_payinfo set bank = '2' where applyId = r.applyid;
    end if;
  end loop;
  return null;
end updatebank;

--for循环更新sql，有返回值

create or replace function getclientstatus(p_applyid number) return varchar2 as
  r re_approvalflow%rowtype;
begin
  for r in (select * from re_approvalflow where applyid=p_applyid and endstatus in ('010701','010508','100001','010607') order by time desc) loop
    if r.endstatus='010607' then
      return '1';
    elsif r.endstatus='010701' then
      return '2';
    elsif r.endstatus='010508' then
      return '3';
    elsif r.endstatus='100001' then
      return '4';
    end if;
  end loop;
  return '5';
end getclientstatus;