Created
January 3, 2017 16:53
-
-
Save primogf/3733753edd549ba4f69bce4743440390 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE DEFINER=`root`@`localhost` PROCEDURE `importacao_lancamentos`() | |
BEGIN | |
DECLARE fim INT DEFAULT false; | |
DECLARE var_id_lancamento_contabil_item int; | |
DECLARE var_id_lancamento_contabil int; | |
DECLARE var_id_conta_debito int; | |
DECLARE var_id_conta_credito int; | |
DECLARE var_valor_debito decimal(20,2); | |
DECLARE var_valor_credito decimal(20,2); | |
DECLARE var_stat tinyint; | |
-- criando o cursor | |
DECLARE curDados CURSOR FOR | |
select | |
id_lancamento_contabil_item, | |
id_lancamento_contabil, | |
id_conta_debito, | |
id_conta_credito, | |
valor_debito, | |
valor_credito, | |
stat | |
from | |
con_lancamento_contabil_item | |
where | |
stat <> 0 | |
order by id_lancamento_contabil_item; | |
DECLARE CONTINUE handler FOR NOT found SET fim = TRUE; | |
OPEN curDados; | |
-- loop no cursor e gravando na tabela temporaria | |
READ_LOOP:LOOP | |
FETCH curDados | |
INTO | |
var_id_lancamento_contabil_item, | |
var_id_lancamento_contabil, | |
var_id_conta_debito, | |
var_id_conta_credito, | |
var_valor_debito, | |
var_valor_credito, | |
var_stat; | |
IF var_valor_debito > 0 THEN | |
insert into AccountingTransactionItem (id_transacao_contabil,id_conta_contabil,valor,natureza,stat) values (var_id_lancamento_contabil,var_id_conta_debito,var_valor_debito,'D',var_stat); | |
ELSE | |
insert into AccountingTransactionItem (id_transacao_contabil,id_conta_contabil,valor,natureza,stat) values (var_id_lancamento_contabil,var_id_conta_credito,var_valor_credito,'C',var_stat); | |
end IF; | |
IF fim THEN | |
LEAVE read_loop; | |
end IF; | |
end LOOP; | |
close curDados; | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment