1

I created a store procedure to do some insertions, fetch the records of a table and insert it into another table. For that I used cursor (my first use of cursor). My script below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `tenant_creation`(
  IN admin_id int, tenant_name varchar(50),tenant_url varchar(50),
  tenant_email varchar(50),tenant_phone varchar(50),first_name varchar(50),
last_name varchar(50),plan_id varchar(50),IN is_available tinyint
)
BEGIN

  DECLARE exit handler for sqlexception
   BEGIN
   ROLLBACK;
  END;

DECLARE exit handler for sqlwarning
  BEGIN
   ROLLBACK;
  END;

 BEGIN
    START TRANSACTION;

#insert Tenant details
Insert into tenant(admin_id,tenant_name,tenant_url,tenant_email,tenant_phone,first_name,last_name,plan_id,  is_available) 
values(admin_id,tenant_name,tenant_url,tenant_email,tenant_phone,first_name,last_name,plan_id,is_available);

SET @tenant_id = LAST_INSERT_ID();

#create Administrator Group
Insert into user_group(tenant_id,user_group_name,description) 
values(@tenant_id,"Administrator","Default Administrator's Group");

SET @group_id = LAST_INSERT_ID();

#Create Default Tenan Admin User
Insert into users(tenant_id,username,`password`,firstname,lastname,email) 
values(@tenant_id,"Administrator","$2y$10$AxTjqOxYk6atjDSO2Q4iQOprSvYMnR/jAboqfDYkvC1IG43Rwknw6","Default","Default",tenant_email);

#Attach the user to the group
Set @user_id = LAST_INSERT_ID();

Insert into user_group_relationship(tenant_id,user_group_id,user_id)
values(@tenant_id,@group_id,@user_id);

#create Admin profile
insert into profile(tenant_id,profile_name,description)
values(@tenant_id,"Administrator","Administrator Profile");

#Attach the Admin group to the profile
Set @profile_id = LAST_INSERT_ID();

Insert into profile_group_relationship(tenant_id,group_id,profile_id)
values(@tenant_id,@group_id,@profile_id);

 END;

 BEGIN

#bind the profile to the available screens
 #1 fetch all the modules in system
 DECLARE cur CURSOR FOR Select system_screen.screen_id,system_screen.screen_name,module_screen_relationship.module_id
     from system_screen 
     join module_screen_relationship
     on module_screen_relationship.screen_id = system_screen.screen_id;

     begin
        DECLARE done INT DEFAULT 0;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        OPEN cur;
        read_loop: LOOP

            #Fetch one record from CURSOR and set to some variable(If not found then done will be set to 1 by continue handler)
            FETCH cur INTO c_screen_id,c_screen_name,c_module_id;  
            #If done set to 1 then exit the loop else continue
            IF done THEN
                LEAVE read_loop;  
            END IF;

            Insert into profile_screen_relationship(tenant_id,profile_id,screen_id,module_id,access_level) 
            values(@tenant_id,@profile_id,c_screen_id,c_module_id,4);

        END LOOP read_loop;
        #Closing the cursor
        CLOSE cur; 
    End;

     COMMIT;
   END;
END

but I only got "Error 1327: Undeclared variable: c_screen_id".

What do you think I am doing wrong?

1 Answer 1

1

I believe that you have to declare the variables at the beggining as the official documentation shows:

http://dev.mysql.com/doc/refman/5.7/en/cursors.html

DECLARE a CHAR(16);
DECLARE b, c INT;

....

FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.