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 

Update DN

 -- 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

View Code

 

转载于:https://www.cnblogs.com/morsun/p/5936706.html