1 CREATE PROCEDURE Sys_Init_tblaccountsuser_sortid () 2 3 4 P1: BEGIN 5 declare not_found condition for sqlstate '02000'; 6 DECLARE V_RK_ID INTEGER DEFAULT 0; 7 DECLARE V_ID INTEGER DEFAULT 0; 8 DECLARE V_AT_END INT DEFAULT 0; 9 10 DECLARE CUR1 CURSOR WITH RETURN TO CLIENT FOR 11 select rk,id from (select rank() over(partition by ouid order by id asc) rk,t.* from tracing.tblaccountsuser t); 12 13 14 declare continue handler for not_found set V_AT_END=1; 15 -- Cursor left open for client application 16 OPEN CUR1; 17 ins_loop: 18 LOOP 19 FETCH CUR1 INTO V_RK_ID,V_ID; 20 IF V_AT_END <>0 THEN 21 LEAVE ins_loop; 22 END IF; 23 24 update tblaccountsuser set SORTID = V_RK_ID where id = V_ID ; 25 END LOOP; 26 CLOSE CUR1; 27 END P1
