Posted By: Jovo () on 'CZdatabases' Title: Jovo 5 Date: Mon Dec 11 20:45:23 2000 Z Jovova zapisniku - 5:"Spousteni zkonstruovanych selectu" ========================================================== Sem tam se stava, ze potrebujeme neco spocitat a select nemame primo v kodu, ale zkonstruovany. Typicky ho dostaneme v nejakem retezci. Selectem pro pocitani se zde mysli select, co poskytuje jednu hodnotu, napriklad SELECT SUM(... , SELECT MAX(.... a podobne. Jak ho potom spustit, aby to nezhavarovalo a dostali jsme vysledek do promenne (zde ji nazveme AAA) ? 1] udelame si funkci pro vypocet. // funkce vypoctu function spust_select(retezec str_select, retezec hlaska_chyby, retezec hlaska_chyby_nenalezeno, retezec pro_nenalezeno) { // deklarace lokalnich promennych DECLARE AAA ??????? // (X0) DECLARE status_operace Integer LET status_operace = 0 LET AAA = NULL PREPARE prep_cursor FROM str_select <osetreni pripadne SQL chyby> // (X1) DECLARE c_cursor CURSOR FOR prep_cursor <osetreni pripadne SQL chyby> OPEN c_cursor // (X5) <osetreni pripadne SQL chyby> FETCH c_cursor INTO AAA <osetreni pripadne SQL chyby> IF nic_nenalezl THEN LET status_operace = NENALEZENO // (X2) LET AAA = pro_nenalezeno // (X3) vyhozeni hlasky s textem "CHYBA " + hlaska_chyby_nenalezeno + "pri vypoctu" END IF CLOSE c_cursor <osetreni pripadne SQL chyby> FREE c_cursor <osetreni pripadne SQL chyby> RETURN AAA, status_operace // konec bloku vypoctu } Pozn.: - <osetreni pripadne SQL chyby> = IF byla chyba THEN vyhozeni hlasky s textem "CHYBA " + hlaska_chyby + "pri " + <kde se to stalo (Zdeclare/fetch/...)> LET status_operace = kod SQL chyby RETURN AAA, status_operace END IF Tedy kdyz to vyhuci provedeme hned navrat (zkracene programovani - nemusime psat spoustu IF THEN ELSE END IF - zalezi na stylu programovani, popripade firemni/skolni metodice). "byla chyba" jde poznat u kazde databaze a prekladace jinak. Muze se pouzit standardni promenna SQLCA.SQLCODE, nektere databaze deklaruji i promennou STATUS, nebo SQL_ERRNO, zalezi na konkretni situaci. - v miste oznacenem (X0) si doplnte typ, ktery vam vyhovuje (INT, DECIMAL,STRING,...). Pokud ma vas prekladac k dispozici makra, pak je vhodne celou funkci "spust_select" definovat jako makro a pro (X0) definovat i typ. Kdo nema makra - musi odladit, a pro kazdy typ vytvorit samostatnou funkci (COPY/PASTE) - Vsimnete si, ze promenna "pro_nenalezeno" je definovana jako retezec. Tento postup predpoklada automatickou konverzi mezi typem retezec a int. - v (X2) se prirazuje konstanta NENALEZENO. Ta muze byt pro kazdy system jina (napr. pro INFORMIX NENALEZENO=100) Ziskame vysledek (AAA) a jestli nenastala chyba (status_operace). Pak uz si muzeme vyskakovat :-) Proc to ale obcas pada : Pokud nastane chyba, tak podle mych zkusenosti to vyhuci na 95% v miste (X1) - to znamena, ze mame spatne zkonstruovany retezec selectu. Nejcastejsi chyby jsou v tomto pripade : - spatne napsany select :-) Zkontrolujte si preklepy, seznam tabulek a jestli je mezi podminkami where "AND". A co zavorky, mate je vsechny uzavrene ? Ne, tak pro jistotu vsechno ozavorkujte - to byste se divili, jak chyby rychle mizi :-) Pro uzivatele editoru vi - protejsek zavorek se hleda pres prikaz "%", u editoru joe CTRL+G, u Borland-style editoru CTRL+Q+] Zde hodne napovi vraceny kod SQL chyby (vetsinou chyba syntaxe, spatne jmeno sloupce/tabulky, tabulka nen9 v from sekci,...) - je li retezec skladany z vice casti LET str_select = str_sel + str_from + str_where (X4) a kdyz bude str_sel = "SELECT SUM(sloupec)" str_from = "FROM tabulka" str_where = "WHERE podminka" a jste si NAPROSTO jisti, ze ten select je dobre, tak je chyba ve skladani, prikaz (X4) udela str_select = "SELECT SUM(sloupec)FROM tabulkaWHERE podminka" ^ ^ a pak tady chybi mezery. Pro jistotu tedy (X4) piste jako LET str_select = str_sel + " " + str_from + " " + str_where - v retezci chybi klicova slova, velmi casto se stane, ze programator napise LET str_from = "tabulka" V 2% to vyhuci na miste oznacenem (X2). V podstate to neni chyba, jenom to nic nenalezlo. Zde doporucuji pouzivani isnull (ne SELECT SUM(...), ale SELECT isnull(SUM(...),0) - viz take dil 4 :"Pouziti isnull jako IF") Tak ve 2% pripadu to spadne v (X5). Mame spatne napsany select - viz PS na konci tohoto textu. To 1% si nechavam pro pripadne nestandardni situace (spadne nam spojeni, pad DB stroje, nekdo nam zamkne exclusivne tabulku, uz jsem zazil i vymaz -dropnuti- databaze uprostre prace :-) 2] jeste tu muze nastat situace, ze zkonstruovany select vybere vic hodnot. Nekdo (ve velke vetsine pripadu my samotni) proste napsal select sice formalne dobre, ale dela nam pitomosti. Anebo zkousi, jestli jsou v DB korektni data (napriklad vhodne zvolenym GROUP BY). Reseni je v prepsani funkce tak, aby vzala za vysledek treba jen prvni (nebo jen posledni, druhy, treti .... jak si to kdo napise) hodnotu a vracela i pocet zaznamu. Pak to muze vypadat i takto : Zde je jednodussi varianta - vraci posledni nalezenou hodnotu. Vyhoda je, ze je to jednoduche, ale neefektivni. function spust_select2(retezec str_select, retezec hlaska_chyby, retezec hlaska_chyby_nenalezeno, retezec hlaska_chyby_nalezeno_vice, retezec pro_enalezeno_vice, retezec pro_nenalezeno) { // deklarace lokalnich promennych DECLARE AAA ??????? DECLARE status_operace Integer DECLARE pocet_radku Integer // (Y0) LET status_operace = 0 LET AAA = NULL LET pocet_radku = 0 PREPARE prep_cursor FROM str_select <osetreni pripadne SQL chyby> DECLARE c_cursor CURSOR FOR prep_cursor <osetreni pripadne SQL chyby> // (Y1) FOREACH c_cursor INTO AAA LET pocet_radku = pocet_radku + 1 END FOREACH // (Y2) IF nebyla SQL chyba THEN IF pocet_radku = 0 THEN LET status_operace = NENALEZENO LET AAA = pro_nenalezeno vyhozeni hlasky s textem "CHYBA " + hlaska_chyby_nenalezeno + "pri vypoctu" ELSE IF pocet_radku = 0 THEN LET status_operace = NALEZENO_VICE LET AAA = pro_nalezeno_vice vyhozeni hlasky s textem "CHYBA " + hlaska_chyby_nalezeno_vice + "pri vypoctu" END IF END IF ELSE <osetreni pripadne SQL chyby> END IF RETURN AAA, pocet_radku, status_operace // konec bloku vypoctu } Pozn.: - nevyhoda : kdyz select vybira postupne milion zaznamu, tak se pekne nacekame. Pokud chceme OPRAVDU posledni hodnotu, pak se s tim bud musime smirit, nebo pouzit jiny mechanismus : - zjistit kolik toho vybere (viz nize) - kdyz jen jeden zaznam, pak to rozjet - vse OK - kdyz vybere vic zaznamu je nekolik reseni : - bud zatnout zuby a pretrpet cely cyklus foreach - pouzit nestandardni mechanismus - nektere DB stroje poskytuji promennou, ve ktere je rowid naposled prochazeneho radku, takze muzeme si dat COUNT(*), a pak vybrat jen radek s odpovidajicim rowid. Nelze pouzit vzdy (treba ne na SUM, ...) a ne na vsech strojich. Zjistete si, o kolik je COUNT(*) rychlejsi, nez projizdeni kurzorem pres celou tabulku !!! Budete nemile prekvapeni. - NALEZENO_VICE je zase nejaka vhodne definovana konstanta :-) U INFORMIXu lze pouzit NENALEZENO+1 - kdyby to melo vybirat jen prvni hodnotu, pak musime prepsat: - (Y0) : DECLARE predesle_AAA ??????? - (Y1)-(Y2) : FOREACH c_cursor INTO predesle_AAA LET pocet_radku = pocet_radku + 1 IF pocet_radku = 1 THEN LET AAA = predesle_AAA ELSE // (Y3) EXIT FOREACH // ukonci cyklus, neco jako prikaz break v C/C++ END IF END FOREACH To ma vyhodu, ze se vypocet hned zarazi a nevime kolik radku to vybere. Pokud pocet radku nepotrebujeme, je tento zpusob efektivnejsi, Pokud ho ale chceme, lze pouzit pekny trik : V retezci str_select nahradime cast oznacenou sipkami SELECT ... FROM .... ^^^ za "COUNT(*)" a v (Y3) provedeme jeste jeden vypocet. Proc jen nezavolat tutez funkci rekurzivne ? Nektere prekladace/implementace maji potize s kurzory (zavolame proceduru jednou, ta si nadeklaruje kurzor, neco do nej zacne strkat, pak zavola sama sebe, zjisti, ze kurzor je nadeklarovany, tak do nej zacne rovnou neco strkat a program spadne.) Velmi casto se stava v nasledujici situaci, funkce1 --> funkce2 deklarace kurzoru1 (Y4) / deklarace kurzoru2 foreach (Y5) / prace Volani jine funkce --- zavreni kurzoru2 (Y6) <--------- navrat end foreach kdyz volana sub-funkce (funkce2) zavre kurzor (Y6), tak volajici funkce (funkce1) zhavaruje v miste (Y5) s hlaskou, ze se pokusila udelat fetch na neotevreny kurzor. Na miste (Y6) se nezavre jen kurzor2, ale i kurzor1 ! Na pytel, ze ? A navic, kdo ma na takovou nelogicnost prijit :-) Odstraneni teto chyby je jednoduche : v miste (Y4) misto DECLARE kurzor1 CURSOR .... dejte DECLARE kurzor1 CURSOR ... WITH HOLD Tak to alespon funguje na databazi INFORMIX v7 :-) Tak jsem se v tom trochu zahrabal. Kdo nevi kudy kam, at si prohledne jen funkci "spust_select2", to je celkem pouzitelny mustr. Vas Jovo. PS: - pro oziveni - spousteni prikazu, ktere jen neco delaji a zaroven nemaji vystup : PREPARE prep_cursor FROM str_select <osetreni pripadne SQL chyby> EXECUTE prep_cursor <osetreni pripadne SQL chyby> str_select muze byt napriklad "DELETE FROM tab WHERE podminka", nebo "UPDATE .....", ... Velmi casto se tento postup pouziva pri zadavani kterii uzivatelem, Treba na formulari uzivatel rekne, ze maji byt do tabulky T1 zarazena data z tabulky T2, ktere maji sloupec SSSS treba vetsi nez omezeni, jinak aby tam pridal vsechno LET str_select = "INSERT INTO T2" + "SELECT <seznam sloupcu z T1>" + "FROM T1 " + "WHERE 1=1 " IF uzivatel zadal omezeni THEN LET str_select = str_select + "AND SSSS >" + omezeni END IF PREPARE prep_cursor FROM str_select EXECUTE prep_cursor