BAZY DANYCH 2
Dr inż. Wikarek Jarosław
Katedra Systemów Sterowania i Zarządzania
E-MAIL: j.wikarek@tu.kielce.pl
1
JĘZYK PL/SQL
PRZYKŁADY
Bazy danych
Struktura tabel
Osoby(id_os, Imie, Nazwisko, Data_ur, Plec, i d_Kierownika)
Stanowiska(id_st, Nazwa_st, Min_placa);
2
Stanowiska(id_st, Nazwa_st, Min_placa);
Etaty(id_e, id_os, id_st, data_zat, data_zw),
Place(id_os, data, za_miesiac, podstawa, premia)
Bazy danych
Przykład 1
3
Napisz program w Plsql np. Wyświetlający
napis HELLO WORLD
Bazy danych
Przykład 1 – rozwiązanie
BEGIN
4
BEGIN
Dbms_Output.put_line('HELLO WORL');
END;
Bazy danych
Przykład 2
5
Napisz program w plsql który obliczy równanie
kwadratowe. A,B,C ustawiamy w programie.
Bazy danych
Przykład 2 – rozwiązanie
DECLARE
A NUMBER(5,2);
B NUMBER(5,2);
6
B NUMBER(5,2);
C NUMBER(5,2);
del NUMBER(6,2);
BEGIN
A:=1;
B:=1;
C:=1;
del:=B*B-4*A*C;
Dbms_Output.put_line('delata ='||DEL);
Bazy danych
Przykład 2 – rozwiązanie
IF del>0 THEN
Dbms_Output.put_line('X1 ='|| To_Char((-B-Sqrt(del))/(2*A),'0.99'));
Dbms_Output.put_line('X2 ='|| To_Char((-B+Sqrt(del))/(2*A),'0.99'));
7
Dbms_Output.put_line('X2 ='|| To_Char((-B+Sqrt(del))/(2*A),'0.99'));
ELSIF del=0 THEN
Dbms_Output.put_line('X1,2 ='|| To_Char((-B)/(2*A),'0.99'));
ELSE
Dbms_Output.put_line('brak rozwiazan');
END IF;
END;
Bazy danych
Przykład 3
Napisz program w plsql, który wyświetli ilość
rekordów w tabelach.
8
rekordów w tabelach.
Bazy danych
Przykład 3 – rozwiązanie
DECLARE
ile NUMBER(3);
9
ile NUMBER(3);
BEGIN
SELECT Count(*) INTO ile FROM osoby;
Dbms_Output.put_line('Osoby ilosc rekordow= ‘ ||ile);
END;
Bazy danych
Przykład 4
Rozbuduj program z zad 3 tak aby wypisywał
"brak rekordów" jeśli ilość równa jest 0
10
"brak rekordów" jeśli ilość równa jest 0
Bazy danych
Przykład 4 – rozwiązanie
DECLARE
ile NUMBER(3);
11
ile NUMBER(3);
BEGIN
SELECT Count(*)INTO ile FROM osoby;
IF ile=0 THEN
Dbms_Output.put_line('Osoby - brak rekordow');
ELSE
Dbms_Output.put_line('Osoby ilosc rekordow='||ile);
END IF;
END;
Bazy danych
Przykład 5
Napisz program który wywieli jaki mamy nr
dnia tygodnia np środa nr3 (instrukcja case i
12
dnia tygodnia np środa nr3 (instrukcja case i
funkcja to_char).
Bazy danych
Przykład 5 – rozwiązanie
DECLARE
dzien VARCHAR2(15);
BEGIN
13
dzien:=To_Char(SYSDATE,'DAY');
dzien:=Trim(dzien);
CASE
WHEN dzien='PONIEDZIALEK' THEN
Dbms_Output.put_line('nr dnia tygodnia :'||1);
...
ELSE
Dbms_Output.put_line('Nie wiem');
END CASE; END;
Bazy danych
Przykład 6
Napisz program który wyświetli kto najwięcej
zarobił w danym roku. (do tabel z osoby etaty
14
zarobił w danym roku. (do tabel z osoby etaty
stanowiska place
Bazy danych
Przykład 6 – rozwiązanie
DECLARE
ilosc NUMBER(10,2);
id_ NUMBER(5);
15
id_ NUMBER(5);
naz_imie VARCHAR2(100);
BEGIN
SELECT Max(Sum(podstawa+Nvl(premia,0))) INTO ilosc
FROM place WHERE To_Char(data,'YYYY')='2008'
GROUP BY id_o;
Dbms_Output.put_line('Max ='||ilosc);
Bazy danych
Przykład 6 – rozwiązanie
SELECT id_o INTO id_ FROM place
WHERE To_Char(data,'YYYY')='2008' GROUP BY id_o
HAVING Sum(podstawa+Nvl(premia,0))=ilosc;
16
HAVING Sum(podstawa+Nvl(premia,0))=ilosc;
Dbms_Output.put_line('nr osoby='||id_);
SELECT imie||' '||nazwisko INTO naz_imie FROM osoby
WHERE id=id_;
Dbms_Output.put_line(naz_imie);
END;
Bazy danych
Przykład 7
Napisz program plsql, który wyświetli w
dziesięciu liniach trójkąt składający się z *
17
*
**
***
****
itd aż do 10.
Bazy danych
Przykład 7 – rozwiązanie
BEGIN
FOR i IN 1..10 LOOP
18
FOR i IN 1..10 LOOP
FOR j IN 1..i loop
Dbms_Output.put('*');
END LOOP;
Dbms_Output.put_line('');
END LOOP;
END;
Bazy danych
Przykład 7 – rozwiązanie
Lub tak:
19
BEGIN
FOR i IN 1..10 LOOP
Dbms_Output.put_line(RPad('*',i,'*'));
END LOOP;
End;
Bazy danych
Przykład 8
Wyświetlić
zawartości
tabel:
pracownicy,
20
Wyświetlić
zawartości
tabel:
pracownicy,
stanowiska, etaty, place każdą przy użyciu innej
pętli(loop, for, while).
Bazy danych
Przykład 8 – rozwiązanie
DECLARE
CURSOR kur IS SELECT * FROM pracownicy;
rek pracownicy%ROWTYPE;
21
rek pracownicy%ROWTYPE;
BEGIN
OPEN kur;
LOOP
FETCH kur INTO rek;
EXIT WHEN kur%NOTFOUND;
Dbms_Output.put_line(rek.nr_p||' '||rek.imie||' '||rek.nazwisko);
END LOOP;
CLOSE kur;
END;
Bazy danych
Przykład 8 – rozwiązanie
DECLARE
CURSOR k1 IS SELECT * FROM stanowiska ;
22
CURSOR k1 IS SELECT * FROM stanowiska ;
BEGIN
FOR rek IN k1 LOOP
Dbms_Output.put_line(rPad(rek.nr_st||’ ‘||rek.nazwa||’
‘||rek.minplaca||’ ‘||rek.maxplaca);
END LOOP;
END;
Bazy danych
Przykład 8 – rozwiązanie
DECLARE
CURSOR k1 IS SELECT * FROM place ;
rek k1%ROWTYPE;
23
rek k1%ROWTYPE;
BEGIN
OPEN k1;
FETCH k1 INTO rek;
WHILE k1%FOUND LOOP
Dbms_Output.put_line(rek.id_placy||’ ‘rek.nr_prac||’‘||
rek.za_miesiac||’ ’||rek.podastawa||’‘||nvl(rek.premia,0)||’ ‘||
podstawa+nvl(premia,0));
FETCH k1 INTO rek;
END LOOP; CLOSE k1; END;
Bazy danych
Przykład 9
Wyświetlić pracowników i ich zarobki w danym
24
Wyświetlić pracowników i ich zarobki w danym
roku kalendarzowym, (czyli 2008), skorzystać z
funkcji sysdate.
Bazy danych
Przykład 9 – rozwiązanie
DECLARE
CURSOR k1 IS SELECT * FROM pracownicy;
25
CURSOR k1 IS SELECT * FROM pracownicy;
CURSOR k2(id NUMBER) IS SELECT za_miesiac,
to_Char(data,'MM-DD‘) dat, podstawa+Nvl(premia,0) plac
FROM place WHERE nr_p=id AND To_Char(data,'YYYY')=
To_Char(sysdate,'YYYY');
Bazy danych
Przykład 9 – rozwiązanie
BEGIN
FOR i IN k1 LOOP
Dbms_Output.put_line(i.imie||' '||i.nazwisko);
26
Dbms_Output.put_line(i.imie||' '||i.nazwisko);
FOR j IN k2(i.nr_p) LOOP
Dbms_Output.put_line(j.za_miesiac||' '|| j.dat||‘ '||j.plac);
END LOOP;
END LOOP;
END;
Bazy danych
Przykład 10
Do zadania 9 dodaj podsumowanie z danego roku.
27
Do zadania 9 dodaj podsumowanie z danego roku.
Dla każdego pracownika ile on zarobił.
Bazy danych
Przykład 10 – rozwiązanie
DECLARE
CURSOR k1 IS SELECT * FROM pracownicy;
CURSOR k2(id NUMBER) IS SELECT za_miesiac,
28
CURSOR k2(id NUMBER) IS SELECT za_miesiac,
to_Char(data,'MM-DD') dat, podstawa+Nvl(premia,0) plac
FROM place WHERE nr_p=id AND To_Char(data,'YYYY')= '2008';
ile NUMBER(9,2);
BEGIN
FOR i IN k1 LOOP
Dbms_Output.put_line(i.imie||' '||i.nazwisko);
FOR j IN k2(i.nr_p) LOOP
Dbms_Output.put_line(j.za_miesiac||' '|| j.dat||‘ '||j.plac);
END LOOP;
Bazy danych
Przykład 10 – rozwiązanie
SELECT Sum(podstawa+nvl(premia,0)) INTO ile FROM place
WHERE nr_p=i.nr_p AND To_Char(data,'YYYY')='2008';
IF (ile IS not NULL) then
29
IF (ile IS not NULL) then
Dbms_Output.put_line('RAZEM W ROKU: '||ile);
END IF;
END LOOP;
END;
Zamiast select można w pętli sumować place i ta sumę wyświetlić
Bazy danych
Przykład 11
Wyświetl pracowników i ich kierowników, jeśli
30
Wyświetl pracowników i ich kierowników, jeśli
dany pracownik nie ma kierownika to ma się
wyświetlać brak kierownika.
Bazy danych
Przykład 11 – rozwiązanie
DECLARE
CURSOR k1 IS SELECT p.imie,p.nazwisko, k.imie kimie, k.nazwisko
k.nazwisko FROM pracownicy p, pracownicy k WHERE
31
k.nazwisko FROM pracownicy p, pracownicy k WHERE
p.nr_kier=k.nr_p(+) ;
rek k1%ROWTYPE;
BEGIN
OPEN k1;
FETCH k1 INTO rek;
WHILE k1%FOUND LOOP
Dbms_Output.put(rek.imie||' '||rek.nazwisko||'kierownik: ');
Bazy danych
Przykład 11 – rozwiązanie
IF (rek.kimie IS NULL ) THEN
Dbms_Output.put_line('Brak kierownika');
ELSE
32
ELSE
Dbms_Output.put_line(rek.kimie||' '||rek.knazwisko);
END IF;
FETCH k1 INTO rek;
END LOOP;
CLOSE k1;
END;