O que é um procedimento armazenado? Como é que eles funcionam? Qual é a composição de um procedimento armazenado (coisas que cada têm que ser um procedimento armazenado)?
Stored procedures são um lote de instruções SQL que podem ser executadas de várias maneiras. A maioria das principais DBMs suportam procedimentos armazenados; no entanto, nem todas o fazem. Você precisará verificar com o seu SGBD em particular a documentação de ajuda para procedimentos específicos. Como eu estou mais familiarizado com o SQL Server, vou usar isso como minhas amostras.
Para criar um procedimento armazenado, a sintaxe é bastante simples:
CREATE PROCEDURE <owner>.<procedure name>
<param> <datatype>
AS
<Body>
Então, por exemplo:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Um benefício dos procedimentos armazenados é que você pode centralizar a lógica de acesso aos dados em um único lugar que é então fácil para a DBA's otimizar. Os procedimentos armazenados também têm um benefício de segurança na medida em que você pode conceder direitos de execução a um procedimento armazenado, mas o usuário não precisará ter permissões de leitura/gravação nas tabelas subjacentes. Este é um bom primeiro passo contra a injeção SQL.
Os procedimentos armazenados vêm com desvantagens, basicamente a manutenção associada à sua operação básica CRUD. Deixe's dizer que para cada tabela você tem um Insert, Update, Delete e pelo menos um select baseado na chave primária, isso significa que cada tabela terá 4 procedimentos. Agora pegue uma base de dados em tamanho decente de 400 tabelas, e você terá 1600 procedimentos! E isso's assumindo que você não'tenhas duplicatas que provavelmente terás.
Aqui é onde o uso de um ORM ou algum outro método para gerar automaticamente suas operações CRUD básicas tem uma tonelada de mérito.
Um procedimento armazenado é um conjunto de instruções SQL pré-compiladas que são utilizadas para realizar uma tarefa especial.
Exemplo: Se eu tiver uma tabela "Funcionário
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Primeiro estou recuperando a tabela 'Empregado':
Create Procedure Employee details
As
Begin
Select * from Employee
End
Para executar o procedimento no SQL Server:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Em segundo lugar, estou inserindo o valor na Tabela de Empregados
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Para executar o procedimento parametrizado no SQL Server:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Exemplo: @Nome Varchar(30)
Na tabela Employee' a coluna
Name' deve ter o tamanho varchar(30)
.
Geralmente, um procedimento armazenado é um "SQL Function." Eles têm:
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
Este é um exemplo focado em T-SQL. Os procedimentos armazenados podem executar a maioria das instruções SQL, retornar valores escalares e baseados em tabelas, e são considerados mais seguros porque evitam ataques de injeção SQL.