CODEWITHSUNDEEP
mysql
Sunil kumar j s
Sunil kumar j s

Reputation: 21

nesting of two cursor in mysql

i am trying to nest two cursors.however i am getting syntax for line "declare no_more rows boolean:=FALSE".i am using phpMyAdmin and running in this on MySQL console. Also can i do this without declaring stored procedure? please help me out here. this thing is breaking my head past three days. following is the best code i could write:

Delimiter $$
create procedure test1()
BEGIN
BLOCK1: begin
   declare v_col1 int(10);                     
   declare no_more_rows boolean1 := FALSE;  
   declare cursor1 cursor for              
    select content_id
    from   topic_list where topic_id=1;
   declare continue handler for not found  
    set no_more_rows1 := TRUE;           
   open cursor1;
   LOOP1: loop
      fetch cursor1
      into  v_col1;
      if no_more_rows1 then
        close cursor1;
        leave LOOP1;
      end if;
    BLOCK2: begin
        declare v_col2 int(10);
        declare no_more_rows2 boolean := FALSE;
        declare cursor2 cursor for
            select content_id
            from   content_upvotes
            where  u_id_upvoter = v_col1;
        declare continue handler for not found
           set no_more_rows2 := TRUE;
        open cursor2;
        LOOP2: loop
            fetch cursor2
            into  v_col2;
            if no_more_rows then
                close cursor2;
                leave LOOP2;
            end if;
        end loop LOOP2;
    end BLOCK2;
    end loop LOOP1;
end BLOCK1;
end $$
DELIMITER ;

Upvotes: 1

Views: 130

Answers (1)

Shadow
Shadow

Reputation: 34231

If you check mysql's documentation on declare syntax, xou can see that the defsult value is set using the DEFAULT keyword, not the asdignment operator:

DECLARE var_name [, var_name] ... type [DEFAULT value]

So, your declaration should be

declare no_more_rows tinyint(1) default 0;

I also mapped the boolean type to tinyint(1).

Upvotes: 1

Related Questions