CODEWITHSUNDEEP
db2
spinjector
spinjector

Reputation: 3535

What does this mean in DB2: DECLARE CONTINUE HANDLER FOR NOT FOUND..?

I am trying to understand the meaning of this DB2 statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND

This appears in the IBM code example for the LOOP statement here, and which I've pasted below. Other than DECLARE, I'm not sure which of the other words are discrete keywords, and I cannot find anything helpful in the documentation.

It seems like the Visual Basic statement ON ERROR GOTO labelname, and in context seems to be saying "return -1 if not found". But I'm not certain how or where it kicks in, or whether it refers to the SQL, the LOOP, or or what exactly.

CREATE PROCEDURE LOOP_UNTIL_SPACE (OUT COUNTER INTEGER)
    LANGUAGE SQL
BEGIN
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE v_firstnme VARCHAR(12);
    DECLARE v_midinit CHAR(1);
    DECLARE v_lastname VARCHAR(15);
    DECLARE c1 CURSOR FOR
        SELECT firstnme, midinit, lastname
        FROM employee;
    DECLARE CONTINUE HANDLER FOR NOT FOUND -- <--?
        SET counter = -1;                  -- <--?
    OPEN c1;
    fetch_loop:
    LOOP
        FETCH c1 INTO v_firstnme, v_midinit, v_lastname;
        IF v_midinit = ' ' THEN
            LEAVE fetch_loop;
        END IF;
        SET v_counter = v_counter + 1;
    END LOOP fetch_loop;
    SET counter = v_counter;
    CLOSE c1;
END;

Upvotes: 0

Views: 4445

Answers (1)

mao
mao

Reputation: 12287

This fragment:

DECLARE CONTINUE HANDLER FOR NOT FOUND -- <--?
        SET counter = -1;                  -- <--?

means that whenever an SQL statement returns SQLCODE=100 (no rows found), then execute this single-statement set counter = -1 and then continue executing the next statement (i.e. the statement that follows the statement triggering the SQLCODE 100).

In your code example, the statement following FETCH that got sqlcode=100 will execute next.

As counter is an output parameter, the consequence is that counter will be set to -1 whenever sqlcode 100 happens. This may not be what was intended.

Upvotes: 1

Related Questions