sql - 在plsql中的以下数据中查找重叠

Question

code  Low   High
A      0      99
E      0      99
T      0      99
A    100     **199**
E    100     **199**
T    100     **199**
A    **199**     299
E    **199**     299
T    **199**     299
A    300     399
E    300     399
T    300     399

我想检查重叠，例如 199。如果重叠，则在 oracle 中抛出异常。

标签： sqloracleoracle12c

Answer 1

LAG()可能会创建带有游标的过程，该游标包含具有分析功能的查询

CREATE OR REPLACE PROCEDURE Pr_Check_Overlap( i_code tab.code%type ) IS
BEGIN
    FOR c IN
    (
      SELECT t.*, LAG(high,1,high) OVER (PARTITION BY code ORDER BY code,low) AS previous_high
        FROM tab t
       WHERE code=i_code
       ORDER BY Low
     )
    LOOP
       DBMS_OUTPUT.PUT_LINE(c.code||'   '||c.low||'   '||c.high);
      IF c.low = c.previous_high THEN
         RAISE_APPLICATION_ERROR( -20101, 'First Met Overlapping Value is '||c.low );
      END IF;
    END LOOP;
END;
/

并为其参数调用可互换的三个值，如下所示

BEGIN
  Pr_Check_Overlap('A'); -- 'E', 'T'
END;