使用 CASE 和 IN 进行更新 - Oracle

Question

我编写了一个查询，它在 SQL Server 中就像一个魅力一样。不幸的是它需要在 Oracle 数据库上运行。我一直在网上彻底搜索如何转换它的解决方案，但没有成功：/

查询看起来像这样 i SQL:

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in (1001,1012,50055))  THEN 'BP_GR_A'   
      WHEN (budgpost in (5,10,98,0))  THEN 'BP_GR_B'  
      WHEN (budgpost in (11,876,7976,67465))     
      ELSE 'Missing' END`

我的问题还在于列 budgetpost_gr1 和 Budgetpost 是字母数字和Oracle 似乎希望将列表视为数字。该列表是预定义为逗号分隔列表的变量/参数，仅转储到查询中。

Answer 1

你说预算帖子是字母数字的。这意味着它正在寻找与字符串的比较。您应该尝试将参数括在单引号中（并且您缺少 Case 表达式中的最后一个 THEN）。

UPDATE tab1   
SET budgpost_gr1=   CASE  
                        WHEN (budgpost in ('1001','1012','50055'))  THEN 'BP_GR_A'   
                        WHEN (budgpost in ('5','10','98','0'))  THEN 'BP_GR_B'  
                        WHEN (budgpost in ('11','876','7976','67465')) THEN 'What?'
                        ELSE 'Missing' 
                        END 

Answer 2

使用 to_number 将 budgpost 转换为数字：

when to_number(budgpost,99999) in (1001,1012,50055) THEN 'BP_GR_A' 

编辑：确保 to_number 中有足够的 9与最大的预算职位相匹配。

如果存在非数字预算帖子，您可以在查询末尾使用 where 子句将其过滤掉：

where regexp_like(budgpost, '^-?[[:digit:],.]+
)

Answer 3

得到了一个可以运行的解决方案。但不知道这是否是最佳的。我所做的是根据 http://blogs.oracle.com 拆分字符串/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

使用：
从对偶中选择 regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level)
connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

所以我的最终代码如下所示 ($bp_gr1'是像 1,2,3 这样的字符串）：

UPDATE TAB1
SET    BUDGPOST_GR1 =
          CASE
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR1',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR1'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR2',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR2'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( ' $BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR3',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR3'
             WHEN ( BUDGPOST IN (SELECT     REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                 FROM       DUAL
                                 CONNECT BY REGEXP_SUBSTR ( '$BP_GR4',
                                                            '[^,]+',
                                                            1,
                                                            LEVEL )
                                               IS NOT NULL) )
             THEN
                'BP_GR4'
             ELSE
                'SAKNAR BUDGETGRUPP'
          END;

有没有办法让它运行得更快？

Answer 4

“该列表是预定义为逗号分隔列表的变量/参数”。
您的意思是您的查询实际上是

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in ('1001,1012,50055'))  THEN 'BP_GR_A'   
      WHEN (budgpost in ('5,10,98,0'))  THEN 'BP_GR_B'  
      WHEN (budgpost in ('11,876,7976,67465'))     
      ELSE 'Missing' END`

如果是的话，您需要一个函数来获取字符串并将其解析为数字列表。

create type tab_num is table of number;

create or replace function f_str_to_nums (i_str in varchar2) return tab_num is
  v_tab_num tab_num := tab_num();
  v_start   number := 1;
  v_end     number;
  v_delim   VARCHAR2(1) := ',';
  v_cnt     number(1) := 1;
begin
  v_end := instr(i_str||v_delim,v_delim,1, v_start);
  WHILE v_end > 0 LOOP
    v_cnt := v_cnt + 1;
    v_tab_num.extend;
    v_tab_num(v_tab_num.count) := 
                  substr(i_str,v_start,v_end-v_start);
    v_start := v_end + 1;
    v_end := instr(i_str||v_delim,v_delim,v_start);
  END LOOP;
  RETURN v_tab_num;
end;
/

然后你可以像这样使用该函数：

select column_id, 
   case when column_id in 
     (select column_value from table(f_str_to_nums('1,2,3,4'))) then 'red' 
   else 'blue' end
from  user_tab_columns
where table_name = 'EMP'

Answer 5

您可以使用另一种解决方法来使用联接进行更新。下面的示例假设您希望通过包含查找值（在本例中在表中存储用户名）来对表进行反规范化。更新包括查找名称的联接，并在支持找到或未找到名称的 CASE 语句中评估输出。完成这项工作的关键是确保来自连接的所有列都具有唯一的名称。在示例代码中，请注意 b.user_name 如何与 a.user_name 列冲突，并且必须使用唯一名称“user_user_name”作为别名。

UPDATE
(
    SELECT a.user_id, a.user_name, b.user_name as user_user_name
    FROM some_table a
    LEFT OUTER JOIN user_table b ON a.user_id = b.user_id
    WHERE a.user_id IS NOT NULL
)
SET user_name = CASE
    WHEN user_user_name IS NOT NULL THEN user_user_name
    ELSE 'UNKNOWN'
    END;