Calculation function with dynamic cursor

1

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

Follow

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 would like that when prm_entrada_registro is equal to occurrence look for table ocorrencias_historico and case prm_entrada_registro is equal to task search from table tarefas_historico .

Is it possible?

Currently generating 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_historical CURSOR FOR SELECT   id, status , date_action, time_action FRO 'at line 37

    
asked by anonymous 30.11.2017 / 18:49

1 answer

1

As far as I know MySQL has some restrictions with DECLARE, You can not do DECLARE after some operations, so work in this way, Declare the 2 cursors and work with the IF's checking where to use cursor 1 or 2 in 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 ;
    
30.11.2017 / 19:02