ˇ systémy Databázové a informacní
1/9
ˇ 2. cvicení: PL/SQL
ˇ DATABÁZOVÉ A INFORMACNÍ SYSTÉMY
Katedra informatiky FEI VŠB – Technická univerzita Ostrava
2012/2013
ˇ systémy Databázové a informacní
2/9
ˇ 2. cvicení: PL/SQL
TABULKA S T U D E N T Pokud nebude ˇreˇceno jinak budou se všechny pˇríkazy týkat tabulky Student: CREATE TABLE Student ( login CHAR(6) PRIMARY fname VARCHAR(30) NOT lname VARCHAR(50) NOT email VARCHAR(50) NOT tallness INT NOT NULL);
KEY, NULL, NULL, NULL,
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 1: Rˇ ÍDÍCÍ KONSTRUKCE 1. Pˇridejte do tabulky Student atribut isTall, který bude nabývat hodnoty 0 nebo 1. 2. Vytvoˇrte proceduru IsStudentTall s jedním parametrem p_login, která nalezne záznam s daným ˇ hodnotu atributu isTall na 0 loginem. Nastaví u nej ˇ pokud je atribut tallness menší než jeho prum ˚ erná ˇ hodnota a hodnotu 1 v opaˇcném pˇrípade (pˇríkaz IF 1 ). 3. Vytvoˇrte funkci LoginExist s jedním parametrem p_login, která vrátí true pokud existuje záznam s loginem p_login. Použijte funkci LoginExist k rozšíˇrení procedury AddStudent2, která bude vytváˇret login tak dlouho dokud nenalezne nepoužitý login (pˇríkaz LOOP2 ). 1 2
Oracle: PL/SQL Language Reference, str. 139. Oracle: PL/SQL Language Reference, str. 139.
3/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 2: K URZOR
1. Upravte proceduru IsStudentTall aby procházela všechny záznamy a nastavovala pˇríslušnou hodnotu atributu isTall. Procedura tedy bude bez parametru. ˚ Využijte typ student%ROWTYPE a pˇríkazy OPEN, FETCH, CLOSE3 . 2. Pˇrepište proceduru IsStudentTall aby používala cyklus FOR4 .
3 4
Oracle: PL/SQL Language Reference, str. 217. Oracle: PL/SQL Language Reference, str. 229.
4/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 3: Z ÁLOHA TABULKY 1. Vytvoˇrte uloženou proceduru CopyTableStructure s jedním parametrem p_table_name, která vytvoˇrí kopii (pouze atributy) tabulky se jménem p_table_name. Nová tabulka bude prázdná, bude mít pˇríponu ’_old’ a bude mít stejná jména atributu˚ (a stejné typy) jako puvodní ˚ tabulka. ˇ Postup rˇešení: Jména a typy jednotlivých atributu˚ naˇctete ˇ ze systémového katalogu. Sestavte do ˇretezce pˇríkaz CREATE TABLE, kterým vytvoˇríte novou tabulku. Použijte ˇ pˇríkaz Execute Immediate5 k jeho spuštení. 2. Vytvoˇrte uloženou proceduru CopyTable s jedním parametrem p_table_name, která nejprve vytvoˇrí kopii tabulky a pak zkopíruje také všechny záznamy. 5
Oracle: PL/SQL Language Reference, str. 264.
5/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 4: V ÝJIMKY 1/2 Poznámka: pokud nebude rˇeˇceno jinak budou se všechny pˇríkazy týkat tabulky Student. 1. Upravte funkci LoginExist tak, aby vrátila false ˇ že student s daným loginem v skuteˇcneˇ jen v pˇrípade, tabulce není. V pˇrípadeˇ jiné chyby výjimku propagujte6 . 2. Napišteˇ funkci InsertStudent, která vloží záznam ˇ že záznam byl úspešn ˇ eˇ studenta a vrátí true v pˇrípade, vložen. V pˇrípadeˇ že login studenta již v tabulce existuje, tak vrátí false. Využijte zachycení výjimky DUP_VAL_ON_INDEX. Použijte tuto funkci v proceduˇre AddStudent3, která bude fungovat stejneˇ jako AddStudent2. 6
Oracle: PL/SQL Language Reference, str. 404.
6/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 4: V ÝJIMKY 2/2
1. Upravte proceduru StudentBecomeTeacher aby se byla schopna vypoˇrádat i se záznamy v tabulce Student_Course, které se odkazují na pˇresunovaného studenta. (Deklarujte výjimku asociovanou s cˇ íslem a odchyt’te7 ).
7
Oracle: PL/SQL Language Reference, str. 410.
7/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 5: T RIGGERY 1. Vytvoˇrte trigger OperationCount který zaznamená do tabulky Statistics poˇcty operací insert, update a delete. ˇ Tabulka Statistics bude tabulka se dvema atributy. První atribut operation bude pˇredstavovat typ operace a druhý atribut operationCount bude pˇredstavovat poˇcty daných operací (Použijte detekci DML operace v triggeru8 ). 2. Pˇridejte atribut kapacita do tabulky Kurz, který bude pˇredstavovat maximální kapacitu daného kurzu. Vytvoˇrte trigger kontrolaKapacity, který vypíše varovnou ˇ že je kapacita kurzu pˇrekroˇcena. hlášku v pˇrípade, 3. V triggeru kontrolaKapacity deklarujte výjimku capacity_exceeded, kterou vyvolejte v pˇrípadeˇ pˇrekroˇcení kapacity9 . 8 9
Oracle: PL/SQL Language Reference, str. 344. Oracle: PL/SQL Language Reference, str. 413.
8/9
ˇ systémy Databázové a informacní ˇ 2. cvicení: PL/SQL
Ú KOL 6: BALÍKY PROCEDUR A FUNKCÍ 1. Vytvoˇrte balík student_package, který bude obsahovat všechny funkce a procedury, které se vztahují k tabulce Student. Vložte do tohoto balíku všechny pˇríslušné funkce a procedury1011 . 2. Vložte do balíku výjimku capacity_exceeded z pˇredchozího úkolu a použijte tuto výjimku v triggeru kontrolaKapacity. Pro vkládání záznamu do tabulky studijniPlan si napište anonymní proceduru, která odchytí výjimku capacity_exceeded.
10 11
Oracle Academy Advanced PL/SQL Student Guide, str. 35. Oracle: PL/SQL Language Reference, str. 388.
9/9
