Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --zad1
- CREATE OR REPLACE PROCEDURE NowyPracownik
- (pNazwisko IN VARCHAR,
- pNazwa IN VARCHAR,
- pSzef IN VARCHAR,
- pPlaca IN NUMBER) IS
- BEGIN
- INSERT INTO Pracownicy (Nazwisko, ID_ZESP, ID_SZEFA, PLACA_POD, Zatrudniony, Etat, ID_PRAC)
- VALUES
- (pNazwisko,
- (SELECT ID_ZESP FROM Zespoly WHERE Nazwa = pNazwa),
- (SELECT ID_PRAC FROM Pracownicy WHERE Nazwisko = pSzef),
- pPlaca,
- CURRENT_DATE,
- 'STAZYSTA',
- (SELECT MAX(ID_PRAC) FROM PRACOWNICY)+1);
- END NowyPracownik;
- /
- EXECUTE NowyPracownik('DYNDALSKI','ALGORYTMY','BLAZEWICZ',250);
- /
- --zad2
- CREATE OR REPLACE FUNCTION PlacaNetto
- (pPlaca IN NUMBER,
- pPodatek IN NUMBER DEFAULT 20)
- RETURN NUMBER IS
- vNetto NUMBER(10,2);
- BEGIN
- vNetto := pPlaca * ((100 - pPodatek) /100);
- RETURN vNetto;
- END PlacaNetto;
- /
- SELECT nazwisko, placa_pod AS BRUTTO, PlacaNetto(placa_pod,35) AS NETTO FROM Pracownicy WHERE etat= 'PROFESOR'ORDER BY nazwisko;
- /
- --zad3
- CREATE OR REPLACE FUNCTION Silnia
- (pN IN NUMBER)
- RETURN NUMBER IS
- vWynik NUMBER(15) := 1;
- BEGIN
- FOR vIndeks IN 1..pN LOOP
- vWynik := vWynik*vIndeks;
- END LOOP;
- RETURN vWynik;
- END Silnia;
- /
- SELECT Silnia(8) FROM Dual;
- /
- --zad4
- CREATE OR REPLACE FUNCTION SilniaRek
- (pN IN NUMBER)
- RETURN NUMBER IS
- vWynik NUMBER(15) := 1;
- BEGIN
- IF pN > 1 THEN
- vWynik:= pN * SilniaRek(pN-1);
- END IF;
- RETURN vWynik;
- END SilniaRek;
- /
- SELECT SilniaRek(10) FROM DUAL;
- /
- --zad5
- CREATE OR REPLACE FUNCTION IleLat
- (pData IN DATE)
- RETURN NUMBER IS
- pLata NUMBER(3);
- BEGIN
- pLata := (EXTRACT (YEAR FROM CURRENT_DATE)) - (EXTRACT (YEAR FROM pData));
- IF (EXTRACT (MONTH FROM CURRENT_DATE)) < (EXTRACT (MONTH FROM pData)) THEN
- pLata := pLata-1;
- ELSE IF (EXTRACT (MONTH FROM CURRENT_DATE)) = (EXTRACT (MONTH FROM pData))
- AND (EXTRACT (DAY FROM CURRENT_DATE)) < (EXTRACT (DAY FROM pData)) THEN
- pLata := pLata -1;
- END IF;
- END IF;
- RETURN pLata;
- END IleLat;
- /
- SELECT nazwisko, zatrudniony, IleLat(zatrudniony) AS staz FROM Pracownicy WHERE placa_pod> 1000 ORDER BY nazwisko;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement