database - 甲骨文 while 循环

Question

这是我当前的 oracle 语句:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
             FROM pump_box_hist
       )
     , table2_ AS (
          SELECT DATETIME
               , TOTALTIME1
               , RAWOUTPUT1
               , Pumpe1_1
               , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
               , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
            FROM table_
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table2_ 
 WHERE (
                Pumpe1_1 = next_
            AND Pumpe1_1 <> priv_
            AND DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
            AND DATETIME < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
       )
     ;

它工作正常，但需要将近 50 秒才能执行。因为数据按 DATETIME 排序，所以如果 DATETIME 大于给定值，则执行 while 循环 退出。在这个循环中，我将对小于给定的日期使用 IF。我尝试了一段时间，但没有成功。

我试过的代码:

WHILE (SELECT DATETIME FROM pump_box_hist) < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
LOOP
    IF DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS') THEN
        WITH table_ AS (
                    SELECT DATETIME
                         , TOTALTIME1
                         , RAWOUTPUT1
                         , CASE BITAND(RAWOUTPUT1, POWER(2,0)) 
                               WHEN 0 THEN 'FALSE' 
                               ELSE        'TRUE'
                           END AS Pumpe1_1 
                      FROM pump_box_hist
             )
           , table2_ AS (
                   SELECT DATETIME
                        , TOTALTIME1
                        , RAWOUTPUT1
                        , Pumpe1_1
                        , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
                        , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
                     FROM table_
             )
      SELECT DATETIME
           , TOTALTIME1
           , RAWOUTPUT1
           , Pumpe1_1 
        FROM table2_ 
       WHERE (
                   Pumpe1_1 = next_ 
               AND Pumpe1_1 <> priv_
             )
           ;
    END IF;
END LOOP;

Error: unknown command in line 32 and 33 (Editor's note: these have been lines 11 and 12 in the original formatting containing END IF; and END LOOP;)

那么我怎样才能让 LOOP 和 IF 工作呢？谢谢

Answer 1

您从客户端收到“未知命令”错误，因为您试图在纯 SQL 中使用 PL/SQL 控制语句； while、if、end if 和 end loop 在 SQL 中无效。

您真正要做的似乎只是移动过滤器，这样它们就可以进入您的第一个 CTE:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
             FROM pump_box_hist
            WHERE DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
              AND DATETIME < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
       )
     , table2_ AS (
          SELECT DATETIME
               , TOTALTIME1
               , RAWOUTPUT1
               , Pumpe1_1
               , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
               , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
            FROM table_
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table2_ 
 WHERE (
                Pumpe1_1 = next_
            AND Pumpe1_1 <> priv_
       )
     ;

但这现在意味着 table_ 中的第一行和最后一行将没有更早/更晚的行供滞后/领先查找，所以 priv_/ next_ (分别)对于那些可能会影响您的最终结果的人来说将为空。不过，您可以在最后的 where 子句中显式检查空值:

...
  FROM table2_ 
 WHERE (
                (Pumpe1_1 = next_ or next_ is null)
            AND (Pumpe1_1 <> priv_ or priv_ is null)
       )

您也可以通过对原始值而不是计算标志进行超前/滞后来使用单个 CTE 来执行此操作，这不会有很大不同，但会更短一些；尽管为了清楚起见，您可能更喜欢两个 CTE:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
               , CASE BITAND(LAG(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS priv_
               , CASE BITAND(LEAD(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS next_
             FROM pump_box_hist
            WHERE DATETIME > to_date('30.10.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
              AND DATETIME < to_date('02.11.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table_ 
 WHERE (
                (Pumpe1_1 = next_ or next_ is null)
            AND (Pumpe1_1 <> priv_ or priv_ is null)
       )
     ;