Datab ze standardu SQL, d¡l 18.
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:

CREATE VIEW UDAVAC(RC, JMENO, PRIJMENI, KOEFICIENT, POCET, CELKEM, UPRAVENO)
AS SELECT RCU, JMENOU, PRIJMENIU, KOEU, COUNT(*), SUM(PRACHY),SUM(FINAL)
FROM UDANICKO GROUP BY RCU;

CREATE VIEW OBET(RC, JMENO, PRIJMENI, KOEFICIENT, POCET, CELKEM, UPRAVENO)
AS SELECT RCO, JMENOO, PRIJMENIO, KOEO, COUNT(*), SUM(PRACHY),SUM(FINAL)
FROM UDANICKO GROUP BY RCO;

CREATE VIEW PRECIN(CISLO, NAZEV, CENA, POCET, CELKEM,UPRAVENO)
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:

CREATE PROCEDURE NOVY_CLOVEK (RCX VARCHAR(10), JX VARCHAR(30), VX INTEGER)
AS
BEGIN
INSERT INTO CLOVEK (RC, JMENO, VYSKA) VALUES (:RCX,:JX,:VX);
END

Vol n¡ je opØt jednoduch‚:

EXECUTE PROCEDURE NOVY_CLOVEK "6104115471", "ANNIE", 9;

Procedury, kter‚ nØco vracej¡ 
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:

CREATE PROCEDURE NANECO (X INTEGER) RETURNS (X2 INTEGER, X3 INTEGER)
AS
BEGIN
:X2=:X*:X;
:X3=:X*:X*:X;
END 

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:

EXECUTE PROCEDURE NANECO 7 RETURNING_VALUES :P, :Q;

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:

CREATE PROCEDURE NEJMENSI (JJJ VARCHAR(30)) RETURNS (VVV INTEGER)
AS
BEGIN
SELECT MIN(VYSKA) FROM CLOVEK 
WHERE JMENO=:JJJ
INTO :VVV;
END 

CREATE PROCEDURE KDOTOJE (RCX VARCHAR(10)) RETURNS (JJJ VARCHAR(30), PPP VARCHAR(30))
AS
BEGIN
SELECT JMENO, PRIJMENI FROM CLOVEK
WHERE RC=:RCX
INTO :JJJ, :PPP; 
END

CREATE PROCEDURE UCET_TED (CUC VARCHAR(20)) RETURNS (P DECIMAL(10,2), V DECIMAL(10,2)) 
AS
BEGIN
SELECT SUM(CASTKA) FROM UCET WHERE CU=:CUC AND POHYB="P"
INTO :P;
SELECT SUM(CASTKA) FROM UCET WHERE CU=:CUC AND POHYB="V"
INTO :V;
END


CREATE PROCEDURE STAV_TED(CUC VARCHAR(20)) RETURNS (STAV DECIMAL(10,2))
AS
DECLARE VARIABLE A DECIMAL (10,2);
DECLARE VARIABLE B DECIMAL (10,2);
BEGIN
EXECUTE PROCEDURE UCET_TED :CUC RETURNING_VALUES :A, :B;
:STAV=:A - :B;
END

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:

EXECUTE PROCEDURE STAV_TED "6674157-471/0531" RETURNING_ VALUES :ST;

VØtven¡ v proceduýe
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:

CREATE PROCEDURE PRIDEJ_HO (RCX VARCHAR(10), JJJ VARCHAR (30))
AS
BEGIN
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:

CREATE PROCEDURE PRIDEJ_INFO (RCX VARCHAR(10), JJJ VARCHAR (30))
AS
BEGIN
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)
AS
DECLARE VARIABLE K INTEGER;
BEGIN
:K=0;
:S=0;
WHILE :K<=:N 
DO BEGIN
:S=:S+:K;
:K=:K+1;
END 
END

CREATE PROCEDURE PRUSVIH RETURNS (K INTEGER,S INTEGER)
AS
DECLARE VARIABLE KNEW INTEGER;
DECLARE VARIABLE SNEW INTEGER;
BEGIN
:K=0;
:S=0;
WHILE YES
DO BEGIN
:SNEW=:S+:K;
WHEN ANY
DO EXIT;
:KNEW=:K+1;
:S=:SNEW;
:K=:KNEW;
END 
END

Po naŸerp n¡ znalost¡ o ulo§enìch procedur ch se m…§eme pustit do dokonŸen¡ projektu a t¡m i seri lu v pý¡çt¡m d¡le.
Jarom¡r Kukal