· 4 years ago · Feb 09, 2021, 02:48 PM
1Set serveroutput on;
2
3SELECT*FROM PROJ;
4SELECT*FROM STAT_PROJ_S20465;
5SELECT * FROM PROJ_EMP;
6SELECT * FROM EMP;
7
8CREATE OR REPLACE PROCEDURE procka
9AS
10CURSOR krs IS SELECT PROJNO FROM proj;
11nazwa2 varchar(30);
12budzet2 INT;
13uczestnicy INT;
14wyplaty INT;
15projnr INT;
16zmienna INT;
17BEGIN
18 DELETE FROM STAT_PROJ_S20465;
19 OPEN krs;
20 LOOP
21 FETCH krs INTO projnr;
22 EXIT WHEN krs%NOTFOUND;
23 SELECT projno INTO projnr FROM proj where projnr=projno;
24 SELECT PNAME Into nazwa2 FROM proj where projno=projnr;
25 SELECT BUDGET Into budzet2 FROM proj where projno=projnr;
26 SELECT COUNT (empno) INTO uczestnicy FROM PROJ_EMP WHERE projno=projnr;
27 SELECT SUM (SAL) INTO wyplaty FROM EMP INNER JOIN PROJ_EMP ON EMP.EMPNO=PROJ_EMP.EMPNO WHERE PROJNO=projnr;
28 SELECT MAX(ID)+1 INTO zmienna FROM STAT_PROJ_S20465;
29
30 IF zmienna IS NOT NULL THEN
31
32 INSERT INTO STAT_PROJ_S20465(ID, NUMER, NAZWA, BUDZET, LICZBA_UCZESTNIKOW, SUMA_WPLAT)
33 VALUES ((SELECT MAX(ID)+1 FROM STAT_PROJ_S20465), projnr, nazwa2, budzet2, uczestnicy, wyplaty);
34 DBMS_OUTPUT.put_line(projnr );
35 ELSE
36 INSERT INTO STAT_PROJ_S20465(ID, NUMER, NAZWA, BUDZET, LICZBA_UCZESTNIKOW, SUMA_WPLAT)
37 VALUES (1, projnr, nazwa2, budzet2, uczestnicy, wyplaty);
38 DBMS_OUTPUT.put_line(projnr );
39 END IF;
40 END LOOP;
41 CLOSE krs;
42END;
43
44
45-------------------------------------------------------------------------------
46CREATE TABLE STAT_PROJ_S20465(
47ID INT NOT NULL,
48numer INT NOT NULL,
49nazwa VARCHAR(30) NULL,
50budzet INT NOT NULL,
51liczba_uczestnikow INT NOT NULL,
52suma_wplat INT NOT NULL,
53data DATE default sysdate NOT NULL,
54constraint id primary key (ID) -- PRIMARY KEY (id)
55);
56
57create or replace trigger wyzwalacz1
58before update or insert or delete of pensja on hodowca
59FOR EACH ROW
60declare domyslna INT := 1525;
61BEGIN
62IF deleting then
63if :old.pensja<1000 then
64RAISE_APPLICATION_ERROR(-20999,'Nie oplaca nam sie zwalniac pracownikow zarabiajacych ponizej 1000!');
65end if;
66end if;
67if inserting then
68IF :new.Pensja > 4000 OR :new.Pensja <200 then
69:new.pensja := domyslna;
70DBMS_OUTPUT.put_line('W naszej firmie jako nowicjusze nie zarabiamy powyzej 4000 i ponizej 200, z tego wzgledu pracownik otrzymuje pensje o wartosci sredniej: ' || domyslna);
71end if;
72end if;
73if updating then
74if :new.pensja>:old.pensja then
75RAISE_APPLICATION_ERROR(-20999,'W tych ciezkich czasach nie dajemy zadnych podwyzek!');
76end if;
77end if;
78END;
79
80delete from hodowca where id_pracownika = 20;
81
82INSERT INTO Hodowca
83(ID_PRACOWNIKA, IMIE, NAZWISKO, ID_HODOWLI, PENSJA)
84VALUES
85((SELECT MAX(ID_PRACOWNIKA) FROM Hodowca)+1,'Burak','Yilmaz',3,20500);
86
87
88UPDATE HODOWCA
89SET pensja = 699
90WHERE ID_PRACOWNIKA=2;
91
92-----------------------------------------------------------------
93
94--PROCEDURY
95
96--Procedura dodaje nowego hodowce, przypisuje mu pensje rowna sredniej pensji oraz wypisuje pracownikow lepiej od niego zarabiajacych
97ALTER PROCEDURE nowy_hodowca
98@Imie VARCHAR(15),
99@Nazwisko VARCHAR(15),
100@NazwaHodowli VARCHAR(15)
101AS
102IF EXISTS (SELECT 'j' FROM Hodowla WHERE Nazwa = @NazwaHodowli)
103BEGIN
104DECLARE @ID_Pracownika INT
105SELECT @ID_Pracownika = ISNULL(MAX(ID_Pracownika), 0) + 1
106FROM Hodowca
107DECLARE @Pensja INT
108SELECT @Pensja = (Select AVG(Pensja) FROM Hodowca) -100
109FROM Hodowca
110DECLARE @IdHodowli INT
111SELECT @IdHodowli = (Select ID_Hodowli FROM Hodowla WHERE Nazwa=@NazwaHodowli)
112FROM Hodowca
113INSERT INTO Hodowca (ID_Pracownika, Imie, Nazwisko, Hodowla_ID_Hodowli, Pensja)
114VALUES (@ID_Pracownika, @Imie, @Nazwisko, @IdHodowli, @Pensja)
115DECLARE qursor CURSOR FOR
116SELECT Nazwisko, Pensja FROM Hodowca WHERE Pensja > (SELECT Pensja FROM Hodowca WHERE Id_pracownika=@ID_Pracownika)
117DECLARE @nazw VARCHAR(50), @pen INT
118PRINT 'Pracownicy zarabiajacy wiecej niz nowo dodany pracownik ' + @Imie +' '+ @Nazwisko
119OPEN qursor
120FETCH NEXT FROM qursor INTO @nazw, @pen
121WHILE @@FETCH_STATUS = 0
122BEGIN
123PRINT @nazw + ' ' + Cast(@pen As Varchar)
124FETCH NEXT FROM qursor INTO @nazw, @pen
125END
126CLOSE qursor
127DEALLOCATE qursor;
128END
129ELSE
130BEGIN
131PRINT 'Nie ma takiej hodowli'
132END
133EXEC nowy_hodowca 'Gekon','Harek','Care4pet'
134
135--Procedura wstawiajaca pozwolenie o takiej samej nazwie jak gatunek nowo dodanego zwierzecia
136
137ALTER PROCEDURE zwierzeadd
138@Hodowla VARCHAR(15),
139@Plec VARCHAR(1),
140@Wiek INT,
141@Gatunek VARCHAR(15)
142AS
143IF @Plec = 'M' OR @Plec = 'K'
144BEGIN
145DECLARE @ID_Zwierzecia INT
146SELECT @ID_Zwierzecia = ISNULL(MAX(ID_Zwierzecia), 0) + 1
147FROM Zwierze
148DECLARE @NazwaZezwolenia VARCHAR(15)
149SELECT @NazwaZezwolenia = @Gatunek
150FROM Zezwolenia
151DECLARE @IdHodowli INT
152SELECT @IDHodowli = (SELECT ID_Hodowli FROM Hodowla WHERE Nazwa=@Hodowla)
153FROM Zwierze
154DECLARE @IdGatunku INT
155SELECT @IdGatunku = ISNULL(MAX(ID_Gatunku), 0) +1
156From Gatunek
157DECLARE @IdZezwolenia INT
158SELECT @IDZezwolenia = ISNULL(MAX(ID_Zezwolenia), 0) + 1
159From Zezwolenia
160INSERT INTO Zwierze (ID_Zwierzecia, Plec, Wiek, Hodowla_ID_Hodowli, Gatunek_ID_Gatunku)
161VALUES (@ID_Zwierzecia, @Plec, @wiek, @idhodowli, @IdGatunku)
162INSERT INTO Zezwolenia(ID_Zezwolenia, NrZezwolenia, NazwaZezwolenia, Hodowla_ID_Hodowli)
163VALUES(@idzezwolenia, @idzezwolenia, @Gatunek, @idhodowli)
164INSERT
165END
166ELSE
167BEGIN
168PRINT 'Plec musi miec postac "K" lub "M"'
169END
170
171EXEC zwierzeadd 'Care4pet', 'K', '3', 'Pirania'
172
173
174--Wyzwalacze
175--Nie mozna zarabiac wiecej niz 4000 i mniej niz 200
176ALTER TRIGGER wyzwalacz ON Hodowca
177FOR INSERT
178AS
179DECLARE @Pensja INT
180SELECT @Pensja = Pensja FROM Inserted
181IF @Pensja > 4000 OR @Pensja <200
182BEGIN
183ROLLBACK
184RAISERROR('Zarabianie powyzej 4000 i poniezej 200 to nie u nas :)',1,2)
185END
186
187INSERT INTO Hodowca(ID_Pracownika, Imie, Nazwisko, Hodowla_ID_Hodowli, Pensja)
188VALUES (99, 'Josh', 'Bunny', 1, 100)
189