Procedure no MYSQL

[Evandro Choma]

Boa Tarde

Tenho no sistema em Delphi 7 com Mysql 5.0.37 a importação de dados que acontece de um arquivo texto selecionado pelo usuário. Estava dando erro quando o arquivo passava de 4 mil linhas, daí implementei de criar uma tabela temporária no banco e jogar todo o conteúdo do arquivo texto nesta temporária, agilizou o processo e resolver de pegar arquivos muito longos, mas acontece que meus arquivos reais giram em torno de 9 mil linhas e para fazer esta importação demora-se em torno de 15 minutos, fiz o teste com todos os produtos da empresa, inclusive os inativos para testar e a base de 26.926 linhas demorou mais de 50 minutos para processar, daí resolvi fazer um procedure no banco de dados para dar agilidade ao processo, mas nunca trabalhei antes com procedure, function ou trigger no MYSQL.

Fiz o seguinte no Delphi eu crio a tabela temporária, e povoa ela com o arquivo texto, isto acontece em frações de segundos. Daí chamo a procedure para processar a temporária e salvar nas tabelas corretas os dados, depois deleto a temporária, esta é a idéia.

1ª Dúvida.

O nome da tabela temporária que eu passo via parâmetro na hora de chamar a procedure, este parâmetro chamei de var_sTabela, como fazer o select nela já que o nome da tabela está em parâmetro?

Se não fosse por parâmetro faria assim:

DECLARE varreTemporaria CURSOR FOR SELECT * FROM nome_da_tabela;

2ª Dúvida

Preciso retirar de alguns campos os caracteres “, ‘ e ´, para isto utilizo a função replace, a forma abaixo está correta? No caso da ‘ (aspa simples) poderia como no Delphi utilizar o chr(39)?

SET var_sUnidade = REPLACE(var_sUnidade, '"', '');

SET var_sUnidade = REPLACE(var_sUnidade, '`', '');

SET var_sUnidade = REPLACE(var_sUnidade, chr(39), '');

SET var_sUnidade = LTRIM(var_sUnidade);

SET var_sUnidade = RTRIM(var_sUnidade);

Na ultima replace puderia já utilizar o TRIM como no Delphi deixando assim?

SET var_sUnidade = RTRIM(LTRIM (REPLACE(var_sUnidade, chr(39), '')));

3ª Dúvida

para a quantidade o valor que é passado no arquivo texto é multiplicado por 1000 para ser inteiro então eu vejo o valor e divido por 1000 assim tenho o valor, inteiro ou fracionado. estaria certo pegar o valor em String e fazer desta forma para ter o valor de ponto flutuante?

fPosicaoEstoque := convert(sPosicaoEstoque, signed) / 1000;

Obrigado

Evandro Choma

[Denis Courcy]

...

1ª Dúvida.

O nome da tabela temporária que eu passo via parâmetro na hora de chamar a procedure, este parâmetro chamei de var_sTabela, como fazer o select nela já que o nome da tabela está em parâmetro?

Se não fosse por parâmetro faria assim:

DECLARE varreTemporaria CURSOR FOR SELECT * FROM nome_da_tabela;

Preciso verivicar alguns pontos. Te respondo depois.

2ª Dúvida

Preciso retirar de alguns campos os caracteres “, ‘ e ´, para isto utilizo a função replace, a forma abaixo está correta? No caso da ‘ (aspa simples) poderia como no Delphi utilizar o chr(39)?

SET var_sUnidade = REPLACE(var_sUnidade, '"', '');

SET var_sUnidade = REPLACE(var_sUnidade, '`', '');

SET var_sUnidade = REPLACE(var_sUnidade, chr(39), '');

SET var_sUnidade = LTRIM(var_sUnidade);

SET var_sUnidade = RTRIM(var_sUnidade);

Na ultima replace puderia já utilizar o TRIM como no Delphi deixando assim?

SET var_sUnidade = RTRIM(LTRIM (REPLACE(var_sUnidade, chr(39), '')));

Você pode usar "'" para representar a aspas simples ou '"' para representar as aspas duplas. Para o MySQL ' ou " podem ser usados como delimitadores de string

Você pode usar a função TRIM para retirar os espaços antes e depois. Abaixo alguns exemplos de como usá-la:

mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'

3ª Dúvida

para a quantidade o valor que é passado no arquivo texto é multiplicado por 1000 para ser inteiro então eu vejo o valor e divido por 1000 assim tenho o valor, inteiro ou fracionado. estaria certo pegar o valor em String e fazer desta forma para ter o valor de ponto flutuante?

fPosicaoEstoque := convert(sPosicaoEstoque, signed) / 1000;

O MySQl converte automaticamente string para numerico sem necessidade de uso de função. Então,

SET fPosicaoEstoque = sPosicaoEstoque / 1000;

é uma operação válida.

[Evandro Choma]

Sobre o nome da Tabela ele gerar tempo o código do cliente eu modifiquei a idéia e estou criando a tabale com o nome TempImp assim resolve o primeiro problema, acredito que não será feito 2 importações ao mesmo tempo.

Em relação a substituir os caracteres fiz assim então:

SET var_sCodigoProduto = REPLACE(var_sCodigoProduto, '"', '');

SET var_sCodigoProduto = REPLACE(var_sCodigoProduto, '`', '');

SET var_sCodigoProduto = TRIM(REPLACE(var_sCodigoProduto, "'", ''));

e a conversão é mais fácil do que pensei.

[Denis Courcy]

...

1ª Dúvida.

O nome da tabela temporária que eu passo via parâmetro na hora de chamar a procedure, este parâmetro chamei de var_sTabela, como fazer o select nela já que o nome da tabela está em parâmetro?

Se não fosse por parâmetro faria assim:

DECLARE varreTemporaria CURSOR FOR SELECT * FROM nome_da_tabela;

Preciso verivicar alguns pontos. Te respondo depois.

Como pensei. Após ler o manual do MySQL constatei não haver meio de passar uma instrução SQl para a construção de um cursor em tempo de execução.

Você pode criar instruções SQL em tempo de execução com o comando PREPARE, EXECUTE e DEALLOC mas este comando não pode gerar um cursor.

Uma saída poder ser usar um nome de tabela padrão. DROP e RECRIE a tabela antes da execução da procedure. Assim a instução SQl a que seu cursor se refere não será afetada.

[Evandro Choma]

Outras dúvidas apareceram

Fazendo via código Delphi ou até mesmo PHP eu faço um select pego resultado com um try se não vim nada vai para o except onde forço o valor 0 na variável que recebe o resultado do Select. pois se a variável for 0 eu faço o comando insert caso contrário faço o Update. como faria aqui?

Como eu utilizo o resultado de um select estilo o select abaixo ou um count?

SELECT ID FROM produtos

WHERE Clientes_ID = var_iClienteID and

CODIGOCLIENTE = var_sCodigoProduto ;

[Denis Courcy]

Outras dúvidas apareceram

Fazendo via código Delphi ou até mesmo PHP eu faço um select pego resultado com um try se não vim nada vai para o except onde forço o valor 0 na variável que recebe o resultado do Select. pois se a variável for 0 eu faço o comando insert caso contrário faço o Update. como faria aqui?

Como eu utilizo o resultado de um select estilo o select abaixo ou um count?

SELECT ID FROM produtos

WHERE Clientes_ID = var_iClienteID and

CODIGOCLIENTE = var_sCodigoProduto ;

Faça assim, supondo que seu código esteja dentro de uma procedure:

DECLARE meuID int unsigned;
SET meuID = (SELECT ID FROM produtos 
WHERE Clientes_ID = var_iClienteID and 
      CODIGOCLIENTE = var_sCodigoProduto );
IF meuID IS NULL THEN
   SET meuID = 0;
END IF;

[Evandro Choma]

Terminei a procedure, porém na hora de criar a mesma no PHPMyAdmin deu o seguinte erro:

Mensagens do MySQL :

#1064 - 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 'DELIMITER $$

DROP PROCEDURE IF EXISTS PCIMPORTAPRODUTOS $$

CREATE PROCEDURE ' at line 1

O início da procedure está assim:

DELIMITER $$

DROP PROCEDURE IF EXISTS PCIMPORTAPRODUTOS $$

CREATE PROCEDURE PCIMPORTAPRODUTOS (

IN var_iClienteID INT UNSIGNED,

IN var_iServico INT UNSIGNED,

IN var_USUARIO INT UNSIGNED,

OUT var_iQtdeErro INT UNSIGNED,

OUT var_iQtdeAtualizado INT UNSIGNED,

OUT var_MENSAGEM TEXT)

BEGIN

-- variaveis que serao utilizadas

DECLARE var_sCodigoProduto varchar(20);

DECLARE var_sDescricao varchar(100);

[Denis Courcy]

Terminei a procedure, porém na hora de criar a mesma no PHPMyAdmin deu o seguinte erro:

Mensagens do MySQL :

#1064 - 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 'DELIMITER $$

DROP PROCEDURE IF EXISTS PCIMPORTAPRODUTOS $$

CREATE PROCEDURE ' at line 1

Já tive alguns erros deste tipo.

Algumas verões do MySQL informa que a instrução delimiter deve ser como você descreveu. Outras versões, como a sua, você deverá usar deste jeito:

-- Inicie com o comando abaixo
DELIMITER $$;
-- e encerre com o comando abaixo
DELIMITER;$$

[Evandro Choma]

Agora deu o seguinte erro:

Erro

consulta SQL:

DELIMITER $$;

Mensagens do MySQL :

#1064 - 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 'DELIMITER $$' at line 1

[Denis Courcy]

'Evandro Choma',

Verifique se sua procedure está assim:

DELIMITER $$

USE `nome do banco`$$

DROP PROCEDURE IF EXISTS `nome da procedure`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `nome da procedure`(IN dtinicial DATE, IN dtfinal DATE)
BEGIN
 todas as instruções aqui dentro terminam com;
END$$

DELIMITER;

Eu uso a versão 5.1Para a versão 5.0.37 use :

DELIMITER $$;

USE `nome do banco`$$

DROP PROCEDURE IF EXISTS `nome da procedure`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `nome da procedure`(IN param1 DATE, IN param2 DATE)
BEGIN
 todas as instruções aqui dentro terminam com;
END$$

DELIMITER $$;

[Evandro Choma]

O Erro foi o mesmo, veja a procedure como está:

DELIMITER $$;

USE `sie` $$

DROP PROCEDURE IF EXISTS `PCIMPORTAPRODUTOS` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `PCIMPORTAPRODUTOS` (

IN var_iClienteID INT UNSIGNED,

IN var_iServico INT UNSIGNED,

IN var_USUARIO INT UNSIGNED,

OUT var_iQtdeErro INT UNSIGNED,

OUT var_iQtdeAtualizado INT UNSIGNED,

OUT var_MENSAGEM TEXT)

BEGIN

-- variaveis que serao utilizadas

DECLARE var_sCodigoProduto varchar(20);

DECLARE var_sDescricao varchar(100);

DECLARE var_sUnidade varchar(10);

DECLARE var_sControlaLote char(1) DEFAULT 'N';

DECLARE var_sControlaDV char(1) DEFAULT 'N';

DECLARE var_sCampo1 varchar(100);

DECLARE var_sCampo2 varchar(100);

DECLARE var_sCampo3 varchar(100);

DECLARE var_sCampo4 varchar(100);

DECLARE var_sCampo5 varchar(100);

DECLARE var_sCodigoBarras varchar(20);

DECLARE var_sSKU varchar(20);

DECLARE var_iSKU INT DEFAULT 1;

DECLARE var_sPosicaoEstoque varchar(20);

DECLARE var_fPosicaoEstoque decimal(16,5) DEFAULT '0.0000';

DECLARE existe_mais_linhas INT DEFAULT 0;

DECLARE var_sMotivoPendente varchar(1000);

DECLARE var_ID_PROD INT unsigned;

DECLARE var_ID_PRODSKU INT unsigned;

DECLARE var_Existe INT unsigned;

SET var_iQtdeErro = 0;

SET var_iQtdeAtualizado = 0;

DECLARE varreTemporaria CURSOR FOR SELECT * FROM TempImp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET existe_mais_linhas=1;

OPEN varreTemporaria;

REPEAT

FETCH varreTemporaria INTO var_sCodigoProduto, var_sDescricao, var_sUnidade, var_sControlaLote, var_sControlaDV, var_sCampo1, var_sCampo2, var_sCampo3, var_sCampo4, var_sCampo5, var_sCodigoBarras, var_sSKU, var_sPosicaoEstoque;

SET var_sMotivoPendente = '';

SET var_sCodigoProduto = REPLACE(var_sCodigoProduto, '"', '');

SET var_sCodigoProduto = REPLACE(var_sCodigoProduto, '`', '');

SET var_sCodigoProduto = TRIM(REPLACE(var_sCodigoProduto, "'", ''));

IF (var_sCodigoProduto IS NULL) then

SET var_sMotivoPendente = 'código do produto não informado, ';

END IF;

SET var_sDescricao = REPLACE(var_sDescricao, '"', '');

SET var_sDescricao = REPLACE(var_sDescricao, '`', '');

SET var_sDescricao = TRIM(REPLACE(var_sDescricao, "'", ''));

IF (var_sDescricao IS NULL) then

SET var_sMotivoPendente = var_sMotivoPendente + 'descrição do produto não informada, ';

END IF;

SET var_sUnidade = REPLACE(var_sUnidade, '"', '');

SET var_sUnidade = REPLACE(var_sUnidade, '`', '');

SET var_sUnidade = TRIM(REPLACE(var_sUnidade, "'", ''));

SET var_sControlaLote = REPLACE(var_sControlaLote, '"', '');

SET var_sControlaLote = REPLACE(var_sControlaLote, '`', '');

SET var_sControlaLote = TRIM(REPLACE(var_sControlaLote, "'", ''));

IF (var_sControlaLote IS NULL) then

SET var_sControlaLote = 'N';

END IF;

SET var_sControlaDV = REPLACE(var_sControlaDV, '"', '');

SET var_sControlaDV = REPLACE(var_sControlaDV, '`', '');

SET var_sControlaDV = TRIM(REPLACE(var_sControlaDV, "'", ''));

IF (var_sControlaDV IS NULL) then

SET var_sControlaDV = 'N';

END IF;

SET var_sCampo1 = REPLACE(var_sCampo1, '"', '');

SET var_sCampo1 = REPLACE(var_sCampo1, '`', '');

SET var_sCampo1 = TRIM(REPLACE(var_sCampo1, "'", ''));

SET var_sCampo2 = REPLACE(var_sCampo2, '"', '');

SET var_sCampo2 = REPLACE(var_sCampo2, '`', '');

SET var_sCampo2 = TRIM(REPLACE(var_sCampo2, "'", ''));

SET var_sCampo3 = REPLACE(var_sCampo3, '"', '');

SET var_sCampo3 = REPLACE(var_sCampo3, '`', '');

SET var_sCampo3 = TRIM(REPLACE(var_sCampo3, "'", ''));

SET var_sCampo4 = REPLACE(var_sCampo4, '"', '');

SET var_sCampo4 = REPLACE(var_sCampo4, '`', '');

SET var_sCampo4 = TRIM(REPLACE(var_sCampo4, "'", ''));

SET var_sCampo5 = REPLACE(var_sCampo5, '"', '');

SET var_sCampo5 = REPLACE(var_sCampo5, '`', '');

SET var_sCampo5 = TRIM(REPLACE(var_sCampo5, "'", ''));

SET var_sCodigoBarras = REPLACE(var_sCodigoBarras, '"', '');

SET var_sCodigoBarras = REPLACE(var_sCodigoBarras, '`', '');

SET var_sCodigoBarras = TRIM(REPLACE(var_sCodigoBarras, "'", ''));

IF (var_sCodigoBarras IS NULL) then

SET var_sMotivoPendente = var_sMotivoPendente + 'código de barra do produto não informado, ';

END IF;

SET var_sSKU = REPLACE(var_sSKU, '"', '');

SET var_sSKU = REPLACE(var_sSKU, '`', '');

SET var_sSKU = TRIM(REPLACE(var_sSKU, "'", ''));

IF (var_sSKU IS NULL) then

SET var_iSKU = 1;

ELSE

SET var_iSKU = convert(var_sSKU, signed);

END IF;

SET var_sPosicaoEstoque = REPLACE(var_sPosicaoEstoque, '"', '');

SET var_sPosicaoEstoque = REPLACE(var_sPosicaoEstoque, '`', '');

SET var_sPosicaoEstoque = REPLACE(var_sPosicaoEstoque, ',', '');

SET var_sPosicaoEstoque = REPLACE(var_sPosicaoEstoque, '.', '');

SET var_sPosicaoEstoque = TRIM(REPLACE(var_sPosicaoEstoque, "'", ''));

IF ((var_sPosicaoEstoque IS NULL) or (var_sPosicaoEstoque = '0')) then

SET var_fPosicaoEstoque = 0;

ELSE

SET var_fPosicaoEstoque := var_sPosicaoEstoque / 1000;

END IF;

-- Caso possua algo pendente não modifica o banco de dados e grava a mensagem para apresentar ao usuário

IF (var_sMotivoPendente <> '') then

SET var_iQtdeErro = var_iQtdeErro + 1;

SET var_MENSAGEM = var_mensagem + var_iQtdeErro + 'º Registro não gravado. Código: ' + var_sCodigoProduto + ' Descrição do Produto: ' + var_sDescricao + chr(13) + 'Motivo: ' + (var_sMotivoPendente + chr(13);

ELSE

-- Não tendo nada pendente salva ou altera as informações no Banco de Dados

SET var_iQtdeAtualizado = var_iQtdeAtualizado + 1;

-- Produtos

SET var_ID_PROD = (SELECT ID FROM produtos WHERE Clientes_ID = var_iClienteID and CODIGOCLIENTE = 'var_sCodigoProduto');

IF var_ID_PROD IS NULL THEN

insert into produtos (Clientes_ID, CODIGOCLIENTE, DESCRICAO, UNIDADE,

CONTROLALOTE, CONTROLADATAVALIDADE, CAMPO1, CAMPO2, CAMPO3,

CAMPO4, CAMPO5, USUARIO_INCLUSAO, DATA_INCLUSAO

) values ( var_iClienteID, 'var_sCodigoProduto', 'var_sDescricao', 'var_sUnidade',

'var_sControlaLote', 'var_sControlaDV', 'var_sCampo1', 'var_sCampo2', 'var_sCampo3',

'var_sCampo4', 'var_sCampo5', var_USUARIO, now()

);

SET var_ID_PROD = ( SELECT DISTINCT LAST_INSERT_ID() FROM produtos);

ELSE

update produtos set CODIGOCLIENTE = 'var_sCodigoProduto',

DESCRICAO = 'var_sDescricao',

UNIDADE = 'var_sUnidade',

CONTROLALOTE = 'var_sControlaLote',

CONTROLADATAVALIDADE = 'var_sControlaDV',

CAMPO1 = 'var_sCampo1',

CAMPO2 = 'var_sCampo2',

CAMPO3 = 'var_sCampo3',

CAMPO4 = 'var_sCampo4',

CAMPO5 = 'var_sCampo5',

USUARIO_ULT_ALTERACAO = var_USUARIO,

DATA_ULT_ALTERACAO = now()

where ID = var_ID_PROD;

END IF;

-- Produto SKU

SET var_ID_PRODSKU = (SELECT ID FROM produtosku WHERE Produtos_ID = var_ID_PROD and CODIGOBARRAS = 'var_sCodigoBarras');

IF var_ID_PRODSKU IS NULL THEN

insert into produtosku (Produtos_ID, CODIGOBARRAS, QUANTIDADE, USUARIO_INCLUSAO, DATA_INCLUSAO

) values ( var_ID_PROD, 'var_sCodigoBarras', var_iSKU, var_USUARIO, now() );

SET var_ID_PRODSKU = ( SELECT DISTINCT LAST_INSERT_ID() FROM produtosku);

ELSE

update produtosku set QUANTIDADE = var_iSKU,

USUARIO_ULT_ALTERACAO = var_USUARIO,

DATA_ULT_ALTERACAO = now()

where ID = var_ID_PRODSKU ;

END IF;

-- Posição de estoque

SET var_Existe = (SELECT count(POSICAOESTOQUE) FROM produtoskuservico WHERE ProdutoSKU_ID = var_ID_PRODSKU and Servicos_ID = var_iServico);

IF var_Existe = 0 THEN

insert into produtoskuservico (ProdutoSKU_ID, Servicos_ID, POSICAOESTOQUE, USUARIO_INCLUSAO, DATA_INCLUSAO

) values (var_ID_PRODSKU, var_iServico, 'var_fPosicaoEstoque', var_USUARIO, now() );

ELSE

update produtoskuservico set POSICAOESTOQUE = 'var_fPosicaoEstoque',

USUARIO_ULT_ALTERACAO = var_USUARIO,

DATA_ULT_ALTERACAO = now()

where ProdutoSKU_ID = var_ID_PRODSKU and

Servicos_ID = var_iServico;

END IF;

END IF;

UNTIL existe_mais_linhas END REPEAT;

CLOSE varreTemporaria;

update servicos set DATAPOSICAOESTOQUE = now() where ID = var_iServico ;

DROP TABLE TempImp;

END $$

DELIMITER $$;