Title: Vnoren
1Vnorené SQL(embedded SQL)Dotazovací jazyky I
- Jan Sequens
- MFF UK, ak. rok 2009/2010
2Použ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
3Obsah 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
4Co 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
5Co 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)
6Jak vnorené SQL používáme?
- Náš databázový systém musí mít podporu vnoreného
SQL v našem programovacím jazyce
7Jak vnorené SQL používáme?
IBM DB2
Oracle
Microsoft SQL Server
MySQL
8Jak vnorené SQL používáme?
IBM DB2
C/C, COBOL, FORTRAN, REXX
Oracle
Microsoft SQL Server
MySQL
9Jak 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
10Jak 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
11Jak 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
12Jak 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
-
13Jak 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
14Jak 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
15Hostitelské 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
16Indiká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
17Indiká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.
18Indiká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
19Indiká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é.
20Prí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)
21Prí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
22Prí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
23Prí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í
24Príkaz SELECT
- Víme-li, že náš SELECT vrátí nejvýše jeden rádek,
je situace jednoduchá
25Prí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
26Prí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
27Kurzor
- 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
28Princip 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
29DECLARE 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
30DECLARE 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
31OPEN
- Navázání hostitelských promenných
- Vykonání dotazu
- EXEC SQL OPEN osoby_kurzor
32FETCH
- Nactení rádku z výsledku
- Posunutí ukazatele na další rádek
- EXEC SQL FETCH osoby_kurzor
- INTO jmeno_osoby, prijmeni_osoby
33FETCH
- 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.
34CLOSE
- Uzavre kurzor, uvolní zdroje.
- Posunutí ukazatele na další rádek
- EXEC SQL CLOSE osoby_kurzor
35Oš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
36Explicitní 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).
37Implicitní 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
38Prí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í)
39Kurzory 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
40Kurzory 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
41Posuvné (scrollable) kurzory
- Dovolují i jiný pohyb ve vybraných záznamech než
jen o jeden záznam vpred
42Posuvné (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
43Zá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")
44Zá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
45Zá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)
46Zá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)
47Konec prezentace
- Cas pro carokrásnou referující Šárku