Mysql Cursor Parameter -

Mysql Cursor Parameter -

i new mysql ,in oracle can accomplish using cursor parameter .

i want print

output : section number 10 employee details ravi kumar 3000 10 vijay kumar 5000 10 section number 20 employee details john null 3000 10

below code

create procedure xx_dept_emp_dtls(out x_status varchar(200)) begin declare l_department_id ,l_employee_id ,l_dept_id int ; declare l_first_name ,l_last_name ,l_job_id varchar(50) ; declare d boolean default false ; declare cur_dept cursor select department_id dept department_id in(select department_id emp); declare cur_emp cursor select first_name,last_name,last_name emp department_id =l_department_id ; declare go on handler not found set d = true ; declare go on handler 1329 set x_status = 'error' ; declare go on handler sqlexception select 'program error please check'; select 1; open cur_dept ; l_dept: loop fetch cur_dept l_department_id ; if d close cur_dept ; leave l_dept ; end if ; select l_department_id ; open cur_emp ; l_emp: loop select 2; fetch cur_emp l_first_name ,l_last_name ,l_job_id ; if d close cur_emp ; leave l_emp ; end if ; select l_first_name ,l_last_name ,l_job_id ,l_dept_id ; end loop l_emp; end loop l_dept; end ;

so coming first record please help me out above output

finally got solution .

create procedure xx_multi_cursor() block1: begin declare v_col1 int; declare no_more_rows1 boolean default false; declare cursor1 cursor select department_id dept department_id in (90,30); declare go on handler not found set no_more_rows1 = true; open cursor1; loop1: loop fetch cursor1 v_col1; select v_col1 ; if no_more_rows1 close cursor1; leave loop1; end if; block2: begin declare v_col2 int; declare no_more_rows2 boolean default false; declare cursor2 cursor select employee_id emp department_id = v_col1; declare go on handler not found set no_more_rows2 = true; open cursor2; loop2: loop fetch cursor2 v_col2; select v_col2; if no_more_rows2 close cursor2; leave loop2; end if; end loop loop2; end block2; end loop loop1; end block1;

mysql