Categoria: MySql

CREATE  PROCEDURE `updateOffer`()
BEGIN
 declare v_col2 int default 0;
 declare no_more_rows2 int default 0;
DECLARE finito INT default 0;
DECLARE OrderItemID INT;
DECLARE userId INT;
DECLARE example_cursor CURSOR FOR select id from imps_customer_tb;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 1;

  — open the cursor
OPEN example_cursor;
  ciclo: WHILE NOT finito DO
       — read the name from next row into the variable l_name
  FETCH  example_cursor INTO   userId;

                 INSERT INTO imps_offer_tb (`PROG_ANNO`, `EMISSION_DATE`, `DIVISION_ID`, `CUSTOMER_ID`, `CREATION_DATE`, `LAST_UPDATE_DATE`, `STATO_ID`, `TOTAL_AMOUNT`,`PRIMARIA`) VALUES (1, ‘2013-11-28’, 1, userId, ‘2013-11-28 17:03:25’, ‘2013-11-28 17:03:26′, 1, 0,’y’);
    set OrderItemID = LAST_INSERT_ID();

BLOCK2: begin
declare v_col2 int;
declare no_more_rows2 INT default 0;
  declare cursor2 cursor for    select distinct of.id from imps_course_tb of, imps_offer_tb o   WHERE    o.CUSTOMER_ID = userId and of.OFFER_ID = o.ID ;
    declare continue handler for not found   set no_more_rows2 = 1;
    open cursor2;
            LOOP2: WHILE NOT no_more_rows2 DO
                fetch cursor2 into  v_col2;
                          if v_col2 <> 0 then
         UPDATE `imps_course_tb` SET `OFFER_ID`=OrderItemID  WHERE  `ID`=v_col2;
                          end if;
            END WHILE  LOOP2;
            CLOSE cursor2;
       end BLOCK2;

        END WHILE ciclo;
CLOSE example_cursor;

END