Oracle 自定义函数

计算两个日期间工作日

create or replace function f_pd_gzr_days(v_kssj in date, v_jssj in date)
  return number is
  v_days     number := 0;
  v_days_all number := 0;
  v_days_jjr number := 0;
  v_real_kssj date  := v_kssj;
  v_real_jssj date  := v_jssj;
  v_kssj_jjr number := 0;
  v_jssj_jjr number := 0;
begin
  -- 计算两个日期之间的天数
  if v_kssj >= v_jssj then -- 如果起始时间大于结束时间，将起始时间与结束时间替换
    select v_real_kssj, v_real_jssj into v_real_jssj, v_real_kssj from dual;
  end if;
  
  begin
      
     -- 如果开始时间是节假日，开始时间改为当日24点
    select count(1) into v_kssj_jjr
      from sys_holiday
     where to_date(holiday, 'yyyy/mm/dd') = trunc(v_real_kssj);
       
     if v_kssj_jjr != 0 then
       v_real_kssj := trunc(v_real_kssj + 1);
     end if;
       
     -- 如果结束时间是节假日，结束时间改为当日零点
    select count(1) into v_jssj_jjr
      from sys_holiday
     where to_date(holiday, 'yyyy/mm/dd') = trunc(v_real_jssj);
       
     if v_jssj_jjr != 0 then
       v_real_jssj := trunc(v_real_jssj);
     end if;
       
     -- 计算两日期间相隔天数
     v_days_all := v_real_jssj - v_real_kssj;
     
    select count(1) into v_days_jjr
      from sys_holiday
     where to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') >= v_real_kssj
       and to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') < v_real_jssj;
         
  exception
    when others then
      v_days := 0;
  end;
  
  v_days := v_days_all - v_days_jjr;

  return v_days;
exception
  when others then
    return 0;
end f_pd_gzr_days;

 

计算若干工作日之后的日期

create or replace function f_get_after_day(v_kssj date, v_delay number)
return date is
v_real_kssj date  := v_kssj;
v_real_jssj date;
v_kssj_jjr number := 0;
v_jssj_jjr number := 0;
v_days_jjr number := 0;
begin

  -- 如果开始时间是节假日，开始时间改为当日24点
  select count(1) into v_kssj_jjr
    from sys_holiday
   where to_date(holiday, 'yyyy/mm/dd') = trunc(v_real_kssj);
         
   if v_kssj_jjr != 0 then
     v_real_kssj := trunc(v_real_kssj + 1);
   end if;
   
  v_real_jssj := v_real_kssj + v_delay;
   
    
  -- 如果结束时间是节假日，结束时间改为次日
  select count(1) into v_jssj_jjr
    from sys_holiday
   where to_date(holiday, 'yyyy/mm/dd') = trunc(v_real_jssj);
           
   if v_jssj_jjr != 0 then
     v_real_jssj := v_real_jssj + 1;
     -- 计算两日期之间节假日的天数, 减去结束日期
    select count(1) - 1 into v_days_jjr from sys_holiday
    where to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') >= v_real_kssj
      and to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') < v_real_jssj;
   else
    -- 计算两日期之间节假日的天数
    select count(1) into v_days_jjr from sys_holiday
    where to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') >= v_real_kssj
      and to_date(holiday, 'yyyy-mm-dd hh24:mi:ss') < v_real_jssj;
   end if;

    
  if v_days_jjr !=0 then
    -- 迭代
    v_real_jssj := f_get_after_day(v_real_jssj,v_days_jjr);
  end if;

return v_real_jssj;
end f_get_after_day;

 

十进制转三十六进制

CREATE OR REPLACE FUNCTION FN_10_TO_36(V_NUM NUMBER)
RETURN VARCHAR IS RESULT VARCHAR(800);
  NUM   NUMBER;
  TMP  NUMBER;
  TEMP  NUMBER;
BEGIN
NUM := V_NUM;
TMP := TRUNC(NUM / 36);
   WHILE NUM > 0 LOOP
    TEMP := MOD(NUM, 36);
    IF TEMP < 10 THEN
      RESULT := TEMP || RESULT;
    ELSE
      RESULT := CHR(TEMP + 55) || RESULT;
    END IF;
    NUM := TMP;
    TMP := TRUNC(NUM / 36);
  END LOOP;
  IF V_NUM <= 0 THEN
     return V_NUM;
     END IF;
return RESULT;
end;

 

获取拼音码

CREATE OR REPLACE FUNCTION F_TRANS_PINYIN_CAPITAL(P_NAME IN VARCHAR2)
  RETURN VARCHAR2 AS
  V_COMPARE VARCHAR2(100);
  V_RETURN  VARCHAR2(4000);

  FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
  END;
BEGIN
  FOR I IN 1 .. LENGTH(P_NAME) LOOP
    V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
    IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN
      V_RETURN := V_RETURN || 'a';
    ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN
      V_RETURN := V_RETURN || 'b';
    ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN
      V_RETURN := V_RETURN || 'c';
    ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN
      V_RETURN := V_RETURN || 'd';
    ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN
      V_RETURN := V_RETURN || 'e';
    ELSIF V_COMPARE >= F_NLSSORT('发') AND V_COMPARE <= F_NLSSORT('猤') THEN
      V_RETURN := V_RETURN || 'f';
    ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN
      V_RETURN := V_RETURN || 'g';
    ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN
      V_RETURN := V_RETURN || 'h';
    ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN
      V_RETURN := V_RETURN || 'j';
    ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN
      V_RETURN := V_RETURN || 'k';
    ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN
      V_RETURN := V_RETURN || 'l';
    ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN
      V_RETURN := V_RETURN || 'm';
    ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN
      V_RETURN := V_RETURN || 'n';
    ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN
      V_RETURN := V_RETURN || 'o';
    ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN
      V_RETURN := V_RETURN || 'p';
    ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN
      V_RETURN := V_RETURN || 'q';
    ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN
      V_RETURN := V_RETURN || 'r';
    ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN
      V_RETURN := V_RETURN || 's';
    ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN
      V_RETURN := V_RETURN || 't';
    ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN
      V_RETURN := V_RETURN || 'w';
    ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN
      V_RETURN := V_RETURN || 'x';
    ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN
      V_RETURN := V_RETURN || 'y';
    ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN
      V_RETURN := V_RETURN || 'z';
    ELSE V_RETURN := V_RETURN || SUBSTR(P_NAME, I, 1);
    END IF;
  END LOOP;
  RETURN UPPER(V_RETURN);
END;