P²edposlední díl naτeho seriálu ze sv╪ta databází standardu SQL je zde a s ním i návod, jak pracovat s procedurami. Ale nep²edbíhejme. Nejd²íve samoz²ejm╪ dokonƒíme problematiku z dílu minulého.
Jak uloºit proceduru
Od okamºiku, kdy jsme studovan∞ systém opustili naposledy, je uº provedením p²edchozích operací dob²e nastaven a kdokoli v n╪m bude manipulovat s daty, neporuτí jeho integritu. Tak vznikl reáln╪ ºivotaschopn∞ systém, kde ubrání jakéhokoli integritního omezení zpàsobí chybnou funkci. P²idání dalτích omezení je pl∞tváním. Následující tabulka 1 ukazuje poƒet integritních omezení tabulek podle jejich typu.
Zàstaσme jeτt╪ chvíli v DDL a pokusme se vytvo²it hierarchick∞ systém p²ísluτn∞ch pohledà. První VIEW UDANICKO zobrazí pohled do vτech t²í tabulek souƒasn╪:
CREATE VIEW UDANICKO(RCU, JMENOU, PRIJMENIU, KOEU, RCO, JMENOO, PRIJMENIO, KOEO, DEN, CIC, NAZEVC, PRACHY, FINAL) AS
SELECT RCU,U.JMENO,U.PRIJMENI, U.KOEUD,RCO,O.JMENO, O.PRIJMENI,O.KOEO,DEN,CIC, NAZEVC,CENAC,CENAC*U.KOEOD*O.KOEOB FROM CIN, UDANI, CLOVEK U, CLOVEK O
WHERE CIC=CICINU AND RCU=U.RC AND RCO=O.RC;
Virtuální tabulka obsahuje opravdu vτechno pot²ebné a nahrazuje tabulku UDANI. Dalτí t²i VIEW se hodí k sumárnímu pohledu na udavaƒe, jejich ob╪ti a p²eƒiny:
AS SELECT CIC, NAZEVC, PRACHY, COUNT(*), SUM(PRACHY),SUM(FINAL)
FROM UDANICKO GROUP BY CIC;
Pod vlivem minul∞ch dílà seriálu snadno vytvo²íme pohledy na rodná ƒísla udavaƒà a ob╪tí:
CREATE VIEW RCUDAV(RC) AS SELECT RCU FROM UDANI GROUP BY RCU;
CREATE VIEW RCOBET(RC) AS SELECT RCO FROM UDANI GROUP BY RCO;
Poslední VIEW je pro vás mal∞m rébusem:
CREATE VIEW MEDAILE(RC, JMENO, PRIJMENI) AS
SELECT RC, JMENO, PRIJMENI FROM OBET
WHERE RC NOT IN RCUDAV;
Pro zavrτení trpkého humoru se vra£me do DML a zadejme n╪kolik p²íkazà SELECT, které prov╪²í dàkladnost p²edchozí p²ípravy:
SELECT TOP 10 PERCENT * FROM UDAVAC ORDER BY POCET DESC;
SELECT * FROM UDANICKO WHERE RCU IN RCOBET OR RCO IN RCUDAV;
SELECT COUNT(*) POCET_KUSU FROM MEDAILE;
Co jeτt╪ zb∞vá
Umíme uº pracovat s tabulkami, které obsahují data vzájemn╪ provázaná doménov∞mi, entitními a referenƒními integritami, a jsme schopni zajistit bezrozpornost uloºen∞ch dat pomocí definic tabulek v DDL. Dále víme, jak se efektivn╪ podívat do jedné nebo více tabulek pomoci VIEW. Zatím vτak nevíme, jak efektivn╪ pracovat s p²íkazy pro aktualizaci dat v tabulkách. Ideální by bylo mít moºnost formulovat jeden p²íkaz, kter∞ na serveru vyvolá spuτt╪ní jednoho nebo více p²íkazà. Naτt╪stí jazyk SQL DDL takové ²eτení p²ímo nabízí pomocí uloºen∞ch procedur. Ty mají svàj název a vnit²ní obsah tvo²en jednotliv∞mi p²íkazy. Uloºenou proceduru je moºné vytvo²it, zruτit a spustit. Po spuτt╪ní procedury se vykonají její vnit²ní p²íkazy v p²edem stanoveném po²adí podle algoritmu v procedu²e. Pokud jste jiº programovali v n╪kterém jazyce, nebude pro vás obtíºné konstruovat i sloºit╪jτí algoritmy. Na druhé stran╪ práv╪ proto nechávám uloºené procedury jako poslední téma. Pokud by ƒtená² o jejich existenci a moºnostech v╪d╪l d²íve, patrn╪ by nebyl ochoten k dekompozici systému do více tabulek a k vnímání integritních omezení a vτechno by cht╪l ²eτit algoritmicky. Cht╪l jsem zabránit tomu, aby znalci algoritmizace, ke kter∞m se také hrd╪ hlásím, nevyrazili kvapem po slepé koleji tvorby obrovsk∞ch strukturovan∞ch procedur, a to bez stop databázového myτlení. P²es to vτechno by bez uloºen∞ch procedur nebylo moºno realizovat rozumn╪ ºádn∞ databázov∞ systém. Dalτím kladn∞m rysem uloºen∞ch procedur je jejich nep²eruτitelnost. Proceduru spustíme jedním p²íkazem a ten se provede naráz, p²estoºe màºe mít sloºitou vnit²ní strukturu. Vhodná konstrukce procedur vede potom k minimalizaci kolizí s integritními omezeními a záleºí pouze na nás, jak tuto moºnost vyuºijeme.
Procedura bez parametrà
Kaºd∞ zaƒátek màºe b∞t lehk∞, je-li la£ka dostateƒn╪ nízko. Nejsnazτí je vytvo²it uloºenou proceduru, která nemá ºádné parametry. Typické jsou procedury zajiτ£ující hromadn∞ úklid. Procedura KONCIME postupn╪ zruτí data v tabulkách A, B a C. Takovou proceduru vytvo²íme v DDL p²íkazem:
CREATE PROCEDURE KONCIME
AS
BEGIN
DELETE FROM A;
DELETE FROM B;
DELETE FROM C;
END
Pak proceduru snadno spustíme p²íkazem:
EXECUTE PROCEDURE KONCIME;
V∞hody takové procedury jsou patrné, pokud tabulky B a C jsou ƒíselníky a tabulka A do nich odkazuje. Pak procedura KONCIME ruτí obsahy tabulek ve správném po²adí a nebudou problémy s referenƒními integritami. Dále nemusíme znát názvy pàvodních tabulek podobn╪ jako ve VIEW a do t²etice p²ístupové právo k procedu²e KONCIME nemusí mít kaºd∞ nezodpov╪dn∞ jedinec, ale nap²íklad jenom správce databáze.
Procedura se vstupními parametry
Rozτí²íme moºnosti procedur o komunikaci prost²ednictvím vstupních parametrà. Kaºd∞ vstupní parametr je dán sv∞m jménem a datov∞m typem. Jejich seznam se p²i vytvá²ení procedury uvede uzav²en∞ do kulat∞ch závorek za názvem procedury. Vstupní parametry mají sice mnohdy podobné názvy a v∞znam jako jednotlivé sloupce v tabulkách, ale jsou to pouze lokální prom╪nné, které vznikají uvnit² procedury pro její vnit²ní pot²ebu. P²i volání procedury do t╪chto prom╪nn∞ch zvenku vstupují konkrétní hodnoty, které jsou uvnit² procedury pouºity jako souƒásti v∞razà. Pro rozliτení názvà sloupcà tabulek a názvà lokálních prom╪nn∞ch se pouºívá dvojteƒková konvence. Je-li p²ed názvem uvedena dvojteƒka, jde o název lokální prom╪nné. ¼asto pot²ebujeme proceduru pro ruτení osoby podle rodného ƒísla. Nejprve vytvo²íme jednoduchou proceduru:
CREATE PROCEDURE KILLER(RCX VARCHAR(10))
AS
BEGIN
DELETE FROM CLOVEK WHERE RC=:RCX;
END
Po spuτt╪ní procedury KILLER p²íkazem:
EXECUTE PROCEDURE KILLER "5511273208";
si uv╪domíme, ºe konkrétní osobu není moºné zruτit, protoºe má vazby z jin∞ch tabulek. Proto proceduru nejprve zniƒíme a vytvo²íme dokonalejτí dílo zkázy. To vτe ovτem za p²edpokladu, ºe tabulky A, B, C neobsahují zásadní informace, které je nutno uchovávat i po smrti:
DROP PROCEDURE KILLER;
CREATE PROCEDURE KILLER(RCX VARCHAR(10))
AS
BEGIN
DELETE FROM A WHERE RC=:RCX;
DELETE FROM B WHERE RCIS=:RCX;
DELETE FROM C WHERE RRCCIISS=:RCX;
DELETE FROM CLOVEK WHERE RC=:RCX;
END
Procedury se vstupními parametry hrají zásadní roli p²i aktualizaci dat. Následující procedura je vhodná pro zm╪nu k²estního jména konkrétní osoby:
CREATE PROCEDURE KRESTNI (RCX VARCHAR(10), NOVE VARCHAR(30))
AS
BEGIN
UPDATE CLOVEK SET JMENO=:NOVE WHERE RC=:RCX;
END
K p²ejmenování konkrétní osoby pouºijeme p²íkaz:
EXECUTE PROCEDURE KRESTNI "510611030", "JOE";
Koneƒn╪ màºeme i p²idávání nového ƒlov╪ka do tabulky chápat jako proceduru zaτti£ující jeden komplikovan∞ p²ikaz:
V n╪kter∞ch p²ípadech pot²ebujeme, aby procedura vrátila zjiτt╪né hodnoty lokálních prom╪nn∞ch. P²i vytvá²ení procedury uvedeme seznam vracen∞ch prom╪nn∞ch v závorce za klíƒové slovo RETURNS. Následující pon╪kud um╪l∞, ale názorn∞ p²íklad procedury NANECO ukazuje, jak lze vytvo²it snadno souƒasn╪ druhou a t²etí mocninu celého ƒísla:
Zajímají-li nás mocniny ƒísla 7, musíme mít deklarovány dv╪ prom╪nné, nap²íklad P a Q. Potom vyvoláme proceduru p²íkazem s klíƒov∞m slovem RETURNING_VALUES p²ed v∞stupními parametry:
N╪kdy pot²ebujeme uvnit² procedury spustit p²íkaz SELECT tak, aby vypoƒetl dàleºité údaje z tabulek, a to nap²íklad pomocí agregaƒních funkcí. Pokud nechceme jako odpov╪╘ tabulku, pouºijeme v p²íkazu SELECT klíƒové slovo INTO aº na konci. Za ním uvedeme seznam lokálních prom╪nn∞ch, do kter∞ch má b∞t uloºen v∞sledek. Následují ukázky pouºití na procedurách NEJMENSI, KDOTOJE, UCET_TED a STAV_TED:
První t²i uvedené procedury se hodí na zjiτt╪ní nejmenτí v∞τky ƒlov╪ka podle k²estního jména, na identifikaci ƒlov╪ka z rodného ƒísla a na sumarizaci p²íjmà a v∞dajà na úƒtu. Poslední procedura pro celkov∞ stav na úƒtu je zajímavá ve dvou sm╪rech. P²edn╪ demonstruje moºnost volání procedury procedurou s uloºením dílƒích v∞sledkà do prom╪nn∞ch A, B. Dále vidíme, jak ²eτit nedostatek lokálních prom╪nn∞ch. Mezi klíƒov∞mi slovy AS a BEGIN jsou deklarovány dv╪ lokální prom╪nné A, B, které nejsou ani vstupními, ani v∞stupními parametry procedury. Zajímá-li nás stav úƒtu, staƒí se z klientu zeptat:
Na p²edchozích p²íkladech bylo snadné pochopit princip procedur a p²edávání parametrà. Pro realizaci uºiteƒn╪jτích procedur budeme muset um╪t ²ídit postup v∞poƒtu. Zaƒneme v╪tvením, které pouºívá konstrukce IF-THEN-ELSE k podmín╪nému provád╪ní p²íkazà. Chceme-li p²idat osobu do tabulky, u které NEM╡ME OMYLEM zajiτt╪nou entitní integritu, staƒí napsat p²idávací proceduru PRIDEJ_HO:
IF NOT EXISTS(SELECT RC FROM CLOVEK WHERE RC=:RCX)
THEN INSERT INTO CLOVEK (RC, JMENO) VALUES (:RCX, :JJJ);
END
Pokud neexistuje v tabulce CLOVEK ani jeden ²ádek se stejn∞m rodn∞m ƒíslem jako RCX, je zaloºena nová poloºka s tímto rodn∞m ƒíslem a p²ísluτn∞m jménem JJJ. V opaƒném p²ípad╪ se ned╪je nic. Vidíte názorn╪, jak nev∞chovné jsou jednoduché p²íklady. Leckdo si te╘ pomyslí, ºe primární klíƒe a unikátní indexové soubory jsou k niƒemu. Hlavní smysl integritních omezení je v tom, ºe nás nezávisle hlídají nap²íklad i p²i spouτt╪ní nedomyτlen∞ch procedur. P²edstavte si, ºe v procedu²e PRIDEH_HO by omylem chyb╪la spojka NOT. Inteligentn╪jτí procedura PRIDEJ_INFO by mohla mít stejné parametry, ale jiné chování. V p²ípad╪ jiº existujícího rodného ƒísla RCX nebude rezignovat, ale opraví jméno ƒlov╪ka:
IF NOT EXISTS(SELECT RC FROM CLOVEK WHERE RC=:RCX)
THEN INSERT INTO CLOVEK (RC, JMENO) VALUES (:RCX, :JJJ);
ELSE UPDATE CLOVEK SET JMENO=:JJJ WHERE RC=:RCX;
END
P²i volání procedury PRIDEJ_INFO nemusíme v╪d╪t p²edem, zda jde o nového, ƒi o starého známého. U klientu se pak set²e rozdíl mezi opravou a p²idáním dat. Pokud uvedenou techniku povaºujete za hazard, pouºívejte v╪tvení na ²eτení jin∞ch situací. Pak se vám jist╪ bude líbit procedura:
CREATE PROCEDURE ZRUS_HO (RCX VARCHAR(10))
AS
BEGIN
IF NOT EXISTS(SELECT RC FROM UCET WHERE MAJITEL=:RCX)
THEN DELETE FROM CLOVEK WHERE RC=:RCX;
END
Jaromír Kukal
Dalτí moºnosti ²ízení v∞poƒtu
Neº se pustíme do dokonƒení projektu OZNAMKA, rád bych popsal obecné moºnosti ²ízení v∞poƒtu uvnit² procedur. Nejdàleºit╪jτí je pojem bloku. BLOK je skupina p²íkazà sev²en∞ch mezi BEGIN a END. Uvnit² procedury je vºdy jeden blok tvo²ící její t╪lo. Bloky se mohou téº zahníz╘ovat a vytvá²et tak struktury. Posloupnost p²íkazà uzav²ená do bloku se totiº nejen v SQL chová jako jeden p²íkaz. Toho vyuºíváme jak p²i v╪tvení, tak p²i cyklech. P²íkaz v╪tvení má dva obecné tvary:
IF logick∞ v∞raz
THEN p²íkaz proveden∞ nejv∞τe jednou
IF logick∞ v∞raz
THEN p²íkaz proveden∞ p²i spln╪ní podmínky
ELSE opaƒn∞ p²íkaz
Logické v∞razy a p²íkazy jiº známe dávno a bloky usnadní konverzi posloupnosti p²íkazà na jeden p²íkaz.
Pro cyklus s testováním podmínky p²ed zapoƒetím práce se pouºívá schéma:
WHILE logick∞ v∞raz
DO p²íkaz opakovan∞ n╪kolikrát
Pro cyklus p²es vτechny ²ádky tabulky urƒené selectem se pouºívá schéma:
FOR select p²íkaz
DO p²íkaz aplikovan∞ na ²ádek selectu
Pro oτet²ení chyb pouºijeme schéma:
WHEN ANY
DO p²íkaz ƒíhající na chybu
Pro p²edƒasn∞ v∞chod z procedury pouºijeme p²íkaz EXIT.
Pouºití cyklu, bloku a oτet²ení v∞jimek je uvedeno v následujících dvou procedurách. Procedura GAUSS ²eτí Gaussàv τkoláck∞ problém seƒtení ƒísel od 1 do N. Procedura PRUSVIH ²eτí stejn∞ problém, ale hledá nejvyττí moºné ƒíslo, pro které se v∞poƒet jeτt╪ nezhroutí. Vτimn╪te si práce s bloky a lokálními prom╪nn∞mi:
CREATE PROCEDURE GAUSS (N INTEGER) RETURNS (S INTEGER)
