Changes between Version 3 and Version 4 of DatabaseProgramming
- Timestamp:
- 05/31/26 00:26:50 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
DatabaseProgramming
v3 v4 26 26 27 27 ``` 28 -- Проверка дали книгата е достапна29 28 SELECT available 30 29 INTO v_available … … 36 35 END IF; 37 36 38 -- Проверка дали членот е активен39 37 SELECT membership_status 40 38 INTO v_membership_status … … 46 44 END IF; 47 45 48 -- Креирање borrowing49 46 INSERT INTO borrowing( 50 47 borrowing_id, … … 68 65 ); 69 66 70 -- Update availability 67 71 68 UPDATE bookcopy 72 69 SET available = 0 … … 112 109 113 110 ``` 114 -- check for existing member 111 115 112 SELECT COUNT(*) 116 113 INTO v_existing_member … … 122 119 END IF; 123 120 124 -- check for existing email 121 125 122 SELECT COUNT(*) 126 123 INTO v_existing_email … … 132 129 END IF; 133 130 134 -- create new member135 131 INSERT INTO member( 136 132 member_id, … … 191 187 192 188 ``` 193 -- take info from borrowing 189 194 190 SELECT due_date, copy_id, return_date 195 191 INTO v_due_date, v_copy_id, v_return_date … … 197 193 WHERE borrowing_id = p_borrowing_id; 198 194 199 -- check for existing borrow 195 200 196 IF v_copy_id IS NULL THEN 201 197 RETURN 'Borrowing record not found'; 202 198 END IF; 203 199 204 -- check if book is returned205 200 IF v_return_date IS NOT NULL THEN 206 201 RETURN 'Book already returned'; 207 202 END IF; 208 203 209 -- Update return date210 204 UPDATE borrowing 211 205 SET return_date = CURRENT_DATE 212 206 WHERE borrowing_id = p_borrowing_id; 213 207 214 -- bookstatus 208 215 209 SELECT status_id 216 210 INTO v_status_id … … 218 212 WHERE copy_id = v_copy_id; 219 213 220 -- check for dameged book 214 221 215 IF v_status_id = 5 THEN 222 216 UPDATE bookcopy … … 227 221 END IF; 228 222 229 -- book is available 223 230 224 UPDATE bookcopy 231 225 SET available = 1, … … 233 227 WHERE copy_id = v_copy_id; 234 228 235 -- check for late retun 229 236 230 IF v_due_date IS NOT NULL THEN 237 231 v_late_days := CURRENT_DATE - v_due_date; … … 280 274 281 275 ``` 282 -- free copy 276 283 277 SELECT copy_id 284 278 INTO v_copy_id … … 288 282 LIMIT 1; 289 283 290 -- doesnt have-error 284 291 285 IF v_copy_id IS NULL THEN 292 286 RAISE EXCEPTION 'No available copy for this book!'; 293 287 END IF; 294 288 295 -- borrowing row 289 296 290 INSERT INTO borrowing( 297 291 book_id, … … 319 313 ); 320 314 321 -- not available 315 322 316 UPDATE bookcopy 323 317 SET available = 0 … … 355 349 356 350 ``` 357 -- check for free copy 351 358 352 SELECT copy_id 359 353 INTO v_copy_id … … 363 357 LIMIT 1; 364 358 365 --if doesnt have copy, make reservation 359 366 360 IF v_copy_id IS NULL THEN 367 361 … … 478 472 479 473 ``` 480 --if membership is expiredd 474 481 475 IF NEW.membership_end_date < CURRENT_DATE THEN 482 476 NEW.membership_status := 'EXPIRED'; 483 477 484 --if doesn't 478 485 479 ELSE 486 480 NEW.membership_status := 'ACTIVE';
