DATABÁZOVÉ A INFORMAČNÍ SYSTÉMY

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní

ˇ DATABÁZOVÉ A INFORMACNÍ SYSTÉMY Michal Krátký, Radim Baˇca Katedra informatiky FEI VŠB – Technická univerzita Ostrava

2012/2013

1/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní DAIS

ˇ O BSAH P REDNÁŠKY ˇ u, 1. Úvod, struktura pˇredmet ˚ podmínky zápoˇctu ˇ 2. Architektura SRBD 3. Úvod, vlastnosti. 4. Blok PL/SQL. 5. Anonymní procedury, pojmenované procedury a funkce. ˇ 6. Rídící konstrukce. 7. Statické a dynamické PL/SQL. 8. Výjimky

2/62


M ICHAL K RÁTKÝ

I

tel.: +420 596 993 239

I

room: A1004

I

e-mail: [email protected]

I

web: http://www.cs.vsb.cz/kratky/

I

ˇ u: web databázových pˇredmet ˚ http://dbedu.cs.vsb.cz/ uživatelské jméno/heslo: student/tuodbedu

3/62


4/62

DAIS

ˇ S TRUKTURA DATABÁZOVÝCH KURZ U˚ BAKALÁ RSKÉHO STUDIA 1. Úvod do databázových systému˚ (3s): konceptuální model, relaˇcní datový model, SQL, funkˇcní analýza. ˇ systémy (4s): zotavení, 2. Databázové a informacní ˇ transakce; fyzická implementace SRBD, datová vrstva IS. 3. Databázové systémy (5s): optimalizace a vykonávání dotazu, ˚ fyzický návrh databáze; bezpeˇcnost; XML datový model. 4. Administrace databázových systému˚ (6s): instalace, ˇ ˇ nejvýznamnejších ˇ konfigurace a ladení SRBD.


ˇ S RBD

Duraz ˚ bude kladen na standard SQL, nicméneˇ v každém kurzu se budeme zabývat ruznými ˚ databázovými systémy: 1. UDBS: SQL Server, 2. DAIS, DBS: Oracle, SQL Server, 3. ADBS: Oracle, SQL Server, MySQL. Duraz ˚ bude kladen na praktickou implementaci.

5/62


ˇ ˚ P REDNÁŠEJÍCÍ DATABÁZOVÝCH KURZ U

I

Radim Baˇca

I

Radoslav Fasuga

I

Peter Chovanec

I

ˇ u˚ Michal Krátký, garant databázových pˇredmet bakaláˇrského studia

6/62


ˇ ˇ P ODMÍNKY UD ELENÍ ZÁPO CTU

I

ˇ je ukonˇcen zkouškou. Minimum 30 z 55b. Pˇredmet

I

Maximální poˇcet bodu˚ na zápoˇcet je 45: 1. 2× praktickým test (PL/SQL, T-SQL). Minimum: 6b z 10. 2. Implementace informaˇcního systému nad platformou .NET (nebo J2EE). Duraz ˚ bude kladen na implementaci datové vrstvy. Minimum 13b z 25.

7/62


ˇ ˇ P ROGRAM P REDNÁŠEK A CVI CENÍ

I

PL/SQL, T-SQL

I

Zotavení, transakce, ACID

I

ˇ Objektove-relaˇ cní datový model

I

ˇ Datová vrstva informaˇcního systému, objektove-relaˇ cní mapování

I

Webový informaˇcní systém: ASP.NET (J2EE)

I

ˇ Fyzický model SRBD, vykonávání dotazu˚

8/62


ˇ Z ÁPO CET 1/2

I

První polovina semestru bude ukonˇcena praktickým testem z PL/SQL (4. týden) a T-SQL (7. týden). I

Je možná pouze jedna oprava.

I

ˇ logický model, Týden pˇred testem bude zveˇrejnen vylosované zadání musí být vypracováno do 1h:15min.

I

Pouze funkˇcní a správné ˇrešení (kód jde pˇreložit, spustit a dává korektní výsledky) je hodnoceno poˇctem bodu˚ vyšším než je minimum.

9/62


ˇ Z ÁPO CET 2/2

I

Druhá polovina semestru bude ukonˇcena odevzdáním semestrálního projektu - webového informaˇcního systému s durazem ˚ na implementaci datové vrstvy: I

ASP.NET (J2EE).

I

Pro obeˇ platformy jsou k dispozici šablony projektu. ˚

10/62


D OSTUPNÝ HARDWARE A SOFTWARE ˇ u: 1. Servery pro výuku databázových pˇredmet ˚ I

dbedu.cs.vsb.cz: 2 × Intel Xeon X5670 2.93GHz 6C, ˇ celkem tedy 12 jader, 64GB pameti

I

dbsys.cs.vsb.cz: 2 × Intel Xeon E5 2690 2.9GHz, 2×12C, ˇ ˇ (pro každý SRBD 288GB pameti vyhraženo pˇribližneˇ 9GB).

2. Oracle 11G R2 x64 Enterprise Edition, SQL Server 2012 3. Oracle SQL Developer1 , Microsoft Management Studio

1

http://www.oracle.com/technetwork/developer-tools/ sql-developer/overview/index.html

11/62


O RACLE ACADEMY

1. Od srpna 2009 je katedra úˇcastníkem Oracle Academy. 2. Studenti mají pˇrístup k materiálum ˚ komerˇcních kurzu, ˚ dostupné na http://dbedu.cs.vsb.cz/ 3. V záˇrí budou otevˇreny kurzy Database Design a Database Programming. Pokud máte zájem, napište mail Radoslavu Fasugovi ([email protected]).

12/62


13/62

DAIS

L ITERATURA 1. M. Krátký, R. Baˇca: Databázové systémy, http://dbedu.cs.vsb.cz/, odkaz Literatura/Studijní literatura - momentálneˇ 258 stran pokrývající všechny kurzy, bude se postupneˇ rozšiˇrovat. 2. C.J. Date: An Introduction to Database Systems. Addison Wesley, 2003. 3. H. Garcia-Molina, J.D. Ullman, J. Widom: Database systems: the complete book. Prentice Hall, 2002. 4. J. Pokorný: Dotazovací jazyky. Nakladatelství Univerzity Karlovy, Praha, ISBN 80-246-0497-3, 2002. 5. Dokumentace k Oracle 11g: http://www.oracle.com/pls/db112/portal.all_books.


A RCHITEKTURA IS Prezentační vrstva

I

UDB – DBMS

I

DAIS – DBMS, datová vrstva, model, prezentaˇcní vrstva

I

DBS – DBMS

I

ADBS – DBMS

Model aplikace

Objektově-relační mapování (ORM)

Datová vrstva

DBMS

14/62


15/62

DAIS

ˇ A RCHITEKTURA S RBD

Uživatel/aplikace

dotazy, modifikace dat (příkazy JMD) Překladač dotazu plán vyhodnocení dotazu Vyhodnocování dotazu

požadavky na soubory a záznamy Manažer indexů, souborů a záznamů

požadavky na stránky Manažer vyrovnávacích pamětí

čtení/zápis stránky Manažer úložiště dat

Úložiště dat

Administrátor databáze

příkazy pro transakce

příkazy JDD

Transakční manažer

JDD překladač

Manažer log souborů a zotavení

Manažer paralelního přístupu

Metadata statistiky

stránky logovacícho souboru

Vyrovnávací paměti

Tabulka zámků

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ PL/SQL, blok, promenné

ˇ P ROCEDURÁLNÍ ROZŠÍ RENÍ SQL – PL/SQL

1. Jazyk PL/SQL2 pˇredstavuje rozšíˇrení jazyka SQL o procedurální rysy. 2. Syntaxe PL/SQL je založena na jazyku ADA. 3. Podobná procedurální rozšíˇrení pak byla vyvinuta i pro další relaˇcní databáze: Transact-SQL pro Sybase a Microsoft SQL Server, PL/pgSQL pro PostgreSQL a SQL PL pro IBM DB2.

2

http://www.oracle.com/pls/db112/portal.all_books, PL/SQL Language Reference

16/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ PL/SQL, blok, promenné

V LASTNOSTI PL/SQL Výhody: I

Snadná a efektivní kombinace procedurální logiky a SQL.

I

Možné nižší množství pˇrenášených dat (pˇrenáší se jen ˇ koneˇcné výsledky) – PL/SQL kód je uložen v SRBD.

I

Sdílení kódu mezi aplikacemi.

I

ˇ Nezávislost na platforme.

Nevýhody: I

ˇ Horší pˇrenositelnost aplikace mezi SRBD ruzných ˚ výrobcu˚ (to je ovšem problém i na úrovni SQL).

I

ˇ Potˇrebujeme pˇrenositelnost mezi SRBD?

17/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ PL/SQL, blok, promenné Blok PL/SQL

Z ÁKLADNÍ STRUKTURA PL/SQL BLOKU

ˇ 1. DECLARE – nepovinná deklarace lokálních promenných a kurzoru, ˚ 2. BEGIN – povinné otevˇrení bloku pˇríkazu˚ uložené procedury, 3. EXCEPTION – nepovinné zachytávání výjimek, 4. END – povinné ukonˇcení bloku procedury.

18/62


ˇ P RÍKLAD , T RANSAKCE V PL/SQL Pˇri vložení nové osoby chceme zárovenˇ pˇridat této osobeˇ roli "Autor" (která má id=1). Transakce tedy bude vypadat takto: BEGIN INSERT INTO Person VALUES( ’ sob28 ’ , ’ j a n . sobota@vsb . cz ’ , ’ heslo ’ , ’ Jan ’ , NULL, ’ Sobota ’ , NULL, NULL ) ; INSERT INTO PersonRole ( ’ son28 ’ , 1 ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK ; END;

Pokud selže vložení osoby (napˇr. osoba je už v systému evidována), pak vložení role osoby nedává smysl a celá transakce bude zrušena.

19/62


P OZNÁMKY BEGIN −− jednoradkova poznamka INSERT INTO Person VALUES( ’ sob28 ’ , ’ j a n . sobota@vsb . cz ’ , ’ heslo ’ , ’ Jan ’ , NULL, ’ Sobota ’ , NULL, NULL ) ; /∗ ∗ v i c e r a d k o v a poznamka ∗/ INSERT INTO PersonRole ( ’ son28 ’ , 1 ) ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK ; END;

20/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ PL/SQL, blok, promenné SET AUTOCOMMIT ON

SET AUTOCOMMIT ON/OFF

I

ˇ SET AUTOCOMMIT ON, bude každý SQL Po spuštení pˇríkaz proveden v jedné transakci. Tzn. operace COMMIT a ROLLBACK pozbývají významu.

I

ˇ SET AUTOCOMMIT OFF, zaˇcíná transakce po Po spuštení ukonˇcení transakce pˇredchozí a konˇcí operacemi COMMIT nebo ROLLBACK.

21/62


22/62

ˇ PL/SQL, blok, promenné ˇ Promenné v PL/SQL

ˇ P ROM ENNÉ ˇ V sekci DECLARE mužeme ˚ definovat promenné, jenž mohou být použity v proceduˇre. Definice má následující tvar: jmeno_promenne typ_promenne [NOT NULL : = hodnota ] ; Kde: I

ˇ jmeno_promenne je název promenné, který má obvykle 3 prefix ’v_’ ,

I

ˇ typ_promenne je platný typ promenné,

I

hodnota je nepovinná cˇ ást, která definuje výchozí ˇ hodnotu promenné.

3

Oracle Academy: Oracle Database 10g: Advanced PL/SQL, Student Guide. str. 26.


23/62


ˇ P ROM ENNÉ 1/2 I

ˇ ˇ Promenné mužeme ˚ využít pˇri behu PL/SQL kódu pro uložení doˇcasných hodnot a pro manipulaci s nimi.

I

ˇ ˇ Hodnota muže ˚ být do promenné zapsána nekolika zpusoby: ˚ Syntaxe jmeno_promenne := hodnota SELECT sloupec INTO jmeno_promenne FROM jmeno_tabulky

I

Pˇríklad v_vek := 20 SELECT vek INTO v_vek FROM student WHERE login LIKE ’bon007’

SELECT musí vracet práveˇ jeden záznam, jinak je vygenerována výjimka NO_DATA_FOUND resp. TOO_MANY_ROWS.

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ PL/SQL, blok, promenné ˇ Promenné v PL/SQL

ˇ P ROM ENNÉ 2/2

I

ˇ Pˇri práci s promennou mužeme ˚ využít standardní ˇ ˇ aritmetické operátory v pˇrípade císel.

I

ˇ u˚ a Dále mužeme ˚ využít operátor || pro konkatenaci ˇretezc také standardní SQL funkce (TO_CHAR, TO_DATE, SUBSTR, LENGTH, atd.).

24/62


25/62


ˇ ˇ P ROM ENNÉ , P RÍKLAD DECLARE v_fname VARCHAR2( 2 0 ) ; v_lname VARCHAR2( 2 0 ) ; v_email VARCHAR2( 6 0 ) ; BEGIN SELECT fname , lname INTO v_fname , v_lname FROM s t u d e n t WHERE l o g i n = ’ bon007 ’ ; v_email : = v_fname | |

’. ’

| | v_lname | |

UPDATE s t u d e n t s e t e m a i l = v_email WHERE l o g i n = ’ bon007 ’ ; END;

’@vsb . cz ’ ;


O PERÁTOR %TYPE I

ˇ Zadávání datových typu˚ promenných muže ˚ být problematické: datové typy velmi cˇ asto kopírují datové typy atributu˚ tabulek.

I

ˇ eˇ datového typu atributu v tabulce pak musíme Pˇri zmen ˇ kód. menit

I

Proto namísto konkrétního datového typu používáme operátor %TYPE.

Pˇríklad: v_lg Student.login%TYPE; ... ˇ Kde promenná v_lg je stejného typu jako atribut login tabulky Student.

26/62


O PERÁTOR %ROWTYPE I

ˇ V nekterých pˇrípadech používáme strukturovaný datový ˇ typ, který obsahuje promenné stejných datových typu˚ jako jsou datové typy atributu˚ tabulek.

I

Instance takového strukturovaného datového typu reprezentuje záznam z tabulky.

I

V tomto pˇrípadeˇ mužeme ˚ použít operátor %ROWTYPE. ˇ Výsledkem je typ se stejnými promennými a typy jako má tabulka na kterou je operátor aplikován.

Pˇríklad: v_st Student%ROWTYPE; ... ˇ ˇ Kde promenná v_st obsahuje stejné promenné a datový typy jako tabulka Student.

27/62


ˇ ˇ P ROM ENNÉ , P RÍKLAD

DECLARE C_VCHAR_MAXLEN CONSTANT NUMBER : = 32767; v_date DATE : = SYSDATE ; v_number NUMBER NOT NULL : = 1 ; v _ s t u d e n t Student%ROWTYPE; v_name Student . name%TYPE ; BEGIN ... END;

28/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní Procedury

P ROCEDURY

ˇ ˇ PL/SQL umožnuje vytváˇret nekolik typu˚ procedur. V zásadeˇ se ˇ liší pˇredevším zpusobem ˚ jakým jsou spoušteny: I

Anonymní procedury

I

Pojmenované procedury

I

Pojmenované funkce

29/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní Procedury Anonymní Procedury

A NONYMNÍ PROCEDURY

I

Anonymní procedury jsou nepojmenované procedury, které nemohou být volány z jiné procedury.

I

Tyto procedury mohou být uloženy v souboru, nebo pˇrímo ˇ z klienta (napˇr. zapsány na pˇríkazový ˇrádek, a spoušteny Oracle SQL Developer).

I

Anonymní procedury nejsou pˇredkompilovány a mohou být proto pomalejší než pojmenované procedury a funkce.

30/62


31/62

Procedury Anonymní Procedury

ˇ P RÍKLAD 1

ˇ Mejme anonymní proceduru, která vkládá email do tabulky Email s jedním atributem email typu VARCHAR2(30). DECLARE v_name VARCHAR2( 3 0 ) : = ’ m i c h a l . kratky@vsb . cz ’ ; BEGIN INSERT INTO Email VALUES ( v_name ) ; END;

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní Procedury Anonymní Procedury

ˇ P RÍKLAD 2 V druhé proceduˇre vložíme do tabulky Email adresu studenta z tabulky Student s emailem bon007. Tabulka Student má dva atributy: email typu VARCHAR2(30) a login typu CHAR(6). DECLARE v _ l o g i n CHAR( 6 ) : = ’ bon007 ’ ; BEGIN INSERT INTO e m a i l y SELECT e m a i l FROM Student WHERE l o g i n = v _ l o g i n ; END;

32/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní Procedury Pojmenované procedury

P OJMENOVANÉ PROCEDURY

I

Pojmenované procedury obsahují hlaviˇcku se jménem a parametry procedury.

I

Takovouto proceduru je možné volat z jiných procedur ˇ pˇríkazem EXECUTE (zkráceneˇ EXEC). nebo spouštet

I

Na rozdíl od anonymních procedur jsou pojmenované procedury pˇredkompilovány a uloženy v databázi.

33/62


34/62

Procedury Pojmenované procedury

S TRUKTURA POJMENOVANÉ PROCEDURY 1/4 CREATE [OR REPLACE] PROCEDURE jmeno_procedury [ ( jmeno_parametru [ mod ] datovy_typ , . . . IS | AS ˇ d e f i n i c e l o k á l n í c h prom enných BEGIN t eˇ l o procedury END [ jmeno_procedury ] Kde: I

4

jmeno_parametru je název parametru, který má obvykle prefix ’p_’ 4 .

Oracle Academy: Oracle Database 10g: Advanced PL/SQL, Student Guide. str. 26.

)]


35/62



mod je mód parametru, který muže ˚ mít hodnotu IN (vstupní ˇ ˇ promenná), OUT (výstupní promenná) nebo IN OUT ˇ (vstupneˇ výstupní promenná).

)]


36/62



ˇ ˇ datovy_typ je platný datový typ promenné. Promenné ˇ bez závorek, typu VARCHAR2 nebo NUMBER se uvádejí které by specifikovaly jejich velikost.

)]


37/62


S TRUKTURA POJMENOVANÉ PROCEDURY 4/4 CREATE [OR REPLACE] PROCEDURE jmeno_procedury [ ( jmeno_parametru [ mod ] datovy_typ , . . . IS | AS ˇ d e f i n i c e l o k á l n í c h prom enných BEGIN t eˇ l o procedury END [ jmeno_procedury ] I

ˇ Vstupní promenné jsou v PL/SQL implicitneˇ pˇredávány ˇ ˇ odkazem, za bežných okolností ale není možné menit ˇ ˇ hodnoty techto promenných.

I

Za klíˇcovým slovem AS (nebo IS) pak mužeme ˚ ˇ specifikovat lokální promenné.

)]


ˇ P RÍKLAD 1/2 Tato pojmenovaná procedura vkládá email studenta jehož login pˇredáváme jako parametr. CREATE OR REPLACE PROCEDURE I n s e r t E m a i l ( p _ l o g i n VARCHAR2) AS v_email VARCHAR2( 6 0 ) ; BEGIN SELECT e m a i l INTO v_email FROM Student WHERE l o g i n = p _ l o g i n ; INSERT INTO Email VALUES( v_email ) ; END;

38/62


ˇ P RÍKLAD 2/2

Uloženou proceduru pak mužeme ˚ (pokud máme právo pˇrístupu) spustit pˇríkazem EXECUTE (nebo EXEC). EXECUTE I n s e r t E m a i l ( ’ jan440 ’ ) ;

39/62


ˇ P REKLAD PROCEDUR

I

Uloženou proceduru v klientském prostˇredí (jako je Oracle ˇ SQL Developer) vytvoˇríme spuštením kódu zaˇcínajícím CREATE a konˇcícím END;.

I

ˇ Takto spuštená procedura je pˇreložena a uložena v databázi.

I

Pˇri pˇrekladu se mohou objevit chyby, které zobrazíme v Oracle SQL Developeru v okneˇ log.

40/62


41/62

Procedury Funkce

F UNKCE I

ˇ ˇreˇceno pojmenované funkce) jsou velmi Funkce (pˇresneji podobné procedurám.

I

Oproti procedurám specifikují návratový typ a musí vracet hodnotu.

Struktura funkce: CREATE [OR REPLACE] FUNCTION jmeno_funkce [ ( jmeno_parametru [ mod ] datovy_typ , RETURN n a v r a t o v y _ d a t o v y _ t y p IS | AS ˇ d e f i n i c e l o k á l n í c h prom enných BEGIN t eˇ l o procedury END [ jmeno_procedury ]

...

)]

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní Procedury Funkce

ˇ P RÍKLAD 1/2 Tato pojmenovaná funkce vrací email studenta jehož login pˇredáváme jako parametr. CREATE OR REPLACE FUNCTION GetStudentEmail ( p _ l o g i n IN s t u d e n t . l o g i n%TYPE) RETURN s t u d e n t . e m a i l%TYPE AS v_email s t u d e n t . e m a i l%TYPE ; BEGIN SELECT e m a i l INTO v_email FROM s t u d e n t WHERE l o g i n = p _ l o g i n ; RETURN v_email ; END GetStudentEmail ;

42/62


ˇ P RÍKLAD 2/2

Uloženou proceduru pak mužeme ˚ (pokud máme právo pˇrístupu) spustit pˇríkazem EXECUTE. SET SERVEROUTPUT ON; EXECUTE DBMS_OUTPUT. PUT_LINE ( GetStudentEmail ( ’ sob28 ’ ) ) ; Prvním pˇríkazem povolujeme standardní výstup serveru a druhým pˇríkazem voláme uloženou funkci a vypisujeme výsledek.

43/62


VOLÁNÍ PL/SQL PROCEDUR A FUNKCÍ I

Pomocí anonymního bloku: BEGIN InsertEmail(’jan440’); END;

I

Pomocí SQL: SELECT InsertEmail(’jan440’) FROM DUAL;

I

V Oracle SQL Developer cˇ i SQL*Plus: EXEC InsertEmail(’jan440’);

44/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Základní rˇídící konstrukce

ˇ Z ÁKLADNÍ RÍDÍCÍ KONSTRUKCE

I

ˇ V PL/SQL mužeme ˚ použít nekolik základních ˇrídících konstrukcí jako je podmínka a cyklus.

I

Jejich syntaxe se výrazneˇ neliší od podobných konstrukcí v jiných programovacích jazycích.

45/62


P ODMÍNKA

Podmínka má následující syntaxi: I F podminka1 THEN p ˇr í k a z y [ ELSIF podminka2 THEN p ˇr í k a z y ] [ ELSE p ˇr í k a z y ] END I F ;

46/62


C YKLY 1/3 V zásadeˇ máme k dispozici tˇri druhy cyklu. ˚ I

První typ cyklu se ukonˇcuje pomocí klíˇcového slova EXIT.

I

Podmínka ukonˇcení cyklu muže ˚ být zapsána pomocí EXIT WHEN podminka.

I

Takto mužeme ˚ zapsat cyklus s podmínkou na konci.

LOOP p ˇr í k a z y c y k l u [ EXIT ; | EXIT WHEN podminka ; ] END LOOP;

47/62


C YKLY 2/3

Dalším typem je cyklus s podmínkou na zaˇcátku. WHILE podminka LOOP p ˇr í k a z y c y k l u END LOOP;

48/62


C YKLY 3/3

ˇ eˇ používaným typem cyklu je cyklus FOR, kde Posledním bežn ˇ pˇredem známe poˇcet iterací. Promenná value1 pˇredstavuje ˇ výchozí hodnotu promenné jmeno_promenne a value2 koncovou hodnotu. FOR jmeno_promenne IN [REVERSE] value1 . . value2 LOOP p ˇr í k a z y c y k l u END LOOP;

49/62


ˇ C YKLY, P RÍKLAD

ˇrádky výsledku dotazu. Pruchod ˚ BEGIN FOR radek IN (SELECT a , b from TAB) LOOP I F radek . a = . . . THEN ; END I F ; END LOOP; END;

50/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Statické a dynamické PL/SQL

S TATICKÉ PL/SQL V PL/SQL bloku nemužeme ˚ pˇrímo volat všechny dostupné SQL pˇríkazy. Pˇríkazy, které lze volat v PL/SQL pˇrímo nazýváme statické pˇríkazy PL/SQL. Mezi statické pˇríkazy patˇrí: I

SELECT, INSERT, UPDATE, DELETE, MERGE

I

LOCK TABLE, COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

Je zˇrejmé, že mezi pˇríkazy které nemužeme ˚ volat pˇrímo jsou všechny pˇríkazy DDL.

51/62

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Statické a dynamické PL/SQL

DYNAMICKÉ PL/SQL I

ˇ Dynamické PL/SQL umožnuje sestavit a volat jakýkoli SQL ˇ pˇríkaz (na který má uživatel právo) za behu aplikace.

I

Tento zpusob ˚ muže ˚ být zejména užiteˇcný pokud pˇredem neznáme pˇresný tvar SQL pˇríkazu, který má být volán.

I

ˇ rit syntaktickou Nevýhodou je, že nelze jednoduše oveˇ správnost a sémantické vazby mezi objekty (správné datové typy, poˇcet parametru˚ atd.). Navíc se vystavujeme ˇ SQL injection! nebezpecí

I

ˇ Ve vetšin eˇ pˇrípadu˚ spouštíme dynamické PL/SQL pˇríkazem EXECUTE IMMEDIATE.

I

ˇ Upozornení: Dynamické PL/SQL používáme jen v ˇ kdy není možné použít statické PL/SQL! pˇrípade,

52/62


53/62

ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Statické a dynamické PL/SQL

ˇ DYNAMICKÉ PL/SQL, P RÍKLAD V tomto pˇríkladeˇ vytvoˇríme a odstraníme tabulky s použitím pˇríkazu EXECUTE IMMEDIATE. DECLARE v_command VARCHAR2( 5 0 ) ; BEGIN EXECUTE IMMEDIATE ’ Create t a b l e book ’ | | ’ ( i d INT UNIQUE, name VARCHAR2( 5 0 ) , ’ | | ’ a u t h o r INT REFERENCES a u t h o r ( a u t h o r _ i d ) ) ’ ; v_command : = ’DROP TABLE book ’ ; EXECUTE IMMEDIATE v_command ; END;

ˇ systémy – 1. Úvod, PL/SQL I Databázové a informacní ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Výjimky

V ÝJIMKY

I

ˇ ˇ Výjimka je chyba, která se vyskytne behem provádení PL/SQL kódu.

I

Jazyk PL/SQL nabízí vlastní mechanismus pro zpracování výjimek.

I

Výjimka muže ˚ vzniknout jak v samotném Oracle serveru ˇ nejakého ˇ (chyba provádení SQL dotazu), tak muže ˚ být vytvoˇrena samotným PL/SQL kódem.

54/62


ˇ ÁST EXCEPTION C I

Ke zpracování výjimek v PL/SQL bloku slouží cˇ ást EXCEPTION. ... BEGIN ... EXCEPTION WHEN jmeno_vyjimky THEN zpracování vyjímky END;

I

V pˇrípadeˇ chyby program automaticky skoˇcí do této cˇ ásti, konkrétneˇ do cˇ ásti, která zpracovává danou výjimku (pokud taková cˇ ást existuje).

55/62


ˇ ÁST EXCEPTION C ... BEGIN ... EXCEPTION WHEN jmeno_vyjimky THEN zpracování vyjímky END; I

ˇ V pˇrípadeˇ úspešného zpracování se výjimka již dále nepropaguje do cˇ ástí aplikace, které PL/SQL blok volaly.

I

V pˇrípadeˇ že chceme zpracovat jakoukoli výjimku (kromeˇ ˇ co již jsou zpracovány jinými WHEN pˇríkazy), pak tech namísto jmeno_vyjimky dáme klíˇcové slovo OTHERS.

56/62


57/62

ˇ Rídící konstrukce, statické vs dynamické SQL, výjimky Výjimky

V ÝJIMKY BALÍKU STANDARD ˇ V této tabulce vidíme nekteré výjimky balíku STANDARD. Jméno výjimky ACCESS_INTO_NULL

ˇ Císlo chyby ORA-06530

DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER NO_DATA_FOUND TOO_MANY_ROWS

ORA-00001 ORA-01001 ORA-01722 ORA-01403 ORA-01422

VALUE_ERROR

ORA-06502

Popis Pokus o pˇriˇrazení hodnoty do neinicializovaného objektu Pokus vložit duplicitní hodnotu Neplatná operace s kurzorem ˇ Selhala konverze cˇ ísla na ˇretezec Pˇríkaz SELECT nevrátil data Pˇríkaz SELECT INTO vrátil více než jeden ˇrádek Chybná manipulace s hodnotou


58/62


Z ACHYCENÍ VÝJIMKY Následující procedura vypíše zprávu ’Hodnota atributu login musí být unikátní!’ v pˇrípadeˇ výjimky DUP_VAL_ON_INDEX. V pˇrípadeˇ jiné výjimky vypíše chybovou hlášku dané chyby. BEGIN I n s e r t i n t o s t u d e n t ( l o g i n , fname , lname ) v a l u e s ( ’ bon007 ’ , ’ James ’ , ’ Bond ’ ) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT. p u t _ l i n e ( ’ Hodnota a t r i b u t u l o g i n musí b ý t u n i k á t n í ! ’ ) ; WHEN OTHERS THEN DBMS_OUTPUT. p u t _ l i n e ( DBMS_UTILITY .FORMAT_ERROR_STACK ) ; END;


V YVOLÁNÍ VÝJIMKY

I

ˇ PL/SQL umožnuje vyvolat výjimku v pˇrípadeˇ chyby.

I

K tomuto úˇcelu se používá klíˇcové slovo RAISE.

I

Je možné tak vyvolat standardní nebo uživatelem definovanou výjimku.

59/62


V ÝJIMKA DEFINOVANÁ UŽIVATELEM I

ˇ Stejneˇ jako promenné nebo kurzory je možné v PL/SQL bloku definovat také výjimku.

I

ˇ Výjimka se tedy deklaruje spolu s ostatními promennými. Syntaxe je následují: jmeno_vyjimky EXCEPTION;

I

Rozsah platnosti výjimky je jen pro danou proceduru.

I

Pokud budeme chtít výjimku deklarovanou v proceduˇre odchytit mimo tuto proceduru, pak je to možné jen s použitím OTHERS.

60/62


61/62


ˇ P RÍKLAD V následujícím výpise dojde k vyvolání výjimky too_many_records, která není v proceduˇre ošetˇrena. Výjimka tedy bude propagována do nadˇrazeného kódu. DECLARE too_many_records EXCEPTION ; v _ r e c o r d s INT ; BEGIN S e l e c t count ( ∗ ) i n t o v _ r e c o r d s from s t u d e n t ; I F v _ r e c o r d s > 20 THEN r a i s e too_many_records ELSE I n s e r t i n t o s t u d e n t ( l o g i n , fname , lname ) v a l u e s ( ’ bon007 ’ , ’ James ’ , ’ Bond ’ ) ; END I F ; END;


R EFERENCE

I

Portál materiálu˚ k Oracle: http://www.oracle.com/pls/ db112/portal.all_books I

PL/SQL Language Reference

I

PL/SQL Packages and Types Reference

62/62

DATABÁZOVÉ A INFORMAČNÍ SYSTÉMY

Recommend Documents