| POLITECHNIKA ŚWIĘTOKRZYSKA |
|---|
| Bazy danych 2 |
Zespół: id27 Banasik Michał Barwinek Patryk Grupa: 211A |
Data i godzina wykonania ćwiczenia Data: 09.03.2012 Godzina: 11.40 – 13.10 |
CREATE OR REPLACE PROCEDURE zad1(nazwa VARCHAR2, placa NUMBER)
IS
BEGIN
INSERT INTO Stanowiska
VALUES (Seq_Stanowiska.NEXTVAL, nazwa, placa);
END;
/
CALL zad1('Murarz', 2000);
/
SELECT * FROM stanowiska;
WYNIK:
| ID_ST | NAZWA_ST | MIN_PLACA |
|---|---|---|
| 1 | Dyrektor | 3500 |
| 2 | Kierownik | 2400 |
| 3 | Programista | 2100 |
| 4 | Informatyk | 2000 |
| 5 | Sekretarka | 1800 |
| 6 | Sprzątaczka | 1200 |
| 7 | Administrator | 3000 |
| 8 | Kadrowa | 2150 |
| 9 | Księgowa | 2300 |
| 14 | Murarz | 2000 |
CREATE OR REPLACE PROCEDURE zad2(imie VARCHAR2, nazwisko VARCHAR2, data DATE, plec VARCHAR2)
IS
BEGIN
INSERT INTO Osoby
VALUES (Seq_Osoby.NEXTVAL, imie, nazwisko, data, plec, 2);
END;
/
CALL zad2('Kamil','Massssz',To_Date('1980-11-13','YYYY-MM-DD'), 'K');
/
SELECT * FROM Osoby;
WYNIK:
| ID_OS | IMIE | NAZWISKO | DATA_UR | PLEC | ID_KIEROWNIKA |
|---|---|---|---|---|---|
| 1 | Jan | Kowalski | 22.05.1952 00:00:00 | M | |
| 2 | Piotr | Nowak | 02.03.1962 00:00:00 | M | |
| 4 | Anna | Kruk | 14.02.1974 00:00:00 | K | 2 |
| 5 | Maria | Nowak | 17.11.1969 00:00:00 | K | 2 |
| 6 | Ewa | Wrona | 10.12.1963 00:00:00 | K | 1 |
| 7 | Zbigniew | Lis | 21.02.1981 00:00:00 | M | |
| 8 | Paweł | Zajšc | 26.03.1968 00:00:00 | M | |
| 9 | Marek | Gil | 13.11.1977 00:00:00 | M | |
| 10 | Jadwiga | Ptak | 19.10.1979 00:00:00 | K | |
| 11 | Katarzyna | Dzik | 05.03.1981 00:00:00 | K | 2 |
| 12 | Marek | Wilk | 13.11.1977 00:00:00 | M | |
| 13 | Anna | Tracz | 19.10.1979 00:00:00 | K | |
| 14 | Katarzyna | Wrona | 05.03.1981 00:00:00 | K | 2 |
| 100 | Kamil | Massssz | 13.11.1980 00:00:00 | K | 2 |
CREATE OR REPLACE FUNCTION zad3(idOs NUMBER)
RETURN VARCHAR2
IS CURSOR kursor IS (SELECT id_os FROM Osoby);
tmp kursor%ROWTYPE;
idKier NUMBER;
idOso NUMBER;
imie VARCHAR2(15);
nazwisko VARCHAR2(20);
BEGIN
SELECT id_os INTO idOso FROM Osoby
WHERE id_os=idOs;
IF idOso IS NULL THEN
DBMS_output.PUT_LINE('Brak osoby');
RETURN 'Brak osoby';
ELSE
SELECT id_Kierownika INTO idKier FROM Osoby
WHERE id_os=idOso;
IF idKier IS NULL THEN
DBMS_output.PUT_LINE('Osoba nie posiada kierownika');
RETURN 'osoba nie posiada kierownika';
ELSE
SELECT o.imie, o.nazwisko INTO imie, nazwisko FROM Osoby o
WHERE id_os=idKier;
RETURN (imie || ' ' || nazwisko);
END IF;
END IF;
END;
/
SELECT zad3(1) FROM dual
/
WYNIK:
| ZAD3(6) |
|---|
| Jan Kowalski |
CREATE OR REPLACE FUNCTION zad4(id_osoby NUMBER)
RETURN NUMBER IS
s NUMBER(7,2);
BEGIN
SELECT SUM(podstawa)+SUM(premia)+(SELECT podstawa*3 FROM Place
WHERE id_os=id_osoby AND za_miesiac='04')
+(SELECT podstawa*6 FROM Place
WHERE id_os=id_osoby AND za_miesiac='06') Zarobki INTO s FROM Place
WHERE id_os=id_osoby GROUP BY id_os;
IF s>85000 THEN s:=40/100*s;
ELSIF s>46000 AND s<=50000
THEN s:=30/100*s;
ElSIF s>3500 THEN s:=19/100*s;
END IF;
RETURN s;
END;
/
SELECT zad4(1) AS zaplata FROM dual;
/
WYNIK:
| ZAPLATA |
|---|
| 8626,1 |
SET SERVEROUTPUT ON;
/
CREATE OR REPLACE PROCEDURE zad5(r VARCHAR2)
IS
CURSOR kursor IS
SELECT o.imie,o.nazwisko,s.nazwa_st,e.data_zat,e.data_zw FROM Etaty e,Osoby o,Stanowiska sWHERE e.id_os=o.id_os
AND s.id_st=e.id_st;
BEGIN
FOR i IN kursor LOOP
IF i.data_zw IS NOT NULL THEN
IF EXTRACT(YEAR FROM i.data_zw)>=EXTRACT(YEAR FROM TO_DATE(r,'YYYY'))
AND EXTRACT(YEAR FROM i.data_zat)<=EXTRACT(YEAR FROM TO_DATE(r,'YYYY'))
THEN dbms_output.put_line(i.imie || ' ' || i.nazwisko);
END IF;
ELSIF EXTRACT(YEAR FROM i.data_zat)<=EXTRACT(YEAR FROM TO_DATE(r,'YYYY'))
THEN dbms_output.put_line(i.imie || ' ' || i.nazwisko);
END IF;
END LOOP;
END;/
CALL zad5(2001);
/
WYNIK:
| Text |
|---|
| Jan Kowalski |
| Jan Kowalski |
| Piotr Nowak |
| Ewa Wrona |
| Paweł Zając |
| Jadwiga Ptak |
CREATE OR REPLACE PROCEDURE zad6(stan VARCHAR2, rok VARCHAR2)
AS
CURSOR kursor IS
SELECT o.imie,o.nazwisko,s.nazwa_st,e.data_zat,e.data_zw
FROM Etaty e,Osoby o,Stanowiska s
WHERE e.id_os=o.id_os
AND s.id_st=e.id_st
AND s.nazwa_st=stan;
BEGIN
FOR i IN kursor LOOP
IF i.data_zw IS NOT NULL THEN
IF EXTRACT(YEAR FROM i.data_zw)>=EXTRACT(YEAR FROM TO_DATE(rok,'YYYY')) AND EXTRACT(YEAR FROM i.data_zat)<=EXTRACT(YEAR FROM TO_DATE(rok,'YYYY')) THEN
dbms_output.put_line(i.imie || ' ' || i.nazwisko); END IF;
ELSIF EXTRACT(YEAR FROM i.data_zat)<=EXTRACT(YEAR FROM TO_DATE(rok,'YYYY')) THEN dbms_output.put_line(i.imie || ' ' || i.nazwisko); END IF;
END LOOP;
END;
/
EXECUTE zad6('Informatyk', '2011');
/
WYNIK:
| Text |
|---|
| Maria Nowak |
CREATE OR REPLACE PROCEDURE zad7
AS
v_zarobki place%ROWTYPE;
v_suma NUMBER;
v_rok NUMBER;
v_suma_lat NUMBER;
v_osoba osoby%ROWTYPE;
CURSOR c_osoba
IS SELECT * FROM osoby;
CURSOR placa
IS SELECT * FROM place
WHERE extract(YEAR FROM data)=v_rok AND id_os=v_osoba.id_os;
BEGIN
DBMS_OUTPUT.PUT_LINE('RAPORT PLAC Z DNIA: '||TO_CHAR(SYSDATE, 'dd-mm-yy'));
DBMS_OUTPUT.PUT_LINE(' ');
open c_osoba;
loop
v_rok:=2006;
v_suma_lat:=0;
FETCH c_osoba INTO v_osoba;
EXIT WHEN c_osoba%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('IMIE NAZWISKO DATA_URODZENIA ');
DBMS_OUTPUT.PUT_LINE(v_osoba.imie ||' '|| v_osoba.nazwisko);
LOOP
DBMS_OUTPUT.PUT_LINE('place za rok: '|| v_rok);
DBMS_OUTPUT.PUT_LINE('DATA PODSTAWA PREMIA RAZEM ');
v_suma:=0;
OPEN placa;
LOOP
FETCH placa INTO v_zarobki;
EXIT WHEN placa%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_zarobki.data ||' '||v_zarobki.podstawa||' '||v_zarobki.premia||' '||(NVL(v_zarobki.premia,0)+v_zarobki.podstawa));
v_suma:=v_suma+(NVL(v_zarobki.premia,0)+v_zarobki.podstawa);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Suma za rok '|| v_rok||': '|| v_suma);
v_suma_lat:=v_suma_lat+NVL(v_suma,0);
CLOSE placa;
v_rok:=v_rok+1;
IF(v_rok>2008) then DBMS_OUTPUT.PUT_LINE('Suma ze wszytskich lat: '|| v_suma_lat);
END IF;
EXIT WHEN v_rok>2008;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
/
EXECUTE zad7;
/
WYNIK:
| Text |
|---|
| RAPORT PLAC Z DNIA: 15-03-12 |
| IMIE NAZWISKO DATA_URODZENIA |
| Jan Kowalski |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| 06/05/10 3600 1200,5 4800,5 |
| 06/06/10 3500 1000 4500 |
| 06/07/10 3500 800 4300 |
| Suma za rok 2006: 13600,5 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 13600,5 |
| IMIE NAZWISKO DATA_URODZENIA |
| Piotr Nowak |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| 06/05/10 2500 2500 |
| 06/06/10 2400 450 2850 |
| 06/07/10 2400 500 2900 |
| Suma za rok 2006: 8250 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 8250 |
| IMIE NAZWISKO DATA_URODZENIA |
| Anna Kruk |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| 06/05/10 2100 500 2600 |
| 06/06/10 2100 2100 |
| 06/07/10 2100 300 2400 |
| Suma za rok 2006: 7100 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 7100 |
| IMIE NAZWISKO DATA_URODZENIA |
| Maria Nowak |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| 06/05/10 1800 300 2100 |
| 06/06/10 1800 250 2050 |
| 06/07/10 1800 1800 |
| Suma za rok 2006: 5950 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 5950 |
| IMIE NAZWISKO DATA_URODZENIA |
| Ewa Wrona |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| 06/05/10 2050 150 2200 |
| 06/06/10 2000 2000 |
| 06/07/10 2000 350 2350 |
| Suma za rok 2006: 6550 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 6550 |
| IMIE NAZWISKO DATA_URODZENIA |
| Zbigniew Lis |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Paweł Zajšc |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Marek Gil |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Jadwiga Ptak |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Katarzyna Dzik |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Marek Wilk |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Anna Tracz |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Katarzyna Wrona |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
| IMIE NAZWISKO DATA_URODZENIA |
| Kamil Massssz |
| place za rok: 2006 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2006: 0 |
| place za rok: 2007 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2007: 0 |
| place za rok: 2008 |
| DATA PODSTAWA PREMIA RAZEM |
| Suma za rok 2008: 0 |
| Suma ze wszytskich lat: 0 |
CREATE OR REPLACE PROCEDURE zad8(imie IN VARCHAR2,nazwisko IN VARCHAR2) IS
n NUMBER(2);
CURSOR kursor(imie_p VARCHAR2,nazwisko_p VARCHAR2) IS SELECT o.id_os FROM Osoby o,Stanowiska s,Etaty e
WHERE o.id_os=e.id_os AND e.id_st=s.id_st AND o.imie=imie_p AND o.nazwisko=nazwisko_p AND s.id_st=2;
CURSOR kursor2 IS SELECT * FROM Osoby o WHERE o.id_kierownika IS NOT NULL;
wyjatek EXCEPTION;
BEGIN
FOR i IN kursor(imie,nazwisko) LOOP n:=i.id_os; END LOOP;
IF n IS NULL THEN RAISE wyjatek; END IF;
dbms_output.put_line('Sklad zespolu kierownika: ' || imie || ' ' || nazwisko || '.');
FOR i IN kursor2 LOOP dbms_output.put_line('- ' || i.imie || ' ' || i.nazwisko); END LOOP;
EXCEPTION
WHEN wyjatek THEN dbms_output.put_line('Osoba nie istnieje lub nie jest kierownikiem');
END;
/
EXECUTE zad8('Piotr','Nowak');
/
WYNIK:
| Text |
|---|
| Sklad zespolu kierownika: Piotr Nowak. |
| - Anna Kruk |
| - Maria Nowak |
| - Ewa Wrona |
| - Katarzyna Dzik |
| - Katarzyna Wrona |
| - Kamil Massssz |
CREATE OR REPLACE PROCEDURE zad9(imie VARCHAR2,nazwisko VARCHAR2,stan VARCHAR2) IS
ido NUMBER(2); ids NUMBER(2); ide NUMBER(2); liczba NUMBER(2); data_ur DATE;
CURSOR kursor(imie_p VARCHAR2,nazwisko_p VARCHAR2) IS SELECT o.id_os,o.data_ur FROM Osoby o WHERE o.imie=imie_p AND o.nazwisko=nazwisko_p;
CURSOR kursor2 IS SELECT * FROM Etaty;
mn18 EXCEPTION; juzp EXCEPTION;
BEGIN
OPEN kursor(imie,nazwisko);
FETCH kursor INTO ido,data_ur;
CLOSE kursor;
IF (EXTRACT(YEAR FROM sysdate)-EXTRACT(YEAR FROM data_ur))<18 THEN RAISE mn18; END IF;
SELECT s.id_st INTO ids FROM Stanowiska s WHERE s.nazwa_st=stan;
FOR i IN kursor2 LOOP
IF i.id_os=ido AND i.id_st=ids THEN RAISE juzp; END IF;
END LOOP;
SELECT max(e.id_e) INTO ide FROM Etaty e;
INSERT INTO Etaty(id_e,id_os,id_st,data_zat) VALUES (ide+1,ido,ids,TO_DATE(sysdate,'YYYY-MM-DD'));
dbms_output.put_line('Ok');
EXCEPTION
WHEN mn18 THEN dbms_output.put_line('Osoba ma mniej niz 18 lat');
WHEN juzp THEN dbms_output.put_line('Osoba pracuje juz na tym stanowisku!');
END;
/
SELECT * FROM Etaty;
EXECUTE zad9('Piotr','Nowak','Informatyk');
SELECT * FROM Etaty;
/
WYNIK:
| ID_E | ID_OS | ID_ST | DATA_ZAT | DATA_ZW |
|---|---|---|---|---|
| 1 | 1 | 1 | 29.01.2000 00:00:00 | |
| 2 | 2 | 2 | 29.01.2001 00:00:00 | |
| 3 | 4 | 3 | 29.03.2006 00:00:00 | |
| 4 | 5 | 4 | 29.01.2002 00:00:00 | |
| 5 | 4 | 4 | 29.01.2004 00:00:00 | 19.03.2006 00:00:00 |
| 7 | 6 | 8 | 29.03.2006 00:00:00 | |
| 6 | 6 | 5 | 29.01.2000 00:00:00 | 29.03.2006 00:00:00 |
| 8 | 7 | 7 | 29.01.2003 00:00:00 | |
| 9 | 8 | 4 | 29.01.2000 00:00:00 | 05.10.2005 00:00:00 |
| 10 | 9 | 7 | 29.01.2002 00:00:00 | |
| 11 | 10 | 2 | 29.01.2000 00:00:00 | 10.03.2003 00:00:00 |
| 12 | 1 | 3 | 29.01.2001 00:00:00 | |
| 13 | 4 | 7 | 19.03.2006 00:00:00 | |
| 14 | 6 | 9 | 29.03.2006 00:00:00 | |
| 15 | 2 | 4 | 15.03.0012 00:00:00 |
CREATE OR REPLACE PROCEDURE zad10(st VARCHAR2)
IS
CURSOR kursor IS SELECT osoby.imie, osoby.nazwisko, etaty.data_zat, osoby.id_os FROM osoby, etaty, stanowiska
WHERE stanowiska.id_st=etaty.id_st AND etaty.id_os=osoby.id_os AND stanowiska.nazwa_st=st ORDER BY osoby.imie;
pod NUMBER;
prem NUMBER;
suma NUMBER;
BEGIN
FOR k IN kursor
LOOP
SELECT Sum(podstawa) INTO pod FROM place WHERE id_os=k.id_os;
SELECT Sum(premia) INTO prem FROM place WHERE id_os=k.id_os;
suma:=pod+prem;
DBMS_OUTPUT.put_line ('Pracownicy: ');
DBMS_OUTPUT.put_line (k.imie||' '||k.nazwisko);
DBMS_OUTPUT.put_line ('Data zatrudnienia: '||k.data_zat);
DBMS_OUTPUT.put_line ('Wartosc zarobkow:'||suma);
END LOOP;
END;
/
EXECUTE zad10('Programista');
WYNIK:
| Text |
|---|
| Pracownicy: |
| Anna Kruk |
| Data zatrudnienia: 06/03/29 |
| Wartosc zarobkow:7100 |
| Pracownicy: |
| Jan Kowalski |
| Data zatrudnienia: 01/01/29 |
| Wartosc zarobkow:13600,5 |
CREATE OR REPLACE FUNCTION zad11(Id_osoby NUMBER)
RETURN NUMBER IS
s NUMBER;
BEGIN
SELECT SUM(podstawa)+SUM(premia) Zarobki INTO s FROM Place WHERE id_os=id_osoby GROUP BY id_os;
RETURN s;
END;
/
SELECT zad11(1) AS zaplata FROM DUAL;
WYNIK:
| ZAPLATA |
|---|
| 13600,5 |