Čo je uložená procedúra? Ako fungujú? Aké je zloženie uloženej procedúry (čo všetko musí byť uloženou procedúrou)?
Uložené procedúry sú dávky príkazov SQL, ktoré možno vykonať niekoľkými spôsobmi. Väčšina hlavných DBM podporuje uložené procedúry, nie všetky však áno. Podrobnosti si musíte overiť v dokumentácii nápovedy konkrétneho DBMS. Keďže najlepšie poznám SQL Server, použijem ho ako ukážky.
Syntax vytvorenia uloženej procedúry je pomerne jednoduchá:
CREATE PROCEDURE <owner>.<procedure name>
<param> <datatype>
AS
<Body>
Takže napríklad:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Výhodou uložených procedúr je, že môžete centralizovať logiku prístupu k údajom na jedno miesto, ktoré potom DBA's ľahko optimalizuje. Uložené procedúry majú aj bezpečnostnú výhodu v tom, že uloženej procedúre môžete udeliť práva na vykonávanie, ale používateľ nemusí mať práva na čítanie/zápis do základných tabuliek. To je dobrý prvý krok proti SQL injection.
Uložené procedúry majú aj nevýhody, v podstate ide o údržbu spojenú s vašou základnou operáciou CRUD. Povedzme, že pre každú tabuľku máte Insert, Update, Delete a aspoň jeden select na základe primárneho kľúča, to znamená, že každá tabuľka bude mať 4 procedúry. Teraz si vezmite slušne veľkú databázu so 400 tabuľkami a budete mať 1600 procedúr! A to za predpokladu, že nemáte duplikáty, čo pravdepodobne budete mať.
V tomto prípade má obrovskú výhodu použitie ORM alebo inej metódy na automatické generovanie základných operácií CRUD.
Uložená procedúra je súbor predkompilovaných príkazov SQL, ktoré sa používajú na vykonanie špeciálnej úlohy.
Príklad: Ak mám tabuľku Employee
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Najprv načítam tabuľku Zamestnanec
:
Create Procedure Employee details
As
Begin
Select * from Employee
End
Spustenie postupu na serveri SQL Server:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Po druhé, vložím hodnotu do tabuľky Zamestnanci
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Spustenie parametrizovanej procedúry na serveri SQL Server:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Príklad: @Názov Varchar(30)
V tabuľke Zamestnanec
musí mať stĺpec Name
veľkosť varchar(30)
.
Všeobecne platí, že uložená procedúra je "funkcia SQL." Majú:
-- 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
Toto je príklad zameraný na T-SQL. Uložené procedúry môžu vykonávať väčšinu príkazov SQL, vracať skalárne a tabuľkové hodnoty a považujú sa za bezpečnejšie, pretože zabraňujú útokom SQL injection.