DATABÁZOVÉ SYSTÉMY I

1. DATABÁZOVÉ SYSTÉMY I 3. PROJEKT PL/SQL Tomáš Baďura ARI 2011

2. PL/SQL • PL/SQL (Procedural Language/Structured Query Language) je procedurálnínadstavbajazykaSQLodfirmyOracle. • Výsledkem je strukturální jazyk, mocnější než samotné SQL. • Program v PL/SQL se skládá z bloků, • Bloky mohou být vnořeny jeden do druhého.

3. 1. úloha • ZALOZTE SI NASLEDUJICI TABULKY A NAPLNTE JE DATY PODLE VZORU • PRACE je cizím klíčem do tabulky FUNKCE • PRISLUSNOST je cizím klíčem do tabulky BREW • pro vytváření primárních klíčů použijte sekvence Sekvence: CREATE SEQUENCE funkce_seq MINVALUE 0 START WITH 0 --počáteční hodnota nesmí být menší než minvalue INCREMENT BY 1; CREATE SEQUENCE brew_seq START WITH 99 INCREMENT BY 1; CREATE SEQUENCE emp_seq START WITH 499 INCREMENT BY 1;

4. CREATE TABLE funkce (id_f numberprimarykey, nazev varchar2(20)); BEGIN INSERT INTO funkce (id_f, nazev) VALUES (funkce_seq.NEXTVAL, 'Vedouci'); INSERT INTO funkce (id_f, nazev) VALUES (funkce_seq.NEXTVAL, 'Skladnik'); INSERT INTO funkce (id_f, nazev) VALUES (funkce_seq.NEXTVAL, 'Ucetni'); INSERT INTO funkce (id_f, nazev) VALUES (funkce_seq.NEXTVAL, 'Sladek'); INSERT INTO funkce (id_f, nazev) VALUES (funkce_seq.NEXTVAL, 'Pomocnik'); END; SELECT * FROM funkce CREATE TABLE brew (id_p numberprimarykey, jmeno varchar2(20)); BEGIN INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Litovel'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Holba'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Zubr'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Staropramen'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Gambrinus'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Pilsner'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Cerna Hora'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Budvar'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Bernard'); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,'Branik'); END; SELECT * FROM brew

5. CREATE TABLE empl (id_zamnumberprimarykey,prijmeni varchar2(20),vek number,pracenumberreferences funkce, prislusnostnumberreferencesbrew,jmeno varchar2(20), plat number); BEGIN INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Konecny',35,3,104,'Karel',15200); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Prisoudil',42,1,106,'Jiri',13600); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Kvapil',38,2,108,'Petr',14800); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Zabransky',53,4,109,'Pavel',16000); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Volejnik',46,5,105,'Milan',11300); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Hrusovsky',40,5,104,'Tomas',10500); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Balbin',41,5,100,'Vladimir',9800); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Dvorak',50,3,101,'Miroslav',12800); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Novak',56,4,102,'Martin',15800); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Novacek',35,2,103,'Jan',7900); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Hajsky',47,5,104,'Petr',7000); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Jizansky',35,4,104,'Radim',13600); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Dacicky',29,3,104,'Libor',14100); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Jansky',25,4,104,'Artur',12600); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Halek',27,3,104,'Rene',13000); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Petrik',28,2,104,'Milos',9500); INSERT INTO empl(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES (empl_seq.NEXTVAL,'Mechura',31,1,104,'Vaclav',18700); END; SELECT * FROM empl

6. 2. úloha • VYPIS CELEHO JMENA ZAMESTNANCE PIVOVARUNapiste funkci cele_jmeno, jejizvstupnim parametrem bude id zamestnance a ktera bude vracet cele jmenozamestnance. CREATE OR REPLACE FUNCTION cele_jmeno (p_cislo IN NUMBER) RETURN VARCHAR2 AS v_jmenoempl.jmeno%TYPE; v_prijmeniempl.prijmeni%TYPE; v_bad_name VARCHAR(100):='Zamestnanec neexistuje'; BEGIN BEGIN SELECT jmeno,prijmeni INTO v_jmeno,v_prijmeni FROM empl WHERE id_zam=p_cislo; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN v_bad_name; END; RETURN v_jmeno||' '||v_prijmeni; END; SELECT cele_jmeno ('555') FROM dual

7. 3. úloha • FUNKCE VRACEJICI POCET ZAMESTNANCU PIVOVARU Napiste funkci, jejizvstupnim parametrem bude nazev pivovaru a ktera bude vracet počet jeho zamestnancu. Funkce se bude jmenovat pocet_zamcu. CREATE OR REPLACE FUNCTION pocet_zamcu (p_nazev_pivovaru IN VARCHAR) RETURN NUMBER AS pocet NUMBER; CURSOR c_pocet IS SELECT COUNT(e.id_zam) AS celkem FROM empl e INNER JOIN brew b ON e.prislusnost=b.id_p WHERE b.jmeno=p_nazev_pivovaru; BEGIN OPEN c_pocet; LOOP FETCH c_pocet INTO pocet; EXIT WHEN c_pocet%NOTFOUND; RETURN pocet; END LOOP; CLOSE c_pocet; END;

8. 4. úloha • FUNKCE VRACEJICI NAZEV PIVOVARU, POCET VYSKYTUJICICH SE FUNKCI A PRUMERNY PLAT Napiste funkci, jejizvstupnim parametrem bude id pivovaru a ktera bude vracet jeho nazev, nazvy funkci, ktere se v pivovaru vyskytuji (kazdou funkci jen jednou!), a prumerny plat v danem pivovaru. Funkce se bude jmenovat piv_stat. CREATE OR REPLACE FUNCTION piv_stat (id_pivovaru IN NUMBER) RETURN VARCHAR AS v_nazev VARCHAR (100); v_jmeno VARCHAR (100); v_prumer REAL; CURSOR c_status IS SELECT DISTINCT f.nazev FROM funkce f JOIN empl e ON f.id_f=e.prace WHERE prislusnost=id_pivovaru; BEGIN SELECT jmeno INTO v_jmeno FROM brew WHERE id_p=id_pivovaru; v_nazev:='Nazev pivovaru: '||v_jmeno;

9. FOR funkce IN c_status LOOP IF c_status%ROWCOUNT = 1 THEN v_nazev:=v_nazev || '[' || funkce.nazev ; ELSE v_nazev:=v_nazev ||','|| funkce.nazev; END IF; EXIT WHEN c_status%NOTFOUND; END LOOP; SELECT ROUND(AVG(plat)) INTO v_prumer FROM empl WHEREprislusnost=id_pivovaru; v_nazev:=v_nazev||'], ' || 'Prumerny plat: '|| v_prumer ||'Kc'; RETURN v_nazev; END; SELECT piv_stat(104) FROM DUAL

10. 5. úloha • PROCEDURA S PARAMETRICKYM KURZOREM Napiste proceduru, kteravyuziva parametricky kurzor. Procedura se bude jmenovat EMP_info, na vstupu dostane nazev funkce a bude vypisovat cele jmeno, plat a vek osob, kterezastavaji tuto funkci. Parametrem kurzoru bude id dane funkce. CREATE OR REPLACE PROCEDURE emp_info(p_nazev_funkce IN VARCHAR) AS v_jmeno VARCHAR(20); v_prijmeni VARCHAR(20); v_plat NUMBER; v_vek NUMBER; v_vystup VARCHAR(200); v_id_funkce NUMBER; CURSOR c_empl (cislo_fce IN NUMBER) IS SELECT jmeno, prijmeni,plat,vek FROM empl WHERE prace=(SELECT id_f FROM funkce WHERE id_f=cislo_fce); BEGIN DBMS_OUTPUT.PUT_LINE ('Vypis osob s funkci: ' || p_nazev_funkce); SELECT id_f INTO v_id_funkce FROM funkce WHERE nazev=p_nazev_funkce;

11. OPEN c_empl (v_id_funkce); LOOP FETCH c_empl INTO v_jmeno,v_prijmeni,v_plat,v_vek; EXIT WHEN c_empl%NOTFOUND; v_vystup := 'Jmeno: '||v_jmeno ||' '|| v_prijmeni ||',Plat: '||v_plat||',Vek: '||v_vek; DBMS_OUTPUT.PUT_LINE (v_vystup); END LOOP; CLOSE c_empl; END; BEGIN EMP_INFO('Skladnik'); END;

12. 6. úloha • PROCEDURA S VKLADANIM Napiste proceduru, jejimiz parametry bude prijmeni, jmeno, vek, plat, nazev funkce a nazev pivovaru. V priprade, ze zadana funkce nebude v databazi, pridejte ji do tabulky funkce, v pripade neexistence zadaneho pivovaru, bude dany pivovar pridan do prislusne tabulky. Nakonec v pripade, ze nebude existovat ani samotnyzamestnanec, bude i on pridan do tabulky zamci CREATE OR REPLACE PROCEDURE zaloz_zam (in_prijmeni IN varchar2, in_jmeno IN varchar2, in_vek IN number,in_plat IN number, in_nazev IN varchar2, in_jmeno_piv IN varchar2) IS v_nazevnumber; v_jmenonumber; v_zamnumber; BEGIN SELECT COUNT(id_f) INTO v_nazev FROM funkce WHERE nazev LIKE in_nazev; SELECT COUNT(id_p) INTO v_jmeno FROM brew WHERE jmeno LIKE in_jmeno_piv; SELECT COUNT(id_zam) INTO v_zam FROM empl WHERE (jmeno LIKE in_jmeno) AND (prijmeni LIKE in_prijmeni) AND (vek = in_vek) AND (plat = in_plat);

13. IF v_nazev =0 THEN INSERT INTO funkce (id_f,nazev) VALUES (funkce_seq.NEXTVAL,in_nazev); dbms_output.put_line('Zakladam novou funkci: '||in_nazev); ELSE DBMS_OUTPUT.PUT_LINE('Funkce: ' || in_nazev); END IF; SELECT id_f INTO v_nazev FROM funkce WHERE nazev LIKE in_nazev; DBMS_OUTPUT.PUT_LINE('ID funkce je: ' || v_nazev); IF v_jmeno =0 THEN dbms_output.put_line('Zakladamzaznam o pivovaru: '||in_jmeno_piv); INSERT INTO brew (id_p,jmeno) VALUES (brew_seq.NEXTVAL,in_jmeno_piv); ELSE DBMS_OUTPUT.PUT_LINE('Pivovar: ' || in_jmeno_piv); END IF; SELECT id_p INTO v_jmeno FROM brew WHERE jmeno LIKE in_jmeno_piv; DBMS_OUTPUT.PUT_LINE('ID pivovaru je: ' || v_jmeno); IF v_zam =0 THEN DBMS_OUTPUT.PUT_LINE('Probihazalozeninovehozamestnance: '); INSERT INTO empl (id_zam, prijmeni, vek, prace, prislusnost, jmeno, plat) VALUES (empl_seq.NEXTVAL,in_prijmeni, in_vek,v_nazev,v_jmeno, in_jmeno, in_plat); DBMS_OUTPUT.PUT_LINE('Prijmeni: ' || in_prijmeni); DBMS_OUTPUT.PUT_LINE('Jmeno: ' || in_jmeno); DBMS_OUTPUT.PUT_LINE('Vek: ' || in_vek); DBMS_OUTPUT.PUT_LINE('Plat: ' || in_plat); ELSE DBMS_OUTPUT.PUT_LINE('Zamestnanecjiz existuje!'); END IF; END; zaloz_zam('Mucha','Alfons',99,55000,'Ochutnavac','Radegast');

14. 7. úloha • DML TRIGGER Napistetrigger, kteryautomatizovane zajisti vlozeni ID_ZAM z prislusne sekvence při vkladaninovehozaznamu do tabulky EMP. CREATE OR REPLACE TRIGGER "BI_emp" before insert on "EMPL" for each row begin if :NEW."ID_ZAM" is null then select "EMP_SEQ".nextval into :NEW."ID_ZAM" from dual; end if; end;

15. DĚKUJI ZA POZORNOST