How to insert into a table using stored procedures in DB2 using SQL?

Question

I wanted to know how to insert into a table using stored procedures in DB2 using SQL. I have created a table as follows:

create table ADCLIBT.Itest
(ITNBR CHAR(15) CCSID 65535 NOT NULL DEFAULT '');

This table contains a list of items. Using this list I want to insert various other fields to another table. But, just for example sake, let's say I just want to insert these values one by one using cursors. I have written the stored procedure as follows:

create procedure ADCLIBT.itest1()
    LANGUAGE SQL
BEGIN
    DECLARE itemno char(15);
    DECLARE END_TABLE INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '20000';
    DECLARE c CURSOR FOR 
        select ITNBR from ADCLIBT.ITEMAT;
    DECLARE CONTINUE HANDLER FOR not_found
    SET END_TABLE = 1;

    open c;
    fetch from c into itemno;
    WHILE END_TABLE = 0 DO
        insert into ADCLIBT.ITEST
                (ITNBR)
                values
        (select a.ITNBR from ADCLIBT.ITEMAT a where ITNBR=itemno GROUP BY a.ITNBR);
    END WHILE;
    Close c;
END;

This is giving me an infinite loop. Can anyone please tell me how do I stop the infinite loop and insert these records. I want to use the cursor because I want to further use itemno to compare and get single results.

sebmaldo · Accepted Answer · 2013-02-12 18:01:20Z

1

You are opening the cursor but not feching in the while, you must do a fetch in the while of the cursor. Here an example from the IBM documentation.

CREATE PROCEDURE sum_salaries(OUT sum INTEGER) LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE p_sum INTEGER;
DECLARE p_sal INTEGER;
DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;

 SET p_sum = 0;

 OPEN c;

 FETCH FROM c INTO p_sal;

 WHILE(SQLSTATE = '00000') DO
    SET p_sum = p_sum + p_sal;
    FETCH FROM c INTO p_sal; 
 END WHILE;

 CLOSE c;

 SET sum = p_sum;

END%

answered Feb 12, 2013 at 18:01

sebmaldo

597 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sebmaldo Over a year ago

Yes, if you don't do that you are just in the first row.

racerxnox Over a year ago

Thanks for the prompt reply.I tried doing it but it is still giving an infinite loop. 'open c; fetch from c into itemno; WHILE END_TABLE = 0 DO insert into ADCLIBT.ITEST (ITNBR) values (select a.ITNBR from ADCLIBT.ITEMAT a where ITNBR=itemno GROUP BY a.ITNBR); fetch from c into itemno; END WHILE; Close c;' There are around 10k rows

sebmaldo Over a year ago

Sorry... I have not clue what is going on.

racerxnox Over a year ago

can you tell me if I should use the continue handler or an EXIT handler to get out of the table after the records are all gone through?

racerxnox Over a year ago

Got it finally, how to do this. The DECLARE not_found CONDITION FOR SQLSTATE '20000'; was giving a problem , so I just used the normal continue` handler with NOT FOUND

Collectives™ on Stack Overflow

How to insert into a table using stored procedures in DB2 using SQL?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related