Vnořené SQL ( embedded SQL) Dotazovací jazyky I

1. Vnořené SQL(embedded SQL)Dotazovací jazyky I Jan Sequens MFF UK, ak. rok 2009/2010

2. Použité zdroje • Chapter 6: Embedded SQL, Pro*C/C++ PrecompilerProgrammer’s guide, Release 9.2, OracleCorporation • http://download.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_06sql.htm • Chapter 13: ApplicationProgramming I: Embedded SQL, A Guide to DB2, 4/e, C J Date, Colin J White • Embedded SQL : Introduction to Pro*C, Ankur Jain and Jeff Ullman • http://infolab.stanford.edu/~ullman/fcdb/oracle/or-proc.html • Embedded SQL v C/C++, Kateřina Opočenská • http://www.ms.mff.cuni.cz/~kopecky/vyuka/oracle2/ora2_opoc.pdf

3. Obsah této prezentace • Co je vnořené SQL a jak se používá • Hostitelské a indikátorové proměnné • Příkazy INSERT, UPDATE, DELETE, SELECT • Kurzory • Ošetření chyb

4. Co a k čemu je vnořené SQL? • Způsob použití SQL v programovacích jazycích • SQL vepsané ve zdrojovém kódu jiného programovacího jazyka • Všechny příkazy interaktivního SQL jdou použít ve vnořeném SQL

5. Co a k čemu je vnořené SQL? • Způsob použití SQL v programovacích jazycích • SQL vepsané ve zdrojovém kódu jiného programovacího jazyka • Všechny příkazy interaktivního SQL jdou použít ve vnořeném SQL (nikoliv naopak)

6. Jak vnořené SQL používáme? • Náš databázový systém musí mít podporu vnořeného SQL v našem programovacím jazyce

7. Jak vnořené SQL používáme?

8. Jak vnořené SQL používáme?

9. Jak vnořené SQL používáme?

10. Jak vnořené SQL používáme?

11. Jak vnořené SQL používáme?

12. Jak vnořené SQL používáme? • Příklad vnořeného SQL (Oracle a C): #include <stdio.h> #include <sqlca.h> int main(void) { EXEC SQL INSERT INTO osoby (prijmeni) VALUES (‘Sequens’); return 0; }

13. Jak vnořené SQL používáme? • Vložíme hlavičkový soubor struktury SQL Communication Area (SQLCA) • Příkazy vnořeného SQL začínáme direktivou EXEC SQL a ukončujeme středníkem

14. Jak vnořené SQL používáme? • Oracle prostředí pro SQL vnořené v C/C++ se nazývá Pro*C/C++ • Zdrojové soubory s příponou *.pc • Prekompilátor Pro*C/C++ přeloží *.pc na čistý C/C++ kód • Nahrazení konstrukcí vnořeného SQL voláními standardní run-time knihovny • C/C++ kód je standardně zkompilován

15. Hostitelské proměnné • Předávání dat mezi SQL a C/C++ • Ve vnořeném SQL označujeme dvojtečkou • Vstupní • EXEC SQL INSERT INTO osoby (prijmeni) VALUES (:prijmeni_osoby); • Výstupní • EXEC SQL SELECT prijmeni INTO :prijmeni_osoby FROM osoby;

16. Indikátorové proměnné • Motivace: Co uložit do proměnné, když SELECT vrátí NULL? • Řešení: Použijeme druhou, „spřátelenou“ proměnnou, která bude indikovat, jak a zda je původní proměnná naplněna • Indikátorová proměnná se zapisuje bezprostředně za původní proměnnou, oddělená dvojtečkou

17. Indikátorové proměnné u výstupních proměnných

18. Indikátorové proměnné u výstupních proměnných - příklad EXEC SQL SELECT pocet_deti INTO :pocet:ind_pocet FROM osoby WHERE prijmeni = ‘Sequens’; if (ind_pocet == -1) /* NULL v databázi */ pocet = 0;

19. Indikátorové proměnné u vstupních proměnných • Ovlivní, co se zapíše do databáze v příkazech INSERT nebo UPDATE

20. Příkaz INSERT • Stejné použití jako při interaktivním SQL • Skutečný zápis proveden až po zapsání změn příkazem COMMIT EXEC SQL INSERT INTO osoby (jmeno, prijmeni) VALUES (:jmeno_osoby, :prijmeni_osoby);

21. Příkaz UPDATE • Stejné použití jako při interaktivním SQL • Skutečný zápis proveden až po zapsání změn příkazem COMMIT EXEC SQL UPDATE osoby SET jmeno = :jmeno_osoby WHERE prijmeni = :prijmeni_osoby;

22. Příkaz DELETE • Stejné použití jako při interaktivním SQL • Skutečné smazání provedeno až po zapsání změn příkazem COMMIT EXEC SQL DELETE FROM osoby WHERE prijmeni = :prijmeni_osoby;

23. Příkaz SELECT • V zásadě stejné použití jako u interaktivního SQL • Je třeba vyřešit, kam a jak uložíme to, co SELECT vrátí

24. Příkaz SELECT • Víme-li, že náš SELECT vrátí nejvýše jeden řádek, je situace jednoduchá

25. Příkaz SELECT • Víme-li, že náš SELECT vrátí nejvýše jeden řádek, je situace jednoduchá EXEC SQL SELECT jmeno, prijmeni INTO :jmeno_osoby, :prijmeni_osoby FROM osoby;

26. Příkaz SELECT • Víme-li, že náš SELECT vrátí nejvýše jeden řádek, je situace jednoduchá EXEC SQL SELECT jmeno, prijmeni INTO :jmeno_osoby, :prijmeni_osoby FROM osoby; • Vrací-li SELECT více než jeden řádek, je třeba použít ke zpracování výsledku tzv. kurzor

27. Kurzor • Nástroj pro práci s množinou řádků, kterou vrací příkaz SELECT • Udržuje ukazatel na aktuální zpracovávaný řádek

28. Princip práce s kurzorem • DECLARE CURSOR • Určení SQL dotazu, se kterým bude kurzor pracovat • OPEN • Provedení SQL dotazu, se kterým kurzor pracuje • FETCH • Načtení jednoho řádku z výsledku dotazu • CLOSE • Ukončení práce s kurzorem

29. DECLARE CURSOR • Pojmenování kurzoru • Asiociace s dotazem EXEC SQL DECLARE osoby_kurzor CURSOR FOR SELECT jmeno, prijmeni FROM osoby WHERE pocet_deti > :minimalni_plodnost;

30. DECLARE CURSOR • Pojmenování kurzoru • Asiociace s dotazem EXEC SQL DECLARE osoby_kurzor CURSOR FOR SELECT jmeno, prijmeni FROM osoby WHERE pocet_deti > :minimalni_plodnost; • Nezahrnujeme klauzuli INTO

31. OPEN • Navázání hostitelských proměnných • Vykonání dotazu EXEC SQL OPEN osoby_kurzor;

32. FETCH • Načtení řádku z výsledku • Posunutí ukazatele na další řádek EXEC SQL FETCH osoby_kurzor INTO :jmeno_osoby, :prijmeni_osoby;

33. FETCH • Načtení řádku z výsledku • Posunutí ukazatele na další řádek EXEC SQL FETCH osoby_kurzor INTO :jmeno_osoby, :prijmeni_osoby; • Klauzule INTO se použije zde, nikoliv v deklaraci kurzoru • Kurzor musí být deklarován a otevřen.

34. CLOSE • Uzavře kurzor, uvolní zdroje. • Posunutí ukazatele na další řádek EXEC SQL CLOSE osoby_kurzor;

35. Ošetření chyb • Ke smysluplnému příkladu použití kurzoru nám chybí způsob, jak ošetřit chyby • První možnost je testovat po každém vnořeném SQL příkazu, zda-li nedošlo k chybě, tzv. explicitní testování • Druhá možnost je implicitní testování pomocí příkazu WHENEVER

36. Explicitní ošetření chyb • Po vykonání každého vnořeného SQL příkazu obsahuje struktura SQLCA v poli sqlcode kontrolní hodnotu

37. Implicitní ošetření chyb • Zjednodušení oproti explicitnímu ošetření • Není třeba testovat po každém příkazu, testovací podmínka platí až do určení podmínky nové • Zajištění příkazem WHENEVER se syntaxí: EXEC SQL WHENEVER <podmínka> <akce>;

38. Příkaz WHENEVER EXEC SQL WHENEVER <podmínka> <akce>; • Kde <podmínka> je • NOT FOUND (sqlcode je 100, prázdná data), • SQLWARNING (varování jiné než prázdná data), • SQLERROR (chyba), • A <akce> je • CONTINUE (program se pokusí pokračovat) • DO <volání_funkce> (zavolání C/C++ funkce) • GOTO <návěští> (odskok) • STOP (program ihned skončí)

39. Kurzory – příklad použití EXEC SQL DECLARE osoby_kurzor CURSOR FOR SELECT jmeno FROM osoby WHERE pocet_deti = :minimalni_plodnost; EXEC SQL OPEN osoby_kurzor; EXEC SQL WHENEVER NOT FOUND DO break; while(1) { EXEC SQL FETCH emp_cursor INTO :jmeno_osoby; } EXEC SQL CLOSE osoby_kurzor;

40. Kurzory FOR UPDATE • Kurzory sloužící také pro úpravu či mazání záznamů v tabulce • Kurzor je deklarován s klauzulí FOR UPDATE OF <jméno_tabulky> na konci • Příkazy UPDATE či DELETE ho mohou využít v části WHERE, byl-li již kurzor otevřen a proveden příkaz FETCH • EXEC SQL UPDATE … WHERE CURRENT OF osoby_kurzor; • Odkaz na poslední záznam získaný příkazem FETCH

41. Posuvné (scrollable) kurzory • Dovolují i jiný pohyb ve vybraných záznamech než jen o jeden záznam vpřed

42. Posuvné (scrollable) kurzory • Dovolují i jiný pohyb ve vybraných záznamech než jen o jeden záznam vpřed • FETCH FIRST • FETCH LAST • FETCH PRIOR • FETCH NEXT • FETCH CURRENT • FETCH RELATIVE n • FETCH ABSOLUTE n

43. Závěrečný příklad (1/4) #include <stdio.h> /* deklarace hostitelských proměnných */ char userid[12] = "SCOTT/TIGER"; char jmeno_zamestnance[10]; int cislo_zamestanance; int cislo_oddeleni; char temp[32]; void sql_error(); /* SQL Communications Area */ #include <sqlca.h> main() { emp_number = 7499; /* ošetření chyb */ EXEC SQL WHENEVER SQLERROR do sql_error("Oracle chyba");

44. Závěrečný příklad (2/4) /* připojení k Oracle databázi */ EXEC SQL CONNECT :userid; printf(„Pripojeno.\n"); /* deklarace kurzoru */ EXEC SQL DECLARE zam_kurzor CURSOR FOR SELECT prijmeni FROM zam WHERE cislo_oddeleni = :cislo_oddeleni; printf(„Cislo oddeleni? "); gets(temp); dept_number = atoi(temp); /* otevření kurzoru a vykonání dotazu */ EXEC SQL OPEN zam_kurzor;

45. Závěrečný příklad (3/4) printf(„Prijmeni zamestnance\n"); printf("-------------\n"); /* načtení dat ve smyčce pomocí FETCH opuštění smyčky, když nebude další řádek */ EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH zam_kurzor INTO :jmeno_zamestnance; printf("%s\n", jmeno_zamestnance); } EXEC SQL CLOSE zam_kurzor; EXEC SQL COMMIT WORK RELEASE; exit(0); }

46. Závěrečný příklad (4/4) void sql_error(msg) char *msg; { char buf[500]; int buflen, msglen; EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; buflen = sizeof (buf); sqlglm(buf, &buflen, &msglen); printf("%s\n", msg); printf("%*.s\n", msglen, buf); exit(1); }

47. Konec prezentace • Čas pro čarokrásnou referující Šárku