Vnoren

1
Vnorené SQL(embedded SQL)Dotazovací jazyky I

• Jan Sequens
• MFF UK, ak. rok 2009/2010

2
Použité zdroje

• Chapter 6 Embedded SQL, ProC/C Precompiler
  Programmers guide, Release 9.2, Oracle
  Corporation
• http//download.oracle.com/docs/cd/B10501_01/appde
  v.920/a97269/pc_06sql.htm
• Chapter 13 Application Programming I Embedded
  SQL, A Guide to DB2, 4/e, C J Date, Colin J White
• Embedded SQL Introduction to ProC, Ankur Jain
  and Jeff Ullman
• http//infolab.stanford.edu/ullman/fcdb/oracle/or
  -proc.html
• Embedded SQL v C/C, Katerina Opocenská
• http//www.ms.mff.cuni.cz/kopecky/vyuka/oracle2/o
  ra2_opoc.pdf

3
Obsah této prezentace

• Co je vnorené SQL a jak se používá
• Hostitelské a indikátorové promenné
• Príkazy INSERT, UPDATE, DELETE, SELECT
• Kurzory
• Ošetrení chyb

4
Co a k cemu je vnorené SQL?

• Zpusob použití SQL v programovacích jazycích
• SQL vepsané ve zdrojovém kódu jiného
  programovacího jazyka
• Všechny príkazy interaktivního SQL jdou použít ve
  vnoreném SQL

5
Co a k cemu je vnorené SQL?

• Zpusob použití SQL v programovacích jazycích
• SQL vepsané ve zdrojovém kódu jiného
  programovacího jazyka
• Všechny príkazy interaktivního SQL jdou použít ve
  vnoreném SQL (nikoliv naopak)

6
Jak vnorené SQL používáme?

• Náš databázový systém musí mít podporu vnoreného
  SQL v našem programovacím jazyce

7
Jak vnorené SQL používáme?
IBM DB2

Oracle

Microsoft SQL Server

MySQL

8
Jak vnorené SQL používáme?
IBM DB2
C/C, COBOL, FORTRAN, REXX
Oracle

Microsoft SQL Server

MySQL

9
Jak vnorené SQL používáme?
IBM DB2
C/C, COBOL, FORTRAN, REXX
Oracle
Ada, C/C, COBOL, Fortran, Pascal, PL/1
Microsoft SQL Server

MySQL

10
Jak vnorené SQL používáme?
IBM DB2
C/C, COBOL, FORTRAN, REXX
Oracle
Ada, C/C, COBOL, Fortran, Pascal, PL/1
Microsoft SQL Server
Od verze 2008 ukoncena oficiální podpora
MySQL

11
Jak vnorené SQL používáme?
IBM DB2
C/C, COBOL, FORTRAN, REXX
Oracle
Ada, C/C, COBOL, Fortran, Pascal, PL/1
Microsoft SQL Server
Od verze 2008 ukoncena oficiální podpora
MySQL
Nepodporováno
12
Jak vnorené SQL používáme?

• Príklad vnoreného SQL (Oracle a C)
• include ltstdio.hgt
• include ltsqlca.hgt
• int main(void)
• EXEC SQL INSERT INTO osoby (prijmeni) VALUES
  (Sequens)
• return 0

13
Jak vnorené SQL používáme?

• Vložíme hlavickový soubor struktury SQL
  Communication Area (SQLCA)
• Príkazy vnoreného SQL zacínáme direktivou EXEC
  SQL a ukoncujeme stredníkem

14
Jak vnorené SQL používáme?

• Oracle prostredí pro SQL vnorené v C/C se
  nazývá ProC/C
• Zdrojové soubory s príponou .pc
• Prekompilátor ProC/C preloží .pc na cistý
  C/C kód
• Nahrazení konstrukcí vnoreného SQL voláními
  standardní run-time knihovny
• C/C kód je standardne zkompilován

15
Hostitelské promenné

• Predávání dat mezi SQL a C/C
• Ve vnoreném SQL oznacujeme dvojteckou
• Vstupní
• EXEC SQL INSERT INTO osoby (prijmeni) VALUES
  (prijmeni_osoby)
• Výstupní
• EXEC SQL SELECT prijmeni INTO prijmeni_osoby
  FROM osoby

16
Indikátorové promenné

• Motivace Co uložit do promenné, když SELECT
  vrátí NULL?
• Rešení Použijeme druhou, sprátelenou
  promennou, která bude indikovat, jak a zda je
  puvodní promenná naplnena
• Indikátorová promenná se zapisuje bezprostredne
  za puvodní promennou, oddelená dvojteckou

17
Indikátorové promenné u výstupních promenných
Indikátorová promenná Hostitelská promenná
-2 Oríznutá hodnota z databáze, hodnota se do hostitelské promenné nevejde a její velikost nemuže být urcena.
-1 Nedefinovaná hodnota, v databázi byla NULL.
0 Hodnota z databáze, není to NULL.
gt0 Oríznutá hodnota z databáze, hodnota se do hostitelské promenné nevejde. Indikátorová promenná obsahuje velikost hodnoty v databázi.
18
Indikátorové promenné u výstupních promenných -
príklad

• EXEC SQL SELECT pocet_deti INTO pocetind_pocet
  FROM osoby WHERE prijmeni Sequens
• if (ind_pocet -1) / NULL v databázi /
• pocet 0

19
Indikátorové promenné u vstupních promenných

• Ovlivní, co se zapíše do databáze v príkazech
  INSERT nebo UPDATE

Indikátorová promenná Zápis do databáze
-1 Zapíše se NULL (hodnota hostitelské promenné se bude ignorovat).
gt 0 Zapíše se hodnota hostitelské promenné.
20
Príkaz INSERT

• Stejné použití jako pri interaktivním SQL
• Skutecný zápis proveden až po zapsání zmen
  príkazem COMMIT
• EXEC SQL INSERT INTO osoby (jmeno, prijmeni)
• VALUES (jmeno_osoby, prijmeni_osoby)

21
Príkaz UPDATE

• Stejné použití jako pri interaktivním SQL
• Skutecný zápis proveden až po zapsání zmen
  príkazem COMMIT
• EXEC SQL UPDATE osoby
• SET jmeno jmeno_osoby
• WHERE prijmeni prijmeni_osoby

22
Príkaz DELETE

• Stejné použití jako pri interaktivním SQL
• Skutecné smazání provedeno až po zapsání zmen
  príkazem COMMIT
• EXEC SQL DELETE FROM osoby
• WHERE prijmeni prijmeni_osoby

23
Príkaz SELECT

• V zásade stejné použití jako u interaktivního SQL
• Je treba vyrešit, kam a jak uložíme to, co SELECT
  vrátí

24
Príkaz SELECT

• Víme-li, že náš SELECT vrátí nejvýše jeden rádek,
  je situace jednoduchá

25
Príkaz SELECT

• Víme-li, že náš SELECT vrátí nejvýše jeden rádek,
  je situace jednoduchá
• EXEC SQL SELECT jmeno, prijmeni
• INTO jmeno_osoby, prijmeni_osoby
• FROM osoby

26
Príkaz SELECT

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

27
Kurzor

• Nástroj pro práci s množinou rádku, kterou vrací
  príkaz SELECT
• Udržuje ukazatel na aktuální zpracovávaný rádek

28
Princip práce s kurzorem

• DECLARE CURSOR
• Urcení SQL dotazu, se kterým bude kurzor pracovat
• OPEN
• Provedení SQL dotazu, se kterým kurzor pracuje
• FETCH
• Nactení jednoho rádku z výsledku dotazu
• CLOSE
• Ukoncení 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 gt 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 gt minimalni_plodnost
• Nezahrnujeme klauzuli INTO

31
OPEN

• Navázání hostitelských promenných
• Vykonání dotazu
• EXEC SQL OPEN osoby_kurzor

32
FETCH

• Nactení rádku z výsledku
• Posunutí ukazatele na další rádek
• EXEC SQL FETCH osoby_kurzor
• INTO jmeno_osoby, prijmeni_osoby

33
FETCH

• Nactení rádku z výsledku
• Posunutí ukazatele na další rá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 otevren.

34
CLOSE

• Uzavre kurzor, uvolní zdroje.
• Posunutí ukazatele na další rádek
• EXEC SQL CLOSE osoby_kurzor

35
Ošetrení chyb

• Ke smysluplnému príkladu použití kurzoru nám
  chybí zpusob, jak ošetrit chyby
• První možnost je testovat po každém vnoreném SQL
  príkazu, zda-li nedošlo k chybe, tzv. explicitní
  testování
• Druhá možnost je implicitní testování pomocí
  príkazu WHENEVER

36
Explicitní ošetrení chyb

• Po vykonání každého vnoreného SQL príkazu
  obsahuje struktura SQLCA v poli sqlcode kontrolní
  hodnotu

Hodnota sqlcode Výsledek posledního príkazu
0 Probehl v porádku
gt 0 Probehl, ale s problémy (warning). Napr. hodnota 100 znací, že nebyla nalezena žádná data.
lt 0 Skoncil chybou (error).
37
Implicitní ošetrení chyb

• Zjednodušení oproti explicitnímu ošetrení
• Není treba testovat po každém príkazu, testovací
  podmínka platí až do urcení podmínky nové
• Zajištení príkazem WHENEVER se syntaxí
• EXEC SQL WHENEVER ltpodmínkagt ltakcegt

38
Príkaz WHENEVER

• EXEC SQL WHENEVER ltpodmínkagt ltakcegt
• Kde ltpodmínkagt je
• NOT FOUND (sqlcode je 100, prázdná data),
• SQLWARNING (varování jiné než prázdná data),
• SQLERROR (chyba),
• A ltakcegt je
• CONTINUE (program se pokusí pokracovat)
• DO ltvolání_funkcegt (zavolání C/C funkce)
• GO TO ltnáveštígt (odskok)
• STOP (program ihned skoncí)

39
Kurzory prí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 ci mazání
  záznamu v tabulce
• Kurzor je deklarován s klauzulí FOR UPDATE OF
  ltjméno_tabulkygt na konci
• Príkazy UPDATE ci DELETE ho mohou využít v cásti
  WHERE, byl-li již kurzor otevren a proveden
  príkaz FETCH
• EXEC SQL UPDATE WHERE CURRENT OF osoby_kurzor
• Odkaz na poslední záznam získaný príkazem FETCH

41
Posuvné (scrollable) kurzory

• Dovolují i jiný pohyb ve vybraných záznamech než
  jen o jeden záznam vpred

42
Posuvné (scrollable) kurzory

• Dovolují i jiný pohyb ve vybraných záznamech než
  jen o jeden záznam vpred
• FETCH FIRST
• FETCH LAST
• FETCH PRIOR
• FETCH NEXT
• FETCH CURRENT
• FETCH RELATIVE n
• FETCH ABSOLUTE n

43
Záverecný príklad (1/4)

• include ltstdio.hgt
• / deklarace hostitelských promenných /
• char userid12 "SCOTT/TIGER"
• char jmeno_zamestnance10
• int cislo_zamestanance
• int cislo_oddeleni
• char temp32
• void sql_error()
• / SQL Communications Area /
• include ltsqlca.hgt
• main()
• emp_number 7499
• / ošetrení chyb /
• EXEC SQL WHENEVER SQLERROR do
  sql_error("Oracle chyba")

44
Záverecný príklad (2/4)

• / pripojení 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)
• / otevrení kurzoru a vykonání dotazu /
• EXEC SQL OPEN zam_kurzor

45
Záverecný príklad (3/4)

• printf(Prijmeni zamestnance\n")
• printf("-------------\n")
• / nactení dat ve smycce pomocí FETCH
• opuštení smycky, když nebude další rá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áverecný príklad (4/4)

• void
• sql_error(msg)
• char msg
• char buf500
• 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

• Cas pro carokrásnou referující Šárku