Oracle in SQL stored procedure to realize a simple: loop to user table a field assignment-CodePudding

User have id in the table, m, n, k four fields, k is empty now,

Write a stored procedure proc_test:
Incoming parameters: id=3, a=2008, b=2012.
Achieve purpose: for each row in turn fields k value assignment,
M=2008 (start, cycle to 2012

If n is zero, no pay, will be set to null, the current row k
If n is 2, represents the pay cost, then look at last year's k value is empty, if not null, the current row k value is set to the same as the previous year's k value,
The K value is null if the previous year, the current row K value is set to the current row m value.

CodePudding user response:

 create or replace PROCEDURE proc_test 
(v_id in varchar2, 
A. in varchar2, 
B in varchar2 
) 
Is 
V_n varchar2 (10); 
The begin 

For I in a.. Loop b 
Select n into v_n the from user where m=I and id=v_id; 
If v_n=0 then 
Update user set k is null where m=I and id=v_id; 
Elseif v_n=2 then 
Update the user set k=NVL (lag (n) over (order by m), m) where m=I and id=v_id; 
end if; 
commit; 
end loop; 
End proc_test;

CodePudding user response:

 create or replace PROCEDURE proc_test 
(v_id in varchar2, 
A. in varchar2, 
B in varchar2 
) 
Is 
V_n varchar2 (10); 
The begin 

For I in a.. Loop b 
Select n into v_n the from user where m=I and id=v_id; 
If v_n=0 then 
Update user set k is null where m=I and id=v_id; 
Elseif v_n=2 then 
Update the user set k=NVL (lag (n) over (order by m), m) where m=I and id=v_id; 
end if; 
commit; 
end loop; 
End proc_test;