· 4 years ago · Jun 07, 2021, 03:44 PM
1
2CREATE TABLE addresses(
3 addressId number not null,
4 city varchar2(50),
5 country varchar2(50),
6 street varchar2(50),
7 house_nr varchar2(5),
8 PRIMARY KEY(addressId)
9);
10
11CREATE TABLE users (
12 userId number not null,
13 addressId int not null,
14 username varchar2(25),
15 password varchar2(25),
16 acc_balance number,
17 PRIMARY KEY(userId),
18 FOREIGN KEY(addressId) REFERENCES addresses(addressId)
19);
20
21CREATE TABLE cars(
22 carId number not null,
23 carBrand varchar2(50),
24 carModel varchar2(50),
25 carValue number,
26 PRIMARY KEY (carId)
27);
28
29
30CREATE TABLE carsRental(
31 rentalId number not null,
32 carId number not null,
33 userId number not null,
34 rentalDate date,
35 rentalEndDate date,
36 rentalValue number,
37 PRIMARY KEY (rentalId),
38 FOREIGN KEY(carId) REFERENCES cars(carId),
39 FOREIGN KEY(userId) REFERENCES users(userId)
40);
41
42CREATE TABLE logs(
43 logId number PRIMARY KEY,
44 userId number,
45 carId number,
46 rentalId number,
47 rentalDate date,
48 rentalEndDate date,
49 rentalValue number,
50 rentalOperation varchar2(20)
51);
52insert into addresses values(1,'admin','admin','admin','1');
53insert into users values (1,1,'admin','admin',9999);
54
55insert into cars values (1,'BMW','M3',150);
56insert into cars values (2,'BMW','M5',150);
57insert into cars values (3,'BMW','M8',500);
58insert into cars values (4,'Audi','RS5',450);
59insert into cars values (5,'Dogde','Challenger',350);
60insert into cars values (6,'Chevrolet','Camaro SS',300);
61insert into cars values (7,'Ford','Mustang GT',300);
62-- ======= USER PACKAGE definition
63
64CREATE OR REPLACE PACKAGE projekt_packet AUTHID DEFINER AS
65logged boolean := false;
66saved_username varchar(50) := '';
67-- user procedure
68PROCEDURE register(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2, u_username varchar2, u_password varchar2);
69PROCEDURE login(u_username varchar2, u_password varchar2);
70PROCEDURE logout;
71PROCEDURE add_balance(u_acc_balance number);
72PROCEDURE change_address(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2);
73PROCEDURE showProfile;
74PROCEDURE showHistory;
75-- car procedure
76PROCEDURE showAllCars;
77PROCEDURE showAllCarsAdmin;
78PROCEDURE showLogsAdmin;
79PROCEDURE addCar(u_carBrand varchar2, u_carModel varchar2, u_carValue number);
80FUNCTION checkBalance(u_carId number, u_BeginDate date, u_EndDate date ) RETURN boolean;
81FUNCTION checkDate(u_BeginDate date, u_EndDate date, u_carBrand varchar2, u_carModel varchar2) RETURN number;
82FUNCTION checkCar(u_carBrand varchar2, u_carModel varchar2) RETURN boolean;
83PROCEDURE rentCar(u_carBrand varchar2, u_carModel varchar2, u_BeginDate date, u_EndDate date);
84end projekt_packet;
85
86-- ======= USER PACKAGE body
87CREATE OR REPLACE PACKAGE BODY projekt_packet AS
88
89-- ******************** USER PROCEDURES
90PROCEDURE register (u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2, u_username varchar2, u_password varchar2) IS
91addressExist BOOLEAN;
92user_exist BOOLEAN;
93address_count Number;
94user_count Number;
95address_id number;
96user_already_exist EXCEPTION;
97BEGIN
98addressExist:=false;
99user_exist:=false;
100address_count:=0;
101user_count:=0;
102address_id:=0;
103 FOR address_record in (select * from addresses)
104 LOOP
105 if address_record.city = u_city and address_record.country = u_country and address_record.street = u_street and address_record.house_nr = u_house_nr then
106 addressExist:=true;
107 address_id:= address_record.addressId;
108 END IF;
109 address_count:=address_count+1;
110 END LOOP;
111 address_count:=address_count+1;
112 if addressExist = false then
113 insert into addresses values(address_count,u_city,u_country,u_street,u_house_nr);
114 address_id:=address_count;
115 else
116 update addresses set city = u_city, street = u_street, country = u_country, house_nr=u_house_nr where addressId = address_id;
117 end if;
118
119 FOR user_record in (select * from users) LOOP
120 if(user_record.username = u_username) then
121 user_exist:=true;
122 raise user_already_exist;
123 end if;
124 user_count:=user_count+1;
125 END LOOP;
126 user_count:= user_count+1;
127 if user_exist = false then
128 insert into users values (user_count,address_id,u_username,u_password,0);
129 DBMS_OUTPUT.PUT_LINE('User succesfuly created');
130 end if;
131 EXCEPTION
132 when user_already_exist then
133 DBMS_OUTPUT.PUT_LINE('User already exists, try other username');
134END register;
135
136
137PROCEDURE login (u_username varchar2, u_password varchar2)
138IS
139user_exist BOOLEAN;
140uAreLogged exception;
141BEGIN
142 if logged = false then
143 for user_record in (select * from users) LOOP
144 if user_record.username = u_username and user_record.password = u_password then
145 DBMS_OUTPUT.PUT_LINE('Password and login are correct u are logged in');
146 logged:=true;
147 user_exist:=true;
148 saved_username:=u_username;
149 end if;
150 end LOOP;
151 if user_exist = false then
152 DBMS_OUTPUT.PUT_LINE('Password and login are not correct, try again');
153 end if;
154 else
155 raise uAreLogged;
156 end if;
157EXCEPTION
158 when uAreLogged then
159 DBMS_OUTPUT.PUT_LINE('You cant log in if you are already logged');
160END login;
161
162PROCEDURE logout is
163begin
164 if logged = true then
165 logged:=false;
166 DBMS_OUTPUT.PUT_LINE('You have been logged out');
167 else
168 DBMS_OUTPUT.PUT_LINE('Please login first');
169 end if;
170end logout;
171
172PROCEDURE add_balance(u_acc_balance number) is
173begin
174 if logged = true then
175 update users set acc_balance = u_acc_balance where username = saved_username;
176 DBMS_OUTPUT.PUT_LINE('You have added money to your account');
177 else
178 DBMS_OUTPUT.PUT_LINE('Please login first');
179 end if;
180end add_balance;
181
182PROCEDURE change_address(u_city varchar2,u_country varchar2,u_street varchar2,u_house_nr varchar2) is
183aintLogged exception;
184v_addressId number;
185begin
186 if logged = true then
187 select addressId into v_addressId from users where username = saved_username;
188 update addresses set city = u_city, country = u_country, street = u_street, house_nr = u_house_nr where addressId=v_addressId;
189 DBMS_OUTPUT.PUT_LINE('Your address have been changed');
190 else
191 raise aintLogged;
192 end if;
193EXCEPTION
194 when aintLogged then
195 DBMS_OUTPUT.PUT_LINE('Please login first');
196end change_address;
197
198-- ******************** CARS PROCEDURES
199PROCEDURE showAllCars is
200cursor searching_cars is select * from cars;
201begin
202 if logged = true then
203 for car in searching_cars loop
204 DBMS_OUTPUT.PUT_LINE('Car brand: '||car.carBrand||' | Car model: '||car.carModel||' | Car value: '||car.carValue);
205 end loop;
206 else
207 DBMS_OUTPUT.PUT_LINE('U aint logged');
208 end if;
209end showAllCars;
210-- ******************** ADMIN procedures
211PROCEDURE showAllCarsAdmin is
212aintAdmin exception;
213cursor searching_cars is select * from cars;
214begin
215 if saved_username = 'admin' then
216 DBMS_OUTPUT.PUT_LINE('===SHOW CARS only ADMIN===');
217 for car in searching_cars loop
218 DBMS_OUTPUT.PUT_LINE('Car id: '||car.carId||' | Car brand: '||car.carBrand||' | Car model: '||car.carModel||' | Car value: '||car.carValue);
219 end loop;
220 else
221 raise aintAdmin;
222 end if;
223EXCEPTION
224 when aintAdmin then
225 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
226end showAllCarsAdmin;
227
228PROCEDURE showAllUsersAdmin is
229aintAdmin exception;
230cursor searching_users is select * from users;
231begin
232 if saved_username = 'admin' then
233 for s_user in searching_users loop
234 DBMS_OUTPUT.PUT_LINE('User id: '||s_user.userId||' | User name: '||s_user.username||' | User password: '||s_user.password||' | User balance: '||s_user.acc_balance);
235 end loop;
236 else
237 raise aintAdmin;
238 end if;
239EXCEPTION
240 when aintAdmin then
241 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
242end showAllUsersAdmin;
243
244PROCEDURE showLogsAdmin is
245aintAdmin exception;
246begin
247 if saved_username = 'admin' then
248 DBMS_OUTPUT.PUT_LINE('===LOGS only ADMIN===');
249 FOR singleRecord in (select logId, userId, carId, rentalId, rentalDate, rentalEndDate, rentalValue, rentalOperation from logs) LOOP
250 DBMS_OUTPUT.PUT_LINE('Log id: '||singleRecord.logId||' | User id: '||singleRecord.userId|| ' | Car id: '||singleRecord.carId||' | Rental id: '||singleRecord.rentalId||' | Rental date: '||singleRecord.rentalDate||' | Rental end date: '||singleRecord.rentalEndDate||' | Rental value: '||singleRecord.rentalValue||' | Rental operation: '||singleRecord.rentalOperation);
251 END LOOP;
252 else
253 raise aintAdmin;
254 end if;
255EXCEPTION
256 when aintAdmin then
257 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
258end showLogsAdmin;
259
260PROCEDURE addCar(u_carBrand varchar2, u_carModel varchar2, u_carValue number) is
261carCount number;
262toLowCarValue exception;
263aintAdmin exception;
264begin
265 if saved_username = 'admin' then
266 if u_carValue < 100 then
267 raise toLowCarValue;
268 end if;
269
270 select count(*) into carCount from cars;
271 carCount := carCount + 1;
272
273 insert into cars values (carCount,u_carBrand,u_carModel,u_carValue );
274 DBMS_OUTPUT.PUT_LINE('Car succesfuly added');
275 else
276 raise aintAdmin;
277 end if;
278EXCEPTION
279 when toLowCarValue then
280 DBMS_OUTPUT.PUT_LINE('Car value is below 100pln');
281 when aintAdmin then
282 DBMS_OUTPUT.PUT_LINE('ERROR you dont have permission to that command');
283end addCar;
284
285PROCEDURE showProfile is
286aintLogged exception;
287v_username varchar2(50);
288v_password varchar2(50);
289v_acc_balance number;
290v_city varchar2(50);
291v_country varchar2(50);
292v_street varchar2(50);
293v_house_nr varchar2(50);
294begin
295 if logged = true then
296 select u.username,u.password,u.acc_balance,a.city,a.country,a.street,a.house_nr into v_username, v_password, v_acc_balance, v_city, v_country, v_street, v_house_nr from users u, addresses a where u.username = saved_username and u.addressId = a.addressId;
297
298 DBMS_OUTPUT.PUT_LINE('=== PROFILE ===');
299 DBMS_OUTPUT.PUT_LINE(v_username||' '||v_password||' | Acc balance: '||v_acc_balance);
300 DBMS_OUTPUT.PUT_LINE('=== ADDRESS ===');
301 DBMS_OUTPUT.PUT_LINE(v_country||' '||v_city||' '||v_street||' '||v_house_nr);
302 else
303 raise aintLogged;
304 end if;
305EXCEPTION
306 when aintLogged then
307 DBMS_OUTPUT.PUT_LINE('Please login first');
308end showProfile;
309
310PROCEDURE showHistory is
311aintLogged exception;
312v_userId number;
313begin
314 if logged = true then
315 select userId into v_userId from users where username = saved_username;
316 DBMS_OUTPUT.PUT_LINE('===HISTORY===');
317 FOR singleRecord in (select r.rentalDate, r.rentalEndDate, r.rentalValue, c.carBrand, c.carModel from carsRental r, cars c where r.userId = v_userId and r.carId = c.carId) LOOP
318 DBMS_OUTPUT.PUT_LINE('Car: '||singleRecord.carBrand||' '||singleRecord.carModel||' | Rental date: '||singleRecord.rentalDate||'-'||singleRecord.rentalEndDate||' | Value: '||singleRecord.rentalValue);
319 END LOOP;
320 else
321 raise aintLogged;
322 end if;
323EXCEPTION
324 when aintLogged then
325 DBMS_OUTPUT.PUT_LINE('Please login first');
326end showHistory;
327
328FUNCTION checkDate(u_BeginDate in date, u_EndDate in date, u_carBrand in varchar2, u_carModel in varchar2)
329RETURN number
330is
331aintLogged exception;
332invalidDate exception;
333v_carId number;
334begin
335 if logged = true then
336 v_carId:=0;
337 if u_BeginDate>u_EndDate then
338 raise invalidDate;
339 end if;
340
341 for singleRecord in (select c.carId from carsRental r, cars c where c.carBrand = u_carBrand and c.carModel = u_carModel and c.carId = r.carId and ((r.rentalDate >= u_BeginDate and r.rentalEndDate <= u_EndDate) or u_EndDate>= r.rentalDate and u_EndDate<= r.rentalEndDate)) LOOP
342 v_carId := singleRecord.carId;
343 end LOOP;
344 if v_carId = 0 then
345 select carId into v_carId from cars where carBrand = u_carBrand and carModel = u_carModel;
346 else
347 v_carId := 0;
348 end if;
349 RETURN v_carId;
350 else
351 raise aintLogged;
352 end if;
353EXCEPTION
354 when aintLogged then
355 DBMS_OUTPUT.PUT_LINE('Please login first');
356 when invalidDate then
357 DBMS_OUTPUT.PUT_LINE('Begining rental date is after ending date');
358end checkDate;
359
360FUNCTION checkBalance(u_carId in number, u_BeginDate in date, u_EndDate in date )
361return boolean
362is
363dateDifference number;
364accountBalance number;
365carPerDay number;
366sumUp number;
367aintLogged exception;
368begin
369 if logged = true then
370 dateDifference := u_BeginDate - u_EndDate;
371 select acc_balance into accountBalance from users where username = saved_username;
372 select carValue into carPerDay from cars where carId = u_carId;
373 sumUp:= carPerDay * dateDifference;
374 if accountBalance<sumUp then
375 RETURN true;
376 else
377 RETURN false;
378 end if;
379 else
380 raise aintLogged;
381 end if;
382EXCEPTION
383 when aintLogged then
384 DBMS_OUTPUT.PUT_LINE('Please login first');
385end checkBalance;
386FUNCTION checkCar (u_carBrand varchar2, u_carModel varchar2)
387return boolean
388is
389aintLogged exception;
390countNumber number;
391begin
392 if logged = true then
393 countNumber:= 0 ;
394 select count(*) into countNumber from cars where carBrand = u_carBrand and carModel = u_carModel;
395
396 if countNumber = 0 then
397 return false;
398 else
399 return true;
400 end if;
401 else
402 raise aintLogged;
403 end if;
404EXCEPTION
405 when aintLogged then
406 DBMS_OUTPUT.PUT_LINE('Please login first');
407end checkCar;
408PROCEDURE rentCar(u_carBrand varchar2, u_carModel varchar2, u_BeginDate date, u_EndDate date)
409is
410aintLogged exception;
411freeCarNotFound exception;
412notEnoughMoney exception;
413carNotFound exception;
414
415v_carId number;
416v_ableToPay boolean;
417v_rentCount number;
418v_userId number;
419v_carValue number;
420v_sumUp number;
421v_date number;
422begin
423 if logged = true then
424 if checkCar(u_carBrand,u_carModel) = false then
425 raise carNotFound;
426 end if;
427 v_carId := checkDate(u_BeginDate, u_EndDate, u_carBrand, u_carModel);
428 if v_carId = 0 then
429 raise freeCarNotFound;
430 end if;
431 v_ableToPay := checkBalance(v_carId, u_BeginDate, u_EndDate);
432 if v_ableToPay = true then
433 raise notEnoughMoney;
434 end if;
435 select count(*) into v_rentCount from carsRental;
436 v_rentCount := v_rentCount + 1;
437 select userId into v_userId from users where username = saved_username;
438 select carValue into v_carValue from cars where carId = v_carId;
439 v_date := u_EndDate - u_BeginDate;
440 v_sumUp := v_carValue * v_date;
441 insert into carsRental values (v_rentCount,v_carId,v_userId,u_BeginDate,u_EndDate,v_sumUp);
442 update users set acc_balance = acc_balance - v_sumUp where username = saved_username;
443 DBMS_OUTPUT.PUT_LINE('You succesfuly rent '||u_carBrand||' '||u_carModel||' it will be delivered to your address');
444 else
445 raise aintLogged;
446 end if;
447EXCEPTION
448 when aintLogged then
449 DBMS_OUTPUT.PUT_LINE('Please login first');
450 when freeCarNotFound then
451 DBMS_OUTPUT.PUT_LINE('This car isnt free at this date, try other one');
452 when notEnoughMoney then
453 DBMS_OUTPUT.PUT_LINE('You dont have enough money for this car');
454 when carNotFound then
455 DBMS_OUTPUT.PUT_LINE('We dont have that car in our registry');
456
457end rentCar;
458
459end projekt_packet;
460
461CREATE OR REPLACE TRIGGER tr_rentCars
462 AFTER INSERT OR UPDATE OF rentalId, carId, userId, rentalDate, rentalEndDate, rentalValue
463 ON carsRental
464 FOR EACH ROW
465 DECLARE
466 v_logId number;
467 operationName varchar(20);
468 BEGIN
469 select count(*) into v_logId from log := v_logId + 1;
470 CASE
471 WHEN INSERTING then
472 operationName :='INSERT';
473 WHEN UPDATING then
474 operationName :='UPDATE';
475 END CASE;
476 insert into logs values (v_logId,:NEW.userId, :NEW.carId, :NEW.rentalId, :NEW.rentalDate, :NEW.rentalEndDate, :NEW.rentalValue, operationName );
477 END;s;
478 v_logId
479
480
481
482begin
483
484projekt_packet.login('maciek','maciek');
485projekt_packet.add_balance(50000);
486projekt_packet.showProfile();
487projekt_packet.change_address('Lukow','PL','Zimna Woda','3B');
488projekt_packet.showProfile();
489projekt_packet.showAllCars();
490--projekt_packet.rentCar('Audi','RS5',TO_DATE('2021-06-05','YYYY-MM-DD'),TO_DATE('2021-06-10','YYYY-MM-DD'));
491projekt_packet.showHistory();
492projekt_packet.logout();
493
494projekt_packet.login('admin','admin');
495projekt_packet.addCar('Fiat','126P', 150);
496projekt_packet.showAllCarsAdmin();
497projekt_packet.showLogsAdmin();
498projekt_packet.logout();
499end;
500
501