oracle CASE 语句什么也不做
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37812374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE statement to do nothing
提问by Teju
I have a trigger with a case statement. In the last section of the code in the ELSE block, i want the trigger to not do anything and exit if the values do not match in the previous CASE statements. How do i do this:
我有一个带有 case 语句的触发器。在 ELSE 块中代码的最后一部分中，我希望触发器不执行任何操作并在值与前面的 CASE 语句中的值不匹配时退出。我该怎么做呢：
create or replace TRIGGER "CONVEYANCE_REQUEST_T3"
BEFORE
insert or update on "CONVEYANCE_REQUEST"
for each row
begin
CASE
when :NEW.REGULAR_TRAVEL_MODE = '2 WHEELER' THEN
BEGIN
CASE
when :NEW.WAY_TYPE = 'ONE WAY' THEN
SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 1 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;
WHEN :NEW.WAY_TYPE ='TWO WAY' THEN
SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 2 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;
END CASE;
END;
when :NEW.REGULAR_TRAVEL_MODE = '4 WHEELER' THEN
BEGIN
CASE
when :NEW.WAY_TYPE = 'ONE WAY' THEN
SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 1 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;
WHEN :NEW.WAY_TYPE ='TWO WAY' THEN
SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 2 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;
END CASE;
END;
ELSE
****statement to just exit & not do anything*****
END CASE;
END;
回答by Boneist
That's an awfully complicated case statement, repeating essentially the same sort of select statement each time.
这是一个非常复杂的 case 语句，每次都重复本质上相同类型的 select 语句。
Since the only difference between each statement is whether the multiplier is 1 or 2, you could just rewrite the whole thing in one case statement containing a sql statement with a case expression like so:
由于每个语句之间的唯一区别是乘数是 1 还是 2，因此您可以在一个 case 语句中重写整个内容，其中包含一个带有 case 表达式的 sql 语句，如下所示：
create or replace trigger "CONVEYANCE_REQUEST_T3"
before
insert or update on "CONVEYANCE_REQUEST"
for each row
begin
case when rate_per_km in ('2 WHEELER', '4 WHEELER')
and new.way_type in ('ONE WAY', 'TWO WAY') then
select rate_per_km
* (select distance_oneway from distance_master where project_code = :new.project_code)
* case when :new.way_type = 'ONE WAY'
then 1
when :new.way_type = 'TWO WAY'
then 2
else null
end regular_amount
into :new.regular_amount
from conveyance_rate where travel_mode = :new.regular_travel_mode;
else null;
end case;
end;
/
You could even get away with not having the outer case statement (although you'd have to push the rate_per_km in ('2 WHEELER', '4 WHEELER')condition into the case expression, but then the query would have to be run every time for each row and that might reduce performance if most of your rows being inserted aren't 2 or 4 wheelers.
您甚至可以不使用外部 case 语句（尽管您必须将rate_per_km in ('2 WHEELER', '4 WHEELER')条件推送到 case 表达式中，但随后每次都必须为每一行运行查询，如果您的大多数情况下，这可能会降低性能插入的行不是 2 或 4 轮车。
