我试图仅在存在变量的情况下执行代码块。这是一个代码窃听器。您可以在IF块中嵌套Begin ... End语句吗?

我已经重新设计了几次。有什么建议吗?

delimiter //


drop trigger if exists example_trigger;//
create trigger example_trigger AFTER UPDATE on some_table for each row
BLOCK1: begin

    -- check current status
    DECLARE done BOOLEAN DEFAULT FALSE;


    -- cap check
    if (new.CURRENT_ALLOCATED >= new.TOTAL_ALLOWED_QTY) then
        SET done = TRUE;
    end if; -- cap check end


    -- o
    if (done != TRUE and new.O_KEY is not null and new.A_KEY is null) then

        OBLOCK: begin
            DECLARE done_o BOOLEAN DEFAULT FALSE;
            DECLARE pd_nbr INT;
            DECLARE no_more_rows BOOLEAN;
            DECLARE cur_pd CURSOR FOR
                select pd.STATUS_KEY
                from PROD_DEL_V pd
                    join PROD_T p on pd.KEY_NBR = p.KEY_NBR
                where pd.STATUS not in ('PU', 'EX')
                    and p.O_KEY = new.O_KEY;
            DECLARE CONTINUE HANDLER FOR NOT FOUND
                SET done_o = TRUE;

            -- run updates
            if (done_o != TRUE) then

                -- open cursor
                OPEN cur_pd;

                -- loop start
                loop_it: LOOP

                    FETCH cur_pd INTO pd_nbr;

                    -- exit loop if..
                    if no_more_rows = TRUE THEN
                        CLOSE cur_pd;
                        LEAVE loop_it;
                    end if;


                    INSERT INTO STATUS_TABLE (
                        STATUS_KEY
                        , STATUS
                        , NOTE_TXT
                        )
                        (
                        SELECT
                            PD.STATUS_KEY
                            , 'PU' AS STATUS
                            , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']') AS NOTE_TXT
                        FROM  PROD_DEL_TABLE PD
                        WHERE PD.STATUS_KEY = pd_nbr
                        );

                END LOOP loop_it;

            end if; -- run updates end

        end OBLOCK:; -- end block

    end if; -- o


    -- a
    if (done != TRUE and new.O_KEY is null and new.A_KEY is not null) then

        ABLOCK: begin
            DECLARE done_a BOOLEAN DEFAULT FALSE;
            DECLARE pd_nbr INT;
            DECLARE no_more_rows BOOLEAN;
            DECLARE cur_pd CURSOR FOR
                select pd.STATUS_KEY
                from PROD_DEL_V pd
                    join PROD_T p on pd.KEY_NBR = p.KEY_NBR
                    join A_O_T a on a.O_KEY = p.O_KEY
                where pd.STATUS not in ('PU', 'EX')
                    and a.A_KEY = new.A_KEY;
            DECLARE CONTINUE HANDLER FOR NOT FOUND
                SET done_a = TRUE;

            -- run updates
            if (done_a != TRUE) then

                -- open cursor
                OPEN cur_pd;

                -- loop start
                loop_it: LOOP

                    FETCH cur_pd INTO pd_nbr;

                    -- exit loop if..
                    if no_more_rows = TRUE THEN
                        CLOSE cur_pd;
                        LEAVE loop_it;
                    end if;


                    INSERT INTO STATUS_TABLE (
                        STATUS_KEY
                        , STATUS
                        , NOTE_TXT
                        )
                        (
                        SELECT
                            PD.STATUS_KEY
                            , 'PU' AS STATUS
                            , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']' AS NOTE_TXT
                        FROM  PROD_DEL_TABLE PD
                        WHERE PD.STATUS_KEY = pd_nbr
                        );

                END LOOP loop_it;

            end if; -- run updates end

        end ABLOCK; -- end block

    end if; -- a
end BLOCK1; -- end large block
//

delimiter ;

最佳答案

IF和BEGIN ... END子句有什么问题?看一个简单的例子-

CREATE PROCEDURE procedure1(IN Param1 VARCHAR(255))
BEGIN
  IF Param1 = 1 THEN
    BEGIN
      DECLARE i INT;
      -- do something
    END;
  ELSE
    BEGIN
      DECLARE i INT;
      -- do something
    END;
  END IF;
END

10-05 22:51
查看更多