SQL no código x Stored Procedures
Fernanda Acacia
Mais Respostas
Ronaldo Lanhellas
11/10/2014
1 - Um Controle de numeração customizado de contrato. Você pode querer que cada contrato gerado (inserido) tenha um número personalizado e para isso pode usar stored procedures em conjunto com triggers.
Fernanda Acacia
11/10/2014
Ronaldo Lanhellas
11/10/2014
Considere que um processamento por uma stored procedure é mais rápido que um processamento de um SQL usado na aplicação. Isso porque a aplicação ainda tem que chamar a conexão com o banco de dados, estabelecer e estabiliza a mesma para só então executar o comando. Sobrecarregar ? Se for um bom banco de dados você nem sentirá a diferença de uma trigger sendo disparada.
Fernanda Acacia
11/10/2014
eu queria apenas para as operações basicas do banco.
Ronaldo Lanhellas
11/10/2014
eu queria apenas para as operações basicas do banco.
Depende muito fernanda do tipo de regra de negócio que você está aplicando. Geralmente CRUD (operações básicas) são feitas na aplicação e não no banco, isso porque o CRUD por mais básico que seja pode exigir tratamentos mais complexos.
Fernanda Acacia
11/10/2014
Ronaldo Lanhellas
11/10/2014
Fique a vontade. As operações que você acha que pode exigir a criação de stored procedures são aquelas no qual a sua aplicação não precisa saber, como é o caso da numeração de contratos que citei, a aplicação não precisa saber se o banco está gerando um número de contrato através de uma stored procedure, ela apenas usa.
Não quer dizer que coisas complexas fiquem na aplicação ou no banco, não existe essa divisão. O caso do CRUD que citei adapta-se melhor na aplicação, mas podem existem outros casos que o melhor é o banco.
Fernanda Acacia
11/10/2014
Fernanda Acacia
11/10/2014
Ronaldo Lanhellas
11/10/2014
Se tratando de CRUD, seja simples ou não, deixe por conta da aplicação.
Fernanda Acacia
11/10/2014
Clayton Silva
11/10/2014
Porém store procedures são mais recomendadas para querys mais complexas.
Mas tudo depende da aplicação e o cenário aplicado.
Hoje em dia as ORMs fazem tudo, então esse negócio de store vs códigos não é muito discutida hoje em dia.
Fernanda Acacia
11/10/2014
Isso depende mais do programador, projeto, equipe?
e por que vc usa com frequencia os stored procedure?
Clayton Silva
11/10/2014
A desvantagem é que a manutenção é maior. Pois a programação fica no banco de dados.
É uma boa quando se tem um DBA que cuide do código. Ele pode fazer alterações de performance sem que precise mecher na aplicação.
Se o código fica na aplicação, o DBA tem que pedir para o programador alterar.
Porém se o programador precisa alterar ele pode terá que ir no BD alterar ou solicitar o DBA que faça, caso o desenvolvedor não tenha acesso.
Ronaldo Lanhellas
11/10/2014
Bom, trabalhei com grandes projetos e em todos usamos CRUD na aplicação, nunca no banco, por mais simples que seja.
Clayton Silva
11/10/2014
Mas depende do que cada projeto demande. Tem prós e contras em qualquer forma adotada.
Fernanda Acacia
11/10/2014
Ronaldo e Clayton, mais uma: eu não tinha percebido o grau de importancia e esqueci totalmente de um detalhe "equipe".
Marisiana Battistella
11/10/2014
Isso não gera confusão Fernanda... Essa é uma ótima forma de se trabalhar pois o foco principal é a qualidade das aplicações, seja em performance ou em manutenção. Só vejo pontos positivos em utilizar isso como padrão de desenvolvimento.
Fernanda Acacia
11/10/2014
Marisiana Battistella
11/10/2014
Você pode concentrar as procedures e functions que se destinam para uma mesma aplicação ou para um mesmo assunto que está sendo tratado em packages, isso facilita mais ainda o trabalho e a compreensão dos códigos.
Não esqueça de colocar um comentário em cada procedure e function justificando a regra que ele atende, pois a mesma pode ser utilizada em mais de um local da aplicação.
Fernanda Acacia
11/10/2014
Marisiana Battistella
11/10/2014
Sempre é bom poder ajudar!
Maria Araújo
11/10/2014
Como gostei muito da matéria estou desenvolvendo mais esse conhecimento.
Contudo, seguindo este tópico tive uma dúvida em relação ao CRUD.
Eu criei procedures para tal, na BD, mas Ronaldo disse
Bom, trabalhei com grandes projetos e em todos usamos CRUD na aplicação, nunca no banco, por mais simples que seja.
Fiquei baralhada com isso
Ziobello
11/10/2014
[url]http://celularpecas.com.br/[/url]
Fernanda Acacia
11/10/2014
Como gostei muito da matéria estou desenvolvendo mais esse conhecimento.
Contudo, seguindo este tópico tive uma dúvida em relação ao CRUD.
Eu criei procedures para tal, na BD, mas Ronaldo disse
Bom, trabalhei com grandes projetos e em todos usamos CRUD na aplicação, nunca no banco, por mais simples que seja.
Fiquei baralhada com isso
Pelo que entendi ele quis dizer que só feito somente pela aplicação.
Maria Araújo
11/10/2014
executa (CommandType.Text, "instrução SQL").
Poderá alguém ajudar com outro assunto: Quando e porquê usar functions?
Já çi sobre isso, mas os comentários aqui são sempre mais construtivos
Fernanda Acacia
11/10/2014
Maria, nao me leve a mal, mas é bom abrir um outro post.
Cleverson
11/10/2014
Supondo que temos (Uma table Clientes com todos os campos comuns a ela):
DELIMITER $$ CREATE PROCEDURE inserirCliente(IN idCliente INT, IN Nome VARCHAR(32)) DETERMINISTIC BEGIN IF(SELECT `id` FROM `Clientes` WHERE `id` = idCliente) > 0) THEN UPDATE `Clientes` SET `nomeCompleto` = Nome; ELSE INSERT INTO `Clientes` (`id`, `nomeCompleto`) VALUES (NULL, Nome); END IF ; END $$ DELIMITER ;
Creio que simplesmente você possa enviar o dado, simplesmente se ele existir atualiza, se não insere na table.
Sem falar procedimentos para pesquisa em banco de dados (Que normalmente contem um SQL 'Pequeno') dependendo da aplicação.
Segurança, rapidez (Por enviar somente 1 comando ao BD) e outros benefícios.
Ops já ia esquecendo, você pode usar a procedure em varias aplicações (JAVA, .NET, DELPHI, Web e todas mais)
Basta somente chamar os procedimentos.
vlw
Fernanda Acacia
11/10/2014
Soeuseijothaz
11/10/2014
Certas empresas que trabalhei era obrigatório o uso de stored procedures, pois fica mais fácil para os DBA´s controlar a performance do BD e o que esta sendo executado.
Outra vantagem é que as SP´s ficam pré-copiladas o que da um gás na hora de executar.
A desvantagem é que se existir alguma regra de negócio na SP ela fica encapsulada.
Estando tudo na aplicação fica mais legível.
Cleverson
11/10/2014
Soeuseijothaz
11/10/2014
Pois é vai contra as melhores prática, pois caso haja uma mudança para outro banco de dados o trabalho de portar as regras de negócio pode ser duro. Se as regra de negócio estão em uma camada especifica e as sentenças SQL aderirem o padrão ASNSI o quanto possível, fica mais simples.
A mais ou menos 15 anos atrás trabalhei num projeto gigante usando asp, componentes vb e sybase. Naquela época o IIS e mesmo o windows não eram tão robustos e maduros quanto hoje, sem falar do hardware. Memória era cara e usada com parcimônia. Então tínhamos problemas de performance consideráveis. Veio uma diretriz tecnológica para que todas as regras de negócio e formatação de campos fossem para as SP´s. E realmente tivemos ganhos consideráveis de performance. Ficamso livres de uma baita dor de cabeça. Então para aquele cenário foi válido. Depois de uns anos com uma evolução natural dos softwares e hardware a diretriz mudou, a partir de agora toda as regras de negócio e formatação de campos não devem ser usados em SP.
Quanto o banco de dados senta e chora a "nabada" vai para os DB´s. Eles é que devem resolver o problema. Com o uso de SP´s fica claro tudo o que rodando no BD. Esta tudo ali a disposição para análise e tuning. Quando se usa sentenças SQL na aplicação nunca se sabe o que o infeliz do desenvolvedor vai usar. Poe exemplo trabalhei em uma empresa em que era desaconselhável (proibido mesmo) o uso de SELECT INTO. Então nada melhor do que ter as SP´s a traves de query verificar se alguém esta usando deste expediente. Via aplicação fica difícil. Além do mais ao usar SQL direto na aplicação você permite o desenvolvedor escrever qualquer sandice.
Eu particularmente gosto muito de SP´s, pois quando a sentença SQL é muto complexa fica um porre escrevê-la. O mais fácil é usar um software tipo o Manegement Studio para testar e depois colocar em um string. Usando SP`s a sentença fica mais legível, claro que você tem de ir lá e abrir a sp para ver o conteúdo, mas isto não me parece nada impeditivo.
Agora com estes framework de persistência e o ORM fica depressivo ficar escrevendo CRUD.
Ultimante estou trabalhando com .Net usando entyti e linq to SQL e já não me apego tanto assim a SP´s. Pois o linq realmente é muito interessante e poderoso.
Porém começa a ser questionado se é um boa deixar o entity fazer um cópia do BD com objetos relacionas em memória e sua implicações. Sempre vai haver prós e contras.
E existe ainda a querela entre a modelagem UML e a modelagem do BD. Para os puristas do UML o BD deve ser a cópia fiel da modelagem UML. Para os puristas DB nem sempre. Dai começam as polêmica.
Acho que qualquer que seja a bordagem tudo depende do bom senso e do cenário.
Agora quanto mais as camadas forem separadas melhor, acho que esta é a regra básica.
Fernanda Acacia
11/10/2014
Soeuseijothaz
11/10/2014
Quando uma empresa define que todo o acesso a dados seria obrigatoriamente realizado por SP´s seria para qualquer sistema. Neste caso não importa qual o tamanho do sistema ou mesmo se é para pesquisa ou CRUD.
Seria mais um estratégia para que os DBA´s pudessem ter uma visão e controle maior do que estaria rodando na base de dados.
A grosso modo seria como se você tivesse uma loja. Quando um ciente chega e solicita uma mercadoria que esta na vitrine fica simples atender o pedido. Se a mercadoria estiver no estoque ou mesmo tive de confeccionar ai há demora em atender o pedido. No caso do comerciante se a mercadoria tivesse de ser confeccionada ele agradeceria e dispensaria o cliente. O Mecanismo de banco de dados não têm este livre arbítrio se for feita uma solicitação que esta na vitrine (sp´s) ele atende. Se for feita uma solicitação que não esta na vitrine (sp), mas é uma solicitação válida e ele tem como atender ele vai lá construir a mercadoria (o resultado da execução da sentença slq) e entrega.
Em certas empresas a áreas de Dados tem muita influencia então para eles é melhor usar SP e isto se torna uma norma.
O Sybase e SQL Server usam como estratégia para melhorar a performance as "estatísticas" (os utros bd´s não me lembre se usam, mas acho que sim ) e com SP,que ficam em chace ou pré-compiladas, fica mais fácil atualizá-las.
Agora vai de sua intuição definir qual a melhor forma de abordar a questão. Como nos últimos anos sempre trabalhei em empresas em que o uso era uma norma eu sempre usava, pois acho que fica mais organizado e ficar fácil testá-las sem a necessidade de executar a aplicação. Só que .hoje com entity e linq SQL já não uso tanto. Só uso quando não consigo escrever minhas sentença em linq.
Agora NUCA COLOQUE AS REGRAS DE NEGÓCIO em SP´s, isto sim é um heresia como já expliquei no post anterior.
Se você ficar mais a vontade em colocar as sentenças no código vai em frente desde que sejam bem escritas não vai fazer diferença. Pois merdas podem ser feitas em SP´s ou via código.
Se for usar em código tente escrever algo limpo e usar parâmetros.
SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT usr.IdUsuario,usr.NmUsuario,usr.Senha,usr.Login,usr.IdPerfil,usr.IdFazendeiro,usr.IdLaboratorio, "); sbQuery.Append("fz.IdFazendeiro,fz.NrCPF,fz.NmFazendeiro,fz.NmBairro,fz.NmEndereco,fz.NrCNPJ,fz.NmRazaoSocial, "); sbQuery.Append("lb.IdLaboratorio,lb.NmLaboratorio,lb.IdCidade "); sbQuery.Append("FROM Usuario usr "); sbQuery.Append("left join fazendeiro fz on fz.IdFazendeiro = usr.IdFazendeiro "); sbQuery.Append("left join laboratorio lb on lb.IdLaboratorio = usr.IdLaboratorio "); #endregion #region Filtros StringBuilder sbFiltro = new StringBuilder(); if (filtroUsuario != null) { if (!string.IsNullOrEmpty(filtroUsuario.Login)) { sbFiltro.Append(" usr.Login = @Login "); } if (!string.IsNullOrEmpty(filtroUsuario.Senha)) { sbFiltro.Append(!string.IsNullOrEmpty(sbFiltro.ToString()) ? "AND" : string.Empty); sbFiltro.Append(" usr.Senha = @Senha "); } } if (!string.IsNullOrEmpty(sbFiltro.ToString())) sbQuery.Append("WHERE ").Append(sbFiltro.ToString()); cmd.CommandText = sbQuery.ToString(); if (filtroUsuario != null) { if (!string.IsNullOrEmpty(filtroUsuario.Login)) { cmd.Parameters.AddWithValue("@Login", filtroUsuario.Login); } if (!string.IsNullOrEmpty(filtroUsuario.Senha)) { cmd.Parameters.AddWithValue("@Senha", filtroUsuario.Senha); } } #endregion
E principalmente criar tudo em uma linha só como se fosse um "linguição". E acredite-me já vi muito disso por ai.
Para finalizar existem muitos preconceitos (conceitos preconcebidos) com relação ao uso de certas abordagens e até acho válido. Agora o que não pode ser é intolerante.E para ser um profissional completo saiba usar as expressões SQL tanto no código, como em SP´s ou mesmo framework de persistência. Você só tem a ganhar.
Qualquer dúvida é só se manifestar.
Soeuseijothaz
11/10/2014
Então vai uma correção:
Onde esta:
"E principalmente criar tudo em uma linha só como se fosse um "linguição". E acredite-me já vi muito disso por ai."
O correto é:
E principalmente NUNCA criar tudo em uma linha só como se fosse um "linguição". E acredite-me já vi muito disso por ai.
Soeuseijothaz
11/10/2014
executa (CommandType.Text, "instrução SQL").
Poderá alguém ajudar com outro assunto: Quando e porquê usar functions?
Já çi sobre isso, mas os comentários aqui são sempre mais construtivos
As funciotns no BD são como as fucntions usadas no código.
São códigos que executam uma tarefe e devolve um o resultado.
Você poderia consistir o CNPJ via funciton SQL:
/* Calculo do Digito Verificador para CNPJ por: Antonio Rodrigues dos Santos Filho - antonio@aikon.com.br Instruções 1- O CPF deve ser passado contendo apenas os numero. Não coloque os '.' e '/' 2- O resultado da função será o Digito Verificador em char(2) Exemplo: declare @dv as char(2) set @dv = dbo.CalculaCNPJ ('123456780001') print @dv obs: Neste caso o DV = 95 qualquer erro, o resultado do DV será -- */ alter function dbo.CalculaCNPJ (@cnpj as varchar(13)) returns char(2) as Begin declare @soma as int, @dv as char(2), @dv1 as int, @dv2 as int, @i as int, @somando as int, @cnpj1 as char(8) set @cnpj1 = substring(@cnpj, 1, 8) set @dv = '--' -- Teste 1. O cpf nao pode ser tudo 1 ou 2 e etc if not (@cnpj1 = '11111111' or @cnpj1 = '22222222' or @cnpj1 = '33333333' or @cnpj1 = '44444444' or @cnpj1 = '55555555' or @cnpj1 = '66666666' or @cnpj1 = '77777777' or @cnpj1 = '88888888' or @cnpj1 = '99999999' or @cnpj1 = '00000000') begin -- Teste 2. Verifica se o cpf tem os 9 digitos preenchidos If rtrim(Len(@cnpj)) = 12 begin --Calcula DV1 (1o Digito Verificador) set @soma = 0 set @somando = 5 set @i = 1 while @i < 5 begin set @soma = @soma + Substring(@cnpj, @i, 1) * @somando set @somando = @somando - 1 set @i = @i +1 end set @somando = 9 set @i = 5 while @i < 13 begin set @soma = @soma + Substring(@cnpj, @i, 1) * @somando set @somando = @somando - 1 set @i = @i +1 end set @dv1 = 11 - (@soma - (@soma/11) * 11) If @dv1 > 9 set @dv1 = 0 -- Calcula DV2 (2o Digito Verificador) set @cnpj = @cnpj + ltrim(str(@dv1)) set @soma = 0 set @somando = 6 set @i = 1 while @i < 6 begin set @soma = @soma + Substring(@cnpj, @i, 1) * @somando set @somando = @somando - 1 set @i = @i +1 end set @somando = 9 set @i = 6 while @i < 14 begin set @soma = @soma + Substring(@cnpj, @i, 1) * @somando set @somando = @somando - 1 set @i = @i +1 end set @dv2 = 11 - (@soma - (@soma/11) * 11) If @dv2 > 9 set @dv2= 0 if @dv1 <> 0 begin set @dv = ltrim(str(@dv1 * 10 + @dv2)) end else begin set @dv = '0' + ltrim(str(@dv2)) end end end return (@dv) end
O código acima é só um exemplo, pois a melhor abordagem é fazer este tipo de tarefa na camada de negócios.
No link tem mais algumas informações.
http://www.linhadecodigo.com.br/artigo/687/sql-server-funcoes-de-usuario-user-functions.aspx
Clayton Silva
11/10/2014
Sobre functions, é recomendável evitar o uso delas no BD, melhor serem feitas na aplicação.
Soeuseijothaz
11/10/2014
Sobre functions, é recomendável evitar o uso delas no BD, melhor serem feitas na aplicação.
Como sempre depende do cenário.
No exemplo que postei foi só ilustrativo e para mostra o poder das functions.
Se for relacionado a regras de negócio deve ficar na aplicação sem dúvidas.
No link que postei existe alguns exemplos de uso que não seira uma heresia.
Veja o exemplo:
CREATE FUNCTION funcionariosApos(@dt datetime) RETURNS TABLE AS RETURN (SELECT * FROM FUNCIONARIO WHERE dataContratacao >= @dt)
Para usar:
SELECT * FROM funcionariosApos('2000-01-01')
No caso esta função podeira ser usada em várias consultas, reaproveitando a mesma.
Agora ser for migrar para outro banco de dados pode dar trabalho.
Então deve ser usada com parcimônia e sempre de forma bem planejada.
Fernanda Acacia
11/10/2014
Alex Lekao
11/10/2014
=b
Soeuseijothaz
11/10/2014
Stored procedures vantagens:
1-fica em cache e a performance é maior
2-menor trafego na rede, pois só passa o nome da sp e parâmetros e não todo a expressão.
3-fica mais organizado.
4-ajuda a manter as estatísticas do servidor o que ajuda na performance como um todo
5-pode ser testada fora da aplicação
6-oferece um controle de grant maior
Stored procedures desvantagens:
1-quando da implantação da aplicação na produção tem-se que gerá-las para o novo server
2-o conteúdo fica encapsulado na sp, então tem-se o trabalho de sempre abri-la para ver o conteúdo
3-é mais um objeto para você se preocupar
Usar SQL no código vantagens:
1-dá atomicidade ao código tudo o que é preciso esta no código visível e a mão
2-fica-se livre da área de dados
3-quando da implantação da aplicação na produção não precisa de se preocupar com atualizar nada no server de bd
Usar SQL no código desvantagens:
1-é mais lento pois além do server ter de ler as instrução tem de fazer o parse
2-pode sobrecarregar a rede pois trafega por ela toda a instrução
Fernanda Acacia
11/10/2014
Alex Lekao
11/10/2014
O que tiro disso eh que temos mais vantagens em nao utilizar no codigo. rsrsr
Obrigado.
Aprendendo cada dia mais. rsrsr
Edgard Santos
11/10/2014
Já trabalhei em vários projetos e não recomendo utilizar Stored Procedure. Imagine que as consultas estão nas SPs e você precisa migrar do MySql para o Sql Server, e agora? refazer tudo?
Esse é o problema das Stored Procedure.
Para os projetos utilize ORM, NHibernate ou Entity Framework.
Fernanda Acacia
11/10/2014
Edgard Santos
11/10/2014
Fernanda Acacia
11/10/2014
Edgard Santos
11/10/2014
Fernanda Acacia
11/10/2014
Edgard Santos
11/10/2014
Se sim, anota ai o meu: edgardsantos@curves.com.br
Fernanda Acacia
11/10/2014