What is cursor – it is like query that returns some dataset… but all code works on SQL server side…
Code by my friend…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
CREATE DEFINER=`root`@`localhost` PROCEDURE `CopyOrders`(lid_ord int,lid_firm int,ldt_begin date,ldt_end date,lworker int) BEGIN DECLARE aDate date default ldt_begin; DECLARE aid_ord int; DECLARE aold_id_oj int; DECLARE anew_id_oj int; DECLARE done INT DEFAULT FALSE; DECLARE old_cur CURSOR FOR select id_oj from ord_job where id_ord=lid_ord order by id_oj; DECLARE new_cur CURSOR FOR select id_oj from ord_job where id_ord=aid_ord order by id_oj; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK; START TRANSACTION; select ADDDATE(aDate, INTERVAL -1 DAY) into aDate; REPEAT select ADDDATE(aDate, INTERVAL 1 DAY) into aDate; insert into orders (id_firm,dt_ord) values (lid_firm,aDate); SET aid_ord = (select LAST_INSERT_ID()); insert into ord_job (id_ord,id_job,count,time_begin,time_end,sex,age,ethnic,memo,med_book) (select aid_ord,id_job,count,time_begin,time_end,sex,age,ethnic,memo,med_book from ord_job where id_ord=lid_ord and fl_active=1); if lworker=1 then Open old_cur; Open new_cur; SET done = FALSE; read_loop: LOOP FETCH old_cur INTO aold_id_oj; FETCH new_cur INTO anew_id_oj; IF done THEN LEAVE read_loop; END IF; insert into orders_b (id_ord,id_oj,id_user,id_worker,id_job,time_begin,time_end,tarif,shtraf,bonus) (select aid_ord,anew_id_oj,id_user,id_worker,id_job,time_begin,time_end,tarif,shtraf,bonus from orders_b where id_oj=aold_id_oj and fl_active=1); END LOOP; Close old_cur; Close new_cur; end if; UNTIL aDate >= ldt_end END REPEAT; COMMIT; END |