3. Programiranje korišćenjem kursora

Ako pronađete grešku ili propust, molimo Vas da nam skrenete pažnju otvaranjem primedbe na zvaničnom GitHub repozitorijumu.

Do sada su rezultati naših SQL upita bili kardinalnosti 1. Zbog toga smo mogli da koristimo jednostavnu SELECT INTO naredbu za dohvatanje informacija iz rezultata upita. Ukoliko smo sigurni da će rezultat upita biti jedan red, ovakav način programiranja je prihvatljiv. Međutim, ukoliko znamo da se rezultat može sastojati od više redova, potreban nam je drugačiji pristup.

U slučaju da nam je nepoznat broj redova u rezultatu upita, za dohvatanje rezultata možemo iskoristiti mehanizam zasnovan na kursorima. Kursorima je moguće procesirati svaki red rezultata, bez obzira na to koliko redova rezultat sadrži. Kursor je imenovana kontrolna struktura koja se koristi od strane aplikativnog programa da “pokazuje” na specifičan red u okviru uređenog skupa redova.

3.1 Rad sa kursorima

Rad sa kursorima se najčešće može opisati kroz naredna četiri koraka:

Deklaracija kursora
Otvaranje kursora
Iteriranje kroz kursor
Zatvaranje kursora

3.1.1. Deklaracija kursora

Deklaracija kursora se izvodi navođenjem SQL naredbe DECLARE čija je sintaksa data u nastavku:

DECLARE <IME_KURSORA>
CURSOR FOR <UPIT>
[(FOR READ ONLY)|(FOR UPDATE OF <LISTA_ATRIBUTA>)]
[(WITHOUT HOLD)|(WITH HOLD)]

Promenljiva <IME_KURSORA> mora biti jedinstvena u programu. Vrednost <UPIT> predstavlja upit, tj. naredbu SELECT za koji se kursor vezuje. Upit ne može da sadrži parametarske oznake, ali može sadržati matične promenljive, s tim da deklaracije matičnih promenljivih koje se koriste u upitu moraju biti pre deklaracije kursora.

Ukoliko navedemo klauzu FOR READ ONLY, time definišemo kursor koji služi samo za čitanje podataka. Ukoliko želimo da se podaci menjaju pomoću kursora, tada se nakon <UPIT> navodi klauza FOR UPDATE OF za kojom sledi lista imena kolona u rezultatu upita koji se mogu menjati kursorom, odvojeni zapetama.

Postoje četiri tipa kursora:

Čitajući, koji podržava samo operaciju čitanja podataka. Ovim kursorom nije moguće vršiti operacije brisanja ili ažuriranja.
Brišući, koji podržava operacije čitanja i brisanja podataka. Ovim kursorom nije moguće vršiti operaciju ažuriranja.
Ažurirajući, koji podržava sve tri operacije nad podacima.
Dvosmisleni, ukoliko Db2 nije u stanju da na osnovu upita zaključi da li će se kursor koristiti za čitanje ili menjanje podataka.

U nastavku slede njihove definicije:

Za kursor kažemo da je brišući ako je svaki od narednih uslova ispunjen:
- Klauza FROM u glavnom delu <UPIT> sadrži tačno jednu tabelu ili pogled nad jednom tabelom koji služi za brisanje.
- <UPIT> ne sadrži neku od klauza DISTINCT ili VALUES.
- <UPIT> ne sadrži ORDER BY i klauzu FOR UPDATE OF (zajedno).
- <UPIT> ne sadrži agregatne funkcije ili neku od klauza GROUP BY ili HAVING.
- <UPIT> ne sadrži neki skupovni operator, kao što su UNION, INTERSECT ili EXCEPT sa izuzetkom UNION ALL.
- <UPIT> ne sadrži klauzu FOR READ ONLY.
Za kursor kažemo da je čitajući ukoliko nije brišući.
Za kursor kažemo da je ažurirajući ukoliko je brišući i kolone koje su proglašene za ažuriranje u klauzi FOR UPDATE OF predstavljaju neke od kolona u baznoj tabeli.
Za kursor kažemo da je dvosmisleni ukoliko je upit dinamički pripremljen i nije navedena nijedna od klauza FOR READ ONLY ili FOR UPDATE OF i kursor zadovoljava uslove brišućeg kursora.

Navođenjem neke od opcionih klauza WITHOUT HOLD ili WITH HOLD možemo specifikovati da li će se kursor zatvarati ili ne kao posledica operacije pohranjivanja (engl. commit). Navođenjem klauze WITHOUT HOLD kursor se ne sprečava da bude zatvoren, što je podrazumevano ponašanje. Navođenjem klauze WITH HOLD kursor održava resurse kroz različite jedinice posla. Kada budemo diskutovali o transakcijama, definisaćemo preciznije ovo ponašanje.

3.1.2. Otvaranje kursora

Otvaranje kursora se izvodi navođenjem SQL naredbe OPEN čija je sintaksa data u nastavku:

OPEN <IME_KURSORA>
[USING <LISTA_MATICNIH_PROMENLJIVIH>]

Naredbom OPEN se vrši otvaranje kursora i njegovo izvršavanje, zarad dohvatanja redova iz rezultujuće tabele. U trenutku otvaranja kursora, upit koji je naveden prilikom deklaracije kursora se izvršava. Ukoliko je taj upit sadržao matične promenljive, onda se pretpostavlja da je programer postavio vrednosti tih matičnih promenljivih pre otvaranja kursora. Drugim rečima, vrednosti tih matičnih promenljivih će biti iskorišćene tek prilikom otvaranja kursora.

Promenljiva <IME_KURSORA> mora biti deklarisana naredbom DECLARE pre samog otvaranja kursora. Kada se izvrši naredba OPEN, kursor naziva <IME_KURSORA> mora biti u zatvorenom stanju (bilo da je eksplicitno zatvoren ili da je samo deklarisan pre otvaranja).

Ukoliko se kursor otvara za pripremljenu (dinamičku) SQL naredbu, navođenjem klauze USING možemo navesti vrednosti koje se koriste za zamenu parametarskih oznaka.

Nakon otvaranja kursor je pozicioniran ispred prvog reda rezultujuće tabele.

3.1.3. Iteriranje kroz kursor

Iteriranje kroz kursor se izvodi navođenjem SQL naredbe FETCH čija je sintaksa data u nastavku:

FETCH <IME_KURSORA>
INTO <LISTA_MATICNIH_PROMENLJIVIH>

Naredbom FETCH se vrši pozicioniranje kursora na naredni red iz rezultujuće tabele i dodeljuju se vrednosti iz tog reda ciljanim promenljivama. Promenljiva <IME_KURSORA> mora biti deklarisana naredbom DECLARE pre samog dohvatanja podataka. Dodatno, da bi se izvršila naredba FETCH, kursor naziva <IME_KURSORA> mora biti u otvorenom stanju.

Klauzom INTO se prva vrednost dohvaćenog reda smešta u prvu promenljivu koja je navedena u <LISTA_MATICNIH_PROMENLJIVIH>, druga vrednost reda u drugu promenljivu, itd. Ako dođe do greške pri bilo kojoj dodeli vrednosti, ta vrednost se ne dodeljuje promenljivoj, kao ni bilo koja vrednost nakon nje. Sve do tada dodeljene vrednosti ostaju dodeljene.

Otvoreni kursor ima tri moguće pozicije:

Može biti pozicioniran ispred prvog reda.
Može biti pozicioniran na nekom redu.
Može biti pozicioniran nakon poslednjeg reda.

Kursor može biti samo pozicioniran na nekom redu isključivo primenom naredbe FETCH. Ako se kursor pozicionira na poslednjem redu rezultujuće tabele ili iza njega, izvršavanje naredbe FETCH ima naredne efekte:

Vrednost koda za dijagnostiku greške (u našim programima, vrednost u koju se razmota makro SQLCODE) postavlja se na vrednost +100.
Kursor se pozicionira nakon poslednjeg reda rezultata.
Vrednosti se ne dodeljuju matičnim promenljivama.

Ako je kursor pozicioniran ispred prvog reda, izvršavanjem naredbe FETCH, kursor se pozicionira na prvi red, i vrednosti se dodeljuju matičnim promenljivama klauzom INTO. Ako je kursor pozicioniran na redu koji nije poslednji, izvršavanjem naredbe FETCH, kursor se pozicionira na naredni red i vrednosti tog reda se dodeljuju matičnim promenljivama klauzom INTO.

Ako je kursor pozicioniran na nekom redu, taj red se naziva tekući red kursora. Kursor na koji se referiše u pozicionirajućim naredbama UPDATE ili DELETE mora biti pozicioniran na nekom redu. Moguće je da se, dolaskom do greške, stanje kursora postavi na nepredvidivo.

3.1.4. Zatvaranje kursora

Zatvaranje kursora se izvodi navođenjem SQL naredbe CLOSE čija je sintaksa data u nastavku:

CLOSE <IME_KURSORA>
[WITH RELEASE]

Naredbom CLOSE se vrši zatvaranje kursora. Ukoliko je rezultujuća tabela kreirana kada je kursor otvoren, ta tabela se uništava. Promenljiva <IME_KURSORA> mora biti deklarisana naredbom DECLARE pre samog dohvatanja podataka. Dodatno, da bi se izvršila naredba CLOSE, kursor naziva <IME_KURSORA> mora biti u otvorenom stanju.

Ukoliko se navede opciona klauza WITH RELEASE, prilikom zatvaranja kursora se pokušava sa oslobađanjem svih katanaca koji su držani od strane kursora. S obzirom da se katanci mogu držati drugim operacijama ili procesima, ne znači da će biti nužno i oslobođeni zatvaranjem kursora. O katancima će biti više reči u kasnijim poglavljima.

Na kraju jedinice posla, svi kursori koji pripadaju procesu aplikacije i koji su deklarisani bez klauze WITH HOLD se implicitno zatvaraju.

3.2 Korišćenje kursora za čitanje podataka

Sada smo spremni za rešavanje primera koji zahtevaju upotrebu kursora. Počnimo sa narednim zadacima koji ilustruju čitanje rezultata iz kursora.

Zadatak 3.1: Napisati C/SQL program koji ispisuje identifikator, oznaku, naziv, nivo, broj bodova, zvanje i opis za svaki od studijskih programa.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_1.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hStudyProgramId;
char hStudyProgramLabel[11];
char hStudyProgramName[201];
short hDegreeLevelId;
short hESPB;
char hStudyProgramTitle[101];
struct {
   short int length;
   char data[32700];
} hStudyProgramDesc;
short hIndStudyProgramDesc;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    // Konektujemo se na bazu podataka
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    // Deklarisemo kursor koji prolazi tabelom STUDIJSKIPROGRAM.
    // Ovde navodimo upit kroz koji ce kursor prolaziti, ali koji se jos uvek ne izvrsava.
    EXEC SQL 
        DECLARE cStudyPrograms CURSOR FOR 
        SELECT  ID, 
                OZNAKA, 
                NAZIV, 
                IDNIVOA, 
                OBIMESPB,
                ZVANJE,
                OPIS
        FROM    DA.STUDIJSKIPROGRAM;
    checkSQL("Declare cursor cStudyPrograms");
    
    // Otvaramo kursor, cime se izvrsava upit naveden u deklaraciji.
    // Sada se kursor nalazi na redu "pre prvog reda" u rezultatu.
    EXEC SQL OPEN cStudyPrograms;
    checkSQL("Open cursor cStudyPrograms");
    
    printf("+---------------------------------------------------------------------------------------------------------------------------------------+\n");
    printf("|                                                           STUDIJSKI PROGRAM                                                           |\n");
    printf("+-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");
    printf("|ID   |OZNAKA|NAZIV                         |NIVO|OBIM|ZVANJE                        |OPIS                                              |\n");
    printf("|-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");

    // Sve dok ima redova u rezultujucoj tabeli
    for(;;) {
        // Citamo red po red
        EXEC SQL 
            FETCH   cStudyPrograms
            INTO    :hStudyProgramId, 
                    :hStudyProgramLabel, 
                    :hStudyProgramName, 
                    :hDegreeLevelId, 
                    :hESPB,
                    :hStudyProgramTitle,
                    :hStudyProgramDesc :hIndStudyProgramDesc;
        checkSQL("Fetch from cursor cStudyPrograms");

        // Ako smo stigli do kraja rezultujuce tabele, 
        // izlazimo iz petlje
        if (SQLCODE == 100) {
            break;
        }

        // Inace, stampamo red iz rezultata
        printf("|%-5d|%-6s|%-30.30s|%-4hd|%-4hd|%-30.30s|%-50.50s|\n", 
               hStudyProgramId, hStudyProgramLabel, hStudyProgramName, hDegreeLevelId, hESPB, 
               hStudyProgramTitle, (hIndStudyProgramDesc < 0) ? "NULL" : hStudyProgramDesc.data);
        printf("|-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");
    }

    // Zatvaramo kursor
    EXEC SQL CLOSE cStudyPrograms;
    checkSQL("Close cursor cStudyPrograms");

    // Raskidamo konekciju sa bazom podataka
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

Zadatak 3.2: Napisati C/SQL program kojim se za uneti broj indeksa studenta ispisuju podaci (naziv predmeta, datum polaganja i ocena) za sve ispite koje je on položio. Nakon toga, ispisati prosek ocena tog studenta.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_2.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hIndex;
char hCourseName[151];
char hExamDate[11];
short hIndExamDate;
short hGrade;
short hIndGrade;
double hAverageGrade;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    // Konektujemo se na bazu podataka
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    // Deklarisemo kursor koji za dati broj indeksa nalazi sve polozene ispite
    EXEC SQL 
        DECLARE cPassedExams CURSOR FOR 
        SELECT  NAZIV, 
                DATPOLAGANJA, 
                OCENA 
        FROM    DA.ISPIT I JOIN
                DA.PREDMET P ON I.IDPREDMETA = P.ID
        WHERE   INDEKS = :hIndex AND
                OCENA > 5 AND
                STATUS = 'o';
    checkSQL("Declare cursor");
    
    printf("Unesite broj indeksa studenta:\n");
    scanf("%d", &hIndex);

    // Otvaramo kursor
    EXEC SQL OPEN cPassedExams;
    checkSQL("Open cursor");
    
    printf("+--------------------------------------------------+----------+-----+\n");
    printf("|NAZIV                                             |DATUM     |OCENA|\n");
    printf("+--------------------------------------------------+----------+-----+\n");
    
    for(;;) {
        // Smestamo naredni red rezultata upita u odgovarajuce maticne promenljive
        EXEC SQL 
            FETCH   cPassedExams
            INTO    :hCourseName, 
                    :hExamDate :hIndExamDate, 
                    :hGrade :hIndGrade;
        checkSQL("Fetch cursor");

        // Ako smo stigli do kraja kursora, izlazimo iz petlje
        if (SQLCODE == 100) {
            break;
        }

        // Inace, stampamo naredni ispit
        printf("|%-50.50s|%10s|%5hd|\n", 
               hCourseName, (hIndExamDate > 0) ? hExamDate : "NULL", (hIndGrade > 0) ? hGrade : -1);
    }

    printf("+--------------------------------------------------+----------+-----+\n");
    
    // Zatvaramo kursor
    EXEC SQL CLOSE cPassedExams;
    checkSQL("Close cursor");

    // Za datog studenta racunamo prosecnu ocenu
    EXEC SQL 
        SELECT  COALESCE(AVG(OCENA + 0.0), 5.0) 
        INTO    :hAverageGrade 
        FROM    DA.ISPIT
        WHERE   INDEKS = :hIndex AND
                OCENA > 5 AND
                STATUS = 'o';
    checkSQL("Select into");
    
    printf("Prosecna ocena je: %.2f.\n", hAverageGrade);

    // Raskidamo konekciju sa bazom podataka
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

3.3 Korišćenje kursora za ažuriranje i brisanje podataka

Kao što smo napomenuli, moguće je korišćenje kursora za ažuriranje ili brisanje redova iz tabela. U slučaju ažuriranja redova, potrebno je deklarisati koje kolone se mogu menjati pri deklaraciji kursora, a zatim koristiti UPDATE naredbu oblika:

UPDATE  <TABELA>
SET     <KOLONA_1> = <VREDNOST_1>,
        -- ...
        <KOLONA_N> = <VREDNOST_N>
WHERE   CURRENT OF <IME_KURSORA>

SQL naredba UPDATE koja ažurira podatke u tabeli na osnovu pozicije nekog kursora naziva se pozicionirajuća UPDATE naredba.

Očigledno, kolone <KOLONA_1>, ..., <KOLONA_N> moraju biti deklarisane u FOR UPDATE OF klauzi pri deklaraciji kursora naziva <IME_KURSORA>. Takođe, tabela <TABELA> mora biti jedina tabela koja se nalazi u FROM klauzi kursora. Na ovaj način će upotrebom opisane naredbe UPDATE biti ažuriran tekući red kursora.

Zadatak 3.3: Napisati C/SQL program kojim se za svaki od studijskih programa korisniku postavlja pitanje da li želi da uveća broj bodova za 10. Ako je odgovor potvrdan, vrši se odgovarajuća promena.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_3.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hStudyProgramId;
char hStudyProgramLabel[11];
char hStudyProgramName[201];
short hDegreeLevelId;
short hESPB;
char hStudyProgramTitle[101];
struct {
   short int length;
   char data[32700];
} hStudyProgramDesc;
short hIndStudyProgramDesc;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    // Konektujemo se na bazu podataka
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    // Deklarisemo kursor koji prolazi tabelom STUDIJSKIPROGRAM.
    // Primetite da upit zadovoljava ogranicenje za koriscenje pozicionirajuce UPDATE naredbe.
    EXEC SQL 
        DECLARE cStudyPrograms CURSOR FOR 
        SELECT  ID, 
                OZNAKA, 
                NAZIV, 
                IDNIVOA, 
                OBIMESPB,
                ZVANJE,
                OPIS
        FROM    DA.STUDIJSKIPROGRAM
        FOR     UPDATE OF OBIMESPB; // Na ovaj nacin najavljujemo SUBP
                                    // da cemo vrsiti promenu kolone "OBIMESPB"
    checkSQL("Declare cursor cStudyPrograms");
    
    EXEC SQL OPEN cStudyPrograms;
    checkSQL("Open cursor cStudyPrograms");
    
    printf("+---------------------------------------------------------------------------------------------------------------------------------------+\n");
    printf("|                                                           STUDIJSKI PROGRAM                                                           |\n");
    printf("+-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");
    printf("|ID   |OZNAKA|NAZIV                         |NIVO|OBIM|ZVANJE                        |OPIS                                              |\n");
    printf("|-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");

    for(;;) {
        EXEC SQL 
            FETCH   cStudyPrograms
            INTO    :hStudyProgramId, 
                    :hStudyProgramLabel, 
                    :hStudyProgramName, 
                    :hDegreeLevelId, 
                    :hESPB,
                    :hStudyProgramTitle,
                    :hStudyProgramDesc :hIndStudyProgramDesc;
        checkSQL("Fetch from cursor cStudyPrograms");

        if (SQLCODE == 100) {
            break;
        }

        printf("|%-5d|%-6s|%-30.30s|%-4hd|%-4hd|%-30.30s|%-50.50s|\n", 
               hStudyProgramId, hStudyProgramLabel, hStudyProgramName, hDegreeLevelId, hESPB, hStudyProgramTitle, 
               (hIndStudyProgramDesc > 0) ? hStudyProgramDesc.data : "NULL");
        printf("|-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");
        
        // Sada mozemo da iskoristimo cinjenicu da se nalazimo na redu u kursoru za studijski program 
        // cije smo podatke ispisali iznad, pa mozemo da iskoristimo pozicionirajucu UPDATE naredbu.
        // Naravno, ovo radimo samo ako korisnik potvrdi da zeli da izmeni tekuci red.
        printf("\tDa li zelite da povecate broj ESPB za 10? [d/n] ");
        char userResponse;
        scanf("%c", &userResponse);
        getchar(); // Da bismo procitali i znak za novi red
        
        // Ukoliko korisnik zeli izmenu...
        if (userResponse == 'd') {
            // Izvrsavamo pozicionirajucu UPDATE naredbu
            EXEC SQL 
                UPDATE  DA.STUDIJSKIPROGRAM
                SET     OBIMESPB = OBIMESPB + 10
                WHERE   CURRENT OF cStudyPrograms; // Na ovaj nacin kazemo da zelimo izmenu 
                                                   // nad trenutnim redom u kursoru cStudyPrograms
            checkSQL("Update");
            
            // Dohvatamo azuriranu vrednost iz BP
            EXEC SQL 
                SELECT  OBIMESPB
                INTO    :hESPB
                FROM    DA.STUDIJSKIPROGRAM
                WHERE   ID = :hStudyProgramId;
            checkSQL("Select into");
            
            printf("\tBroj bodova je sada %hd\n", hESPB);
            printf("|-----+------+------------------------------+----+----+------------------------------+--------------------------------------------------+\n");
        }
    }

    EXEC SQL CLOSE cStudyPrograms;
    checkSQL("Close cursor cStudyPrograms");

    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

U slučaju brisanja redova pomoću kursora, nije potrebno deklarisati koje kolone se mogu brisati kao što je to bio slučaj sa ažuriranjem. Potrebno je samo da tabela <TABELA> bude jedina tabela koja se nalazi u FROM klauzi upita kursora naziva <IME_KURSORA>, da bi se mogla koristiti DELETE naredba oblika:

DELETE 
FROM    <IME_TABELE>
WHERE   CURRENT OF <IME_KURSORA>

SQL naredba DELETE koja ažurira podatke u tabeli na osnovu pozicije nekog kursora naziva se pozicionirajuća DELETE naredba.

Zadatak 3.4: Napisati C/SQL program kojim se za sve studente studijskog programa Informatika briše prvi položen ispit (ukoliko ima položenih ispita tog studenta). Za svako obrisano polaganje ispisati: indeks, ime i prezime studenta, datum polaganja i naziv predmeta.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_4.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hIndex;
char hName[51];
char hSurname[51];
char hCourseName[151];
char hExamDate[11];
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    EXEC SQL 
        DECLARE cFirstExams CURSOR FOR 
        WITH POMOCNA AS (
            SELECT      I.INDEKS, 
                        MIN(DATPOLAGANJA) AS DATUMPRVOG
            FROM        DA.ISPIT I JOIN
                        DA.DOSIJE D ON D.INDEKS = I.INDEKS JOIN
                        DA.STUDIJSKIPROGRAM S ON S.ID = D.IDPROGRAMA
            WHERE       S.NAZIV = 'Informatika' AND
                        OCENA > 5 AND
                        STATUS = 'o'
            GROUP BY    I.INDEKS
            HAVING      MIN(DATPOLAGANJA) IS NOT NULL
        )

        SELECT  I.INDEKS,
                (
                    SELECT  IME 
                    FROM    DA.DOSIJE D 
                    WHERE   D.INDEKS = I.INDEKS
                ), 
                (
                    SELECT  PREZIME 
                    FROM    DA.DOSIJE D 
                    WHERE   D.INDEKS = I.INDEKS
                ), 
                (
                    SELECT  NAZIV
                    FROM    DA.PREDMET P
                    WHERE   P.ID = I.IDPREDMETA
                ),
                DATPOLAGANJA
        FROM    DA.ISPIT I
        WHERE   EXISTS (
                    SELECT  *
                    FROM    POMOCNA PO 
                    WHERE   PO.INDEKS = I.INDEKS AND
                            PO.DATUMPRVOG = I.DATPOLAGANJA
                );
    checkSQL("Declare cursor");
    
    EXEC SQL OPEN cFirstExams;
    checkSQL("Open cursor");
    
    // Brojac obrisanih redova
    int i = 0;

    for(;;) {
        EXEC SQL 
            FETCH   cFirstExams
            INTO    :hIndex,
                    :hName, 
                    :hSurname,
                    :hCourseName,
                    :hExamDate;
        checkSQL("Fetch cursor");

        if (SQLCODE == 100) {
            break;
        }

        printf("Brisem polaganje studenta %-15.15s %-15.15s (%d) hExamDatea %s na predmetu %-50.50s\n", hName, hSurname, hIndex, hExamDate, hCourseName);

        // Uvecavamo brojac za jedan
        ++i;

        EXEC SQL 
            DELETE  FROM DA.ISPIT
            WHERE   CURRENT OF cFirstExams; // Na ovaj nacin kazemo da zelimo da obrisemo 
                                            // trenutni red u kursoru cFirstExams
        checkSQL("Delete");
    }

    printf("\n\nObrisali smo %d redova.\n", i);

    EXEC SQL CLOSE cFirstExams;
    checkSQL("Close cursor");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

3.4 Ugnežđeni kursori

Do sada smo videli kako možemo koristiti kursore za upravljanje potencijalno višim brojem redova u rezultatu upita. Obrada ovih redova je do sada obuhvatala jednostavno procesiranje podataka, poput ispisivanja na standardni izlaz, uz eventualne transformacije ili ažuriranje ili brisanje podataka na koje pokazuje kursor.

Međutim, šta raditi ukoliko je potrebno da za svaki red rezultata jednog upita izvršimo akciju nad rezultatom nekog drugog upita? Da li nam kursori u ovakvim situacijama mogu pomoći? Odgovor je potvrdan zbog činjenice da je kursore moguće ugnežđavati. Tipičan tok rada podrazumeva naredne korake u slučaju dva kursora od kojih je jedan (unutrašnji) ugnežđen u drugi (spoljašnji):

Deklaracija spoljašnjeg kursora.
Deklaracija unutrašnjeg kursora.
Otvaranje spoljašnjeg kursora.
Dohvatanje jednog po jednog reda spoljašnjeg kursora. Za svaki dohvaćeni red u spoljašnjem kursoru:
1. Obrada dohvaćenih podataka spoljašnjeg kursora.
2. Otvaranje unutrašnjeg kursora.
3. Dohvatanje jednog po jednog reda unutrašnjeg kursora. Za svaki dohvaćeni red u unutrašnjem kursoru:
  1. Obrada dohvaćenih podataka unutrašnjeg kursora.
4. Zatvaranje unutrašnjeg kursora.
Zatvaranje spoljašnjeg kursora.

Naredna dva zadatka ilustruju rad sa ugnežđenim kursorima.

Zadatak 3.5: Napisati C/SQL program kojim se formira izveštaj o studentima koji su padali neki ispit koji sadrži sledeće informacije: ime, prezime i broj indeksa. Za svaki studijski program formirati posebnu sekciju izveštaja sa zaglavljem koje sadrži identifikator i naziv studijskog programa. Izveštaj urediti po nazivu studijskog programa rastuće, a sadržaj svake sekcije urediti po broju indeksa rastuće.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_5.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hStudyProgramId;
char hStudyProgramName[201];

sqlint32 hIndex;
char hName[21];
char hSurname[21];
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;  
    checkSQL("Konekcija na bazu");
    
    EXEC SQL 
        DECLARE cStudyPrograms CURSOR FOR 
        SELECT      ID, 
                    NAZIV
        FROM        DA.STUDIJSKIPROGRAM
        ORDER BY    NAZIV;
    checkSQL("Deklaracija kursora - cStudyPrograms");
    
    EXEC SQL 
        DECLARE cFailedExams CURSOR FOR 
        SELECT      INDEKS, 
                    IME, 
                    PREZIME
        FROM        DA.DOSIJE D 
        WHERE       D.IDPROGRAMA = :hStudyProgramId AND
                    EXISTS (
                        SELECT  * 
                        FROM    DA.ISPIT
                        WHERE   INDEKS = D.INDEKS AND
                                OCENA = 5 AND
                                STATUS = 'o'
                    )
        ORDER BY    INDEKS;
    checkSQL("Deklaracija kursora - cFailedExams");
    
    /////////////////////////////////////
    // Pocetak obrade spoljasnjeg kursora

    EXEC SQL OPEN cStudyPrograms;
    checkSQL("Otvaranje kursora - cStudyPrograms");

    for(;;) {
        EXEC SQL 
            FETCH   cStudyPrograms
            INTO    :hStudyProgramId, 
                    :hStudyProgramName;
        checkSQL("Dohvatanje podataka iz kursora - cStudyPrograms");

        if(SQLCODE == 100) {
            break;
        }
        
        // Stampamo sekciju za smer
        printf("\n********************************************************************************\n"
               "*Studenti sa studijskog programa %-4.4d: %-40.40s*\n"
               "********************************************************************************\n",
               hStudyProgramId, hStudyProgramName);
        
        /////////////////////////////////////
        // Pocetak obrade unutrasnjeg kursora

        EXEC SQL OPEN cFailedExams;
        checkSQL("Otvaranje kursora - cFailedExams");
        
        for(;;) {
            EXEC SQL 
                FETCH   cFailedExams
                INTO    :hIndex, 
                        :hName, 
                        :hSurname;
            checkSQL("Dohvatanje podataka iz kursora - cFailedExams");

            if(SQLCODE == 100) {
                break;
            }
            
            // Stampamo informacije o studentu
            printf("Student %s %s sa brojem hIndexa %d\n", hName, hSurname, hIndex);    
        }
        
        EXEC SQL CLOSE cFailedExams;
        checkSQL("Zatvaranje kursora - cFailedExams");
        
        // Kraj obrade unutrasnjeg kursora
        //////////////////////////////////
    }

    EXEC SQL CLOSE cStudyPrograms;
    checkSQL("Zatvaranje kursora - cStudyPrograms");

    // Kraj obrade spoljasnjeg kursora
    //////////////////////////////////

    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

Za naredni zadatak je potrebno izvršiti narednu SQL naredbu:

ALTER TABLE DA.ISPIT
ADD COLUMN NAPOMENA VARCHAR(100)

Zadatak 3.6: Napisati C/SQL program kojim se za svaki smer pronalazi student koji ima najviše položenih ESPB bodova. Zatim u tabeli ISPIT u napomeni koja se odnosi na poslednji položeni ispit tog studenta zapisuje 'Ovo je student koji ima najvise polozenih kredita na svom smeru'.

Rešenje: U ovom zadatku smo kreirali pomoćne funkcije koje upravljaju kursorima i obrađuju podatke iz dohvaćenih rezultata, kako bismo povećali modularnost koda. Ono što je važno primetiti jeste da, bez obzira na organizaciju izvornog koda, neophodno je da se deklaracije kursora u kodu nalaze ispred drugih operacija sa kursorima u kodu. Ukoliko to nije slučaj, onda će Db2 pretprocesor prijaviti grešku. Razlog za ovo ponašanje jeste zbog toga što Db2 pretprocesor kod čita kao tekst, ne uzimajući u obzir redosled stvarnog izvršavanja operacija i poziva funkcija. Na primer, ukoliko bi Db2 pretprocesor prvo naišao na naredbu OPEN, pa onda na DECLARE, tada bi prijavio grešku zato što naredba OPEN referiše na naziv kursora za koji Db2 pretprocesor prethodno nije zapamtio da postoji.

Datoteka: vezbe/primeri/poglavlje_3/zadatak_3_6.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
short hSumESPB; 
sqlint32 hStudyProgramId;
sqlint32 hIndex;

char hLastExamDate[11];
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

void declareMaxSumESPBCursor();
void declareLastExamsCursor();
void iterOverMaxSumESPBCursor();
void iterOverLastExamsCursor();

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;  
    checkSQL("Konekcija na bazu");
    
    declareMaxSumESPBCursor();
    declareLastExamsCursor();    
    
    iterOverMaxSumESPBCursor();

    EXEC SQL CONNECT RESET;
    checkSQL("Diskonekcija sa baze");
    
    return 0;
}

void declareMaxSumESPBCursor() {
    EXEC SQL 
        DECLARE cMaxSumESPB CURSOR FOR 
        WITH POMOCNA AS (
            SELECT      D.IDPROGRAMA, 
                        D.INDEKS, 
                        SUM(P.ESPB) SUMA
            FROM        DA.DOSIJE D JOIN
                        DA.ISPIT I ON I.INDEKS = D.INDEKS JOIN
                        DA.PREDMET P ON P.ID = I.IDPREDMETA
            WHERE       OCENA > 5 AND
                        STATUS = 'o'
            GROUP BY    D.IDPROGRAMA, 
                        D.INDEKS
        )
        SELECT      POM.SUMA, 
                    D.INDEKS, 
                    POM.IDPROGRAMA
        FROM        POMOCNA POM JOIN
                    DA.DOSIJE D ON D.INDEKS = POM.INDEKS 
        WHERE       POM.SUMA IN (
                        SELECT  MAX(SUMA) 
                        FROM    POMOCNA POM1 
                        WHERE   POM1.IDPROGRAMA = POM.IDPROGRAMA
                    )
        ORDER BY    IDPROGRAMA;
    checkSQL("Deklaracija kursora - cMaxSumESPB");
}

void declareLastExamsCursor() {
    EXEC SQL 
        DECLARE cLastExams CURSOR FOR 
        WITH POMOCNA AS (
            SELECT      I.INDEKS, 
                        MAX(DATPOLAGANJA) DATUMPOSLEDNJEG
            FROM        DA.ISPIT I JOIN
                        DA.DOSIJE D ON D.INDEKS = I.INDEKS
            WHERE       OCENA > 5 AND
                        STATUS = 'o'
            GROUP BY    I.INDEKS
            HAVING      MAX(DATPOLAGANJA) IS NOT NULL
        )
        SELECT  I.INDEKS
        FROM    DA.ISPIT I
        WHERE   I.INDEKS = :hIndex AND
                EXISTS (
                    SELECT  * 
                    FROM    POMOCNA PO 
                    WHERE   PO.INDEKS = I.INDEKS AND
                            PO.DATUMPOSLEDNJEG = DATPOLAGANJA
                )
        FOR     UPDATE OF NAPOMENA;
    checkSQL("Deklaracija kursora - cLastExams");
}

void iterOverMaxSumESPBCursor() {
    EXEC SQL OPEN cMaxSumESPB;
    checkSQL("Otvaranje kursora - cMaxSumESPB");

    for(;;) {
        EXEC SQL 
            FETCH   cMaxSumESPB                
            INTO    :hSumESPB, 
                    :hIndex, 
                    :hStudyProgramId;
        checkSQL("Dohvatanje podataka iz kursora - cMaxSumESPB");

        if(SQLCODE == 100) {
            break;
        }
        
        // Stampamo informaciju o studentu koji ima najvise kredita na smeru
        printf("Student sa brojem hIndexa %d na smeru %d ima polozeno %d bodova!\n", 
               hIndex, hStudyProgramId, hSumESPB);
        
        iterOverLastExamsCursor();
    }

    EXEC SQL CLOSE cMaxSumESPB;
    checkSQL("Zatvaranje kursora - cMaxSumESPB");
}

void iterOverLastExamsCursor() {
    EXEC SQL OPEN cLastExams;
    checkSQL("Otvaranje kursora - cLastExams");
    
    for(;;) {
        EXEC SQL 
            FETCH   cLastExams
            INTO    :hIndex, 
                    :hLastExamDate;
        checkSQL("Dohvatanje podataka iz kursora - cLastExams");

        if(SQLCODE == 100) {
            break;
        }
        
        // Pokusavamo da unesemo novi red u tabelu DOSIJEEXT
        EXEC SQL 
            UPDATE  DA.ISPIT
            SET     NAPOMENA = 'Ovo je student koji ima najvise polozenih kredita na svom smeru'
            WHERE   CURRENT OF cLastExams;
        checkSQL("Update ISPIT");
    }
    
    EXEC SQL CLOSE cLastExams;
    checkSQL("Zatvaranje kursora - cLastExams");
}

3.5 Zadaci za vežbu

Zadatak 3.7: Napisati C/SQL program koji ispisuje sva ženska imena koja postoje među studentima (zajedno sa brojem pojavljivanja) u opadajućem poretku.

Zadatak 3.8: Napisati C/SQL program koji ispisuje za svakog studenta ime, prezime, poslednji položeni ispit (naziv predmeta koji je položen), kao i datum polaganja tog ispita.

Zadatak 3.9: Napisati C/SQL program koji se za sve studente smera Informatika ažurira u tabeli ISPIT prvi položen ispit (ukoliko ima položenih ispita za tog studenta) tako što povećava ocenu za 1 (ukoliko je ocena bila 5 ili 10 ostavlja je nepromenjenu).

Zadatak 3.10: Napisati C/SQL program koji ispisuje sve napomene koje se nalaze u tabeli ISPIT, navodeći i broj indeksa studenata. (Videti napomenu iznad zadatka 3.6 za kreiranje kolone NAPOMENA.)

Zadatak 3.11: Napisati C/SQL program kojim se sa standardnog ulaza unosi ime studijskog programa, a zatim se ispisuje 10 studenata tog smera koji imaju najviše neuspešnih polaganja na ispitima tokom studija. Izdvojiti ime, prezime, broj indeksa i broj neuspešnih polaganja tokom studija.

Zadatak 3.12: Napisati C/SQL program koji za studenta čiji se broj indeksa zadaje sa standardnog ulaza, ispisuje naziv predmeta, datum polaganja, ocenu i broj osvojenih poena za svaki ispit koji je student položio. Nakon toga ispisuje se prosečna ocena studenta.

Zadatak 3.13: Napisati C/SQL program kojim se omogućava nastavniku da unese naziv predmeta, godinu roka i oznaku roka. Za svako polaganje datog predmeta u datom ispitnom roku ponuditi nastavniku mogućnost da izmeni ocenu koju je student osvojio. Ispisati informacije o indeksu, imenu i prezimenu studenta kao i ocenu koju je dobio, pa zatražiti od nastavnika novu ocenu. Nakon unosa nove ocene, obavestiti nastavnika o uspešnosti izmene i preći na naredno polaganje (ukoliko ih ima više).

Zadatak 3.14: Napisati C/SQL program kojim se brišu sva uspešna polaganja ispita iz barem trećeg pokušaja za studente koji su upisivali najviše N godina, gde se vrednost za N unosi sa standardnog ulaza.

Zadatak 3.15: Napisati C/SQL program kojim se, za svakog studenta koji se upisao u godini koja se učitava sa standardnog ulaza, ispisuju podaci o imenu, prezimenu i prosečnoj oceni, a zatim se ispisuju informacije o položenim ispitima i to: naziv predmeta, ocena i datum polaganja.

Zadatak 3.16: Napisati C/SQL program koji nudi mogućnost ukidanja uslova polaganja za predmete. Program prvo zahteva od korisnika da unese naziv predmeta. Program pronalazi sve predmete sa datim nazivom (može ih biti više) i ispisuje njihove identifikatore, nazive i oznake. Za svaki takav predmet P, program ispisuje identifikatore i nazive njegovih uslovnih predmeta U. Za svaki uslovni predmet U predmeta P, program pita korisnika da li želi da ukine uslovnost P->U. Ukoliko korisnik potvrdi, ukinuti uslov i ispisati poruku.

Zadatak 3.17: Napisati C/SQL program koji ispisuje izveštaj za svaki predmet o njegovim uslovnim predmetima. Za svaki predmet ispisati informacije o identifikatoru predmeta i njegovom nazivu. Svaka sekcija koja izlistava informacije o uslovnim predmetima treba da ispisuje identifikator i naziv uslovnih predmeta za tekući predmet za koji se pravi sekcija, pri čemu se prikazuju informacije samo o onim uslovnim predmetima za koje postoji polaganje sa ocenom 10 na osnovnim akademskim studijama čiji je datum polaganja bio pre 2018. godine.

Zadatak 3.18: Napisati C/SQL program koji za svaki ispitni rok ispisuje njegov naziv i broj uspešnih polaganja za svaku ocenu u tom ispitnom roku. Nakon ispisivanja informacija o ispitnom roku, ponuditi korisniku da izbriše informacije o polaganim ispitima u tom roku. Ukoliko korisnik želi da obriše te podatke, prvo izlistati podatke o indeksu, identifikatoru predmeta, godini roka, oznaci roka i datumu polaganja (ako postoji) za svako polaganje koje se briše. Na kraju brisanja polaganja u jednom ispitnom roku, ispisati ukupan broj obrisanih redova.

Sadržaj