Vnorené SQL

Autor prezentácie: Peter Šípoš

Literatúra

● Programmatic SQL od Pearson Ed● Embedded SQL: http://download.oracle.

com/docs/cd/B10501_01/appdev.920/a97269/pc_06sql.htm

● Oracle Dynamic SQL: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm

● Embedded SQL programming: http://www-136.ibm.com/developerworks/db2

● Oracle/SQL Tutorial, Michael Gertzhttp://www.slideshare.net/MuzeebPower/oracle-17093819

Obsah

● Motivácia● Chybové stavy● Reprezentácia zvláštnych hodnot● Jednoduché dotazy● Jednoriadkové dotazy● Viacriadkové dotazy - Kurzory● Zmeny dát● Dynamické SQL● Typy dotazov v dyn. SQL

Motivácia pre vnorené SQL

● v SQL1992 chýbajú nejaké konštrukty○ IF .. THEN, GOTO.., DO..WHILE missing

● dotazy programov○ užívatel nemusí poznať SQL○ rozšírenie aplikácie prístupom k DB

● riešenie: programové SQL○ vnorené SQL - SQL dotazy v zdrojovom kóde

hostujúcej aplikácie○ Application Programming Interface -

štandardizovaná množina funkcií (ODBC)

Program používajúci vnorené SQL

● nazývame hostujúcim programom● SQL príkazy sú v zdrojovom kóde● potrebujeme prekladač na to pripravený -

precompiler● Pro*C - interface od Oracle pre jazyk C

Prekladač pre Pro*C

Zdroj: Oracle/SQL Tutorial, str. 40

Príklad 1EXEC SQL INCLUDE sqlca;

main(){

EXEC SQL BEGIN DECLARE SECTION;

char * username = ””;

char * password = ””;

EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT :username IDENTIFIED BY :password;

if (sqlca.sqlcode<0) exit(-1);

EXEC SQL CREATE TABLE Pohlad(

cisloTovaru VARCHAR2(5) NOT NULL … )

EXEC SQL COMMIT WORK RELEASE;

}

Chybové a iné stavy (SQLCA)Musíme si zistiť, či dotaz prebiehal v poriadkuSQL Communications Area - štruktúra na uloženie výsledného stavu dotazu (chybové kódy, hlásenia, upozornenia) sqlcode=0 : dotaz bol úspešne vykonaný sqlcode<0 : došlo k chybe sqlcode>0 : úspech, ale niečo nezvyčajné sa stalo (napr. žiadne ďalšie riadky)

EXEC SQL WHENEVER <podmienka> <akcia> - štandardné chovanie našej aplikáciaPodmienky:

● SQLERROR● SQLWARNING● NOT FOUND

Chybové a iné stavy (SQLCA)Akcie príkazu WHENEVER:● CONTINUE● DO nazov_funkcie● DO BREAK● DO CONTINUE● GOTO znacka● STOP

Príklad A :EXEC SQL WHENEVER SQLERROR GOTO error1;

→ if (sqlca.sqlcode<0) goto error1;

Príklad B:EXEC SQL WHENEVER SQLERROR DO BREAK;

→ for(;;){ if (sqlca.sqlcode<0) break; }

Premenné hostujúceho jazyka I

● tvar premennej je :nazov_premennej○ EXEC SQL BEGIN DECLARE SECTION;

float increment;EXEC SQL END DECLARE SECTION;

● mapovanie typov závisí od daného systému○ napr. pre Oracle vid Tabulka 1

● výstupné premenné - dotaz vráti výsledok do nich:EXEC SQL SELECT zam_meno, cislo_zam INTO :meno,:cislo FROM emp

● vstupné premenné - všetky ostatné výskyty

Premenné hostujúceho jazyka II - Tabulka 1

Mapovania premenných z typov používaných v SRBD od Oracle na typy v jazyku C .

Typ od Oracle Typ v jazyku C

CHAR char

CHAR(n), VARCHAR2(n) char[n + 1]

NUMBER(6) int

NUMBER(10) long int

NUMBER(6,2) float

DATE char[10]

Premenné hostujúceho jazyka III

Reprezentácia hodnoty NULL● indikačné premenné

○ formát :nazovPremennej:nazovIndikacnejPremennej○ EXEC SQL BEGIN DECLARE SECTION;

char adresa[51]; short adresaInd;EXEC SQL END DECLARE SECTION;adresaInd=-1;EXEC SQL UPDATE Vlastnik SET adresa=:adresa:adresaInd WHERE …

● bez nich○ EXEC SQL UPDATE Zamestnanec SET plat=NULL○ /* deklaracia hostovanej premennej zam_id */

EXEC SQL INSERT INTO Zamestnanec (zam_id,plat) VALUES (:zam_id,NULL)

Premenné hostujúceho jazyka IV

Hodnoty indikačnej premennej:Podobná konvencia ako u chybových stavov● adresaInd < 0 - adresa má byt interpretovaná ako

NULL● adresaInd = 0 - adresa obsahuje platnú hodnotu● adresaInd > 0 - adresa obsahuje zaokrúhlenú

hodnotuŠanca výskytu hodnoty NULL - používajme indikačnú premennú, inak hrozí chybové hlásenie

Jednoduché dotazy

● príkaz nevráti žiadnu hodnotu (napr.: INSERT, UPDATE, DELETE) - vid Príklad 1

● všeobecné vlastnosti vnorených SQL príkazov:○ začínajú sa nejakým identifikátorom (EXEC SQL,

@SQL)○ končia sa terminátorom závislým na hostujúcom

jazyku (Ada,C - ‘;’ , COBOL - “END-EXEC” )○ možu byt viacriadkove - značka jazyka pre

pokračovanie○ môžu sa vyskytovať všade, kde príkazy hostujúceho

jazyka○ rovnaký efekt ako “interaktívne” zadané varianty

Jednoriadkové dotazyVýsledok dotazu obsahuje maximálne jeden riadok.EXEC SQL

SELECT jmeno,prijmeni,adresa

INTO :jmeno, :prijmeni, :adresa:adresaInd

FROM Vlastnik

WHERE vlastnikID=”CO21”;

Obsah stavového kódu:sqlcode = 0 - jeden riadok a získané hodnoty sú správne mapované do premennýchsqlcode = NOT FOUND - žiadny výsledoksqlcode < 0 - hodnota NULL vo výsledku a nedefinovaná indikačná premenná

Viacriadkové dotazy I - Kurzory

Výsledok dotazu obsahuje viac riadkov - musíme používať kurzor.Kurzor:● umožní prístup k jednotlivým riadkam vo výsledku● deklarácia a otvorenie

○ EXEC SQL DECLARE nazovKurzora CURSOR FOR <dotaz so SELECTom>;

○ EXEC SQL OPEN nazovKurzora;● čítanie a krokovanie pomocou FETCH

○ EXEC SQL FETCH nazovKurzora INTO <zoznam premennych a indikacnych. prem.>

● po použití musí byť zatvorený○ EXEC SQL CLOSE nazovKurzora;

Viacriadkové dotazy II - príkladProgram načíta číslo zamestanca a vypíše nehnutelnosti spravované daným zamestnancom./*deklaracia premennych, pripojenie k databaze, nacitanie hodnoty zamID */

EXEC SQL WHENEVER SQLERROR GOTO error;

EXEC SQL WHENEVER NOT FOUND GOTO done

EXEC SQL DECLARE nehnKurzor CURSOR FOR

SELECT cisloNehn, ulica, mesto

FROM KPronajmu WHERE spravca = :zamID

ORDER BY cisloNehn;

EXEC SQL OPEN nehnKurzor;

for(;;){

EXEC SQL FETCH nehnKurzor INTO :cisloNehn, :adresa, :mesto }

Viacriadkové dotazy III - príklad (pokr.)

error:

/*hlaska chyby*/

done:

EXEC SQL WHENEVER SQLERROR continue;

EXEC SQL CLOSE nehnKurzor;

EXEC SQL COMMIT WORK RELEASE;

Zmeny dát - KurzoryDva typy kurzorov:1. readonly2. updatable - zmena (UPDATE) alebo vymazanie (DELETE CURRENT)

záznamuTabulka musí byť zmenitelnáVloženie nových záznamov funguje vždy - viditelnosť pridaných záznamov závisí na zmenitelnosti tabulky.EXEC SQL DECLARE nazovKurzoru CURSOR FOR <dotaz so SELECTom> FOR UPDATE OF nazovStlpca,[...](podmnozina stlpcov za SELECTom)

Zmeny dát - KurzoryEXEC SQL UPDATE NazovTabulky SET nazovStlpca=novaHodnota [,...] WHERE CURRENT OF nazovKurzora;

Príklad:EXEC SQL DECLARE zam_kurzor CURSOR FOR SELECT meno, plat FROM Zamestnanec WHERE pozicia = 'casnik' FOR UPDATE OF plat; ... EXEC SQL OPEN zam_kurzor; EXEC SQL WHENEVER NOT FOUND GOTO ... for (;;) { EXEC SQL FETCH zam_kurzor INTO :meno_zam, :plat_zam; ... EXEC SQL UPDATE Zamestnanec SET plat = :novy_plat WHERE CURRENT OF zam_kurzor; }

Vymazávanie dát - Kurzory

EXEC SQL DELETE FROM NazovTabulky WHERE CURRENT OF nazovKurzora;Poznámky:● pre DELETE: UPDATE OF nemusíme písat v deklarácii

kurzora ● u Oracle, CURRENT OF nesmieme používať na

indexovanú tabulku

Rozdiely v štandardu ISOPrikaz WHENEVER● neexistuje SQLWARNING

SQL Communications Area● namiesto SQLCODE je použitý SQLSTATE● v Oracle 9i prístupný ako

EXEC SQL BEGIN DECLARE SECTION;char SQLSTATE[6];

EXEC SQL END DECLARE SECTION;

Príkazy kurzorov v štandardu ISO:● definícia kurzoru - až na INSENSITIVE prístupný v Oracle

EXEC SQL DECLARE nazovKurzoru [INSENSITIVE][SCROLL] CURSOR FOR <select> [FOR {READ ONLY | UPDATE [OF zoznam atributov]} ]

Rozdiely v štandardu ISO● Získanie ďalšieho záznamu z kurzoru - aktuálne už je prístupný aj v

OracleEXEC SQL FETCH [smer [ FROM]] nazovKurzoru INTO premenna[,...]smer môžeme zadefinovať ako:○ NEXT - bezprostredne ďalší riadok○ PRIOR - bezprostredne predchádzajuci riadok○ FIRST - prvý riadok výsledku○ LAST - posledný riadok výsledku○ ABSOLUTE - absolútne číslo riadku○ RELATIVE - relatívne číslo riadku od aktuálneho

Odbočka - Výskyt premenných

Výskyt hostovaných premenných na mieste:● hodnôt● výrazovNapr.: SELECT - INTO, FETCH - INTO, UPDATE - SETNie na mieste:● klúčových slov● názvov DB objektovV klauzule ORDER BY áno, ale bez efektu.Motivácia pre dynamické SQL.

Dynamické SQL

Motivácia pre niečo dynamickéV predchádzajúcich príkladoch sme poznali schématu DB, na ktorej sme spustili naše dotazy.Niekedy potrebujeme úplne individuálne dotazy:● EXEC SQL BEGIN DECLARE SECTION;

char nazovTabulky[20];EXEC SQL END DECLARE SECTION;EXEC SQL INSERT INTO :nazovTabulky VALUES (‘hodnota1’,’hodnota2’,’hodnota3’);

● EXEC SQL CREATE DECLARE nazovKurzoru CURSOR FORSELECT *FROM :nazovTabulky;

V statickom vnorenom SQL vyššie príkazy nefungujú.

Kategórie príkazov dyn. SQL

Typy príkazov v dyn. SQL:1. neopakujúce príkazy, nevrátia záznamy2. opakujúce príkazy, nevrátia záznamy3. dotazy, počet atribútov v SELECTu a počet

parametrov poznáme pri kompilácii4. dotazy, pomocou deskriptoru - počet

parametrov zistíme až za behu

Príkaz EXECUTE IMMEDIATE (typ 1)Pomocou tohto prikazu môžeme vykonávať lubovolný SQL príkaz.EXEC SQL EXECUTE IMMEDIATE [host premenna | literál retazca];

Príklad:EXEC SQL BEGIN DECLARE SECTION;char buffer[100];

EXEC SQL END DECLARE SECTION;

sprintf(buffer,”UPDATE Zamestnanec SET plat=plat + %f WHERE zamID=’id21’”, zmena);

EXEC SQL EXECUTE IMMEDIATE :buffer;

Poznámka:● premenná zmena nemusí byť hostovanou premennou● nie je potrebné používať EXEC SQL a zakončovací znak ;

Príkazy PREPARE a EXECUTE (typ 2)optimalizácia dotazu, plán prevedeniaEXECUTE IMMEDIATE - jednorazové dotazyPREPARE a EXECUTE - viackrátEXEC SQL PREPARE nazovStatementu FROM [host premenna | literal]

EXEC SQL EXECUTE nazovStatementu [USING premenna[:indikacna_prem] [,...] | USING DESCRIPTOR nazov_deskriptoru]

● USING - parametry (placeholders)○ lubovolne mená○ nie sú deklarované

EXEC SQL PREPARE prikaz FROM “INSERT INTO Zamestnanec (zamID, meno) VALUES (:v1,:v2)”;

Rozdiely vo vykonaní prikazov

(a) statické SQL(b) dynamické SQL

Príklad PREPARE a EXECUTEEXEC SQL BEGIN DECLARE SECTION;

char buffer[100];

float novaMzda;

char zamID[6];

EXEC SQL END DECLARE SECTION;

sprintf(buffer,”UPDATE Zamestnanec SET mzda = :n_mzda WHERE zamID = :cislo_Zam”);

EXEC SQL PREPARE stmt FROM :buffer;

do{

/*nacitanie ID zamestnanca a hodnoty jeho platu*/

EXEC SQL EXECUTE stmt USING :novaMzda, :zamID;

printf(“Dalsi? (A/N)”);

scanf(“%c”,viac);

}until(viac != ‘A’);

Kurzory v dyn. dotazoch (typ 3)

Používame PREPARE s definíciou kurzora:PREPARE nazovStatementu FROM { :hostPremenna | literal }; DECLARE nazovKurzora [SCROLL] CURSOR FOR nazovStatementu; OPEN nazovKurzora [USING zoznamHostPremennych]; FETCH [ FIRST|PRIOR|NEXT|LAST|CURRENT|RELATIVE n |ABSOLUTE n ] nazovKurzora INTO zoznamHostPremennych; CLOSE nazovKurzora;

Kurzory v dyn. dotazoch (typ 3)Pozn.: DECLARE … USING - nahradi parametrymôžeme definovať mená DB objektov za behu, ale nesmieme používať hostované premenná:● nesprávne:

/* definicia premennej nazovTabulky */PREPARE zamest_stmt “SELECT zamID, meno FROM :nazovTabulky”DECLARE zamest_kurzor CURSOR FOR zamest_stmt;...

● správne:/* definicia premennej buffer */sprintf(buffer,”SELECT zamID, meno FROM %s ”, nazovTabulky);PREPARE zamest_stmt FROM :buffer;

Príklad programu s dotazom typu 3/* lokalne premenna vyskytujuce v programu */

VARCHAR prikaz[80];

VARCHAR zam_meno[10];

/* pripojenie k DB, akcia pri vyskytu chyboveho stavu */

strcpy(prikaz.arr, “SELECT meno FROM Zamestnanec WHERE oddelenie = :v1”);

prikaz.len=strlen(prikaz.arr);

EXEC SQL PREPARE s FROM :prikaz;

EXEC SQL DECLARE c CURSOR FOR s; /* parameter zatial nenastaveny */

EXEC SQL OPEN c USING :c_odd; /* paramater v1 dostane hodnotu */

Príklad programu s dotazom typu 3EXEC SQL WHENEVER NOT FOUND DO break;

for(;;){

EXEC SQL FETCH c INTO :zam_meno;

zam_meno.arr[zam_meno.len] = ‘\0’;

puts((char *) zam_meno.arr);

}

EXEC SQL CLOSE c;

EXEC SQL COMMIT RELEASE;

Originál príkladu: http://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm#1099

Dotazy s deskriptorom (typ 4)Neznámy pocet stlpcov a host premenných.Sekvencia prikazov:EXEC SQL PREPARE nazov_stmt FROM {:premenna | literál }EXEC SQL DECLARE nazov_kurzoru CURSOR FOR nazov_stmt;EXEC SQL DESCRIBE BIND VARIABLES FOR nazov_stmt INTO deskriptor_premennych;EXEC SQL OPEN nazov_kurzoru [USING DESCRIPTOR deskriptor_premennych]EXEC SQL DESCRIBE [SELECT LIST FOR] nazov_stmt INTO select_deskriptor;EXEC SQL FETCH nazov_kurzoru USING DESCRIPTOR select_deskriptor;EXEC SQL CLOSE nazov_kurzoru;

Pripadne rozne kombinácie OPEN a FETCH typu 3 a 4.

Deskriptor v dotazu - štandard ISO

SQL Descriptor Area - struktura na popis dotazovALLOCATE DESCRIPTOR nazov_deskriptoru [WITH MAX poc_vyskyt]

DEALLOCATE DESCRIPTOR nazov_deskriptoru

Získanie a nastavenie položiek v SQLDA:GET DESCRIPTOR nazov_deskriptoru {host_prem=COUNT | VALUE cislo_polozky premenna_pre_hodnotu = nazov_polozky [,...] }

SET DESCRIPTOR nazov_deskriptoru {host_prem=COUNT | VALUE cislo_polozky nazov_polozky = premenna_pre_hodnotu[,...] }

Caste nazvy poloziek: TYPE, LENGTH, INDICATOR, DATA, PRECISION, SCALE, NAME, NULLABLE.

ISO typové kódyTyp SQL ISO Kód

CHARACTER 1

NUMERIC 2

INTEGER 4

FLOAT 6

DOUBLE PRECISION 8

CHARACTER VARYING 12

DECIMAL 3

SMALLINT 5

REAL 7

DATE 9

Príklad programu pre dotaz typu 4Program vypíše čísla zamestnancov, ktorí pracujú v danom oddelení.char *selectStmt=”SELECT cisloZam FROM Zamestnanec WHERE oddelenie = :oddelenieData”

int cisloZamType=12, cisloZamLength=5, oddelenieType=12, oddelenieLength=4;

char * oddelenieData = ‘B001’;

char cisloZamData[6];

EXEC SQL ALLOCATE DESCRIPTOR ‘inSQLDA’;

EXEC SQL ALLOCATE DESCRIPTOR ‘outSQLDA’;

EXEC SQL PREPARE s FROM :selectStmt;

EXEC SQL DESCRIBE INPUT s USING DESCRIPTOR ‘inSQLDA’;

EXEC SQL SET DESCRIPTOR ‘inSQLDA’ VALUE 1 TYPE = :oddelenieType, LENGTH = :oddelenieLength, DATA = :oddelenieData;

Príklad programu pre dotaz typu 4EXEC SQL DECLARE zamKurzor CURSOR FOR s;

EXEC SQL OPEN zamKurzor USING DESCRIPTOR ‘inSQLDA’;

EXEC SQL DESCRIBE OUTPUT s USING DESCRIPTOR ‘outSQLDA’;

EXEC SQL SET DESCRIPTOR ‘outSQLDA’ VALUE 1 TYPE = :cisloZamType, LENGTH = :cisloZamLength, DATA = :cisloZamData;

for(;;){

EXEC SQL FETCH zamKurzor INTO DESCRIPTOR ‘outSQLDA’;

EXEC SQL GET DESCRIPTOR ‘outSQLDA’ VALUE 1 :cisloZamData = DATA;

printf(“Zamestnanec : %s\n”, cisloZamData);

}

Príkaz DECLARE STATEMENTEXEC SQL [AT nazov_db] DECLARE nazov_stmt STATEMENT;

Pouzitie:● neštandardné miesto databáze (typ 2,3,4)

○ EXEC SQL AT vzdialena_db DECLARE sql_stmt STATEMENT;EXEC SQL PREPARE sql_stmt FROM :dyn_sql;EXEC SQL EXECUTE sql_stmt;

● neštandardné poradie príkazov (typ 3,4)○ EXEC SQL DECLARE sql_stmt STATEMENT;

EXEC SQL DECLARE zam_kurzor CURSOR FOR sql_stmt;EXEC SQL PREPARE sql_stmt FROM :dyn_sql;

Ďakujem za pozornosť!