home *** CD-ROM | disk | FTP | other *** search
/ Chip 2000 January / Chip_2000-01_cd.bin / obsahy / Chip_txt / TXT / 178.txt < prev    next >
Text File  |  1999-11-24  |  10KB  |  207 lines
  1. Databáze standardu SQL, díl 19.
  2. Tak²ka po dvou letech zkoumání moºností SQL databází se dostáváme aº do samého finále.
  3.  
  4. Záv╪reƒn∞ sprint 
  5.  
  6. 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:
  7.  
  8. IF logick∞ v∞raz 
  9. THEN p²íkaz proveden∞ nejv∞τe jednou
  10.  
  11. IF logick∞ v∞raz 
  12. THEN p²íkaz proveden∞ p²i spln╪ní podmínky
  13. ELSE opaƒn∞ p²íkaz
  14.  
  15. Logické v∞razy a p²íkazy uº známe dávno a bloky usnadní konverzi posloupnosti p²íkazà na jeden p²íkaz.
  16. Pro cyklus s testováním podmínky p²ed zapoƒetím práce se pouºívá schéma:
  17.  
  18. WHILE logick∞ v∞raz
  19. DO p²íkaz opakovan∞ n╪kolikrát
  20.  
  21. Pro cyklus p²es vτechny ²ádky tabulky urƒené p²íkazem SELECT se pouºívá schéma:
  22.  
  23. FOR select p²íkaz 
  24. DO p²íkaz aplikovan∞ na ²ádek selectu
  25.  
  26. Pro oτet²ení chyb pouºijeme schéma:
  27.  
  28. WHEN ANY
  29. DO p²íkaz ƒíhající na chybu
  30.  
  31. Pro p²edƒasn∞ v∞chod z procedury pouºijeme p²íkaz EXIT. 
  32. 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:
  33.  
  34. CREATE PROCEDURE GAUSS (N INTEGER) RETURNS (S INTEGER)
  35. AS
  36. DECLARE VARIABLE K INTEGER;
  37. BEGIN
  38. :K=0;
  39. :S=0;
  40. WHILE :K<=:N 
  41. DO BEGIN
  42. :S=:S+:K;
  43. :K=:K+1;
  44. END 
  45. END
  46.  
  47. CREATE PROCEDURE PRUSVIH RETURNS (K INTEGER,S INTEGER)
  48. AS
  49. DECLARE VARIABLE KNEW INTEGER;
  50. DECLARE VARIABLE SNEW INTEGER;
  51. BEGIN
  52. :K=0;
  53. :S=0;
  54. WHILE YES
  55. DO BEGIN
  56. :SNEW=:S+:K;
  57. WHEN ANY
  58. DO EXIT;
  59. :KNEW=:K+1;
  60. :S=:SNEW;
  61. :K=:KNEW;
  62. END 
  63. END
  64.  
  65. Konec projektu OZNAMKA 
  66. Pod vlivem p²edchozího v∞kladu uº snadno vytvo²íme pot²ebné uloºené procedury. Zaƒneme s tabulkou CLOVEK, pro jejíº údrºbu je t²eba τest procedur. Procedura NOVYCLOVEK umoºσuje p²idat nového ƒlov╪ka a p²itom hlídat nenulovost rodného ƒísla, jména a p²íjmení spolu s unikátností rodného ƒísla:
  67.  
  68. CREATE PROCEDURE NOVYCLOVEK(RCX VARCHAR(10),JX VARCHAR(30),PX VARCHAR(30))
  69. AS
  70. BEGIN
  71. IF NOT EXISTS(SELECT RC FROM CLOVEK WHERE RC=:RCX) AND :JX IS NOT NULL
  72. AND :PX IS NOT NULL AND :RCX IS NOT NULL
  73. THEN INSERT INTO CLOVEK(RC,JMENO,PRIJMENI,KOEUD,KOEOB) 
  74. VALUES (:RCX,:JX,:PX,1.0,1.0); 
  75. END 
  76.  
  77. Procedu²e NOVYCLOVEK zb∞vá k dokonalosti uº jen logická kontrola rodného ƒísla :RCX, kterou ponechávám ƒtená²i k nedatabázov∞m úvahám. ¼lov╪ka màºeme zruτit, pokud jeτt╪ neudával, respektive nebyl udán tak, jak je uvedeno v procedu²e ZRUSCLOVEK:
  78.  
  79. CREATE PROCEDURE ZRUSCLOVEK(RCX VARCHAR(10))
  80. AS
  81. BEGIN
  82. IF NOT EXISTS(SELECT RCUD FROM UDANI WHERE RCUD=:RCX OR RCOB=:RCX)
  83. THEN DELETE FROM CLOVEK WHERE RC=:RCX; 
  84. END 
  85.  
  86. Zm╪na jména nebo p²íjmení je proti tomu maliƒkost, jak vidíme v procedurách ZMENJMENO a ZMENPRIJMENI:
  87.  
  88. CREATE PROCEDURE ZMENJMENO (RCX VARCHAR(10),JX VARCHAR(30))
  89. AS
  90. BEGIN
  91. IF :JX IS NOT NULL
  92. THEN UPDATE CLOVEK SET JMENO=:JX WHERE RC=:RCX; 
  93. END 
  94.  
  95. CREATE PROCEDURE ZMENPRIJMENI(RCX VARCHAR(10),PX VARCHAR(30))
  96. AS
  97. BEGIN
  98. IF :PX IS NOT NULL
  99. THEN UPDATE CLOVEK SET PRIJMENI=:PX WHERE RC=:RCX; 
  100. END 
  101.  
  102. N╪které ob╪ti mají prominentní postavení a za jejich udání náleºí p²íplatek urƒen∞ koeficientem KOEX v╪tτím neº jedna nebo rovn∞m jedné. Podobn╪ prominentní udavaƒ bere za své sluºby více. Procedury JAKOOBET a JAKOUDAVAC se hodí pro aktualizaci p²íplatkà: 
  103.  
  104. CREATE PROCEDURE JAKOOBET(RCX VARCHAR(10),KOEX DECIMAL(10,3))
  105. AS
  106. BEGIN
  107. IF :KOEX IS NOT NULL AND :KOEX >=1.0
  108. THEN UPDATE CLOVEK SET KOEOB=:KOEX WHERE RC=:RCX; 
  109. END 
  110.  
  111. CREATE PROCEDURE JAKOUDAVAC(RCX VARCHAR(10),KOEX DECIMAL(10,3))
  112. AS
  113. BEGIN
  114. IF :KOEX IS NOT NULL AND :KOEX>=1.0
  115. THEN UPDATE CLOVEK SET KOEUD=:KOEX WHERE RC=:RCX; 
  116. END 
  117.  
  118. Nyní se budeme zab∞vat údrºbou tabulky CIN, na kterou staƒí pouºít ƒty²i procedury. Nejzajímav╪jτí z nich je procedura NOVYCIN, která sama urƒuje hodnotu nového primárního klíƒe CIC a kontroluje unikátnost názvu ƒinu. Je to praktická ukázka nepot²ebnosti p²íkazà TRIGGER p²i dàsledném postupu aktualizace:
  119.  
  120. CREATE PROCEDURE NOVYCIN (NX VARCHAR(30))
  121. AS
  122. DECLARE VARIABLE CIX INTEGER;
  123. BEGIN
  124. SELECT MAX(CIC) FROM CIN INTO :CIX;
  125. IF :CIX IS NULL 
  126. THEN :CIX=1;
  127. ELSE :CIX=:CIX+1;
  128. IF NOT EXISTS(SELECT CIC FROM CIN WHERE NAZEVC=:NX) AND :NX IS NOT NULL
  129. THEN INSERT INTO CIN(CIC,NAZEVC,CENAC) VALUES(:CIX,:NX,0.0 ); 
  130. END 
  131. Procedura ZRUSCIN ruτí jen takov∞ ƒin, na kter∞ jeτt╪ nep²iτlo udání: 
  132.  
  133. CREATE PROCEDURE ZRUSCIN(CIX INTEGER)
  134. AS
  135. BEGIN
  136. IF NOT EXISTS(SELECT CICINU FROM UDANI WHERE CICINU=:CIX )
  137. THEN DELETE FROM CIN WHERE CIC=:CIX; 
  138. END 
  139.  
  140. Aktualizace názvu ƒinu musí b∞t domyτlena tak, aby nedoτlo k duplicit╪ názvà. ⁿeτení tohoto problému je vid╪t v procedu²e NAZEVCINU:
  141. CREATE PROCEDURE NAZEVCINU (CIX INTEGER,NX VARCHAR(30))
  142. AS
  143. BEGIN
  144. IF NOT EXISTS(SELECT CIC FROM CIN WHERE NAZEVC=:NX) AND :NX IS NOT NULL
  145. THEN UPDATE CIN SET NAZEVC=:NX WHERE CIC=:CIX; 
  146. END 
  147.  
  148. Procedura CENACINU umoºσuje m╪nit sazebník poplatkà udavaƒàm na platné hodnoty:
  149.  
  150. CREATE PROCEDURE CENACINU(CIX INTEGER,CX DECIMAL(10,2))
  151. AS
  152. BEGIN
  153. IF :CX IS NOT NULL AND :CX>=0.0
  154. THEN UPDATE CIN SET CENAC=:CX WHERE CIC=:CIX; 
  155. END 
  156.  
  157. Poslední t²i procedury se budou hodit pro aktualizaci tabulky UDANI. Tabulka má sloºen∞ primární kl탠a t²i cizí klíƒe. Nechceme-li spoléhat na integritní omezení, coº d╪láme od samého zaƒátku, musíme vτe p²edem otestovat:
  158.  
  159. CREATE PROCEDURE NOVEUDANI(DX DATETIME, UX VARCHAR(10), OX VARCHAR(10), CX INTEGER)
  160. AS
  161. BEGIN
  162. IF NOT EXISTS(SELECT DEN FROM UDANI WHERE DEN=:DX AND RCUD=:UX AND RCOB=:OX
  163. AND CICINU=:CX) AND :DX IS NOT NULL AND :UX IS NOT NULL AND :OX IS NOT NULL
  164. AND :CX IS NOT NULL AND EXISTS(SELECT RC FROM CLOVEK WHERE RC=:UX)
  165. AND EXISTS(SELECT RC FROM CLOVEK WHERE RC=:OX)
  166. AND EXISTS(SELECT CIC FROM CIN WHERE CIC=:CX)
  167. AND NOT(:UX=:OX)
  168. THEN INSERT INTO (DEN,RCUD, RCOB,CICINU) VALUES(:DX,:UX,:OX, :CX ); 
  169. END 
  170.  
  171. Uvedenou d²inu si màºeme uτet²it pomocí WHEN ANY, které umoºní oτet²it p²ípadné chyby. Podobn∞m trikem by bylo moºné zjednoduτit i n╪které p²edchozí procedury, coº je necháno jako cviƒení. Následuje elegantn╪jτí verze procedury NOVEUDANI:
  172.  
  173. CREATE PROCEDURE NOVEUDANI(DX DATETIME, UX VARCHAR(10), OX VARCHAR(10), CX INTEGER)
  174. AS
  175. BEGIN
  176. INSERT INTO (DEN,RCUD,RCOB,CICINU) VALUES(:DX,:UX,:OX,:CX ); 
  177. WHEN ANY
  178. DO EXIT;
  179. END 
  180.  
  181. N╪kdy je t²eba zahladit vτechny stopy po udavaƒi a jeho stupidní práci. Procedura ZAHLADUDAVACE nejprve zruτí vτechna udání, která uƒinil. Není-li zároveσ ob╪tí jin∞ch udavaƒà, zmizí i z tabulky CLOVEK:
  182.  
  183. CREATE PROCEDURE ZAHLADUDAVACE(RCX VARCHAR(10))
  184. AS
  185. BEGIN
  186. DELETE FROM UDANI WHERE RCUD=:RCX;
  187. IF NOT EXISTS(SELECT RCOB FROM UDANI WHERE RCOB=:RCX )
  188. THEN DELETE FROM CLOVEK WHERE RC=:RCX; 
  189. END 
  190.  
  191. Obdobn╪ pomocí procedury ZAHLADOBET zmizí beze stop ob╪£:
  192.  
  193. CREATE PROCEDURE ZAHLADOBET(RCX VARCHAR(10))
  194. AS
  195. BEGIN
  196. DELETE FROM UDANI WHERE RCOB=:RCX;
  197. IF NOT EXISTS(SELECT RCUD FROM UDANI WHERE RCUD=:RCX )
  198. THEN DELETE FROM CLOVEK WHERE RC=:RCX; 
  199. END 
  200.  
  201. Uºivatelàm databáze OZNAMKA staƒí zve²ejnit seznam názvà, v∞znamà a sloupcà vτech view a seznam názvà, v∞znamà a parametrà vτech uloºen∞ch procedur. P²ísluτné pohledy UDANICKO, UDAVAC, OBET, PRECIN, RCUDAV, RCOBET a MEDAILE nám umoºní dokonalé v∞stupy informací v reálném ƒase a v poºadovaném tvaru. Uloºené procedury NOVYCLOVEK, ZRUSCLOVEK, ZMENJMENO, ZMENPRIJMENI, JAKOOBET, JAKOUDAVAC, NOVYCIN, ZRUSCIN, NAZEVCINU, CENACINU, NOVEUDANI, ZAHLADUDAVACE a ZAHLADOBET poskytnou moºnost bezpeƒné aktualizace databáze. Jak to vτechno funguje a kolik tabulek je schováno "pod povrchem", se nedozví ani uºivatel, ani tvàrce klientské aplikace, pokud mu to n╪kdo nevykecá. Pomocí "ƒínské zdi" z procedur a view je tvrd╪ odd╪len vnit²ní systém realizující p²ísluτné know-how analytika od vn╪jτího prostoru ²adov∞ch realizátorà klientsk∞ch aplikací a nehrozí nebezpeƒí "brigádnického efektu", kdy kaºd∞ inteligentní prázdninov∞ brigádník pochopí vτe pot²ebné ke konkurenƒní ƒinnosti po prázdninách. 
  202.  
  203. Slovo záv╪rem
  204. Ten, kdo pozorn╪ ƒetl cel∞ seriál od τedé a flekaté plastelíny v Chipu 6/98 a pochopil normalizaci tabulek, relace 1:N, integritní omezení, tabulky, domény, indexy, primární, unikátní a cizí klíƒe, moºnosti p²íkazu select a v∞hody view a procedur, ten se jist╪ p²i samostudiu seznámí se zbyl∞mi moºnostmi SQL. Rád bych v prvé ²ad╪ uvedl, proƒ se v seriálu nezab∞vám problematikami TRIGGER a GRANT, které jsou chloubou kaºdého manuálu ke konkrétnímu SQL serveru. Ten, kdo dob²e navrhl databázov∞ systém a data aktualizuje zásadn╪ pomocí uloºen∞ch procedur, objektivn╪ TRIGGER nepot²ebuje. Ledaºe by se cht╪l pochlubit, ºe jej zná nebo se pokouτí velmi moderním zpàsobem automaticky korigovat svàj vlastní chaos. Naproti tomu zajiτt╪ní ràzn∞ch p²ístupov∞ch práv ràzn∞m uºivatelàm pomocí GRANT je ƒinnost pro zdárn∞ chod aplikací na SQL serveru zcela zásadní. Na druhé stran╪ je tak jednoduchá, ºe ji snadno pochopí kaºd∞, kdo doƒetl aº sem. Nejlépe GRANT chápou ti, kdo by nejrad╪ji vτechno vτem ostatním uºivatelàm zakázali. I zde platí vτeho s mírou, a proto by zákazy nem╪ly ochromit rutinní provoz databáze. Pokud vás práv╪ konƒící seriál p²ivedl do rozpakà, pak splnil svàj úƒel. V takovém rozpoloºení nejspíτ n╪co nového dobrého vytvo²íte nebo n╪co starého koneƒn╪ p²ed╪láte tak, aby to fungovalo lépe. Aº budete z klientu posílat SQL dotazy, màºe se stát, ºe nep²ijde kvalitní odpov╪╘, i kdyº je vτechno dob²e navrºeno. Pak patrn╪ nekladete dotaz na SQL server, ale pouze na jeho model v n╪jakém jiném prost²edí. Za dobu svého ƒty²letého SQL misioná²ství mi jen n╪kolik lidí vynadalo, ºe jsem je p²ivedl ke τpatné ví²e. Ve skuteƒnosti nepracovali s daty na SQL serveru, ale se soubory ve formátu DBF ƒi MDB. Jejich potíºe se t∞kaly sdílení dat nebo práce s velk∞m mnoºstvím poloºek v jedné tabulce.
  205. Jaromír Kukal
  206.  
  207.