使用CASE和IN进行更新-Oracle [英] UPDATE with CASE and IN - Oracle

问题描述

我编写了一个查询，该查询的工作方式类似于SQL Server中的超级按钮.不幸的是，它需要在Oracle数据库上运行.我一直在内外搜索Web，以获取有关如何转换它的解决方案，但未成功:/

I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any success :/

查询看起来像是i SQL:

The query looks like this 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似乎希望将列表视为数字.该列表是预先定义为逗号分隔列表的变量/参数，这些变量/参数将被转储到查询中.

My problem is also that the columns budgetpost_gr1 and budgetpost is alphanumeric and Oracle seems to want to see the list as numbers. The list are variables/parameters that is pre-defined as comma separated lists, which is just dumped into the query.

推荐答案

找到了运行的解决方案.虽然不知道它是否是最佳选择.我要做的是根据 http://blogs.oracle.com拆分字符串/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

Got a solution that runs. Don't know if it is optimal though. What I do is to split the string according to http://blogs.oracle.com/aramamoo/2010/05/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement.html

使用:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual
connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

Using:
select regexp_substr(' 1, 2 , 3 ','[^,]+', 1, level) from dual
connect by regexp_substr('1 , 2 , 3 ', '[^,]+', 1, level) is not null;

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

So my final code looks like this ($bp_gr1' are strings like 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;

有没有办法使其运行更快?

Is there a way to make it run faster?

这篇关于使用CASE和IN进行更新-Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！