我有一个触发器和一份案情陈述。在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;
2条答案
按热度按时间wztqucjr1#
这是一个非常复杂的case语句，每次都重复基本相同的select语句。
由于每个语句之间的唯一区别是乘数是1还是2，所以你可以用一个case语句重写整个语句，其中包含一个带有case表达式的sql语句，如下所示：
你甚至可以不使用外部case语句（尽管你必须将
rate_per_km in ('2 WHEELER', '4 WHEELER')条件推入case表达式，但这样的话，每次都必须为每一行运行查询，如果插入的大多数行不是2或4轮的话，这可能会降低性能。qzlgjiam2#
当ss.ssal < fs.fsal时选择大小写，然后选择ss.sname
else“”end from ss
-->键入一个空引号，所以它不会返回任何内容。