Pozdrav kolega čitatelju bloga na Zajednici.
Želio bih govoriti o svom nedavnom iskustvu u optimizaciji kursora u SQL Serveru.
Prva stvar koju trebate znati je kursor nije dobar, ali loš. Tamo gdje je moguće zamijeniti kursor sa INSERT SELECT ili koristiti privremenu tablicu, to treba učiniti (uz rijetke izuzetke). Kursor gotovo uvijek znači dodatne resurse servera i oštar pad performansi u odnosu na druga rješenja.
Drugo, ponekad ne možete bez kursora - gdje ne možete bez prolaska red po red kroz rezultat odabira. U takvim slučajevima vrlo je važno pravilno kreirati potrebnu vrstu kursora - onaj koji odgovara problemu koji se rješava. Opšta sintaksa za deklarisanje kursora je:
DECLARE cursor_name CURSOR
[LOKALNI | GLOBALNO]
[ FORWARD_ONLY | SCROLL ]
[STATIC | KEYSET | DYNAMIC | BRZO NAPRIJED ]
[SAMO ZA ČITANJE | SCROLL_LOCKS | OPTIMISTIČAN ]
[TYPE_WARNING]
ZA select_naredbu
[ ZA AŽURIRANJE [ OF column_name [ ,... n ] ] ] [ ;]
Fokusiraću se na prve tri linije ključnih parametara.
LOKALNO ili GLOBALNO: ako želimo da kursor bude dostupan drugim procedurama, funkcijama, paketima unutar naše sesije, onda GLOBALNO - u ovom slučaju sami se brinemo o brisanju kursora (komanda DEALLOCATE). U svim ostalim slučajevima (tj. u ogromnoj većini) - LOCAL. Pažnja, po defaultu je kreiran GLOBAL kursor!
FORWARD_ONLY ili SCROLL: ako želimo da se krećemo po kursoru kao ludi, naprijed-nazad, onda SCROLL, inače - FORWARD_ONLY. Pažnja, po defaultu se kreira SCROLL kursor!
STATIC ili KEYSET, DYNAMIC, FAST_FORWARD: ako želimo da se trenutne informacije iz tabele prikazuju prilikom prolaska kroz kursor (tj. ako smo nakon otvaranja kursora promijenili informaciju u jednom od polja tabele i želimo da kada prolazimo kroz kursor u željenoj liniji kursora već ima ažurirane informacije), tada koristimo ili KEYSET (ako SVAKA tabela koja učestvuje u odabiru ima jedinstveni indeks) ili DYNAMIC (najsporiji tip). Ako nam treba snimak rezultata uzorka nakon otvaranja kursora - STATIC(najbrži tip - kopija rezultata uzorka se kopira u tempdb bazu podataka i mi radimo s njom). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – momci sa interneta pišu da STATIC-u treba duže da se otvori (pošto se kopija kreira u tempdb), ali radi brže, a FAST_FORWARD je suprotno. Dakle, ako je broj zapisa veliki (koliko praksa pokazuje), onda koristimo STATIC, u suprotnom koristimo FAST_FORWARD. Pažnja, po defaultu se kreira DINAMIČKI kursor.
Dakle, za veliki broj zapisa, u većini slučajeva moj izbor je:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen t
za mali broj zapisa:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement
Sada pređimo na praksu (koja me je zapravo i navela da ovo napišem).
Od pamtivijeka, kada sam deklarirao kursor, koristio sam konstrukciju DECLARE ... CURSOR LOCAL FOR....
Prilikom razvoja integracije sa jednom jako lošom bazom podataka, u kojoj nema ni jednog indeksa ni jednog ključa, koristio sam isti pristup prilikom deklarisanja kursora kao i uvijek. Uzorak jednog kursora sadržavao je 225.000 zapisa. Kao rezultat toga, proces uvoza podataka iz takve baze podataka je trajao 15 sati 14 minuta!!! I iako je uvoz bio primarni (tj. jednokratni), čak i normalno testiranje takvog uvoza bi zahtijevalo nekoliko dana! Nakon zamjene gornje konstrukcije prilikom deklariranja kursora sa DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC ZA.. cijeli proces uvoza je uzeo... pažnju... 10 minuta 5 sekundi!!! Dakle, igra je definitivno vrijedna svijeća.
Želeo bih da ponovim da je idealna opcija da se kursori uopšte ne koriste - za MS SQL DBMS, relacioni, a ne navigacioni pristup je mnogo prirodniji.
Dobio sam niz komentara. U jednom od njih, čitatelj me je zamolio da više pažnje posvetim kursorima, jednom od važnih elemenata pohranjenih procedura.
Budući da su kursori dio pohranjene procedure, u ovom članku ćemo detaljnije pogledati HP. Konkretno, kako izdvojiti skup podataka iz HP-a.
Šta je kursor?
Kursor se ne može koristiti sam u MySQL-u. To je važna komponenta pohranjenih procedura. Uporedio bih kursor sa "pokazivačem" u C/C++ ili iteratorom u PHP foreach naredbi.
Koristeći kursor, možemo iterirati kroz skup podataka i obraditi svaki zapis prema specifičnim zadacima.
Ova operacija obrade zapisa može se obaviti i na PHP sloju, što značajno smanjuje količinu podataka proslijeđenih PHP sloju jer jednostavno možemo vratiti obrađeni sažetak/statistički rezultat nazad (čime se eliminiše obrada odabira za svaki na strani klijenta) .
Budući da je kursor implementiran u pohranjenoj proceduri, on ima sve prednosti (i nedostatke) svojstvene HP-u (kontrola pristupa, prethodna kompilacija, otklanjanje poteškoća, itd.)
Službenu dokumentaciju o kursorima možete pronaći ovdje. Opisuje četiri komande koje se odnose na deklaraciju kursora, otvaranje, zatvaranje i preuzimanje. Kao što je spomenuto, također ćemo pokriti neke druge naredbe pohranjenih procedura. Hajde da počnemo.
Praktični primjer primjene
Moja lična web stranica ima stranicu sa rezultatima utakmica za moj omiljeni NBA tim: Lakerse.
Struktura tabele ove stranice je prilično jednostavna:
Slika 1. Struktura tabele rezultata igre Lakersa
Ovu tabelu popunjavam od 2008. Neki od najnovijih rezultata utakmica Lakersa iz sezone 2013-14 su u nastavku:
Rice. 2. Tabela rezultata utakmica Lakersa (djelimičnih) u sezoni 2013-2014
(Koristim MySQL Workbench kao GUI alat za upravljanje MySQL bazom podataka. Možete koristiti drugi alat po vašem izboru).
Pa, moram priznati da košarkaši Lakersa ne igraju baš najbolje u posljednje vrijeme. 6 poraza u nizu od 15. januara. Definisao sam ove " 6 poraza u nizu", ručnim prebrojavanjem koliko mečeva u nizu, počevši od trenutnog datuma (i sve do ranijih utakmica) imaju vrijednost winlose "L" (gubitak).
Ovo svakako nije nemoguć zadatak, ali ako uslovi postanu složeniji i tabela podataka je mnogo veća, onda će to trajati duže, a vjerovatnoća greške će se također povećati.
Možemo li učiniti isto s jednom SQL naredbom? Nisam stručnjak za SQL, pa nisam mogao shvatiti kako postići željeni rezultat (" 6 poraza u nizu") kroz jednu SQL naredbu. Guruova mišljenja će mi biti vrlo vrijedna - ostavite ih u komentarima ispod.
Možemo li to učiniti preko PHP-a? Da naravno. Možemo dobiti podatke o igri (konkretno kolonu pobjeda) za tu sezonu i iterirati kroz zapise kako bismo izračunali dužinu trenutnog niza pobjeda/poraza.
Ali da bismo to učinili, morali bismo pokriti sve podatke za tu godinu, a većina podataka bi nam bila beskorisna (nije baš vjerovatno da bi bilo koji tim imao niz duži od 20+ utakmica zaredom regularna sezona od 82 utakmice).
Međutim, ne znamo sa sigurnošću koliko zapisa mora biti preuzeto u PHP-u da bi se odredila serija. Dakle, ne možemo bez nepotrebnog izvlačenja nepotrebnih podataka. I na kraju, ako je trenutni broj pobjeda/poraza u nizu jedino što želimo da znamo iz ove tabele, zašto bismo morali da izdvajamo sve redove podataka?
Možemo li ovo uraditi na drugi način? Da, moguće je. Na primjer, možemo kreirati rezervnu tabelu posebno dizajniranu da pohrani trenutnu vrijednost broja pobjeda/gubitaka u nizu.
Dodavanjem svakog novog zapisa ova tabela će se automatski ažurirati. Ali ovo je previše glomazno i podložno greškama.
Pa kako to možemo učiniti bolje?
Korištenje kursora u pohranjenoj proceduri
Kao što ste mogli pretpostaviti iz naslova ovog članka, najbolja alternativa (po mom mišljenju) za rješavanje ovog problema je korištenje kursora u pohranjenoj proceduri.
Kreirajmo prvi HP u MySQL Workbench-u:
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(u cur_year int, out longeststreak int, out status char(1)) BEGIN proglasiti current_win char(1); deklarirati current_streak int; deklarirati current_status char(1); deklarirati kursor za odabir winlose od lakers gdje year=cur_year i winlose<>"" poredak prema id desc; set current_streak=0; open cur; dohvati cur u current_win; set current_streak = current_streak +1; start_loop: petlja dohvati cur u current_status; ako je trenutni_status<>current_win zatim ostavite start_loop; else set current_streak=current_streak+1; kraj ako; end loop; close cur; odaberite current_streak u najduži niz; odaberite current_win u `status`; KRAJ
U ovom HP-u imamo jedan ulazni parametar i dva izlazna. Ovo definiše HP potpis.
U tijelu HP-a također smo deklarirali nekoliko lokalnih varijabli za seriju rezultata (pobjede ili poraze, trenutna_pobjeda), trenutnu seriju i trenutni status pobjeda/gubitaka određene utakmice:
Ova linija je deklaracija kursora. Deklarisali smo kursor po imenu cur i skup podataka povezanih sa ovim kursorom, koji je status pobjeda/poražena za te mečeve (vrijednost kolone winlose može biti ili "W" ili "L", ali ne i prazna) u određene godine koje su poredane po ID-u (zadnje igrane igre će imati veći ID) u opadajućem redoslijedu.
Iako nije vidljiv, možemo zamisliti da će ovaj skup podataka sadržavati niz vrijednosti "L" i "W". Na osnovu podataka prikazanih na slici 2, trebalo bi da bude ovako: “LLLLLLWLL...” (6 vrijednosti “L”, 1 “W” itd.)
Da bismo izračunali broj pobjeda/poraza u nizu, počinjemo s posljednjim (i prvim u datom skupu podataka) mečom. Kada se otvori kursor, on uvijek počinje s prvim zapisom u odgovarajućem skupu podataka.
Nakon što se prvi podaci učitaju, kursor se pomiče na sljedeći zapis. Dakle, ponašanje kursora je slično redu čekanja koji se ponavlja kroz skup podataka koristeći FIFO (First In First Out) sistem. To je upravo ono što nam treba.
Nakon što dobijemo trenutni status pobjeda/gubitaka i broj uzastopnih identičnih elemenata u setu, nastavljamo da petljamo kroz ostatak skupa podataka. U svakoj iteraciji petlje, kursor će “skočiti” na sljedeći zapis sve dok ne prekinemo petlju ili dok se svi zapisi ne iteriraju.
Ako je status sljedećeg rekorda isti kao trenutnog uzastopnog skupa pobjeda/gubitaka, to znači da se niz nastavlja, tada povećavamo broj uzastopnih pobjeda (ili poraza) za još 1 i nastavljamo da se krećemo kroz podatke.
Ako je status drugačiji, to znači da je niz prekinut i da možemo zaustaviti ciklus. Na kraju zatvaramo kursor i ostavljamo originalne podatke. Nakon toga se prikazuje rezultat.
Da bismo testirali rad ovog HP-a, možemo napisati kratku PHP skriptu:
exec("call streak(2013, @longeststreak, @status)"); $res=$cn->query("odaberite @longeststreak, @status")->fetchAll(); var_dump($res); //Izbacite izlaz ovdje da biste dobili sirovi prikaz izlaza $win=$res["@status"]="L"?"Loss":"Win"; $streak=$res["@longeststreak"]; echo "Lejkersi su sada $streak uzastopni $win.n";
Rezultat obrade trebao bi izgledati otprilike ovako:
Izlaz skupa podataka iz pohranjene procedure
Nekoliko puta tokom ovog članka, razgovor se ticao kako izvući skup podataka iz HP-a, koji čini skup podataka iz rezultata obrade nekoliko uzastopnih poziva drugom HP-u.
Korisnik će možda želeti da dobije više informacija pomoću HP-a koji smo prethodno kreirali od samo kontinuiranog niza pobeda/gubitaka za godinu; na primjer, možemo kreirati tabelu koja će prikazati niz pobjeda/gubitaka za različite godine:
| GODINA | Pobjeda/Izguba | Streak |
| 2013 | L | 6 |
| 2012 | L | 4 |
| 2011 | L | 2 |
(U principu, korisnija informacija bi bila trajanje najdužeg niza pobeda ili poraza u određenoj sezoni. Da biste rešili ovaj problem, lako možete proširiti opisani HP, pa ću ovaj zadatak prepustiti onim čitaocima koji su zainteresovani. u okviru trenutnog članka, nastavit ćemo obraditi trenutni niz pobjeda/poraba).
MySQL pohranjene procedure mogu vratiti samo skalarne vrijednosti (cijeli broj, niz, itd.), za razliku od naredbi select ... from ... (rezultati se pretvaraju u skup podataka). Problem je u tome što tabela u kojoj želimo da dobijemo rezultate ne postoji u postojećoj strukturi baze podataka, već se kompajlira iz rezultata obrade uskladištene procedure.
Za rješavanje ovog problema potrebna nam je privremena tablica ili, ako je moguće i potrebno, rezervna tablica. Pogledajmo kako možemo riješiti problem pomoću privremene tablice.
Prvo ćemo kreirati drugi HP, čiji je kod prikazan ispod:
DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`() početi deklarisati cur_year, max_year, min_year int; izaberite max(godina), min(godina) od lakers u max_year, min_year; ISPUSTI PRIVREMENU TABELU AKO POSTOJI yearly_streak; CREATE PRIVREMENU TABELU yearly_streak (sezona int, streak int, char(1)); set cur_year=max_year; year_loop: petlja ako cur_year Nekoliko značajnih napomena o gornjem kodu: Da bismo dobili rezultate, kreiramo još jednu malu PHP skriptu, čiji je kod prikazan ispod: query("call yearly_streak")->fetchAll(); foreach ($res kao $r) ( echo sprintf("U godini %d, najduži W/L nizovi je %d %sn", $r["season"], $r["streak"], $r[ "pobjedi"]);) Prikazani rezultati će izgledati otprilike ovako: Eksplicitni kursor je naredba SELECT eksplicitno definirana u dijelu deklaracije programa. Kada deklarišete eksplicitni kursor, on dobija ime. Eksplicitni kursori se ne mogu definirati za naredbe INSERT, UPDATE, MERGE i DELETE. Definiranjem naredbe SELECT kao eksplicitnog kursora, programer ima kontrolu nad glavnim fazama preuzimanja informacija iz Oracle baze podataka. Određuje kada da se otvori kursor (OPEN), kada da se izaberu redovi iz njega (FETCH), koliko redova da se izabere i kada da se zatvori kursor pomoću naredbe CLOSE. Informacije o trenutnom stanju kursora dostupne su preko njegovih atributa. Upravo ova visoka granularnost kontrole čini eksplicitne kursore neprocjenjivim alatom za programera. Pogledajmo primjer: 1 FUNKCIJA jealousy_level (2 NAME_IN U prijateljima.NAME%TYPE) POVRATAK BROJ 3 KAO 4 KURSOR jealousy_cur 5 JE 6 ODABIR lokacije IZ prijatelja 7 GDJE IME = GORNJE (NAME_IN); 8 8 jealousy_rec jealousy_cur%ROWTYPE; 9 retval NUMBER; 10 POČNI 11 OTVOREN jealousy_cur; 13 12 FETCH jealousy_cur INTO jealousy_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 CLOSE jealousy_cur; 26 22 RETURN retval; 23 IZUZETAK 24 KADA DRUGI ONDA 25 AKO jealousy_cur%ISOPEN ONDA 26 CLOSE jealousy_cur; 27 END IF; 28 END; Sljedećih nekoliko odjeljaka detaljno govori o svakoj od ovih operacija. Termin "kursor" u njima se odnosi na eksplicitne kursore, osim ako je u tekstu izričito navedeno drugačije. Da biste mogli koristiti eksplicitni kursor, on mora biti deklariran u dijelu deklaracije PL/SQL bloka ili paketa: CURSOR cursor_name [ ([ parametar [, parametar...]) ] [ RETURN specification_reEirn ] IS SELECT_command ]; Ovdje je ime kursora ime deklariranog kursora; spiifiction_te?it - opcioni odjeljak RETURN; KOMaHdaSELECT - bilo koja važeća SQL SELECT naredba. Parametri se takođe mogu preneti na kursor (pogledajte odeljak „Parametri kursora“ ispod). Konačno, nakon naredbe SELECT...FOR UPDATE, možete odrediti listu kolona za ažuriranje (također pogledajte ispod). Nakon deklaracije, kursor se otvara naredbom OPEN, a redovi se iz njega preuzimaju naredbom FETCH. Neki primjeri eksplicitnih deklaracija kursora. Eksplicitno ime kursora mora biti dugo do 30 znakova i slijediti ista pravila kao i drugi PL/SQL identifikatori. Ime kursora nije varijabla - to je identifikator pokazivača na zahtjev. Ime kursora nije dodijeljena vrijednost i ne može se koristiti u izrazima. Kursor se koristi samo u naredbama OPEN, CLOSE i FETCH i za kvalificiranje atributa kursora. Eksplicitni kursori su deklarisani u sekciji deklaracije PL/SQL bloka. Kursor se može deklarirati na razini paketa, ali ne unutar određene paketne procedure ili funkcije. Primjer deklariranja dva kursora u paketu: PAKET book_info IS CURSOR titles_cur IS SELECT title FROM books; CURSOR books_cur (title_filter_in IN books.title%TYPE) VRAĆANJE knjiga%ROWTYPE JE SELECT * IZ knjiga GDJE naslov LIKE title_filter_in; END; Prvi kursor titles_cur vraća samo naslove knjiga. Drugi, books_cur , vraća sve redove tabele knjiga u kojima se nazivi knjiga podudaraju sa obrascem navedenim kao parametar kursora (na primjer, "Sve knjige koje sadrže string 'PL/SQL'"). Imajte na umu da drugi kursor koristi odjeljak RETURN, koji deklarira strukturu podataka koju vraća naredba FETCH. Odjeljak RETURN može sadržavati bilo koju od sljedećih struktura podataka: Broj izraza u listi odabira kursora mora odgovarati broju stupaca u tablici_name%ROWTYPE, Kypcop%ROWTYPE ili zapisu tipa zapisa. Tipovi podataka elemenata također moraju biti kompatibilni. Na primjer, ako je drugi element liste za odabir tipa BROJ, onda drugi stupac unosa u sekciji RETURN ne može biti tipa VARCHAR2 ili BOOLEAN. Prije nego pređemo na detaljno ispitivanje odjeljka RETURN i njegovih prednosti, hajde da prvo shvatimo zašto bi moglo biti potrebno deklarirati kursore u paketu? Zašto ne deklarirati eksplicitni kursor u programu u kojem se koristi - u proceduri, funkciji ili anonimnom bloku? Odgovor je jednostavan i uvjerljiv. Definiranjem kursora u paketu, možete ponovo koristiti upit definiran u njemu bez ponavljanja istog koda na različitim mjestima u aplikaciji. Implementacija zahtjeva na jednom mjestu pojednostavljuje njegovu modifikaciju i održavanje koda. Određene uštede u vremenu postižu se smanjenjem broja obrađenih zahtjeva. Također je vrijedno razmisliti o stvaranju funkcije koja vraća varijablu kursora na osnovu REF CURSOR. Program koji poziva dohvaća redove kroz varijablu kursora. Za više informacija, pogledajte odjeljak "Varijable kursora i REF CURSOR". Kada deklarirate kursore u paketima za višekratnu upotrebu, treba uzeti u obzir jednu važnu stvar. Sve strukture podataka, uključujući kursore, deklarirane na "nivou paketa" (ne unutar određene funkcije ili procedure), zadržavaju svoje vrijednosti tokom sesije. To znači da će batch kursor ostati otvoren dok ga eksplicitno ne zatvorite ili dok se sesija ne završi. Kursori deklarisani u lokalnim blokovima se automatski zatvaraju kada se ti blokovi završe. Pogledajmo sada odjeljak POVRATAK. Jedna interesantna stvar u vezi deklarisanja kursora u paketu je da se zaglavlje kursora može odvojiti od njegovog tela. Ovo zaglavlje, koje više podsjeća na zaglavlje funkcije, sadrži informacije koje su programeru potrebne za rad: ime kursora, njegove parametre i tip vraćenih podataka. Telo kursora je naredba SELECT. Ova tehnika je demonstrirana u novoj verziji deklaracije kursora books_cur u paketu book_info: PAKET book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; END; TIJELO PAKET book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * IZ knjiga GDJE naslov LIKE title_filter_in; END; Svi znakovi prije ključne riječi IS čine specifikaciju, a nakon IS dolazi tijelo kursora. Podjela deklaracije kursora može služiti u dvije svrhe. Korišćenje kursora počinje sa njegovim definisanjem u sekciji deklaracija. Zatim se deklarisani kursor mora otvoriti. Sintaksa za naredbu OPEN je vrlo jednostavna: OPEN cursor_name [ (argument [, argument...]) ]; Ovdje je cursorname ime prethodno deklariranog kursora, a argument je vrijednost koja se prosljeđuje kursoru ako je deklariran sa listom parametara. Oracle također podržava FOR sintaksu prilikom otvaranja kursora, koji se koristi za obje varijable kursora (pogledajte odjeljak "Varijable kursora i REF CURSOR") i ugrađeni dinamički SQL. Kada PL/SQL otvori kursor, on izvršava upit koji sadrži. Osim toga, identifikuje aktivni skup podataka - redove svih tabela koje učestvuju u upitu koji odgovaraju kriteriju WHERE i uvjetu spajanja. Komanda OPEN ne preuzima podatke - to je posao naredbe FETCH. Bez obzira na to kada se prvo dohvaćaju podaci, Oracleov model integriteta podataka osigurava da sve operacije dohvaćanja vraćaju podatke u stanju u kojem je kursor otvoren. Drugim riječima, od otvaranja do zatvaranja kursora, prilikom preuzimanja podataka iz njega, operacije umetanja, ažuriranja i brisanja izvršene za to vrijeme potpuno se zanemaruju. Štaviše, ako naredba SELECT sadrži odjeljak FOR UPDATE, svi redovi identificirani kursorom su zaključani kada se kursor otvori. Ako pokušate otvoriti kursor koji je već otvoren, PL/SQL će izbaciti sljedeću poruku o grešci: ORA-06511: PL/SQL: kursor je već otvoren Stoga, prije otvaranja kursora, trebate provjeriti njegovo stanje pomoću vrijednosti atributa %izopen: IF NOT company_cur%ISOPEN THEN OPEN company_cur; ENDIF; Atributi eksplicitnih kursora opisani su u nastavku u odjeljku posvećenom njima. Ako program izvršava FOR petlju koristeći kursor, kursor se ne mora eksplicitno otvarati (dohvaćati, zatvarati). PL/SQL motor to radi automatski. Naredba SELECT kreira virtualnu tablicu - skup redova definiranih klauzulom WHERE sa stupcima definiranim listom SELECT stupaca. Dakle, kursor predstavlja ovu tabelu u PL/SQL programu. Primarna svrha kursora u PL/SQL programima je odabir redova za obradu. Dohvaćanje redova kursora se vrši naredbom FETCH: FETCH cursor_name INTO record_or_variable_list; Ovdje je naziv kursora ime kursora iz kojeg je odabran zapis, a lista zapisa ili varijabli su PL/SQL strukture podataka u koje se kopira sljedeći red aktivnog skupa zapisa. Podaci se mogu staviti u PL/SQL zapis (deklarisan atributom %ROWTYPE ili deklaracijom TYPE) ili u varijable (PL/SQL varijable ili varijable povezivanja - kao što su elementi Oracle Forms). Sljedeći primjeri pokazuju različite načine uzorkovanja podataka. Podaci koji se preuzimaju iz kursora uvijek bi trebali biti smješteni u zapis deklariran pod istim kursorom s atributom %ROWTYPE; Izbjegavajte odabir liste varijabli. Dohvaćanje u zapis čini kod kompaktnijim i fleksibilnijim, omogućavajući vam da promijenite listu preuzimanja bez promjene naredbe FETCH. Jednom kada otvorite kursor, birate redove iz njega jednu po jednu dok se sve ne iscrpe. Međutim, i dalje možete izdati naredbu FETCH nakon ovoga. Začudo, PL/SQL ne stvara izuzetak u ovom slučaju. On jednostavno ne radi ništa. Pošto nema šta drugo da se izabere, vrednosti varijabli u INTO sekciji naredbe FETCH se ne menjaju. Drugim riječima, naredba FETCH ne postavlja ove varijable na NULL. Izraz SELECT u deklaraciji kursora specificira listu stupaca koje vraća. Uz nazive stupaca tablice, ova lista može sadržavati izraze koji se nazivaju izračunate ili virtualne kolone. Alias stupca je alternativno ime navedeno u naredbi SELECT za stupac ili izraz. Definiranjem odgovarajućih aliasa u SQL*Plusu, možete prikazati rezultate proizvoljnog upita u ljudskom čitljivom obliku. U ovim situacijama pseudonimi nisu potrebni. S druge strane, kada se koriste eksplicitni kursori, izračunati aliasi stupaca su potrebni u sljedećim slučajevima: Razmotrite sljedeći upit. Komanda SELECT bira nazive svih kompanija koje su naručile robu tokom 2001. godine, kao i ukupan iznos narudžbi (pod pretpostavkom da je zadana maska formatiranja za trenutnu instancu baze podataka DD-MON-YYYY): SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id I i.invoice_date IZMEĐU "01-JAN-2001" I "31-DEC-2001"; Izvođenje ove naredbe u SQL*Plus-u će proizvesti sljedeći izlaz: Kao što vidite, zaglavlje kolone SUM (INV_AMT) nije pogodno za izvještaj, ali je dobro za jednostavno pregledavanje podataka. Sada pokrenimo isti upit u PL/SQL programu koristeći eksplicitni kursor i dodajmo pseudonim kolone: DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales IZ kompanije C, faktura I GDJE C.company_id = I.company_id I I.invoice_date IZMEĐU "01-JAN-2001" I "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; END; Bez aliasa, neću moći referencirati stupac u strukturi zapisa comp_rec. Ako imate pseudonim, možete raditi sa izračunatom kolonom baš kao i sa bilo kojom drugom kolonom upita: IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE (" Prekoračili ste kreditno ograničenje od $5000 za " || TO_CHAR (comp_rec.total_sales - 5000, "$9999")); ENDIF; Prilikom odabira reda u zapisu deklariranom atributom %ROWTYPE, izračunatoj koloni se može pristupiti samo po imenu - jer je struktura zapisa određena strukturom samog kursora. Nekada davno u djetinjstvu su nas učili da čistimo za sobom i ta navika je ostala u nama (iako ne kod svih) do kraja života. Ispostavilo se da ovo pravilo igra izuzetno važnu ulogu u programiranju, a posebno kada je u pitanju upravljanje kursorima. Nikada ne zaboravite zatvoriti kursor kada vam više ne treba! Sintaksa naredbe CLOSE: CLOSE cursor_name; Ispod su neki važni savjeti i razmatranja u vezi sa zatvaranjem eksplicitnih kursora. Oracle podržava četiri atributa (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) za dobijanje informacija o stanju eksplicitnog kursora. Referenca atributa ima sljedeću sintaksu: kursor%attribute Ovdje je kursor ime deklariranog kursora. Vrijednosti koje vraćaju eksplicitni atributi kursora prikazane su u tabeli. 1. Tabela 1. Eksplicitni atributi kursora Vrijednosti atributa kursora prije i nakon izvođenja različitih operacija s njima prikazane su u tabeli. 2. Kada radite s eksplicitnim atributima kursora, uzmite u obzir sljedeće: Tabela 2. Vrijednosti atributa kursora Upotreba svih ovih atributa prikazana je u sljedećem primjeru: Prethodni blogovi su u više navrata davali primjere korištenja parametara procedura i funkcija. Parametri su sredstvo za prosljeđivanje informacija u programski modul i iz njega. Kada se pravilno koriste, čine module korisnijim i fleksibilnijim. PL/SQL vam omogućava da proslijedite parametre kursorima. Obavljaju iste funkcije kao parametri softverskih modula, kao i nekoliko dodatnih. Broj parametara kursora je neograničen. Kada se pozove OPEN, svi parametri (osim onih koji imaju zadane vrijednosti) moraju biti specificirani za kursor. Kada kursor zahtijeva parametre? Opće pravilo ovdje je isto kao i za procedure i funkcije: ako se očekuje da će se kursor koristiti na različitim mjestima i s različitim vrijednostima u odjeljku WHERE, za njega treba definirati parametar. Uporedimo kursore sa i bez parametra. Primjer kursora bez parametara: CURSOR joke_cur IS SELECT ime, kategoriju, last_used_date FROM Jokes; Skup rezultata kursora uključuje sve unose u tabeli šala. Ako nam je potreban samo određeni podskup redova, odjeljak WHERE je uključen u upit: CURSOR joke_cur IS SELECT ime, kategoriju, last_used_date FROM jokes WHERE kategorija = "MUŽ"; Za izvođenje ovog zadatka nismo koristili parametre i nisu potrebni. U ovom slučaju, kursor vraća sve redove koji pripadaju određenoj kategoriji. Ali šta ako se kategorija promijeni svaki put kada pristupite ovom kursoru? Naravno, ne bismo definisali poseban kursor za svaku kategoriju—to bi bilo potpuno neskladno sa načinom na koji razvoj aplikacija zasnovan na podacima funkcioniše. Potreban nam je samo jedan kursor, ali jedan za koji bismo mogli promijeniti kategoriju - a on bi i dalje vraćao tražene informacije. A najbolje (iako ne i jedino) rješenje za ovaj problem je definiranje parametriziranog kursora: PROCEDURE objasni_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Kursor sa listom parametara koji se sastoji od || iz jednog parametra niza. */ CURSOR joke_cur (category_in IN VARCHAR2) JE SELECT ime, kategoriju, last_used_date FROM Joke WHERE kategorija = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Sada, prilikom otvaranja kursora, argument mu se prosljeđuje */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec; Između naziva kursora i ključne riječi IS sada se nalazi lista parametara. Čvrsto kodirana vrijednost HUSBAND u klauzuli WHERE zamijenjena je referencom na parametar UPPER (kategorija_in). Kada otvorite kursor, možete postaviti vrijednost na MUŽ , muž ili MužAnD - kursor će i dalje raditi. Ime kategorije za koju kursor treba da vrati redove tabele šala je navedeno u naredbi OPEN (u zagradama) kao literal, konstanta ili izraz. Kada se kursor otvori, naredba SELECT se analizira i parametar je pridružen vrijednosti. Zatim se određuje rezultujući skup redova i kursor je spreman za dohvaćanje. Može se otvoriti novi kursor koji označava bilo koju kategoriju: OPEN joke_cur(Jokes_pkg.category); OPEN joke_cur("muž"); OPEN joke_cur("političar"); OPEN joke_cur (Jokes_pkg.relation || "-IN-LAW"); Parametri kursora se najčešće koriste u klauzuli WHERE, ali se mogu referencirati na drugom mjestu u SELECT izrazu: DECLARE CURSOR joke_cur (category_in IN ARCHAR2) JE SELECT ime, category_in, last_used_date FROM joke WHERE kategorija = UPPER (category_in); Umjesto da čitamo kategoriju iz tabele, jednostavno zamjenjujemo parametar category_in u listu odabira. Rezultat ostaje isti jer klauzula WHERE ograničava kategoriju uzorka na vrijednost parametra. Opseg parametra kursora je ograničen na taj kursor. Parametar kursora se ne može referencirati izvan naredbe SELECT pridružene kursoru. Sljedeći PL/SQL isječak se ne kompajlira jer ime_programa nije lokalna varijabla u bloku. Ovo je formalni parametar kursora koji je definiran samo unutar kursora: DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM tales_from_the_crypt WHERE ime_programa = ime_programa; BEGIN program_name:= "MUMIJA KOJA DIŠE"; /* Nevažeći link */ OPEN scariness_cur (program_name); .... CLOSE scariness_cur; END; Sintaksa za parametre kursora je vrlo slična onoj za procedure i funkcije - osim što parametri kursora mogu biti samo IN parametri. Parametri kursora se ne mogu postaviti na OUT ili IN OUT modove. Ovi načini omogućavaju prosljeđivanje vrijednosti i vraćanje iz procedura, što nema smisla za kursor. Postoji samo jedan način da dobijete informacije iz kursora: dohvaćanje zapisa i kopiranje vrijednosti sa liste kolona u odjeljku INTO Parametrima kursora se mogu dodijeliti zadane vrijednosti. Primjer kursora sa zadanom vrijednošću parametra: CURSOR emp_cur (emp_id_in BROJ:= 0) IS SELECT employee_id, emp_name FROM zaposlenog WHERE employee_id = emp_id_in; Budući da parametar emp_id_in ima zadanu vrijednost, može se izostaviti u naredbi FETCH. U tom slučaju, kursor će vratiti informaciju o zaposleniku sa kodom 0. U svom T-SQL kodu uvijek koristim operacije zasnovane na skupovima. Rečeno mi je da su ove vrste operacija ono što je SQL Server dizajniran za obradu i da bi trebao biti brži od serijske obrade. Znam da kursori postoje, ali nisam siguran kako da ih koristim. Možete li dati neke primjere kursora? Možete li dati bilo kakve smjernice o tome kada koristiti kursore? Pretpostavljam da ih je Microsoft uključio u SQL Server s razlogom tako da moraju imati mjesto gdje se mogu koristiti na efikasan način. U nekim krugovima kursori se nikada ne koriste, u drugima su krajnje sredstvo, au drugim grupama se koriste redovno. U svakom od ovih kampova imaju različite razloge za korištenje kursora. imaju svoje mjesto u određenim okolnostima, a ne u drugim. Dakle, sve se svodi na vaše razumijevanje tehnike kodiranja, a zatim na vaše razumijevanje problema koji je pri ruci da donesete odluku o tome da li je obrada zasnovana na kursoru prikladna ili ne. poceli da uradimo sledece: Kreiranje kursora SQL Servera je konzistentan proces, tako da kada naučite korake, lako ćete ih moći duplicirati različitim setovima logike za petlju kroz podatke. Prođimo kroz korake: Odavde, pogledajte primjere u nastavku da biste počeli da znate kada koristiti SQL Server kursore i kako to učiniti. Evo primjera kursora iz savjeta Jednostavna skripta za izradu sigurnosnih kopija svih SQL Server baza podataka gdje se sigurnosne kopije izdaju na serijski način: DECLARE @name VARCHAR(50) -- ime baze podataka DECLARE @path VARCHAR(256) -- putanja za rezervne datoteke DECLARE @fileName VARCHAR(256) -- ime datoteke za rezervnu kopiju DECLARE @fileDate VARCHAR(20) -- koristi se za naziv datoteke SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DEKLIRATI db_cursor KURSOR ZA SELECT ime IZ MASTER.dbo.sysdatabases GDJE ime NIJE IN ("master","model ","msdb","tempdb") OTVORI db_cursor DOWN DALJE IZ db_cursor INTO @name DOK @@FETCH_STATUS = 0 POČNI POSTAVITI @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @ ime NA DISK = @fileName DOVEDI NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor Na osnovu gornjeg primjera, kursori uključuju ove komponente: Saznajte više o SQL Server kursorima i alternativama: U primjeru iznad, sigurnosne kopije se izdaju putem kursora, pogledajte ove druge savjete koji koriste logiku zasnovanu na pokazivaču: Analiza u nastavku služi kao uvid u različite scenarije u kojima logika zasnovana na kursoru može, ali ne mora biti korisna: 1) Koncept kursora 2) Deklaracija kursora DECLARE [{}] [[NO] SCROLL] CURSOR [{SA|BEZ} HOLD] ZA [ZA {SAMO ČITANJE|AŽURIRANJE [OF ]}] 3) Ključne riječi 4) Deklarisanje kursora u SQL Serveru DECLARE CURSOR [LOKALNO|GLOBALNO] [FORWARD_ONLY|SCROLL] [STATIČKI|SET TIPOVA|DINAMIČKI|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIČNO] ZA [ZA AŽURIRANJE [OF ]] . STATIC– Definira kursor koji kreira privremenu kopiju podataka za korištenje od strane kursora. Svi upiti prema kursoru pristupaju navedenoj privremenoj tablici u bazi podataka tempdb, tako da promjene u osnovnim tabelama ne utiču na podatke koje vraćaju uzorci za taj kursor, a sam kursor ne dozvoljava unošenje promjena. 5) Otvaranje kursora 6) Dohvaćanje redova iz kursora FETCH [{SLJEDEĆA|PRIJAVA|PRVA|POSLJEDNJA|{APSOLUTNO|RELATIVNO }}] 7) Opcije pozicioniranja kursora Bilješka: SQL Server dozvoljava cjelobrojnu varijablu @N umjesto N. 8) Zatvaranje kursora 9) Napomene o kursorima 10) Primjer za brisanje podataka iz kursora exec sql deklarirati kursor Cur1 za odabir * od Kupca 11) Primjer povećanja provizija exec sql deklarirati kursor CurCust za odabir * iz SalesPeople SELECT S.Name, MAX(S.City) AS Grad, SUM(O.Amt) KAO Iznos OD SalesPeople S INNER JOIN Narudžbe O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2 DECLARE Cur1 SCROLL CURSOR ZA ODABIR S.Name, MAX(S.City) KAO Grad, SUM(O.Amt) KAO Iznos OD SalesPeople S INNER JOIN Narudžbe O ON S.SNum=O.SNum GRUPA PO S.Name ORDER BY 2
Deklarisanje eksplicitnog kursora
CURSOR company_cur IS SELECT company_id FROM company;
CURSOR name_cur (company_id_in IN BROJ) JE SELECT name FROM company WHERE company_id = company_id_in;
CURSOR emp_cur RETURN zaposlenih%ROWTYPE JE SELECT * FROM zaposlenih WHERE department_id = 10; Ime kursora
Deklarisanje kursora u paketu
Otvaranje eksplicitnog kursora
Dohvaćanje podataka iz eksplicitnog kursora
Primjeri eksplicitnih kursora
DECLARE CURSOR company_cur je SELECT ...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec;
DOBITI new_balance_cur INTO new_balance_dollars;
DOVEZI emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary; Uzorkovanje nakon obrade posljednjeg reda
Eksplicitni alijasi stupaca kursora
IME KOMPANIJE
SUM (INV_AMT)
ACME TURBO INC.
1000
WASHINGTON HAIR CO.
25.20
Zatvaranje eksplicitnog kursora
POČNI OTVARATI my_package.my_cursor; ... Rad sa kursorom CLOSE my_package.my_cursor; IZUZETAK KADA DRUGI ONDA IF mypackage.my_cursor%ISOPEN THEN CLOSE my_package.my_cursor; ENDIF; END;
IF company_cur%ISOPEN THEN CLOSE company_cur; ENDIF; Eksplicitni atributi kursora
Operacija
%PRONAĐEN
%NIJE PRONAĐENO
%ISOPEN
%ROWCOUNT
Prije OPEN
Izuzetak
ORA-01001Izuzetak
ORA-01001FALSE
Izuzetak
ORA-01001Nakon OPEN
NULL
NULL
ISTINITO
0
Prije prvog FETCH uzorka
NULL
NULL
ISTINITO
0
Nakon prvog uzorka
FETCHISTINITO
FALSE
ISTINITO
1
Prije naknadnog
FETCHISTINITO
FALSE
ISTINITO
1
Nakon naknadnog FETCH
ISTINITO
FALSE
ISTINITO
Zavisi od podataka
Prije posljednjeg FETCH uzorka
ISTINITO
FALSE
ISTINITO
Zavisi od podataka
Nakon posljednjeg FETCH uzorka
ISTINITO
FALSE
ISTINITO
Zavisi od podataka
Prije ZATVARATI
FALSE
ISTINITO
ISTINITO
Zavisi od podataka
Nakon CLOSE
Izuzetak
Izuzetak
FALSE
Izuzetak
Kursori sa parametrima
Otvaranje kursora sa opcijama
Opseg parametra kursora
Režimi parametara kursora
Zadane vrijednosti parametara
Rješenje
Kako kreirati SQL Server kursor
Primjer SQL Server kursora
Komponente kursora SQL Servera
Preporučeno čitanje
Dodatni primjeri SQL Server kursora
Analiza kursora SQL Servera
Sljedeći koraci
Interaktivni SQL ne pravi razliku između jednorednih i višerednih upita. Ugrađeni SQL izvršava ove upite drugačije. Jednoredni upiti vraćaju jednu liniju i već smo ih pokrili. Kada je rezultat upita više od jednog reda, ugrađeni SQL mora dozvoliti aplikaciji da dohvati rezultate upita red po red. Za to se koriste kursori. Kursor je varijabla povezana s upitom. Njegova vrijednost je svaki red koji odgovara upitu. Kao i varijable, kursori moraju biti deklarisani prije nego što se mogu koristiti. Za razliku od pogleda, kursori su dizajnirani za obradu red po red.
. OSJETLJIVO|NEOSJETLJIVO|OSJETLJIVO– vidljive su promjene u skupu rezultata | zabranjeno (popravljeno upotrebom kopije skupa podataka)|DBMS sam odlučuje hoće li napraviti kopiju (radi po defaultu).
. SA|BEZ ČEKANJA– ostavlja otvoreno | zatvara kursor ako se naiđe na COMMIT izraz.
. SCROLL– [spriječava] izdvajanje redova rezultata po slučajnom redoslijedu.
. SAMO ZA ČITANJE– definira kursor samo za čitanje.
. ZA AŽURIRANJE– blokira ažuriranje samo navedenih kolona.
. KEYSET– Označava da se članstvo ili redoslijed redova u kursoru ne mijenja nakon što se otvori. Skup ključeva koji jedinstveno identificiraju redove ugrađen je u tablicu u bazi podataka tempdb koja se zove set ključeva.
. DYNAMIC– Definira kursor koji prikazuje sve promjene podataka napravljene u redovima skupa rezultata prilikom pregleda ovog kursora. Vrijednosti podataka, redoslijed i članstvo u redovima u svakom odabiru mogu varirati. Opcija APSOLUTNOG odabira nije podržana od strane dinamičkih kursora.
. BRZO NAPRIJED– Označava kursor FORWARD_ONLY, READ_ONLY za koji je omogućena optimizacija performansi. Opcija FAST_FORWARD se ne može specificirati sa opcijama SCROLL ili FOR_UPDATE.
. SCROLL_LOCKS– Označava da će pozicionirana ažuriranja ili brisanja putem kursora zajamčeno uspjeti. SQL Server zaključava redove dok se čitaju u kursor kako bi se osiguralo da su dostupni za naredne promjene. Opcija SCROLL_LOCKS se ne može specificirati s opcijom FAST_FORWARD ili STATIC.
. OPTIMISTIČNO– Označava da pozicionirana ažuriranja ili brisanja izvršena kroz kursor neće uspjeti ako je red ažuriran od trenutka kada je učitan u kursor. SQL Server ne zaključava redove dok se čitaju u kursor. Umjesto toga, pravi se poređenje vrijednosti kolone vremenske oznake (ili kontrolnih suma ako tabela nema kolonu vremenske oznake) kako bi se utvrdilo da li se red promijenio otkako je pročitan u kursor. Ako je red izmijenjen, njegova pozicionirana izmjena ili brisanje nije moguća. Opcija OPTIMISTIC se ne može specificirati s opcijom FAST_FORWARD.
OD INTO
. SLJEDEĆA|PRIJAVA|PRVA|POSLJEDNJA– na sljedeći|prethodni|prvi|poslednji red skupa rezultata.
. RELATIVNO ±N– na liniju sa pozitivnim ili negativnim pomakom u odnosu na trenutnu liniju.
. APSOLUTNO ±N– na red s eksplicitno specificiranim apsolutnim brojem pozicije od početka ili kraja kursora.
. Ako kursor sadrži više od jedne linije, potrebno je organizirati petlju za dohvaćanje podataka iz njega, periodično provjeravajući da dođe do posljednjeg reda.
. Za razliku od tabela i pogleda, redovi kursora su poredani ili eksplicitno pomoću sekcije POREDAK PO, ili u skladu sa konvencijama usvojenim u određenom DBMS-u.
. Kursori se također koriste za odabir grupa redova iz tabela, koje se mogu ažurirati ili brisati jedan po jedan.
. Da bi kursor mogao da se ažurira, mora da ispunjava iste kriterijume kao i pogled, odnosno da ne sadrži sekcije UNION, RED PO, GRUPA PO, DISTINCT.
gdje Ocjena
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete iz Customer
gdje je struja Cur1; ) – Uzmite podatke za brisanje iz kursora
nije urađeno:
exec sql zatvori kursor Cur1; — Zatvorite kursor
Izlaz();
gdje je SNum u (odaberite SNum od Kupca gdje je Ocjena=300); — Definišite kursor
exec sql otvori kursor CurCust; - Izvršite kursor
while (sqlca.sqlcode==0) ( — Kreirajte petlju za ažuriranje podataka u tabeli
exec sql dohvati CurCust u:Id_num, :SalesPerson, :Loc, :Comm;
exec sql ažuriranje SalesPeople postavlja Comm=Comm+.01 gdje je trenutno
of CurCust; ) – Uzmite podatke za ažuriranje sa kursora
exec sql zatvori kursor CurCust; — Zatvorite kursor
OPEN Cur1
PREUZMI SLJEDEĆE IZ Cur1
WHILE @@FETCH_STATUS=0
POČNI
PREUZMI SLJEDEĆE IZ Cur1
KRAJ
CLOSE Cur1
DEALLOCATE Cur1
