Introdução com Procedimentos Armazenados no SQL Server
Por: Greg Robidoux | Atualizado em: 2020-07-30 | Comments (15) | Relacionados: Mais > Procedimentos Armazenados
Problema
tenho vindo a utilizar o SQL Server por algum tempo, mas todo o código que é issuedagainst o banco de dados é incorporado no código do aplicativo. Eu sei que você pode criar procedimentos, mas eu não estou exatamente certo por onde começar ou o que eu preciso para implementar procedimentos armazenados.,
solução
procedimentos armazenados não são mais do que um lote de declarações T-SQL que são armazenados na base de dados. Em vez de ter que emitir múltiplas declarações para a nossa aplicação, você pode emitir um comando para chamar o procedimento armazenado para fazer um abatch do trabalho em vez de apenas uma declaração. Além disso, uma vez que o código está escrito no banco de dados, você pode emitir o mesmo conjunto de código vezes sem conta a partir de aplicações diferentes ou de uma janela de consulta. Para começar, o resto desta dica olha para alguns procedimentos armazenados por exemplo e como você pode começar e construir sobre eles.,
os exemplos abaixo mostram como é simples criar procedimentos armazenados. Todos estes exemplos utilizam a base de dados AdventureWorks, mas estes exemplos devem ser bastante simples para que possa aplicar estes conceitos nas suas próprias bases de dados e aplicações.
exemplo 1-procedimento simples armazenado
este primeiro exemplo cria um procedimento simples armazenado que obtém o primeiro registo da pessoa.Mesa de contacto.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
após o acima ter sido criado, use o comando abaixo para executar este procedimento stored.,
EXEC uspGetContact
Este é o resultado desta primeira consulta.
Exemplo 2 – procedimento armazenado com um parâmetro
Este exemplo seguinte é uma modificação do primeiro exemplo, mas desta vez addinga parâmetro que é passado para o procedimento para selecionar dinamicamente os registros.Em vez de utilizarmos o procedimento “criar”, estamos a utilizar o procedimento “alterar” para modificar o procedimento que criámos no exemplo 1 em vez de o abandonarmos primeiro e depois o recriarmos.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
abaixo mostra duas maneiras diferentes de executar o procedimento armazenado. O primeiro examplejust passa o valor do parâmetro que queremos usar e o segundo exemplo também inclui o nome do parâmetro juntamente com o valor. Você pode executar o procedimento armazenado com eitherone desses comandos.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Este é o resultado desta primeira consulta.,
Exemplo 3 – procedimento armazenado com um parâmetro e o parâmetro de saída
neste exemplo temos um parâmetro de entrada, bem como um parâmetro de SAÍDA.O parâmetro de saída será usado para passar de volta o ContactID que estamos procurando no procedimento armazenado. Este parâmetro de saída será então usado para selecionar os indivíduos ContactID, nome próprio e LastName, juntamente com qualquer registro de endereço para este pessoa.,
Novamente estamos alterando o procedimento armazenado uspGetContact e, em seguida, em segundo lugar nóssomos a executar o próximo conjunto de código que executa o procedimento de uspGetContact e thenbased sobre o valor de retorno fica ele também irá consultar o nome de pessoas e addressinfo.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
após o procedimento armazenado ter sido alterado, execute o bloco de código abaixo. Isto irá executar o procedimento armazenado acima e se o ContactID tem um valor, também irá devolver a pessoa e informações de endereço.
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
Este é o resultado.,
Exemplo 4 – procedimento armazenado usando a instrução RAISERROR
neste exemplo, nós estamos combinando as duas etapas no Exemplo 3, em um procedimento armazenado.O primeiro passo é obter o ContactID e, em seguida, a segunda parte do procedimento irá procurar o nome das pessoas e informações de endereço. Nós também adicionamos em código para usar a declaração theRAISERROR para retornar um erro se não forem encontrados registros.
Este é então executado duas vezes para mostrar como ele se parece quando os dados são encontrados e quando nenhum dado é encontrado., A declaração RAISERROR pode ser usada para controlar como a sua aplicação não lida com dados ou qualquer outro erro que possa ocorrer.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Este é o resultado.
EXEC uspGetContact @LastName="Job"
Este é o resultado quando não há dados é encontrada.
Exemplo 5 – procedimento armazenado com um procedimento armazenado chamado
Aqui está outro exemplo onde temos dois procedimentos armazenados., O primeiro procedimento storedprocedure uspFindContact procura o primeiro registro que tem um registro de endereço e depois retorna o ContactID para o procedimento de chamada armazenado para exibir novamente a informação do pessoa e endereço.
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
o código abaixo faz uma alteração do procedimento de armazenamento do uspGetContact que chama o buspfindcontact e devolve os conjuntos de registos.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Este é o resultado.
EXEC uspGetContact @LastName="Job"
Este é o resultado.,
Exemplo 6 – procedimento armazenado com comentários
Neste último exemplo, leva a uspGetContact procedimento armazenado e adiciona commentsto o código, então você pode ver como os comentários de trabalho dentro de um procedimento armazenado.
Os comentários podem ser feitos de duas maneiras:
- usando —
- usando/* para iniciar o bloco de comentários e */ para terminar o bloco de comentários.
para além de que nada mais mudou no procedimento armazenado.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
estes são exemplos bastante simples, mas espero que isto lhe dê uma ideia do howeasy é criar procedimentos armazenados para o servidor SQL. Se você pode executar uma declaração selecionada a partir de uma janela de consulta ou de sua aplicação, você pode tão facilmente executar um procedimento de análise como mostrado acima.,
Próximos Passos
- Se você não estiver usando procedimentos armazenados espero que isso dá yousome uma visão do que você precisa fazer para começar a usá-los
- Como mencionado, estes são bastante simples exemplos, mas apenas sobre qualquer coisa que se pode fazer com um lote de declarações que podem ser combinados em um procedimento armazenado andthen usado repetidas vezes para as suas aplicações.,
- confira thestored procedimento do tutorial para mais exemplos
Última actualização: 2020-07-30
Sobre o autor
Greg Robidoux é o Presidente da Edgewood Soluções e co-fundador da MSSQLTips.com.Ver todas as minhas dicas
- Mais um Desenvolvedor de Banco de dados Dicas…