SHARE
TWEET

Untitled

a guest Oct 10th, 2019 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --zad1
  2. CREATE OR REPLACE PROCEDURE NowyPracownik
  3.     (pNazwisko IN VARCHAR,
  4.     pNazwa IN VARCHAR,
  5.     pSzef IN VARCHAR,
  6.     pPlaca IN NUMBER) IS
  7. BEGIN
  8.     INSERT INTO Pracownicy (Nazwisko, ID_ZESP, ID_SZEFA, PLACA_POD, Zatrudniony, Etat, ID_PRAC)
  9.     VALUES
  10.         (pNazwisko,
  11.         (SELECT ID_ZESP FROM Zespoly WHERE Nazwa = pNazwa),
  12.         (SELECT ID_PRAC FROM Pracownicy WHERE Nazwisko = pSzef),
  13.         pPlaca,
  14.         CURRENT_DATE,
  15.         'STAZYSTA',
  16.         (SELECT MAX(ID_PRAC) FROM PRACOWNICY)+1);
  17. END NowyPracownik;
  18. /
  19. EXECUTE NowyPracownik('DYNDALSKI','ALGORYTMY','BLAZEWICZ',250);
  20. /
  21. --zad2
  22. CREATE OR REPLACE FUNCTION PlacaNetto
  23.     (pPlaca IN NUMBER,
  24.     pPodatek IN NUMBER DEFAULT 20)
  25.     RETURN NUMBER IS
  26.     vNetto NUMBER(10,2);  
  27. BEGIN
  28.     vNetto := pPlaca * ((100 - pPodatek) /100);
  29.     RETURN vNetto;
  30.  
  31. END PlacaNetto;
  32. /
  33. SELECT nazwisko, placa_pod AS BRUTTO, PlacaNetto(placa_pod,35) AS NETTO FROM Pracownicy WHERE etat= 'PROFESOR'ORDER BY nazwisko;
  34. /
  35. --zad3
  36. CREATE OR REPLACE FUNCTION Silnia
  37.     (pN IN NUMBER)
  38.     RETURN NUMBER IS
  39.     vWynik NUMBER(15) := 1;
  40. BEGIN
  41.     FOR vIndeks IN 1..pN LOOP
  42.         vWynik := vWynik*vIndeks;
  43.     END LOOP;
  44.     RETURN vWynik;
  45. END Silnia;
  46. /
  47. SELECT Silnia(8) FROM Dual;
  48. /
  49. --zad4
  50. CREATE OR REPLACE FUNCTION SilniaRek
  51.     (pN IN NUMBER)
  52.     RETURN NUMBER IS
  53.     vWynik NUMBER(15) := 1;
  54. BEGIN
  55.     IF pN > 1 THEN
  56.         vWynik:= pN * SilniaRek(pN-1);
  57.     END IF;
  58.     RETURN vWynik;
  59. END SilniaRek;
  60. /
  61. SELECT SilniaRek(10) FROM DUAL;
  62. /
  63. --zad5
  64. CREATE OR REPLACE FUNCTION IleLat
  65.     (pData IN DATE)
  66.     RETURN NUMBER IS
  67.     pLata NUMBER(3);
  68. BEGIN
  69.     pLata := (EXTRACT (YEAR FROM CURRENT_DATE)) - (EXTRACT (YEAR FROM pData));
  70.     IF (EXTRACT (MONTH FROM CURRENT_DATE)) < (EXTRACT (MONTH FROM pData)) THEN
  71.         pLata := pLata-1;
  72.     ELSE IF (EXTRACT (MONTH FROM CURRENT_DATE)) = (EXTRACT (MONTH FROM pData))
  73.         AND (EXTRACT (DAY FROM CURRENT_DATE)) < (EXTRACT (DAY FROM pData)) THEN
  74.             pLata := pLata -1;
  75.     END IF;
  76.     END IF;
  77.     RETURN pLata;
  78.      
  79. END IleLat;
  80. /
  81. SELECT nazwisko, zatrudniony, IleLat(zatrudniony) AS staz FROM Pracownicy WHERE placa_pod> 1000 ORDER BY nazwisko;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top