缺少数字脚本将无法正常工作?

本文介绍了缺少数字脚本将无法正常工作?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

此脚本将从以下字符串中获取序列，年，月和日...然后从序列部分中找到丢失的数字，例如(1111,1112,115，..等)问题是此脚本的输出不正确，不是DBMS缺少数字

This script will get the sequence,year,month and day from the following string... then find the missing number from the sequence part like (1111,1112,115,.. etc)the problem is that the output of this script are not correct does not DBMS the missing numbers

emp-1112_14_01_01_1612_G1

emp-1115_14_01_01_1109_G1

emp-1116_14_01_01_1315_G1

emp-1118_14_01_01_0910_G2

emp-1121_14_01_01_1105_G1

emp-1111_14_01_01_1120_G2

输出结果应该是这样

缺少号码1114

缺少电话1117

缺少号码1118

缺少号码1119

丢失号码1120

declare 
  v_name          table1.ENAME%TYPE;
  V_seq           NUMBER (4);
  V_Year          number(2);
  V_Month         number (2);
  V_day           number (2);
  max_seq         number(4);
  min_seq         number(4);

  CURSOR List_ENAME_cur IS
  SELECT ENAME from table1
  WHERE status = 2;
begin 

  FOR List_ENAME_rec IN List_ENAME_cur loop
    if REGEXP_LIKE(List_ENAME_cur.ENAME,'emp[-][1-9]{4}[_][1-9]{2}[_][1-9]{2}[_][1-9]{2}[_][0-9]{4}[_][G]["1"]') then 
      V_seq := substr(List_ENAME_cur.ename,5,4);
      V_Year := substr(List_ENAME_cur.ename,10,2);
      V_Month := substr(List_ENAME_cur.ename,13,2);
      V_day := substr(List_ENAME_cur.ename,16,2);


      if min_seq is null or V_seq_FILENAME < min_seq then
        min_seq := V_seq_FILENAME;
        DBMS_OUTPUT.PUT_LINE('Missing number '||min_seq );
      end if;

      if max_seq is null or V_seq_FILENAME > max_seq then
        max_seq := V_seq_FILENAME;
        DBMS_OUTPUT.PUT_LINE('Missing number '||max_seq );
      end if;

    end if;
  end loop;    
  DBMS_OUTPUT.PUT_LINE('max_seq '||max_seq||' min_seq '||min_seq);    
end;

推荐答案

查看此示例

WITH ORDH
     AS (SELECT 1111 AS ORDERNO FROM DUAL
         UNION ALL
         SELECT 1112 AS ORDERNO FROM DUAL
         UNION ALL
         SELECT 1115 AS ORDERNO FROM DUAL
         UNION ALL
         SELECT 1116 AS ORDERNO FROM DUAL
         UNION ALL
         SELECT 1118 AS ORDERNO FROM DUAL
         UNION ALL
         SELECT 1121 AS ORDERNO FROM DUAL),
     GOT_NEXT_ORDERNO
     AS (SELECT ORDERNO,
                LEAD ( ORDERNO ) OVER (ORDER BY ORDERNO) AS NEXT_ORDERNO
         FROM   ORDH)
SELECT ORDERNO + 1 AS FROM_NO, NEXT_ORDERNO - 1 AS TO_NO
FROM   GOT_NEXT_ORDERNO
WHERE  ORDERNO + 1 <> NEXT_ORDERNO;

   FROM_NO      TO_NO
---------- ----------
      1113       1114
      1117       1117
      1119       1120

3 rows selected.

这篇关于缺少数字脚本将无法正常工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Emp