Alguns exemplos de stored procedure que utilizei, a nível de estudo, em banco de dados SQL Server 2008 que podem servir de guia para outras ocasiões.
Ex1:
CREATE PROCEDURE SP_VALIDA_MATRICULA
@CODIGO NCHAR(20),
@NOME nvarchar(50) --no primeiro exemplo não é usado
AS
BEGIN
IF EXISTS (SELECT 1
FROM FUNCIONARIO
WHERE FUNCIONARIO.MATRICULA=@CODIGO )
SELECT 'Funcionario valido. Operacao realizada!'
ELSE
SELECT 'Funcionario invalido. Operacao nao realizada!'
END;
Verifica se existe funcionário com um determinado código de matrícula dentro da tabela. A vantagem deste tipo de operação é que a pessoa que realiza o teste não precisa ter conhecimento sobre o banco de dados em si pois receberá somente o nome da procedure. Para executar:
EXEC SP_VALIDA_MATRICULA 152345,'MARCIO DOS SANTOS'
Ex2:
CREATE PROCEDURE SP_VALIDA_MATRICULA
@CODIGO NCHAR(20),
@NOME nvarchar(50)
AS
BEGIN
IF EXISTS (SELECT 1
FROM FUNCIONARIO
WHERE FUNCIONARIO.MATRICULA=@CODIGO )
INSERT INTO DEPENDENTE VALUES (@CODIGO,@NOME)
ELSE
SELECT 'Funcionario invalido. Operacao nao realizada!'
END;
Neste exemplo o funcionário, caso não exista, será inserido na tabela.
Supondo agora a tabela de testes abaixo:
CREATE TABLE dbmar(
NUMBER INT,
firstname VARCHAR(20)
)
Detalhe que NUMBER é uma palavra reservada e não deveria ser utilizada para nome de campo na tabela, mesmo assim o exemplo funcionou.
Ex1:
USE forcecar --- Nome da DataBase
GO
CREATE PROCEDURE testeBusca --- Declarando o nome da procedure
@NUMBER INT=2 --- Declarando varivel (note que utilizamos o @ antes do nome da varivel)
AS
--SELECT number, firstname --- Consulta
--FROM dbmar
UPDATE dbmar
SET firstname='roiMAS'
WHERE NUMBER = @NUMBER --- Utilizando varivel como filtro para a consulta
--Para executar uma procedure basta utiilizar a clusula EXECUTE seguido pelo nome da procedure e na frente o valor a ser utilizado como parmetro.
--Exemplo:
EXECUTE testeBusca '3'
--Para excluir a procedure
DROP PROCEDURE testeBusca
Outro exemplo:
CREATE PROCEDURE sp_vendas
@NumeroNF INT,
@CodProduto INT,
@desconto NUMERIC(4,2),
@quantidade NUMERIC(10,2)
AS
/* CRIA AS VARIAVEIS LOCAIS */
DECLARE @estoque NUMERIC(10,2)
DECLARE @msg CHAR(100)
/* DEFINE O VALOR DE CADA VARIÁVEL */
SET @estoque = (SELECT quantidade FROM PRODUTO WHERE CodProduto = @codproduto)
/* EXECUTO O TESTE, UTILIZANDO IF...ELSE */
IF (@estoque) < (@quantidade)
BEGIN
SET @msg = 'A quantidade a ser vendida é maior que a quantidade em estoque'
PRINT(@msg)
END
ELSE
BEGIN
INSERT produtoitem (numeronf, codproduto, desconto, quantidade)
VALUES (@numeronf, @codproduto, @desconto, @quantidade)
UPDATE PRODUTO SET Quantidade = (@estoque-@quantidade) WHERE CodProduto=@codproduto
END
Verifica possibilidade de venda de produto caso exista em estoque.
Para executar:
exec sp_vendas 1,22,10,100
A procedure pode também ser criada com o comando abaixo:
CREATE OR REPLACE PROCEDURE sp_vendas
Pois, caso já exista uma procedure com o nome não é possível criar outra com o mesmo nome e o replace substitui a anterior.
