CODEWITHSUNDEEP
sqlstored-proceduresdb2db2-luw
danielo
danielo

Reputation: 780

SQL - Declare cursor inside for loop in IBM DB2 Stored Procedure

I am trying to loop through a result in a stored procedure from a cursor using a for loop. The loop works correct with the example below:

CREATE OR REPLACE PROCEDURE ML_ANOMALY_EVENT_CREATOR ()
    DYNAMIC RESULT SETS 1
P1: BEGIN

    DECLARE DATETIME_TEMP TIMESTAMP(6);
    DECLARE TAG_GROUP_TEMP VARCHAR(50);
    DECLARE EVENT_CODE VARCHAR(100);
    DECLARE STMT VARCHAR(1000);
    SET STMT = 'INSERT INTO ML_ANOMALY_EVENTS VALUES(?, ?, CURRENT TIMESTAMP, ?, CURRENT TIMESTAMP)';
    PREPARE S1 FROM STMT;

    FOR v AS AD_DATA CURSOR FOR (SELECT DATETIME, TAG_GROUP FROM ML_AD_MV WHERE DATETIME > (SELECT MAX(DATETIME_END) FROM ML_ANOMALY_EVENTS) AND ANOMALY=2 ORDER BY 2, 1)
        DO
            SET DATETIME_TEMP = v.DATETIME;
            SET TAG_GROUP_TEMP = v.TAG_GROUP;
            SET EVENT_CODE = 'TEST';
            EXECUTE S1 USING EVENT_CODE, TAG_GROUP_TEMP, DATETIME_TEMP;
    END FOR;

END P1

When I instead try this below it fails. I want to extract results from a certain table with the help of the variables I declared, and only DO something (in this example case an insert) if the result contain any rows:

CREATE OR REPLACE PROCEDURE ML_ANOMALY_EVENT_CREATOR ()
    DYNAMIC RESULT SETS 1
P1: BEGIN

    DECLARE DATETIME_TEMP TIMESTAMP(6);
    DECLARE TAG_GROUP_TEMP VARCHAR(50);
    DECLARE EVENT_CODE VARCHAR(100);
    DECLARE STMT VARCHAR(1000);
    SET STMT = 'INSERT INTO ML_ANOMALY_EVENTS VALUES(?, ?, CURRENT TIMESTAMP, ?, CURRENT TIMESTAMP)';
    PREPARE S1 FROM STMT;

    FOR v AS AD_DATA CURSOR FOR (SELECT DATETIME, TAG_GROUP FROM ML_AD_MV WHERE DATETIME > (SELECT MAX(DATETIME_END) FROM ML_ANOMALY_EVENTS) AND ANOMALY=2 ORDER BY 2, 1)
        DO
            SET DATETIME_TEMP = v.DATETIME;
            SET TAG_GROUP_TEMP = v.TAG_GROUP;
            SET EVENT_CODE = 'TEST';
            --EXECUTE S1 USING EVENT_CODE, TAG_GROUP_TEMP, DATETIME_TEMP;

            DECLARE EVENT_CHECKER CURSOR FOR
                SELECT * FROM ML_ANOMALY_EVENTS WHERE TAG_GROUP=TAG_GROUP_TEMP AND DATETIME >= DATETIME_TEMP;

            OPEN EVENT_CHECKER;
                IF (EVENT_CHECKER IS FOUND) THEN
                    EXECUTE S1 USING EVENT_CODE, TAG_GROUP_TEMP, DATETIME_TEMP;
                END IF;
            CLOSE EVENT_CHECKER;

    END FOR;

END P1

How can I solve this?

Upvotes: 0

Views: 10533

Answers (2)

mustaccio
mustaccio

Reputation: 18945

Although the answer by Wen-Yi Chua works, it is more efficient here to use the EXISTS predicate in place of aggregation:

IF EXIST (SELECT 1 FROM ML_ANOMALY_EVENTS 
          WHERE TAG_GROUP=TAG_GROUP_TEMP AND DATETIME >= DATETIME_TEMP) THEN ...

especially if you expect the condition to be true most of the time. EXISTS will return as soon as a single record matching the search condition is found, while the query with COUNT(*) will have to read all matching records.

Upvotes: 1

Wen-Yi Chua
Wen-Yi Chua

Reputation: 61

No need to have event_checker cursor. try

if ((SELECT count(*) FROM ML_ANOMALY_EVENTS 
     WHERE TAG_GROUP=TAG_GROUP_TEMP AND DATETIME >= DATETIME_TEMP ) > 0) then
   EXECUTE S1 USING EVENT_CODE, TAG_GROUP_TEMP,DATETIME_TEMP;
end if

Upvotes: 1

Related Questions