Update DN with Procedure
Update DN
1 CREATE PROCEDURE NKXPRTEMP/UPD_DN(IN DNNO CHAR ( 10)) 2 LANGUAGE SQL MODIFIES SQL DATA 3 4 BEGIN 5 -- Step 1 6 UPDATE DNRITM A SET (DNITTQTY, DNIREQTY) = (SELECT 7 SUM(RTLEXQTY), SUM(RTLEXQTY) FROM DNRSZE B 8 WHERE A.DNDLNNBR = B.DNDLNNBR AND A.MMPRDMAT = B.MMPRDMAT ) 9 WHERE DNDLNNBR = DNNO ; 10 11 -- Step 2 12 UPDATE DNRDHE A SET ( DNOFPQTY, DNROPQTY, DNNFPQTY, DNNRPQTY ) = 13 ( SELECT SUM( DNIFCQTY), SUM(DNIREQTY), SUM( DNIFCQTY), 14 SUM(DNIREQTY) FROM DNRITM B WHERE A.DNDLNNBR = B.DNDLNNBR ) 15 WHERE DNDLNNBR = DNNO ; 16 17 -- Step 3 18 UPDATE DNRHDR A SET(DNOFCQTY, DNORPQTY, DNNVFQTY, DNNVRQTY ) =( 19 SELECT DNOFPQTY, DNROPQTY, DNNFPQTY, DNNRPQTY FROM DNRDHE B 20 WHERE A.DNDLNNBR = B.DNDLNNBR ) 21 WHERE DNDLNNBR = DNNO ; 22 23 END
-- Use
CALL NKXPRTEMP/UPDATE_DN ( 435184008 )
CALL STATEMENT COMPLETE.
-----------------------------------------------------------------------------------------------------------------------------------------------
IBM Information Center
-----------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE PROCEDURE CREDITP( 2 IN i_perinc DECIMAL(3,2), 3 INOUT o_numrec DECIMAL(5,0)) 4 LANGUAGE SQL 5 BEGIN atomic 6 DECLARE proc_cusnbr CHAR(5); 7 DECLARE proc_cuscrd DECIMAL(11,2); 8 DECLARE numrec DECIMAL(5,0); 9 DECLARE at_end INT DEFAULT 0; 10 DECLARE not_found CONDITION FOR '02000'; 11 12 DECLARE c1 CURSOR FOR 13 SELECT cusnbr, cuscrd 14 FROM ordapplib.customer; 15 16 DECLARE CONTINUE HANDLER FOR not_found 17 SET at_end = 1; 18 19 SET numrec = 0; 20 OPEN c1; 21 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 22 WHILE at_end = 0 DO 23 SET proc_cuscrd = proc_cuscrd +(proc_cuscrd * i_perinc); 24 UPDATE ordapplib.customer 25 SET cuscrd = proc_cuscrd 26 WHERE CURRENT OF c1; 27 SET numrec = numrec + 1; 28 FETCH c1 INTO proc_cusnbr, proc_cuscrd; 29 END WHILE; 30 31 SET o_numrec = numrec; 32 CLOSE c1; 33 END