Vývoj databázových aplikací V

Uložené procedury

Uložená procedura (Stored Procedure) je podobně jako tabulka nebo pohled databázový objekt, definovaný uvnitř databáze. Místo vlastních dat však obsahují kód (aplikační logiku). Uložené procedury mohou používat téměř libovolné dotazy (DQL) nebo příkazy pro manipulaci s daty (DML). Jejich použití je vhodné zvláště tam, kde by realizace úlohy na klientské straně znamenala zbytečný přenos většího objemu dat ze serveru a následně zpět na server.

Na rozdíl od příkazů DDL pro návrh struktury dat, jejichž převod lze snadno provést pomocí CASE nástrojů, jsou uložené procedury podobně jako spouště (triggery), funkce nebo balíčky obtížněji přenositelné. Dodavatelé aplikací, kteří musí podporovat databázové servery různých dodavatelů, se tak použití uložených procedur často vyhýbají. Bohužel se tak zároveň vzdávají řady výhod, které použití uložených procedur přináší:

Zpřehlednění systému

Uložené procedury zjednodušují a zpřehledňují architekturu systému.
- Změny lze provádět na serveru, aniž by tím byla dotčena aplikace
- Aplikační logiku lze konzistentně sdílet více moduly nebo aplikacemi
- Uložené procedury jsou validovány serverem a mají transparentní vazby
- Uložené procedury se snadno dokumentují
- Návrh uložených procedur lze do velké míry automatizovat  

Zlepšení odezvy aplikace

Uložené procedury jsou předkompilované a typicky načtené v cache paměti DB serveru. V porovnání se standardním příkazem tak již není nutné ověřovat jejich syntaktickou správnost, provádět kontrolu existence dotčených databázových objektů nebo validovat správnost vazeb. Rovněž odpadá proces optimalizace, kdy se databázový server na základě statistických informací o jednotlivých objektech snaží navrhnout ideální exekuční plán a samozřejmě také vlastní kompilace. Provedení uložené procedury je tak zpravidla výrazně rychlejší než přímé spuštění identické sady příkazů.

Při větších změnách v DB, které by mohli mít dopad na stanovení optimálního exekučního plánu je doporučeno aktualizovat statistiky a provést rekompilaci všech uložených procedur.

Bezpečnost

Použití uložených procedur je mnohem bezpečnější než přímý přístup k datům. Pro každou proceduru je možné přesně stanovit, který uživatel nebo která role má právo ji spustit. Uložená procedura chrání systém před útoky typu SQL injection.

Uložené procedury je také možné využít ke skrytí aplikační logiky. Po zkompilování uložené procedury lze ze serveru odstranit zdrojový kód (typicky uložený v systémových tabulkách v textové podobě).

Produktivita

V současné době je pro většinu databázových serverů k dispozici široká nabídka nástrojů a utilit, které návrh uložených procedur zjednodušují a automatizují.

Pro volání uložených procedur databázových strojů Interbase a Firebird, které vracející datovou sadu nelze použít příkaz ExecProc. Místo toho stačí uloženou proceduru nastavit na "Active". Alternativně lze získat data z procedury s využitím FDQuery. Ve vlastnosti "SQL.text" pak provedeme standardní "select", například 'select * from SEL_TEST'.

Příklady Delphi (InterBase/Firebird)

// Načtení záznamů
procedure TForm1.selClick(Sender: TObject);
begin
  FDStoredProc1.Active := False;
  FDStoredProc1.StoredProcName := 'SEL_TEST';
  FDStoredProc1.Prepare();
  FDStoredProc1.Active := True;
end;

// Vložení nového záznamu
procedure TForm1.insClick(Sender: TObject);
begin
  FDStoredProc1.Active := False;
  FDStoredProc1.StoredProcName := 'INS_TEST';
  FDStoredProc1.Prepare();
  FDStoredProc1.ParamByName('IN_TXT').AsString := Edit1.Text;
  // Zobrazení výstupního parametru
  // v tomto případě ID vloženého záznamu
  ShowMessage(FDStoredProc1.ParamByName('RET').AsString);
  FDStoredProc1.ExecProc();
end;

// Aktualizace záznamu
procedure TForm1.updClick(Sender: TObject);
begin
  FDStoredProc1.Active := False;
  FDStoredProc1.StoredProcName := 'UPD_TEST';
  FDStoredProc1.Prepare();
  FDStoredProc1.ParamByName('IN_TXT').AsString := Edit1.Text;
  FDStoredProc1.ParamByName('IN_ID').AsInteger := Edit2.Text;
  FDStoredProc1.ExecProc();
end;

// Odebrání záznamu
procedure TForm1.delClick(Sender: TObject);
begin
  FDStoredProc1.Active := False;
  FDStoredProc1.StoredProcName := 'DEL_TEST';
  FDStoredProc1.Prepare();
  FDStoredProc1.Params.ParamByPosition(1).AsInteger := StrToInt(Edit2.Text);
  FDStoredProc1.ExecProc();
end;

Příklady C++ Builder (Microsoft SQL Server)

// Načtení záznamů
void __fastcall TForm1::selClick(TObject *Sender)
{
  FDStoredProc1->Active = False;
  FDStoredProc1->StoredProcName = "dbo.up_sel_edice";
  FDStoredProc1->Prepare();
  FDStoredProc1->ParamByName("@eid")->AsInteger = StrToInt(Edit1->Text);
  FDStoredProc1->Active = True;
}

// Vložení nového záznamu
void __fastcall TForm1::insClick(TObject *Sender)
{
  FDStoredProc1->Active = False;
  FDStoredProc1->StoredProcName = "dbo.up_ins_edice";
  FDStoredProc1->Prepare();
  FDStoredProc1->ParamByName("@edice")->AsString = Edit2->Text;
  FDStoredProc1->ExecProc();
}

// Aktualizace záznamu
void __fastcall TForm1::updClick(TObject *Sender)
{
  FDStoredProc1->Active = False;
  FDStoredProc1->StoredProcName = "dbo.up_upd_edice";
  FDStoredProc1->Prepare();
  FDStoredProc1->ParamByName("@eid")->AsInteger = StrToInt(Edit1->Text);
  FDStoredProc1->ParamByName("@edice")->AsString = Edit2->Text;
  FDStoredProc1->ExecProc();
}

// Odebrání záznamu
void __fastcall TForm1::delClick(TObject *Sender)
{
  FDStoredProc1->Active = False;
  FDStoredProc1->StoredProcName = "dbo.up_del_edice";
  FDStoredProc1->Prepare();
  FDStoredProc1->Params->ParamByPosition(2)->AsInteger = StrToInt(Edit1->Text);
  FDStoredProc1->ExecProc();
}

Uložené procedury (a další SQL příkazy) lze spouštět na základě událostí na straně serveru, nebo v požadovaných časech. Vhodné např. pro reporting nebo validaci a čištění dat.

View Petr Houf's profile