Help with simple cursor on DB2 9.5

davidjoyce · #1 (**permalink**) 08-25-10, 16:44

Hi,

I am new to DB2 and cursor development and am running into a problem with what I consider a simple enough cursor. When I run the example below I get this error:

"SQL0206N "V_USER_ID" is not valid in the context where it is used. LINE NUMBER=12..."

Code:

CREATE PROCEDURE DB2INST1.Proc1()
LANGUAGE SQL
P1: BEGIN
    DECLARE v_USER_ID VARCHAR(20);
    DECLARE c1 CURSOR FOR SELECT USR_ID FROM USR_APPL_PRFL_ASSN WHERE APP_NM = 'ABC1';
  
    OPEN c1;
    
    LOOP
    FETCH c1 INTO v_USER_ID;
  
    INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (7, v_USR_ID);

    INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (8, v_USR_ID);
      
    END LOOP;
    
    CLOSE c1;
END P1
;

Basically I am trying to take the output from the select statement and use that then as a parameter in my insert statements.
What is wrong here? I am using RapidSQL to run this.

Thanks

ARWinner · #2 (**permalink**) 08-25-10, 16:48

You declared as V_USER_ID and tried to use it as V_USR_ID.

Andy

davidjoyce · #3 (**permalink**) 08-25-10, 16:55

Thanks! Sometimes these things need another set of eyes.

How can I run this cursor without creating a procedure? I've tried just running this but I get a lot of errors around unexpected tokens and end-of-statements etc:

Code:

BEGIN
    DECLARE v_USER_ID VARCHAR(20);
    DECLARE c1 CURSOR FOR SELECT USR_ID FROM USR_APPL_PRFL_ASSN WHERE APP_NM = 'ABC1';
  
    OPEN c1;
    
    LOOP
    FETCH c1 INTO v_USER_ID;
  
    INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (7, v_USR_ID);

    INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (8, v_USR_ID);
      
    END LOOP;
    
    CLOSE c1;

ARWinner · #4 (**permalink**) 08-25-10, 16:58

You cannot. Cursors are only allowed in Stored Procedures.

Andy

davidjoyce · #5 (**permalink**) 08-25-10, 17:23

Thanks. If an insert for a particular row fails, because of primary key violation, is there a way for the cursor to ignore this and continue with the next row?

n_i · #6 (**permalink**) 08-25-10, 17:54

Quote:

Originally Posted by davidjoyce

Thanks. If an insert for a particular row fails, because of primary key violation, is there a way for the cursor to ignore this and continue with the next row?

Yes, you will need to DECLARE a CONTINUE HANDLER for that particular SQLCODE.

By the way, what you are trying to accomplish can (and should) be done with a single INSERT ... SELECT... statement.

davidjoyce · #7 (**permalink**) 08-25-10, 17:55

How would this be done?

n_i · #8 (**permalink**) 08-25-10, 18:29

Something like this:

Code:

insert into USR_WRK_GRP(WRK_GRP_ID, USR_ID)
 select t.num, u.usr_id 
 from USR_APPL_PRFL_ASSN u, table(values 7,8) t (num) 
 where u.app_nm = 'ABC1' 
 and not exists (
  select 1 from USR_WRK_GRP where usr_id = u.usr_id
 )

Internet Services

Web Development

Digital Marketing

Consumer Tech