带有变量的Oracle案例语句

Question

我有一个要求，如果今天是星期一，则说明一个查询，否则运行另一个查询。为此，我写了以下查询，但是我收到以下错误消息。

DECLARE
   l_today_date VARCHAR2(15) := TO_CHAR(SYSDATE, 'DAY');
 BEGIN
   CASE l_today_date
   WHEN 'MONDAY' THEN
     (SELECT st_time AS SYS_DATE,
       start_time                          AS JOB_START_TIME,
       COALESCE (end_job,'Job Is Running') AS JOB_END_TIME,
       CASE duration_job
         WHEN ' min'
         THEN 'Job is Running'
         ELSE duration_job
       END AS JOB_DURATION,
       CASE duration_job
         WHEN ' min'
         THEN 'Job is Running'
         ELSE 'Complete'
       END AS job_status
     FROM
       (SELECT name,
         st_time,
         ABS(floor(((((st_time - lag(end_time) over (order by end_time desc))*24*60*60)/3600)*3600)/60))
         || ' min' duration_job,
         TO_CHAR(st_time, 'hh24:mi:ss')      AS start_time,
         TO_CHAR(lag(end_time)over(order by end_time desc),'hh24:mi:ss') AS end_job
       FROM sc_stask
       WHERE name IN ( '111 has started' ,'111 has ended' )
       ORDER BY st_time DESC
       )
     WHERE name = '111 has started');
   ELSE
     (SELECT st_time                       AS SYS_DATE,
       start_time                          AS JOB_START_TIME,
       COALESCE (end_job,'Job Is Running') AS JOB_END_TIME,
       CASE duration_job
         WHEN ' min'
         THEN 'Job is Running'
         ELSE duration_job
       END AS JOB_DURATION,
       CASE duration_job
         WHEN ' min'
         THEN 'Job is Running'
         ELSE 'Complete'
       END AS job_status
     FROM
       (SELECT name,
         st_time,
         ABS(floor(((((st_time - lag(end_time)over(order by end_time desc) )*24*60*60)/3600)*3600)/60))
         || ' min' duration_job,
         TO_CHAR(st_time, 'hh24:mi:ss')      AS start_time,
         TO_CHAR(lag(end_time)over(order by end_time desc),'hh24:mi:ss') AS end_job
       FROM sc_stask
       WHERE name                      IN ( '111 has started' ,'111 has completed' )
       AND TO_CHAR(st_time,'DD/MM/YYYY')=TO_CHAR(SYSDATE,'DD/MM/YYYY')
       ORDER BY st_time DESC
       )
     WHERE name = '111 has started'
     );
   END CASE;
   dbms_output.Put_line(l_today_date);
END;

这是我有错误消息

 Error report -
 ORA-06550: line 6, column 6:
 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    <an alternatively-quoted string literal with character set specification>
    <an alternat
 ORA-06550: line 22, column 47:
 PLS-00103: Encountered the symbol "OVER" when expecting one of the following:
    . ( ) , * % & = - + < / > at in is mod remainder not rem =>
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec as between || member submultiset
 06550. 00000 -  "line %s, column %s:n%s"
 *Cause:    Usually a PL/SQL compilation error.

有人可以帮助我如何克服这一点。同样，在每个案例语句中运行的查询都可以单独工作。但是当我将它们放回一个时，它们就不会。

Answer 1

问题是括号；您不应围绕选定语句的那些：

DECLARE
   l_today_date VARCHAR2(15) := TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH');
 BEGIN
   CASE l_today_date
   WHEN 'MONDAY' THEN
     SELECT st_time AS SYS_DATE,
...
     WHERE name = '111 has started';
   ELSE
     SELECT st_time                       AS SYS_DATE,
...
     WHERE name = '111 has started';
   END CASE;
   dbms_output.Put_line(l_today_date);
END;
/

我还为to_char()添加了可选的第三个参数，因此请确保您要搜索的日常名称。否则，如果有人从非英语会话中运行它，则无法正常匹配。

---

顺便说一句，如果您只使用l_tdoay_date值一次 - 并且没有dbms_output调试调用，则不需要该变量；您可以使用case与函数调用：

BEGIN
   CASE TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH')
   WHEN 'MONDAY' THEN
...

，您可以在此处使用if而不是case - 要么有效，但只有一个值被检查，您可能不会在此示例中使用case获得太多。

---

您也可以使用单个查询并将案例语句逻辑移至其where子句中，类似于：

   FROM sc_stask
   WHERE name IN ( '111 has started' ,'111 has ended' )
   AND (TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MONDAY'
     OR TO_CHAR(st_time,'DD/MM/YYYY')=TO_CHAR(SYSDATE,'DD/MM/YYYY')
   )

尽管我通常将最后一部分作为OR TRUNC(st_time) = TRUNC(SYSDATE)做，而不是将两个转换为字符串。无论如何，使用这种方法，您可能根本不需要PL/SQL块。如果您确实拥有它，则如@littlefoot提到的那样，您必须选择(任何一个(查询 in 某物。

Answer 2

不是CASE，而是IF：

begin
  if to_char(sysdate, 'DAY') = 'MONDAY' then
     select ... query you run on monday;
  else
     select ... query you run otherwise
  end if;
end;

[edit：添加的示例in to in to in of子句]

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_cnt number;
  3    l_today varchar2(10) := to_char(sysdate, 'DAY');
  4  begin
  5    if l_today = 'THURSDAY' then
  6       select count(*)
  7         into l_cnt          --> this
  8         from emp
  9         where deptno = 10;
 10    else
 11       select count(*)
 12         into l_cnt
 13         from emp
 14         where deptno <> 10;
 15    end if;
 16
 17    dbms_output.put_line('Today is ' || l_today || ' and count = ' || l_cnt);
 18  end;
 19  /
Today is THURSDAY  and count = 11
PL/SQL procedure successfully completed.
SQL>