Calculation function with dynamic cursor

Question

I am trying to create a database function that when receiving a parameter value that contains a certain value it creates a cursor with the specific table.

Follows

DELIMITER $$

drop function if exists `fn_calculo_sla`;

CREATE FUNCTION `fn_calculo_sla`(
    `prm_id` int,
    `prm_entrada_registro` varchar(50)
)
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN

-- <inicio_declaracoes> 

  -- declara variaveis de associação para usar no loop
  DECLARE var_id INT(11);
  DECLARE var_status INT(11);
  DECLARE var_data_acao DATE;
  DECLARE var_hora_acao TIME;

  -- declara variaveis de associação para utilizar para buscar o próximo registro com base no atual
  DECLARE var_id_next INT(11);
  DECLARE var_status_next INT(11);
  DECLARE var_data_acao_next DATE;
  DECLARE var_hora_acao_next TIME;

  -- declara variaveis de controle
  DECLARE done TINYINT(1) DEFAULT FALSE; -- para uso no CURSOR
  DECLARE tempo_trabalho DOUBLE DEFAULT 0;
  DECLARE tempo_livre DOUBLE DEFAULT 0; -- tempo livre ( exemplo: após horario de trabalho, finais de semana, feriados )

  DECLARE status_ocorrencia_nao_gera_sla  VARCHAR(200) DEFAULT ''; 
  DECLARE status_ocorrencia_fechada       VARCHAR(200) DEFAULT ''; 
  -- declara cursores

  IF(prm_entrada_registro = 'ocorrencia') THEN    
      DECLARE dados_historico CURSOR FOR SELECT id, `status`, data_acao, hora_acao FROM ocorrencias_historico WHERE ocorrencia_id = prm_id order by data_acao, hora_acao;
  ELSEIF (prm_entrada_registro = 'tarefa') THEN   
     DECLARE dados_historico CURSOR FOR SELECT id, `status`, data_acao, hora_acao FROM tarefas_historico WHERE tarefa_id = prm_id order by data_acao, hora_acao;
  END IF;

  -- outros
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- quando não tiver mais registros no CURSOR aberto a variavel 'done' recebe true


-- <fim_declaracoes>

  OPEN dados_historico; -- abre o CURSOR e seleciona os registros e associa

  select ifnull(encerra_ocorrencia,'') into status_ocorrencia_fechada 
    from ( select group_concat( id ) as encerra_ocorrencia from status_ocorrencia where encerra_ocorrencia = 1) x limit 1;

  select ifnull(conta_tempo_sla,'') into status_ocorrencia_nao_gera_sla
    from ( select group_concat( id ) as conta_tempo_sla from status_ocorrencia where conta_tempo_sla = 0) x limit 1;

  FETCH dados_historico INTO var_id, var_status, var_data_acao, var_hora_acao; -- Associa a linha atual do registro nas variaveis, obedecer a ordem do SELECT    

  read_loop: LOOP -- declara a região de LOOP para percorrer o cursor

     FETCH NEXT FROM dados_historico INTO var_id_next, var_status_next, var_data_acao_next, var_hora_acao_next;-- Associa a próxima linha do registro nas variaveis, obedecer a ordem do SELECT    

     IF done THEN -- Verificar se ainda há registros na próxima posição

       IF ( FIND_IN_SET(var_status,status_ocorrencia_nao_gera_sla) = 0 AND FIND_IN_SET(var_status,status_ocorrencia_fechada) = 0 ) THEN

        set var_data_acao_next = CURDATE();
        set var_hora_acao_next = CURTIME();

        set tempo_trabalho = tempo_trabalho + TIMESTAMPDIFF(SECOND, CONCAT(var_data_acao,' ',var_hora_acao), CONCAT(var_data_acao_next,' ',var_hora_acao_next)); -- Calcula a diferença entre as duas datas

        set tempo_livre = tempo_livre + fn_calculo_tempo_livre(var_status,var_data_acao, var_hora_acao, var_data_acao_next, var_hora_acao_next, var_id);

       END IF;

        LEAVE read_loop; -- Sair do loop
     ELSE

        set tempo_trabalho = tempo_trabalho + TIMESTAMPDIFF(SECOND, CONCAT(var_data_acao,' ',var_hora_acao), CONCAT(var_data_acao_next,' ',var_hora_acao_next)); -- Calcula a diferença entre as duas datas

         set tempo_livre = tempo_livre + fn_calculo_tempo_livre(var_status, var_data_acao, var_hora_acao, var_data_acao_next, var_hora_acao_next, var_id);

     END IF;

     SET var_status    := var_status_next;
     SET var_data_acao := var_data_acao_next;
     SET var_hora_acao := var_hora_acao_next;
     set var_id          := var_id_next;

  END LOOP;

  CLOSE dados_historico;  

  IF ( tempo_trabalho > 0 ) THEN
    SET tempo_trabalho = (tempo_trabalho - tempo_livre); 
  END IF;

  RETURN tempo_trabalho;
END $$

DELIMITER ;

I wish that when prm_entrada_registro is equal to occurrence look for the table ocorrencias_historico and case prm_entrada_registro be equal to task table search tarefas_historico.

It is possible?

Currently generating an error:

SQL error (1064) in structure #2: You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'DECLARE data_historico CURSOR FOR SELECT id, status, data_acao, hora_acao FRO' at line 37

Answer 1

As far as I know Mysql has some restrictions with DECLARE, You cannot DECLARE after some operations, so work this way, Declare the 2 cursors and work with the IF’s checking where to use cursor 1 or 2 in the case, something like this:

DELIMITER $$

drop function if exists `fn_calculo_sla`;

CREATE FUNCTION `fn_calculo_sla`(
    `prm_id` int,
    `prm_entrada_registro` varchar(50)
)
RETURNS double
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN

-- <inicio_declaracoes> 

  -- declara variaveis de associação para usar no loop
  DECLARE var_id INT(11);
  DECLARE var_status INT(11);
  DECLARE var_data_acao DATE;
  DECLARE var_hora_acao TIME;

  -- declara variaveis de associação para utilizar para buscar o próximo registro com base no atual
  DECLARE var_id_next INT(11);
  DECLARE var_status_next INT(11);
  DECLARE var_data_acao_next DATE;
  DECLARE var_hora_acao_next TIME;

  -- declara variaveis de controle
  DECLARE done TINYINT(1) DEFAULT FALSE; -- para uso no CURSOR
  DECLARE tempo_trabalho DOUBLE DEFAULT 0;
  DECLARE tempo_livre DOUBLE DEFAULT 0; -- tempo livre ( exemplo: após horario de trabalho, finais de semana, feriados )

  DECLARE status_ocorrencia_nao_gera_sla  VARCHAR(200) DEFAULT ''; 
  DECLARE status_ocorrencia_fechada       VARCHAR(200) DEFAULT ''; 
  -- declara cursores


     DECLARE dados_historico1 CURSOR FOR SELECT id, `status`, data_acao, hora_acao FROM ocorrencias_historico WHERE ocorrencia_id = prm_id order by data_acao, hora_acao;

     DECLARE dados_historico2 CURSOR FOR SELECT id, `status`, data_acao, hora_acao FROM tarefas_historico WHERE tarefa_id = prm_id order by data_acao, hora_acao;


  -- outros
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- quando não tiver mais registros no CURSOR aberto a variavel 'done' recebe true


-- <fim_declaracoes>

  IF(prm_entrada_registro = 'ocorrencia') THEN    
      OPEN dados_historico1;
  ELSEIF (prm_entrada_registro = 'tarefa') THEN   
      OPEN dados_historico2;
  END IF; -- abre o CURSOR e seleciona os registros e associa

  select ifnull(encerra_ocorrencia,'') into status_ocorrencia_fechada 
    from ( select group_concat( id ) as encerra_ocorrencia from status_ocorrencia where encerra_ocorrencia = 1) x limit 1;

  select ifnull(conta_tempo_sla,'') into status_ocorrencia_nao_gera_sla
    from ( select group_concat( id ) as conta_tempo_sla from status_ocorrencia where conta_tempo_sla = 0) x limit 1;


IF(prm_entrada_registro = 'ocorrencia') THEN    
      FETCH dados_historico1 INTO var_id, var_status, var_data_acao, var_hora_acao; -- Associa a linha atual do registro nas variaveis, obedecer a ordem do SELECT  
  ELSEIF (prm_entrada_registro = 'tarefa') THEN   
      FETCH dados_historico2 INTO var_id, var_status, var_data_acao, var_hora_acao; -- Associa a linha atual do registro nas variaveis, obedecer a ordem do SELECT  
  END IF; 



  read_loop: LOOP -- declara a região de LOOP para percorrer o cursor


  IF(prm_entrada_registro = 'ocorrencia') THEN    
      FETCH NEXT FROM dados_historico1 INTO var_id_next, var_status_next, var_data_acao_next, var_hora_acao_next;-- Associa a próxima linha do registro nas variaveis, obedecer a ordem do SELECT    
  ELSEIF (prm_entrada_registro = 'tarefa') THEN   
      FETCH NEXT FROM dados_historico2 INTO var_id_next, var_status_next, var_data_acao_next, var_hora_acao_next;-- Associa a próxima linha do registro nas variaveis, obedecer a ordem do SELECT    
  END IF; 

     IF done THEN -- Verificar se ainda há registros na próxima posição

       IF ( FIND_IN_SET(var_status,status_ocorrencia_nao_gera_sla) = 0 AND FIND_IN_SET(var_status,status_ocorrencia_fechada) = 0 ) THEN

        set var_data_acao_next = CURDATE();
        set var_hora_acao_next = CURTIME();

        set tempo_trabalho = tempo_trabalho + TIMESTAMPDIFF(SECOND, CONCAT(var_data_acao,' ',var_hora_acao), CONCAT(var_data_acao_next,' ',var_hora_acao_next)); -- Calcula a diferença entre as duas datas

        set tempo_livre = tempo_livre + fn_calculo_tempo_livre(var_status,var_data_acao, var_hora_acao, var_data_acao_next, var_hora_acao_next, var_id);

       END IF;

        LEAVE read_loop; -- Sair do loop
     ELSE

        set tempo_trabalho = tempo_trabalho + TIMESTAMPDIFF(SECOND, CONCAT(var_data_acao,' ',var_hora_acao), CONCAT(var_data_acao_next,' ',var_hora_acao_next)); -- Calcula a diferença entre as duas datas

         set tempo_livre = tempo_livre + fn_calculo_tempo_livre(var_status, var_data_acao, var_hora_acao, var_data_acao_next, var_hora_acao_next, var_id);

     END IF;

     SET var_status    := var_status_next;
     SET var_data_acao := var_data_acao_next;
     SET var_hora_acao := var_hora_acao_next;
     set var_id          := var_id_next;

  END LOOP;

  IF(prm_entrada_registro = 'ocorrencia') THEN    
      CLOSE dados_historico1;
  ELSEIF (prm_entrada_registro = 'tarefa') THEN   
      CLOSE dados_historico2;
  END IF; 

  IF ( tempo_trabalho > 0 ) THEN
    SET tempo_trabalho = (tempo_trabalho - tempo_livre); 
  END IF;

  RETURN tempo_trabalho;
END $$

DELIMITER ;