db2 how to create a simple update and select Stored Procedure?

Question

DB2 Stored Procedure update record and select record

CREATE PROCEDURE DB2INST1.GETPEOPLE2(IN ids bigint )

    SPECIFIC DB2INST1.GETPEOPLE2

    DYNAMIC RESULT SETS 1

    MODIFIES SQL DATA

    LANGUAGE SQL 



    BEGIN 

        update test2 set a=a+1 where a>ids;

         DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR  

        select * from db2inst1.test2;

        OPEN rs1;
END

but it's not working. error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "rs1 CURSOR sele" was found following "ids; DECLARE". Expected tokens may include: "". LINE NUMBER=10. SQLSTATE=42601

Please specify what result you expect and what result you are getting. — Cindy Meister
– Cindy Meister, Commented Feb 3, 2016 at 8:37
Check the syntax of compound SQL in the manual. All DECLARE statements must precede any executable statements. — mustaccio
– mustaccio, Commented Feb 3, 2016 at 13:29
DB2 has SELECT FROM FINAL TABLE which will return the altered rows (if any) of an inner UPDATE or INSERT statement. An INCLUDE clause can also show the original values before updates. — Stavr00
– Stavr00, Commented Feb 4, 2016 at 15:36

flyingnn · Accepted Answer · 2016-02-04 01:47:08Z

1

ok,it work:

BEGIN 
        DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR  
            select * from db2inst1.test2;
        update test2 set a=a+1 where a>ids;
        OPEN rs1;
END

answered Feb 4, 2016 at 1:47

flyingnn

611 silver badge8 bronze badges

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

Collectives™ on Stack Overflow

db2 how to create a simple update and select Stored Procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related