Oracle 检查星期只能是1-7的数字不能重复

1、利用Oracle的REGEXP_INSTR匹配数字

select REGEXP_INSTR ('1357', '^[1-7]{1,7}$') into n_Count from dual;

2、循环查找是否存在重复的数字

    for i in 1..length(i_vc_Week) loop
        v_findStr:=substr(i_vc_Week,i,1);
        if nvl(instr(i_vc_Week,v_findStr,(i+1)),0)>0 then
          return 2;
        end if;
     end loop;

完整方法

  FUNCTION P_SR_CheckWeekLegal(i_vc_Week varchar2)
  return integer
  IS
     n_Count integer;
     v_findStr varchar2(1);
  BEGIN
     n_Count:=0;
     select REGEXP_INSTR (i_vc_Week, '^[1-7]{1,7}$') into n_Count from dual;
     if n_Count=0 then
       return 2;
     end if;
     for i in 1..length(i_vc_Week) loop
        v_findStr:=substr(i_vc_Week,i,1);
        if nvl(instr(i_vc_Week,v_findStr,(i+1)),0)>0 then
          return 2;
        end if;
     end loop;
         return n_Count;
    END;

 

参考 https://docs.oracle.com/database/121/SQLRF/functions162.htm#SQLRF06300