Mysql Cursor Parameter

Question

I am new to mysql ,in oracle we can achieve by using cursor parameter .

I want to print

OUTPUT :
Department number
10 
EMPLOYEE DETAILS
Ravi  Kumar  3000  10
vijay Kumar  5000  10
Department number
20 
EMPLOYEE DETAILS
John  NULL  3000  10

Below is my 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 FOR SELECT department_id FROM dept WHERE DEPARTMENT_ID in(SELECT DEPARTMENT_ID FROM EMP);
      DECLARE cur_emp  CURSOR FOR SELECT first_name,last_name,last_name FROM EMP WHERE department_id =l_department_id ;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = TRUE ;
      DECLARE CONTINUE HANDLER FOR 1329 SET X_STATUS = 'error' ;
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'PROGRAM ERROR PLEASE CHECK';
      SELECT 1;
      OPEN cur_dept ;
      l_dept: LOOP
      FETCH cur_dept INTO l_department_id ;
      IF d then
       CLOSE cur_dept ;
       Leave l_dept ;
      END IF ; 
      SELECT l_department_id ;  
       OPEN cur_emp ;
       l_emp: LOOP
       SELECT 2;
       FETCH cur_emp INTO l_first_name
                          ,l_last_name
                          ,l_job_id
                          ;
      IF d then
      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 its coming for first record
Please help me out from above output

Viru · Accepted Answer · 2013-02-18 10:44:04Z

Finally got solution .

CREATE PROCEDURE XX_MULTI_CURSOR()
 BLOCK1: begin
   declare v_col1 int;                     
   declare no_more_rows1 boolean DEFAULT FALSE;  
   declare cursor1 cursor for              
       select DEPARTMENT_ID
       from   DEPT
       where DEPARTMENT_ID IN (90,30);
   declare continue handler for not found  
       set no_more_rows1 = TRUE;           
   open cursor1;
   LOOP1: loop
       fetch cursor1
       into  v_col1;
       SELECT v_col1 ;
       if no_more_rows1 then
           close cursor1;
           leave LOOP1;
       end if;
       BLOCK2: begin
           declare v_col2 int;
           declare no_more_rows2 boolean DEFAULT FALSE;
           declare cursor2 cursor for
               select EMPLOYEE_ID
               from   EMP
               where  DEPARTMENT_ID = v_col1;
           declare continue handler for not found
               set no_more_rows2 = TRUE;
           open cursor2;
           LOOP2: loop
               fetch cursor2
               into  v_col2;
                SELECT v_col2;
               if no_more_rows2 then
                   close cursor2;
                   leave LOOP2;
               end if;
           end loop LOOP2;
       end BLOCK2;
   end loop LOOP1;
   end BLOCK1;

Collectives™ on Stack Overflow

Mysql Cursor Parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related