创建自定义数组cron_type_number
本方法中用到了自定义数组，需要单独创建
create or replace type cron_type_number as table of number(4);
创建函数cron_getnexttimeafter()
1 create or replace function cron_getnexttimeafter(cron varchar2,start_time varchar2 default to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')) 2 return varchar2 3 as 4 result_time varchar2(1024);--返回结果 5 type l_cron_type_number is table of number(4) index by binary_integer; 6 type cron_type_array is table of varchar2(128) index by binary_integer; 7 cron_var varchar2(1024); 8 temp_num number; 9 cron_seconds varchar2(512);--秒 10 cron_minutes varchar2(512);--分 11 cron_hours varchar2(512);--时 12 cron_day varchar2(512);--日 13 cron_month varchar2(512);--月 14 cron_week varchar2(512);--星 15 cron_year varchar2(512);--年 16 17 array_seconds cron_type_number;--秒的数组 18 array_minutes cron_type_number;--分的数组 19 array_hours cron_type_number;--时的数组 20 array_day cron_type_number;--日的数组 21 array_month cron_type_number;--月的数组 22 array_week cron_type_number;--星的数组 23 array_year cron_type_number;--年的数组 24 25 start_seconds number default -1;--开始时间-秒 26 start_minutes number default -1;--开始时间-分 27 start_hours number default -1;--开始时间-时 28 start_day number default -1;--开始时间-日 29 start_month number default -1;--开始时间-月 30 start_week_1 number default -1;--开始时间-星-星期几 31 start_week_2 number default -1;--开始时间-星-第几个 32 start_year number default -1;--开始时间-年 33 34 next_seconds number default -1;--预期时间-秒 35 next_minutes number default -1;--预期时间-分 36 next_hours number default -1;--预期时间-时 37 next_day number default -1;--预期时间-日 38 next_month number default -1;--预期时间-月 39 next_week_1 number default -1;--预期时间-星-星期几 40 next_week_2 number default -1;--预期时间-星-第几个 41 next_year number default -1;--预期时间-年 42 43 l_debug_str varchar2(1000); 44 l_month_day number; 45 46 ------------------------------------------------------------------------------------------------------ 47 --根据','分割字符串，生成list 48 function cron_get_str(str varchar2) 49 return cron_type_array is 50 temp_result_array cron_type_array; 51 begin 52 for i in 1..regexp_count(str,'[^,]+')+1 loop 53 temp_result_array(i) := regexp_substr(str,'[^,]+',1,i); 54 end loop; 55 return temp_result_array; 56 end; 57 ------------------------------------------------------------------------------------------------------ 58 --解析字符串，返回运行数组 59 function cron_get_array(str varchar2,min_num number,max_num number) 60 return l_cron_type_number is 61 temp_result_array_number l_cron_type_number; 62 num number default 1; 63 i number default 0; 64 temp_str_1 varchar2(128); 65 temp_str_2 varchar2(128); 66 temp_str_3 varchar2(128); 67 temp_str_4 varchar2(128); 68 temp_min_num number default -1;--周期开始值 69 temp_max_num number default -1;--周期结束值 70 temp_mod_num number default 1;--周期频率/默认为1 71 begin 72 --start获取周期的开始值/结束值 73 ----值为"*"或"?"时，取该字段最大最小值之间的所有值 74 if str = '*' or str = '?' then 75 temp_min_num := min_num; 76 temp_max_num := max_num; 77 ----值为"数字"时，取该数字 78 elsif regexp_like(str,'^[0-9]+$') then 79 if to_number(str) between min_num and max_num then 80 temp_min_num := to_number(str); 81 temp_max_num := to_number(str); 82 else 83 raise_application_error(-20001,'值的范围为'||min_num||'"-'||max_num||'"'); 84 end if; 85 ----值包含"/"时，根据"/"切割字符串，前半部分为范围，后半部分为频率 86 elsif str like '%/%' then 87 temp_str_1 := regexp_substr(str,'[^/]+',1,1); 88 temp_str_2 := regexp_substr(str,'[^/]+',1,2); 89 if regexp_like(temp_str_2,'^[0-9]+$') and (to_number(temp_str_2) between 1 and max_num) then 90 temp_mod_num := to_number(temp_str_2); 91 if temp_str_1 is null or temp_str_1='*' then 92 temp_min_num := min_num; 93 temp_max_num := max_num; 94 elsif regexp_like(temp_str_1,'^[0-9]+$') and (to_number(temp_str_1)<min_num or to_number(temp_str_1)>max_num) then 95 raise_application_error(-20001,'符号"/"前的值范围为"'||min_num||'-'||max_num||'"'); 96 elsif regexp_like(temp_str_1,'^[0-9]+$') and (to_number(temp_str_1) between min_num and max_num) then 97 temp_min_num := to_number(temp_str_1); 98 temp_max_num := max_num; 99 elsif temp_str_1 like '%-%' then 100 temp_str_3 := regexp_substr(temp_str_1,'[^-]+',1,1); 101 temp_str_4 := regexp_substr(temp_str_1,'[^-]+',1,2); 102 if regexp_like(temp_str_3,'^[0-9]+$') and regexp_like(temp_str_4,'^[0-9]+$') and (to_number(temp_str_3) between min_num and max_num) and (to_number(temp_str_4) between min_num and max_num) then 103 if to_number(temp_str_3)<=to_number(temp_str_4) then 104 temp_min_num := to_number(temp_str_3); 105 temp_max_num := to_number(temp_str_4); 106 else 107 temp_min_num := to_number(temp_str_3); 108 temp_max_num := to_number(temp_str_4)+max_num-min_num+1;--此处是为了处理秒分时的开始值是0，日月年星的开始值是1，当开始值是0时加1，当开始值是1时不变 109 end if; 110 else raise_application_error(-20001,'符号"/"前的值格式错误'); 111 end if; 112 else raise_application_error(-20001,'符号"/"前的值格式错误'); 113 end if; 114 else raise_application_error(-20001,'符号"/"后必须有数字格式的值，且必须>1'||'、<='||max_num); 115 end if; 116 ----值包含"-"时，取两个值之间的所有值，频率为1 117 elsif str like '%-%' then 118 temp_str_1 := regexp_substr(str,'[^-]+',1,1); 119 temp_str_2 := regexp_substr(str,'[^-]+',1,2); 120 if regexp_like(temp_str_1,'^[0-9]+$') and regexp_like(temp_str_2,'^[0-9]+$') and (to_number(temp_str_1) between min_num and max_num) and (to_number(temp_str_2) between min_num and max_num) then 121 if to_number(temp_str_1)<=to_number(temp_str_2) then 122 temp_min_num := to_number(temp_str_1); 123 temp_max_num := to_number(temp_str_2); 124 else 125 temp_min_num := to_number(temp_str_1); 126 temp_max_num := to_number(temp_str_2)+max_num-min_num+1;--此处是为了处理秒分时的开始值是0，日月年星的开始值是1，当开始值是0时加1，当开始值是1时不变 127 end if; 128 else raise_application_error(-20001,'符号"-"前的值格式错误'); 129 end if; 130 else 131 return temp_result_array_number; 132 end if; 133 --end获取周期的开始值/结束值 134 --start获取周期数组 135 i := temp_min_num; 136 while i<=temp_max_num loop 137 if i<=max_num then 138 temp_result_array_number(num) := i; 139 else 140 temp_result_array_number(num) := i-max_num+min_num-1;--此处是为了处理秒分时的开始值是0，日月年星的开始值是1 141 end if; 142 i := i+temp_mod_num; 143 num := num+1; 144 end loop; 145 --end获取周期数组 146 return temp_result_array_number; 147 end; 148 ------------------------------------------------------------------------------------------------------ 149 --获取运行数组 150 function cron_get_str_array(str varchar2,min_num number,max_num number) 151 return cron_type_number is 152 temp_result_array cron_type_number; 153 num number default 1; 154 temp_array_1 cron_type_array;--临时数组1 155 temp_array_2 l_cron_type_number;--临时数组2 156 begin 157 temp_result_array := cron_type_number(); 158 temp_array_1 := cron_get_str(str); 159 for i in 1..temp_array_1.count loop 160 temp_array_2 := cron_get_array(temp_array_1(i),min_num,max_num); 161 temp_result_array.extend(temp_array_2.count); 162 for j in 1..temp_array_2.count loop 163 temp_result_array(num) := temp_array_2(j); 164 num := num+1; 165 end loop; 166 end loop; 167 return temp_result_array; 168 end; 169 ------------------------------------------------------------------------------------------------------ 170 --根据运行数组/开始时间的实际值，获取下次运行的值 171 function cron_get_next_value(cron_array cron_type_number,start_num number) 172 return number 173 as 174 result_num number; 175 begin 176 select min(to_number(column_value)) into result_num from table(cron_array) where to_number(column_value)>=start_num; 177 if result_num is null then 178 select min(to_number(column_value)) into result_num from table(cron_array); 179 end if; 180 return result_num; 181 end; 182 ------------------------------------------------------------------------------------------------------ 183 --翻译替换，将月份中的英文字符转换为数字 184 function cron_replace_month(str varchar2) 185 return varchar2 186 as 187 result_str varchar2(128); 188 begin 189 result_str := replace(str,'JAN','1'); 190 result_str := replace(result_str,'FEB','2'); 191 result_str := replace(result_str,'MAR','3'); 192 result_str := replace(result_str,'APR','4'); 193 result_str := replace(result_str,'MAY','5'); 194 result_str := replace(result_str,'JUN','6'); 195 result_str := replace(result_str,'JUL','7'); 196 result_str := replace(result_str,'AUG','8'); 197 result_str := replace(result_str,'SEP','9'); 198 result_str := replace(result_str,'OCT','10'); 199 result_str := replace(result_str,'NOV','11'); 200 result_str := replace(result_str,'DEC','12'); 201 return result_str; 202 end; 203 ------------------------------------------------------------------------------------------------------ 204 --翻译替换，将星期中的英文字符转换为数字，特别说明英文中星期日为一周的开始 205 function cron_replace_week(str varchar2) 206 return varchar2 207 as 208 result_str varchar2(128); 209 begin 210 result_str := replace(str,'SUN','1'); 211 result_str := replace(result_str,'MON','2'); 212 result_str := replace(result_str,'TUE','3'); 213 result_str := replace(result_str,'WED','4'); 214 result_str := replace(result_str,'THU','5'); 215 result_str := replace(result_str,'FRI','6'); 216 result_str := replace(result_str,'SAT','7'); 217 return result_str; 218 end; 219 ------------------------------------------------------------------------------------------------------ 220 --校验时间格式是否正确 221 function cron_is_date(str VARCHAR2) 222 return number IS 223 val date; 224 begin 225 val := TO_DATE(NVL(str, 'w'), 'yyyy-mm-dd hh24:mi:ss'); 226 return 1; 227 exception 228 when others then 229 return 0; 230 end; 231 ------------------------------------------------------------------------------------------------------ 232 ------------------------------------------------------------------------------------------------------ 233 begin 234 --校验开始时间格式 235 if cron_is_date(start_time)=1 then 236 start_seconds := substr(start_time,18,2)+1; 237 start_minutes := substr(start_time,15,2); 238 start_hours := substr(start_time,12,2); 239 start_day := substr(start_time,9,2); 240 start_month := substr(start_time,6,2); 241 --start_week_1 := to_char(to_date(start_time,'yyyy-MM-dd hh24:mi:ss'),'D'); 242 --start_week_2 := to_char(to_date(start_time,'yyyy-MM-dd hh24:mi:ss'),'W'); 243 start_year := substr(start_time,1,4); 244 next_year := substr(start_time,1,4); 245 else raise_application_error(-20001,'开始时间格式错误,正确格式为"yyyy-MM-dd hh24:mi:ss"'); 246 end if; 247 248 --首尾两端去空格/去空白符换行符/去两个以上空格/转大写 249 cron_var := upper(regexp_replace(replace(replace(replace(trim(cron),chr(9),''),chr(10),''),chr(13),''),'( ){2,}',' ')); 250 251 --判断格式是否合规，否则提示异常 252 if regexp_count(cron_var,' ') is null or regexp_count(cron_var,' ') not in (5,6) then 253 raise_application_error(-20001,'定时字符串格式错误'); 254 end if; 255 256 --取值，根据空格分别获取 257 cron_seconds := regexp_substr(cron_var,'[^ ]+',1,1);--秒 258 cron_minutes := regexp_substr(cron_var,'[^ ]+',1,2);--分 259 cron_hours := regexp_substr(cron_var,'[^ ]+',1,3);--时 260 cron_day := regexp_substr(cron_var,'[^ ]+',1,4);--日 261 --dbms_output.put_line('cron_day:'||cron_day); 262 263 cron_month := cron_replace_month(regexp_substr(cron_var,'[^ ]+',1,5));--月 264 cron_week := cron_replace_week(regexp_substr(cron_var,'[^ ]+',1,6));--星 265 cron_year := nvl(regexp_substr(cron_var,'[^ ]+',1,7),'*');--年 266 --正则校验格式是否正确 267 268 269 --获取预期的运行数组 270 --为了提高效率，本函数将年的范围限定为1949年-2049年，如果有实际需要，可以酌情调整 271 array_seconds := cron_get_str_array(cron_seconds,0,59); 272 array_minutes := cron_get_str_array(cron_minutes,0,59); 273 array_hours := cron_get_str_array(cron_hours,0,23); 274 275 select to_char(last_day(to_date(substr(start_time,1,10),'yyyy-mm-dd')),'dd') into l_month_day from dual; 276 array_day := cron_get_str_array(cron_day,1,l_month_day); 277 278 select wm_concat(column_value) into l_debug_str from table(array_day); 279 --dbms_output.put_line('array_day:'|| to_char(l_debug_str)); 280 281 array_month := cron_get_str_array(cron_month,1,12); 282 array_week := cron_get_str_array(cron_week,1,7); 283 array_year := cron_get_str_array(cron_year,1949,2049); 284 285 --------------------------------------------------------------------------------------------------------------------- 286 --初始化超范围的下级 287 <<goto_day_month_year>> 288 select count(0) into temp_num from table(array_year) where column_value=start_year; 289 if temp_num = 0 then 290 select min(column_value) into start_month from table(array_month); 291 select min(column_value) into start_day from table(array_day); 292 select min(column_value) into start_hours from table(array_hours); 293 select min(column_value) into start_minutes from table(array_minutes); 294 select min(column_value) into start_seconds from table(array_seconds); 295 end if; 296 select count(0) into temp_num from table(array_month) where column_value=start_month; 297 if temp_num = 0 then 298 select min(column_value) into start_day from table(array_day); 299 select min(column_value) into start_hours from table(array_hours); 300 select min(column_value) into start_minutes from table(array_minutes); 301 select min(column_value) into start_seconds from table(array_seconds); 302 end if; 303 select count(0) into temp_num from table(array_day) where column_value=start_day; 304 if temp_num = 0 then 305 select min(column_value) into start_hours from table(array_hours); 306 select min(column_value) into start_minutes from table(array_minutes); 307 select min(column_value) into start_seconds from table(array_seconds); 308 end if; 309 select count(0) into temp_num from table(array_hours) where column_value=start_hours; 310 if temp_num = 0 then 311 select min(column_value) into start_minutes from table(array_minutes); 312 select min(column_value) into start_seconds from table(array_seconds); 313 end if; 314 select count(0) into temp_num from table(array_minutes) where column_value=start_minutes; 315 if temp_num = 0 then 316 select min(column_value) into start_seconds from table(array_seconds); 317 end if; 318 --------------------------------------------------------------------------------------------------------------------- 319 --获取预期的运行时间 320 next_seconds := cron_get_next_value(array_seconds,start_seconds); 321 if start_seconds> next_seconds then 322 start_minutes := start_minutes+1; 323 end if; 324 next_minutes := cron_get_next_value(array_minutes,start_minutes); 325 if start_minutes> next_minutes then 326 start_hours := start_hours+1; 327 end if; 328 next_hours := cron_get_next_value(array_hours,start_hours); 329 if start_hours> next_hours then 330 start_day := start_day+1; 331 end if; 332 next_day := cron_get_next_value(array_day,start_day); 333 if start_day> next_day then 334 start_month := start_month+1; 335 end if; 336 next_month := cron_get_next_value(array_month,start_month); 337 --next_week_1 := cron_get_next_value(array_week,start_week_1); 338 if start_month> next_month then 339 start_year := start_year+1; 340 end if; 341 select min(to_number(column_value)) into next_year from table(array_year) where to_number(column_value)>=start_year; 342 if next_year is null then 343 return ''; 344 end if; 345 --判断日期是否合法，不合法则以此为新的开始时间重新计算 346 if next_year<=array_year(array_year.last) and ((mod(next_year,4)!=0 and next_month=2 and next_day=29) or (next_month=2 and next_day=30) or (next_month in(2,4,6,9,11) and next_day=31)) then 347 start_year := next_year; 348 start_month := next_month+1; 349 start_day := 1; 350 goto goto_day_month_year; 351 end if; 352 result_time := lpad(next_year,4,'0')||'-'||lpad(next_month,2,'0')||'-'||lpad(next_day,2,'0')||' '||lpad(next_hours,2,'0')||':'||lpad(next_minutes,2,'0')||':'||lpad(next_seconds,2,'0'); 353 --dbms_output.put_line(result_time); 354 --判断生成的时间是否合法、是否小于开始时间，如果小于开始时间，则返回为空 355 if cron_is_date(result_time)=0 or result_time<=start_time then 356 result_time := ''; 357 end if; 358 return result_time; 359 end;
调用示例
--因为不存在2月31日，所以执行结果为空 select cron_getnexttimeafter('0 0/1 1 31 2 ? 2019-2029') from dual;
--执行结果为‘2020-02-02 01:00:00’ select cron_getnexttimeafter('0 0/1 1 2 2 ? 2019-2029','2019-07-07 17:22:00') from dual;
--执行结果为当前时间的下一个3月7日0点0分0秒 select cron_getnexttimeafter('0 0 0 7 3 ?') from dual;
注（2020-07-02）：摘自别人的，用了一段时间之后出现了bug，已修复（之前写死了1个月=31天，有部分月份就会跳过1号）
顺便写了个测试函数，以后配置时，先测下再放，要放心一些（不过测试边际条件不足，后续会持续弥补）：
1 create or replace function test_cron_getnexttimeafter(cron in varchar2, 2 times in number default 10) 3 return varchar2 is 4 l_cross_day varchar2(32) := '2020-07-01 23:59:50'; -- 跨天 开始时间格式错误,正确格式为"yyyy-MM-dd hh24:mi:ss" 5 6 l_cross_mon_2_28 varchar2(32) := '2019-02-28 23:59:55'; -- 跨平2月(28天) 7 l_cross_mon_2_29 varchar2(32) := '2020-02-29 23:59:55'; -- 跨闰2月(29天) 8 l_cross_mon_30 varchar2(32) := '2020-06-30 23:59:55'; -- 跨小月(30天) 9 l_cross_mon_31 varchar2(32) := '2020-03-31 23:59:55'; -- 跨大月(31天) 10 11 l_cross_year varchar2(32) := '2020-12-31 23:59:55'; -- 跨年 12 13 l_temp_result varchar2(32) := ''; 14 begin 15 16 dbms_output.put_line('跨天测试 : ' || l_cross_day); 17 dbms_output.put_line('============================'); 18 l_temp_result := l_cross_day; 19 for i in 1 .. times loop 20 21 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 22 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 23 24 end loop; 25 26 dbms_output.put_line(''); 27 28 dbms_output.put_line('跨平2月(28天) : ' || l_cross_mon_2_28); 29 dbms_output.put_line('============================'); 30 l_temp_result := l_cross_mon_2_28; 31 for i in 1 .. times loop 32 33 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 34 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 35 36 end loop; 37 38 dbms_output.put_line(''); 39 40 dbms_output.put_line('跨闰2月(29天) : ' || l_cross_mon_2_29); 41 dbms_output.put_line('============================'); 42 l_temp_result := l_cross_mon_2_29; 43 for i in 1 .. times loop 44 45 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 46 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 47 48 end loop; 49 50 dbms_output.put_line(''); 51 52 dbms_output.put_line('跨小月(30天) : ' || l_cross_mon_30); 53 dbms_output.put_line('============================'); 54 l_temp_result := l_cross_mon_30; 55 for i in 1 .. times loop 56 57 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 58 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 59 60 end loop; 61 62 dbms_output.put_line(''); 63 64 dbms_output.put_line('跨小月(31天) : ' || l_cross_mon_31); 65 dbms_output.put_line('============================'); 66 l_temp_result := l_cross_mon_31; 67 for i in 1 .. times loop 68 69 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 70 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 71 72 end loop; 73 74 dbms_output.put_line(''); 75 76 dbms_output.put_line('跨年 : ' || l_cross_year); 77 dbms_output.put_line('============================'); 78 l_temp_result := l_cross_year; 79 for i in 1 .. times loop 80 81 l_temp_result := cron_getnexttimeafter(cron, l_temp_result); 82 dbms_output.put_line('第 ' || lpad(i,3,'0') || ' 次 : ' || l_temp_result); 83 84 end loop; 85 86 return '请点击输出查看结果是否符合预期！'; 87 end;