使用 if else 阻止缺少右括号错误

Question

我是Oracle新手，对MS SQL有所了解。我正在尝试根据Table2 user_id获取电话号码，这是业务逻辑：

• 案例1：如果在Table1中找到单个匹配项，则从Table2获取其各自的免费电话号码

• 案例 2：如果在Table1中找不到匹配项，则从Table2获取默认免费号码

• 案例3：如果在Table1中找到多个匹配项，则对于所有这些assigned_care_levels从按asc或desc排序的Table2中获取Care值，然后选择顶行电话号码。

我写了以下查询，当我单独运行它时，它工作正常。但是，当我使用 if else 语句对其进行编码时，我收到以下错误ERROR: ORA-00907: missing right parenthesis.这是我的代码：

if ((select count(distinct care_level) from Table1 where user_id = '100') > 0)
    select phone from Table2 where care_level in (select distinct care_level from Table1 where user_id = '100')
    and rownum = 1 
    order by care_level asc
else if((select count(distinct care_level) from Table1 where user_id = '100') = 0)
    select phone from Table2 where care_level = 'default'

Answer 1

SET SERVEROUTPUT ON;
DECLARE
       v_CARE_COUNT NUMBER := 0;
       v_PHONE VARCHAr2(40) := NULL;
BEGIN
      select  count(distinct care_level) 
         into v_CARE_COUNT
      from Table1 
       where user_id = '100';
      IF(v_CARE_COUNT > 0) THEN
         select phone into v_PHONE 
         from Table2 
         where care_level in 
           (select distinct care_level from Table1 where user_id = '100')
         and rownum = 1;
      ELSE
         select phone into v_PHONE
          from Table2
         where care_level = 'default';
      END IF;
      DBMS_OUTPUT.PUT_LINE('PHONE is <'||v_PHONE||'>');
 EXCEPTION
 WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Exception : '||SQLERRM);
 END;
 /

编辑：（作为SIngle SQL）

 SELECT PHONE 
 FROM TABLE2
 WHERE CARE_LEVEL in
       (
          SELECT CARE_LEVEL FROM TABLE1 WHERE USER_ID='100'
           UNION
          SELECT CARE_LEVEL FROM TABLE1 WHERE CARE_LEVEL='default'
            AND NOT EXISTS
              (SELECT 'X' FROM TABLE1 WHERE USER_ID='100')
        )
 AND ROWNUM = 1;

Answer 2

'else if' 语法是错误的。在 Oracle 中，您需要使用"ELSIF"并使用"END IF"完成整个条件语句。中的 SQL 语句后面也应跟 ;

尝试：

IF ((select count(distinct care_level) from Table1 where user_id = '100') > 0) THEN
    select phone from Table2 where care_level in (select distinct care_level from Table1 where user_id = '100')
    and rownum = 1 
    order by care_level asc;
ELSIF ((select count(distinct care_level) from Table1 where user_id = '100') = 0) THEN
    select phone from Table2 where care_level = 'default'; END IF