-
Notifications
You must be signed in to change notification settings - Fork 0
/
script_pt_sql_developer.txt
627 lines (529 loc) · 20.6 KB
/
script_pt_sql_developer.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
-- Ștergere tabela Comenzi
DROP TABLE Comenzi;
-- Ștergere tabela catalog1
DROP TABLE catalog1;
-- Ștergere tabela Piese
DROP TABLE Piese;
-- Ștergere tabela Furnizori
DROP TABLE Furnizori;
-- Ștergere tabela Exceptii
DROP TABLE Exceptii;
-- Ștergere procedura stocată
DROP PROCEDURE GenerateExceptions;
-- Ștergere vedere
DROP VIEW surub_comandat;
CREATE TABLE Furnizori (
idf INT PRIMARY KEY,
numef VARCHAR(255),
adresa VARCHAR(255)
);
CREATE TABLE Piese (
idp INT PRIMARY KEY,
numep VARCHAR(255),
culoare VARCHAR(255)
);
CREATE TABLE catalog1 (
idf INT,
idp INT,
pret DECIMAL(10, 2),
PRIMARY KEY (idf, idp),
FOREIGN KEY (idf) REFERENCES Furnizori(idf),
FOREIGN KEY (idp) REFERENCES Piese(idp)
);
-- Creare tabela Comenzi
CREATE TABLE Comenzi (
idc NUMBER,
idp NUMBER,
idf NUMBER,
cantitate NUMBER,
PRIMARY KEY (idc, idp, idf),
FOREIGN KEY (idp) REFERENCES Piese(idp),
FOREIGN KEY (idf) REFERENCES Furnizori(idf)
);
ALTER TABLE catalog1
ADD moneda VARCHAR(3);
ALTER TABLE catalog1
ADD CONSTRAINT check_pret_positive CHECK (pret >= 0);
ALTER TABLE Piese
ADD CONSTRAINT check_piulita_color CHECK (
(numep LIKE '%piuliță%' AND culoare = 'alb') OR
(numep NOT LIKE '%piuliță%')
);
-- Inserare date pentru Furnizori
INSERT INTO Furnizori (idf, numef, adresa) VALUES (101, 'Furnizor1', 'Localitate1, Strada1, 123');
INSERT INTO Furnizori (idf, numef, adresa) VALUES (102, 'Furnizor2', 'Localitate2, Strada2, 456');
INSERT INTO Furnizori (idf, numef, adresa) VALUES (103, 'Furnizor3', 'Localitate3, Strada3, 788');
INSERT INTO Furnizori (idf, numef, adresa) VALUES (12, 'Furnizor12', 'Localitate12, Strada12, 97');
INSERT INTO Furnizori (idf, numef, adresa) VALUES (15, 'Furnizor15', 'Localitate15, Strada15, 428');
-- Inserare date pentru Piese
INSERT INTO Piese (idp, numep, culoare) VALUES (1, 'surub', 'Alb');
INSERT INTO Piese (idp, numep, culoare) VALUES (2, 'piuliță', 'alb');
INSERT INTO Piese (idp, numep, culoare) VALUES (3, 'Bolt', 'Verde');
INSERT INTO Piese (idp, numep, culoare) VALUES (4, 'Cui', 'Negru');
INSERT INTO Piese (idp, numep, culoare) VALUES (5, 'Ciocan', 'Negru');
INSERT INTO Piese (idp, numep, culoare) VALUES (6, 'Surubelnita', 'Verde');
INSERT INTO Piese (idp, numep, culoare) VALUES (7, 'Firez', 'Gri');
INSERT INTO Piese (idp, numep, culoare) VALUES (8, 'Lopata', 'Negru');
INSERT INTO Piese (idp, numep, culoare) VALUES (9, 'Drujba', 'Portocaliu');
INSERT INTO Piese (idp, numep, culoare) VALUES (10, 'Pensula', 'Rosu');
INSERT INTO Piese (idp, numep, culoare) VALUES (11, 'Cleste', 'Maro');
INSERT INTO Piese (idp, numep, culoare) VALUES (12, 'surub', 'Negru');
-- Inserare date pentru catalog1
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (101, 1, 2.50, 'RON');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (101, 2, 1.50, 'RON');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (101, 3, 5.50, 'RON');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (101, 11, 8.50, 'RON');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 1, 1.50, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 2, 1.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 5, 5.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 7, 6.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 9, 50.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 10, 3.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (102, 11, 7.00, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (103, 1, 1.00, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (103, 2, 1.00, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (103, 3, 2.50, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (103, 11, 3.00, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 2, 3.90, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 4, 4.23, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 6, 8.60, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 8, 11.00, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 10, 25.90, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 11, 3.20, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 1, 1.00, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (12, 3, 2.25, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 1, 2.50, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 2, 3.75, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 3, 4.50, 'EUR');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 8, 10.90, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 9, 12.75, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 10, 1.99, 'USD');
INSERT INTO catalog1 (idf, idp, pret, moneda) VALUES (15, 12, 1.10, 'USD');
-- Inserare date pentru Comenzi
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (1, 101, 1, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (1, 102, 1, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (1, 102, 3, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (1, 103, 2, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (2, 101, 2, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (3, 101, 3, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (4, 101, 11, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (5, 101, 3, 250);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (6, 101, 11, 400);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (7, 102, 1, 2);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (8, 102, 2, 3);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (9, 102, 5, 4);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (10, 102, 7, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (11, 102, 10, 3);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (12, 102, 1, 210);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (13, 102, 1, 400);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (14, 102, 5, 300);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (14, 102, 10, 76);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (16, 103, 11, 4);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (16, 103, 1, 15);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (18, 103, 1, 90);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 1, 250);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 2, 20);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 3, 11);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 4, 9);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 6, 8);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (19, 12, 8, 3);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (20, 12, 10, 45);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (21, 12, 11, 2);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (22, 12, 4, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (23, 12, 4, 9);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (24, 12, 8, 101);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (25, 12, 10, 280);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (26, 12, 11, 34);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (27, 12, 11, 98);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (33, 15, 1, 3);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (34, 15, 2, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (35, 15, 3, 1);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (36, 15, 8, 65);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (37, 15, 9, 4);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (38, 15, 10, 68);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (39, 15, 3, 15);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (40, 15, 3, 21);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (41, 15, 8, 50);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (42, 15, 9, 100);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (43, 15, 9, 250);
INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (43, 15, 12, 10);
-- Interogari
-- 3 a)
SELECT *
FROM Furnizori
WHERE SUBSTR(adresa, -1) = '8'
ORDER BY adresa;
-- 3 b)
SELECT *
FROM Comenzi
WHERE cantitate > 200 AND cantitate != 300;
-- 4 a)
SELECT Comenzi.idc, Comenzi.idf, Comenzi.idp, Comenzi.cantitate
FROM Comenzi
JOIN catalog1 ON Comenzi.idf = catalog1.idf AND Comenzi.idp = catalog1.idp
WHERE catalog1.pret BETWEEN 10 AND 20 AND catalog1.moneda = 'EUR';
-- 4 b)
SELECT DISTINCT c1.idf AS idf1, c2.idf AS idf2
FROM Comenzi c1
JOIN Comenzi c2 ON c1.idp = c2.idp AND c1.cantitate = c2.cantitate
WHERE c1.idc < c2.idc AND c1.idf <> c2.idf;
-- 5 a)
SELECT DISTINCT f.numef
FROM Furnizori f
WHERE NOT EXISTS (
SELECT c1.idp
FROM catalog1 c1
WHERE c1.idf = 101
MINUS
SELECT c2.idp
FROM catalog1 c2
WHERE c2.idf = f.idf
) and f.idf != 101;
-- 5 b)
SELECT *
FROM catalog1 c1
WHERE NOT EXISTS (
SELECT 1
FROM catalog1 c2
WHERE c2.pret > c1.pret
);
-- 6 a)
SELECT C.idf, C.moneda, MIN(C.pret) AS pret_minim, MAX(C.pret) AS pret_maxim
FROM catalog1 C
GROUP BY C.idf, C.moneda
ORDER BY C.idf;
-- 6 b)
SELECT idc, COUNT(DISTINCT idp) AS numar_piese
FROM Comenzi
GROUP BY idc
ORDER BY idc;
-- 7 a)
-- Adaugare comanda
INSERT INTO Comenzi (idc, idf, idp, cantitate)
VALUES (13, 12, 11, 14);
-- Adaugare alta comanda pentru aceeasi piesa la acceasi cantitate
INSERT INTO Comenzi (idc, idf, idp, cantitate)
VALUES (13, 15, 11, 16);
-- 7 b)
-- Identificarea furnizorilor fără piese furnizate
DECLARE
CURSOR furnizor_cursor IS
SELECT DISTINCT f.idf FROM Furnizori f
WHERE NOT EXISTS (SELECT 1 FROM catalog1 c WHERE f.idf = c.idf);
furnizor_rec furnizor_cursor%ROWTYPE;
BEGIN
FOR furnizor_rec IN furnizor_cursor
LOOP
-- Ștergere înregistrări din Comenzi asociate furnizorului
DELETE FROM Comenzi WHERE idf = furnizor_rec.idf;
-- Ștergere înregistrări din catalog1 asociate furnizorului
DELETE FROM catalog1 WHERE idf = furnizor_rec.idf;
-- Ștergere furnizor
DELETE FROM Furnizori WHERE idf = furnizor_rec.idf;
END LOOP;
END;
/
-- 7 c)
-- Reducere preț pentru piesele comandate în cantitate mai mare de 200
UPDATE catalog1
SET pret = pret * 0.98
WHERE idp IN (SELECT idp FROM Comenzi WHERE cantitate > 200);
-- 8
CREATE TABLE Exceptii (
idp NUMBER,
idf NUMBER,
natura_exceptiei VARCHAR2(50),
idf2 NUMBER
);
create or replace PROCEDURE GenerateExceptions AS
Pidp NUMBER;
C1idf NUMBER;
C2idf NUMBER;
V_Count NUMBER;
Already_inserted NUMBER;
BEGIN
FOR P IN (SELECT DISTINCT idp FROM catalog1) LOOP
Pidp := P.idp;
FOR C1 IN (SELECT DISTINCT idf, pret FROM catalog1 WHERE idp = P.idp) LOOP
C1idf := C1.idf;
FOR C2 IN (SELECT DISTINCT idf, pret FROM catalog1 WHERE idp = P.idp AND idf <> C1.idf) LOOP
C2idf := C2.idf;
IF C1.pret IS NOT NULL AND C2.pret IS NOT NULL THEN
-- Verificăm dacă există comenzi pentru piesa și furnizorii selectați
SELECT COUNT(*)
INTO V_Count
FROM Comenzi
WHERE idp = Pidp AND (idf = C1idf OR idf = C2idf);
-- Verificam ca perechea C1idf si C2idf sa nu existe deja
SELECT COUNT(*)
INTO Already_inserted
FROM Exceptii
WHERE idp = Pidp AND ((idf = C1idf AND idf2 = C2idf) OR (idf = C2idf AND idf2 = C1idf));
IF V_Count > 0 AND Already_inserted = 0 THEN
IF C1.pret = C2.pret THEN
INSERT INTO Exceptii (idp, idf, natura_exceptiei, idf2)
VALUES (Pidp, C1idf, 'Preț egal', C2idf);
ELSIF C1.pret < C2.pret THEN
INSERT INTO Exceptii (idp, idf, natura_exceptiei, idf2)
VALUES (Pidp, C1idf, 'Preț mai mic', C2idf);
ELSE
INSERT INTO Exceptii (idp, idf, natura_exceptiei, idf2)
VALUES (Pidp, C1idf, 'Preț mai mare', C2idf);
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
END LOOP;
END;
/
--begin
-- GenerateExceptions();
--end;
--delete from exceptii;
--select * from exceptii
--order by idf
--9 triggere
-- 9 a)
CREATE OR REPLACE TRIGGER CheckMinPrice
BEFORE INSERT ON Comenzi
FOR EACH ROW
DECLARE
MinPrice NUMBER;
SupplierPrice NUMBER;
BEGIN
-- Selectăm prețul minim pentru piesa la care se referă comanda
SELECT MIN(pret)
INTO MinPrice
FROM catalog1
WHERE idp = :NEW.idp;
-- Selectăm prețul furnizorului din catalog1 pentru piesa specificată în comandă
SELECT pret
INTO SupplierPrice
FROM catalog1
WHERE idp = :NEW.idp AND idf = :NEW.idf;
-- Verificăm dacă prețul furnizorului din comandă este mai mic decât minimul
IF SupplierPrice > MinPrice THEN
RAISE_APPLICATION_ERROR(-20004, 'Prețul furnizorului nu este minim pentru piesa comandată.');
END IF;
END;
/
--test
--INSERT INTO Comenzi (idc, idf, idp, cantitate) VALUES (100, 12, 2, 10);
-- 9 b)
CREATE VIEW surub_comandat AS
SELECT idc, f.idf, numef, adresa, p.idp, culoare, pret, cantitate
FROM Furnizori f, Piese p, catalog1 t, Comenzi c
WHERE p.numep = 'surub' AND
f.idf = t.idf AND
p.idp = t.idp AND
c.idf = t.idf AND
c.idp = t.idp;
CREATE OR REPLACE TRIGGER InsteadOfInsertSurubComandat
INSTEAD OF INSERT ON surub_comandat
FOR EACH ROW
DECLARE
PiesaExists NUMBER;
catalog1Exists NUMBER;
FurnizorExists NUMBER;
BEGIN
-- Verificăm dacă piesa și furnizorul există în catalog1
SELECT COUNT(*)
INTO PiesaExists
FROM piese
WHERE idp = :NEW.idp;
SELECT COUNT(*)
INTO catalog1Exists
FROM catalog1
WHERE idp = :NEW.idp AND idf = :NEW.idf;
SELECT COUNT(*)
INTO FurnizorExists
FROM furnizori
WHERE idf = :NEW.idf;
IF PiesaExists > 0 AND catalog1Exists > 0 AND FurnizorExists > 0 THEN
-- Adăugăm comanda în tabela Comanda
INSERT INTO Comenzi (idc, idf, idp, cantitate)
VALUES (:NEW.idc, :NEW.idf, :NEW.idp, :NEW.cantitate);
ELSIF PiesaExists = 0 THEN
-- Aruncăm o excepție că Piesa nu există în Piese
RAISE_APPLICATION_ERROR(-20001, 'Piesa nu există în Piese.');
ELSIF FurnizorExists = 0 THEN
-- Aruncăm o excepție că Furnizorul nu există în Furnizori
RAISE_APPLICATION_ERROR(-20002, 'Furnizorul nu există în Furnizori.');
ELSIF catalog1Exists = 0 THEN
-- Aruncăm o excepție că Piesa nu există în catalog1 pentru Furnizor
RAISE_APPLICATION_ERROR(-20003, 'Piesa nu există în catalog1 pentru Furnizor.');
END IF;
END;
/
--INSERT INTO surub_comandat (idc, idf, numef, adresa, idp, culoare, pret, cantitate)
--VALUES (44, 103, 'Furnizor1', 'Localitate1, Strada1, 123', 2, 'Alb', 0.5, 5);
--piesa nu este in catalog1 pentru frunizor
--INSERT INTO surub_comandat (idc, idf, numef, adresa, idp, culoare, pret, cantitate)
--VALUES (45, 101, 'Furnizor1', 'Localitate1, Strada1, 123', 4, 'Alb', 0.5, 5);
--piesa nu exista in piese
--INSERT INTO surub_comandat (idc, idf, numef, adresa, idp, culoare, pret, cantitate)
--VALUES (45, 101, 'Furnizor1', 'Localitate1, Strada1, 123', 30, 'Alb', 0.5, 5);
--furnizorul nu exista in furnizori
--INSERT INTO surub_comandat (idc, idf, numef, adresa, idp, culoare, pret, cantitate)
--VALUES (45, 206, 'Furnizor206', 'Localitate2, Strada16, 206', 30, 'Alb', 0.5, 5);
-- inca cateva functii si proceduri pentru imbunatatirea site-ului
--functie care calucleaza pretul total al unei comenzi
CREATE OR REPLACE FUNCTION CalculValoareComanda (
p_idc IN NUMBER,
p_idf IN NUMBER,
p_idp IN NUMBER
) RETURN NUMBER
AS
v_valoare NUMBER := 0;
BEGIN
SELECT SUM(cantitate * pret)
INTO v_valoare
FROM Comenzi c
JOIN Catalog1 t ON c.idp = t.idp AND c.idf = t.idf
WHERE c.idc = p_idc AND c.idf = p_idf AND c.idp = p_idp;
RETURN v_valoare;
END;
/
--functie care calucleaza cate piese s-au comandat pentru un idp
CREATE OR REPLACE FUNCTION TotalPieseComandate(idp_in IN NUMBER)
RETURN NUMBER
IS
TotalComenzi NUMBER;
BEGIN
-- Calculăm numărul total de piese comandate
SELECT COUNT(*)
INTO TotalComenzi
FROM Comenzi
WHERE idp = idp_in;
-- Returnăm rezultatul
RETURN TotalComenzi;
END;
/
--functie care returneaza cea mai cumparata piesa
CREATE OR REPLACE FUNCTION CeaMaiCumparataPiesa RETURN NUMBER
IS
PiesaCumparata NUMBER;
BEGIN
-- Selectăm cea mai cumpărată piesă
SELECT idp
INTO PiesaCumparata
FROM (
SELECT idp, COUNT(DISTINCT idc) as NumarComenzi
FROM Comenzi
GROUP BY idp
ORDER BY NumarComenzi DESC
)
WHERE ROWNUM = 1;
RETURN PiesaCumparata;
END CeaMaiCumparataPiesa;
/
--procedura care apica reducerea pentru acea piesa
CREATE OR REPLACE PROCEDURE AplicaReducerea(IN_PiesaID IN NUMBER)
IS
BEGIN
-- Aplicăm reducerea de 5% la toți furnizorii care au piesa în catalog
UPDATE Catalog1
SET pret = pret * 0.95
WHERE idp = IN_PiesaID;
END AplicaReducerea;
/
--procedura care sterge furnizor avand grija sa stearga comenzile si catalogul asociate
CREATE OR REPLACE PROCEDURE StergeFurnizor (
IDF_param IN NUMBER
)
IS
PieseExist NUMBER;
ComenziExist NUMBER;
BEGIN
-- Verificăm dacă furnizorul are piese în catalog
SELECT COUNT(*)
INTO PieseExist
FROM Catalog1
WHERE IDF = IDF_param;
-- Verificăm dacă furnizorul are comenzi
SELECT COUNT(*)
INTO ComenziExist
FROM Comenzi
WHERE IDF = IDF_param;
-- Dacă furnizorul are comenzi, le ștergem
IF ComenziExist > 0 THEN
DELETE FROM Comenzi
WHERE IDF = IDF_param;
END IF;
-- Dacă furnizorul are piese în catalog, le ștergem
IF PieseExist > 0 THEN
DELETE FROM Catalog1
WHERE IDF = IDF_param;
END IF;
-- Ștergem furnizorul
DELETE FROM Furnizori
WHERE IDF = IDF_param;
DBMS_OUTPUT.PUT_LINE('Furnizorul a fost șters cu succes.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Eroare: Furnizorul nu a putut fi șters.');
END StergeFurnizor;
/
--procedura care sterge piesa avand grija sa stearga catalogul si comenzile aferente
CREATE OR REPLACE PROCEDURE StergePiesa (
IDP_param IN NUMBER
)
IS
BEGIN
-- Ștergem înregistrările din Comenzi
DELETE FROM Comenzi
WHERE IDP = IDP_param;
-- Ștergem înregistrările din Catalog
DELETE FROM Catalog1
WHERE IDP = IDP_param;
-- Ștergem înregistrările din Piese
DELETE FROM Piese
WHERE IDP = IDP_param;
DBMS_OUTPUT.PUT_LINE('Piesa a fost ștearsă cu succes.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Eroare: Piesa nu a putut fi ștearsă.');
END StergePiesa;
/
--procedura echivalenta cu trigger ul instead of pe view
CREATE OR REPLACE PROCEDURE AdaugaInComenziSurubComandat (
IDC_param IN NUMBER,
IDF_param IN NUMBER,
IDP_param IN NUMBER,
Cantitate_param IN NUMBER
)
IS
PiesaExists NUMBER;
Catalog1Exists NUMBER;
FurnizorExists NUMBER;
BEGIN
-- Verificăm dacă piesa și furnizorul există în Piese, Catalog1 și Furnizori
SELECT COUNT(*)
INTO PiesaExists
FROM Piese
WHERE IDP = IDP_param;
SELECT COUNT(*)
INTO Catalog1Exists
FROM Catalog1
WHERE IDP = IDP_param AND IDF = IDF_param;
SELECT COUNT(*)
INTO FurnizorExists
FROM Furnizori
WHERE IDF = IDF_param;
IF PiesaExists > 0 AND Catalog1Exists > 0 AND FurnizorExists > 0 THEN
-- Adăugăm comanda în tabela Comenzi
INSERT INTO Comenzi (IDC, IDF, IDP, Cantitate)
VALUES (IDC_param, IDF_param, IDP_param, Cantitate_param);
ELSIF PiesaExists = 0 THEN
-- Aruncăm o excepție că Piesa nu există în Piese
RAISE_APPLICATION_ERROR(-20001, 'Piesa nu există în Piese.');
ELSIF FurnizorExists = 0 THEN
-- Aruncăm o excepție că Furnizorul nu există în Furnizori
RAISE_APPLICATION_ERROR(-20002, 'Furnizorul nu există în Furnizori.');
ELSIF Catalog1Exists = 0 THEN
-- Aruncăm o excepție că Piesa nu există în Catalog1 pentru Furnizor
RAISE_APPLICATION_ERROR(-20003, 'Piesa nu există în Catalog1 pentru Furnizor.');
END IF;
END;
/