how to have insertion happen only once in a cursor loop in mysql-CodePudding

This procedure takes a brand name and matches it with the brandnames in the product table and creates a campaign. When I call this procedure BrandNameCampaign with an input of a brandname that does NOT exist in the product table it is still creating a campaign. I know it's because I have kept the insertion query outside of the loop where it is checking whether camp_c cursor has null values or not. However, if I put the query inside the repeat loop of the cursor, it generates an error code: 1062 duplicate entry 'x' (x being an int) for key 'campaign.PRIMARY'.

How do I fix my code so that a new campaign does not get inserted into the table without creating/firing a trigger. I want it to work within this procedure.

Table code

create table Product (
    ProductID int not null,
    ProductType varchar (20) not null,
    PackageType varchar(20) not null, 
    YearProduced int not null,
    Price float not null,
    Brand varchar(255) not null,
    PRIMARY KEY (ProductID)
) 

create table Campaign (
    CampaignID int not null,
    CampaignStartDate date not null,
    CampaignEndDate date,
    PRIMARY KEY (CampaignID)
) 
create table DiscountDetails (
    ProductID int not null,
    CampaignID int not null,
    MembershipLevel varchar(20) not null, 
    Discount int not null,
    primary key (ProductID, CampaignID, MembershipLevel),
    foreign key (ProductID) references Product (ProductID),
    foreign key (CampaignID) references Campaign (CampaignID)
)

Procedure code

create procedure BrandNameCampaign (in brandname varchar(50))
    begin
    declare v_finished int default 0;
    declare prod_id int;
    declare newcampid int;
    declare camp_brand varchar(255);
    declare camp_c cursor for
        select productid, brand
        from product
        where brandname = brand
        order by price desc limit 5;
        
    declare continue handler for not found set v_finished = 1;
    
    SELECT 
        MAX(CampaignID)
    INTO newcampid FROM
        campaign;
    set newcampid = 1   newcampid;    
    
    insert  into `Campaign`(`CampaignID`,`CampaignStartDate`,`CampaignEndDate`) values 
    (newcampid,date_add(curdate(), interval 4 week), date_add( curdate(), interval 8 week));
    
     -- working with cursor
    open camp_c;
    repeat
        fetch camp_c into prod_id, camp_brand;
    if not (v_finished = 1) then
        insert into discountdetails values (prod_id, newcampid, 'S', 20);
        insert into discountdetails values (prod_id, newcampid, 'G', 30);
        insert into discountdetails values (prod_id, newcampid, 'P', 40);
    end if;
    until v_finished
    end repeat;
    close camp_c;
    end//

CodePudding user response：

One solution would be to use:

insert ignore into `Campaign` ...

The ignore means do not generate an error if the insert results in a duplicate key or other type of error.

A different solution might be to use a boolean variable:

declare v_do_insert tinyint(1) default true;

...

repeat
  insert  into `Campaign`(`CampaignID`,`CampaignStartDate`,`CampaignEndDate`) 
  select newcampid, date_add(curdate(), interval 4 week), date_add( curdate(), interval 8 week)
  from dual where v_do_insert=true;

  set v_do_insert = false;

  ...
until v_finished
end repeat;

In this example, the insert...select would insert one row the first time through the loop, but then on subsequent iterations of the loop, v_do_insert is now false, so the insert...select would insert zero rows.