DBS_I – PL/SQL

1. DBS_I – PL/SQL Vojtěch Hemala ARI

2. 1/ ZALOZTE SI NASLEDUJICI TABULKY A NAPLNTE JE DATY PODLE VZORU INSERTINTO funkce (nazev) VALUES ('Vedouci'); INSERTINTO funkce (nazev) VALUES ('Skladnik'); . . . END; CREATE table "FUNKCE" (     "ID_F" NUMBER,     "NAZEV" VARCHAR2(4000),     constraint "FUNKCE_PK" primary key ("ID_F")) INSERTINTO brew (jmeno) VALUES ('Litovel'); INSERTINTO brew (id_p,jmeno) VALUES ( 'Holba'); INSERTINTO brew (id_p,jmeno) VALUES ('Branik'); . . . END; CREATE table "BREW" (     "ID_P" NUMBER,     "JMENO" VARCHAR2(4000),     constraint "BREW_PK" primary key ("ID_P")) INSERTINTOemp(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES ( 'Konecny',35,3,104,'Karel',15200); INSERTINTOemp(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES ('Prisoudil',42,1,106,'Jiri',13600); INSERTINTOemp(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES ( 'Kvapil',38,2,108,'Petr',14800); INSERTINTOemp(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES ( 'Zabransky',53,4,109,'Pavel',16000); INSERTINTOemp(id_zam,prijmeni,vek,prace,prislusnost,jmeno,plat) VALUES ('Volejnik',46,5,105,'Milan',11300); . . . END; CREATE table "EMP" (     "ID_ZAM" NUMBER,     "PRIJMENI" VARCHAR2(4000),     "VEK" NUMBER,     "PRACE" NUMBER,     "PRISLUSNOST" NUMBER,     "JMENO" VARCHAR2(4000),     "PLAT" NUMBER,     constraint "EMP_PK" primary key ("ID_ZAM")) ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK" FOREIGN KEY ("PRACE") REFERENCES "FUNKCE" ("ID_F") ON DELETE SET NULL ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK2" FOREIGN KEY ("PRISLUSNOST") REFERENCES "BREW" ("ID_P") ON DELETE SET NULL

3. 2/ VYPIS CELEHO JMENA ZAMESTNANCE PIVOVARU Napiste funkci cele_jmeno, jejiz vstupnim parametrem bude id zamestnance a ktera bude vracet cele jmeno zamestnance (podobnou funkci jste jiz na cviceni vytvareli!). CREATE OR REPLACE FUNCTION cele_jmeno(p_id IN INTEGER) RETURN VARCHAR2 AS v_emp_name VARCHAR2(100); v_false brew.jmeno%TYPE :='Zadany zamestnanec neexistuje'; BEGIN BEGIN SELECT jmeno ||' '|| prijmeni INTO v_emp_name FROM emp WHERE id_zam=p_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN v_false; END; RETURN v_emp_name; END;

4. 3/FUNKCE VRACEJICI POCET ZAMESTNANCU PIVOVARU Napiste funkci, jejiz vstupnim parametrem bude nazev pivovaru a která bude vracet pocet jeho zamestnancu. Funkce se bude jmenovat pocet_zamcu. CREATE OR REPLACE FUNCTION pocet_zamcu(p_nazev IN VARCHAR2) RETURN VARCHAR2 AS v_pocet_zam VARCHAR2(50); v_pom NUMBER; v_false brew.jmeno%TYPE := 'Zadany pivovar neexistuje'; BEGIN SELECT count(*) INTO v_pom FROM emp e JOIN brew b ON e.prislusnost=b.id_p WHERE b.jmeno = INITCAP(p_nazev); v_pocet_zam := TO_CHAR(v_pom); IF v_pom >= 1 THEN RETURN v_pocet_zam; ELSE RETURN v_false; END IF; END;

5. 4/ FUNKCE VRACEJICI NAZEV PIVOVARU, POCET VYSKYTUJICICH SE FUNKCI A PRUMERNY PLAT CREATE OR REPLACE FUNCTION piv_stat(p_id_pivovaru IN brew.id_p%TYPE) RETURN VARCHAR2 AS v_nazev_piv VARCHAR2(50); v_funkce VARCHAR2(200); v_plat emp.plat%TYPE; v_jmeno VARCHAR2(200); v_pom VARCHAR2(50); v_false brew.jmeno%TYPE := 'Zadany pivovar nenalezen'; cursor c_emp IS SELECT DISTINCT f.nazev FROM funkce f INNER JOIN emp e ON e.prace=f.id_f WHERE e.prislusnost=p_id_pivovaru; BEGIN v_funkce := ' --> pozice: '; BEGIN SELECT jmeno INTO v_nazev_piv FROM brew WHERE id_p=p_id_pivovaru; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN v_false; END;

6. Napiste funkci, jejiz vstupnim 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. v_jmeno := 'Nazev pivovaru: '|| v_nazev_piv; SELECT ROUND(AVG(plat)) INTO v_plat FROM emp WHERE prislusnost=p_id_pivovaru; OPEN c_emp; LOOP FETCH c_emp INTO v_pom; EXIT WHEN c_emp%NOTFOUND; IF c_emp%ROWCOUNT = 1 THEN v_funkce := v_funkce||v_pom; ELSE v_funkce := v_funkce||', '||v_pom; END IF; END LOOP; CLOSE c_emp; v_jmeno:=v_jmeno||v_funkce||' --> prumernu plat: '||v_plat; RETURN v_jmeno; END;

7. 5/ PROCEDURA S PARAMETRICKYM KURZOREM CREATE OR REPLACE PROCEDURE emp_info(p_nazev_fce IN VARCHAR2) AS v_fce NUMBER; v_false brew.jmeno%TYPE := 'Zadana funkce neexistuje'; cursor c_emp(p_fce IN NUMBER) IS SELECT e.jmeno,e.prijmeni,e.vek,e.plat FROM emp e JOIN funkce f ON e.prace=f.id_f WHERE f.id_f=p_fce; BEGIN DBMS_OUTPUT.PUT_LINE('Vypis osob s funkci: '||INITCAP(p_nazev_fce)); BEGIN SELECT id_f INTO v_fce FROM funkce WHERE nazev=INITCAP(p_nazev_fce); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(v_false); END; FOR info IN c_emp(v_fce) LOOP DBMS_OUTPUT.PUT_LINE('Jmeno: '||info.jmeno||' '||info.prijmeni||', Plat: '||info.plat ||', Vek: '||info.vek); EXIT WHEN c_emp%NOTFOUND; END LOOP; END;

8. 6/ PROCEDURA S VKLADANIM CREATE OR REPLACE PROCEDURE zaloz_zamce(p_prijmeni IN VARCHAR2, p_jmeno IN VARCHAR2,p_vek IN NUMBER,p_plat IN NUMBER,p_naz_fce IN VARCHAR2, p_naz_piv IN VARCHAR2) AS v_id_funkce NUMBER; v_id_piv NUMBER; v_id_zam NUMBER; cursor c_emp IS SELECT id_zam,jmeno,prijmeni,plat,vek FROM emp e WHERE e.prijmeni=INITCAP(p_prijmeni) AND e.jmeno=INITCAP(p_jmeno) AND e.vek=p_vek AND e.plat=p_plat; BEGIN SELECT count(id_f) INTO v_id_funkce FROM funkce WHERE nazev=INITCAP(p_naz_fce); IF v_id_funkce=0 THEN DBMS_OUTPUT.PUT_LINE('Zakladam novou funkci: '||INITCAP(p_naz_fce)); INSERT INTO funkce(nazev) VALUES (INITCAP(p_naz_fce)); ELSE DBMS_OUTPUT.PUT_LINE('Funkce jiz existuje'); END IF; SELECT id_f INTO v_id_funkce FROM funkce WHEREnazev=INITCAP(p_na_fce) DBMS_OUTPUT.PUT_LINE('Id funckce je: '||v_id_funkce);

9. Napiste proceduru, jejimiz parametry bude prijmeni, jmeno, vek, plat, nazev funkce a nazev pivovaru.Kdyz neni zadana funkce,vytvori se nova, to stejne s pivovarem. Nakonec v pripade, ze nebude existovat ani samotny zamestnanec, bude i on pridan do tabulky zamci. SELECT count(id_p) INTO v_id_piv FROM brew WHEREjmeno=p_naz_piv); IF v_id_piv=0 THEN DBMS_OUTPUT.PUT_LINE('Zakladam pivovar: '||INITCAP(p_naz_piv)); INSERT INTO brew(jmeno) VALUES (INITCAP(p_naz_piv)); ELSE DBMS_OUTPUT.PUT_LINE('Pivovar jiz existuje'); END IF; SELECT id_p INTO v_id_piv FROM brew WHERE jmeno=p_naz_piv; DBMS_OUTPUT.PUT_LINE('Id pivovaru je: '||v_id_piv); SELECT count(id_zam) INTO v_id_zam FROM emp e WHERE e.prijmeni=INITCAP(p_prijmeni) AND e.jmeno=INITCAP(p_jmeno) AND e.vek=p_vek AND e.plat=p_plat; IF v_id_zam=0 THEN DBMS_OUTPUT.PUT_LINE('Zakladam noveho zamestnance!'); INSERT INTO emp(prijmeni,jmeno,vek,plat,prace,prislusnost) VALUES(INITCAP(p_prijmeni),INITCAP(p_jmeno),p_vek,p_plat, v_id_funkce,v_id_piv ); ELSE DBMS_OUTPUT.PUT_LINE('Zamestnanec jiz existuje'); END IF;

10. FOR info IN c_emp LOOP DBMS_OUTPUT.PUT_LINE('ID zamestnance: '||info.id_zam); DBMS_OUTPUT.PUT_LINE('Prijmeni: '||info.prijmeni); DBMS_OUTPUT.PUT_LINE('Jmeno: '||info.jmeno); DBMS_OUTPUT.PUT_LINE('Vek: '||info.vek); DBMS_OUTPUT.PUT_LINE('Plat: '||info.plat); EXIT WHEN c_emp%NOTFOUND; END LOOP; END;

11. 7/ DML TRIGGER Napiste trigger, ktery automatizovane zajisti vlozeni ID_ZAM z prislusne sekvence pri vkladni noveho zaznamu do tabulky EMP. CREATE OR REPLACE TRIGGER "BI_EMP" BEFORE insert on "EMP" FOR each row BEGIN if :NEW."ID_ZAM" is null then SELECT "EMP_SEQ".nextval into :NEW."ID_ZAM" from dual; end if; END;

12. Děkuji za pozornost… …a dotazy?