· 7 years ago · Jan 05, 2019, 08:34 PM
1CREATE OR REPLACE PACKAGE vakantie_pkg
2AS
3PROCEDURE voeg_klant_toe
4(p_klnr IN klanten.klnr%TYPE,
5 p_achternaam IN klanten.achternaam%TYPE,
6 p_voornaam IN klanten.voornaam%TYPE,
7 p_straat IN klanten.achternaam%TYPE,
8 p_huisnr IN klanten.huisnr%TYPE,
9 p_postcode IN klanten.postcode%TYPE,
10 p_gemeente IN klanten.gemeente%TYPE,
11 p_status IN klanten.status%TYPE) ;
12PROCEDURE voeg_reservatie_toe
13(p_resnr IN reservaties.resnr%TYPE,
14 p_bunr IN reisburs.bunr%TYPE,
15 p_klnr IN reservaties.klnr%TYPE,
16 p_parkcode IN reservaties.parkcode%TYPE,
17 p_typenr IN reservaties.typenr%TYPE,
18 p_hnr IN reservaties.hnr%TYPE,
19 p_boekingsdatum IN reservaties.boekingsdatum%TYPE DEFAULT SYSDATE,
20 p_begindat IN DATE,
21 p_einddat IN DATE,
22 p_kode IN reservaties.kode%TYPE,
23 p_status IN reservaties.status%TYPE DEFAULT 'OPEN',
24 p_promocode IN reservaties.promocode%TYPE);
25PROCEDURE verwijder_klant
26(p_klnr In klanten.klnr%TYPE);
27/* vraag 3 overloading */
28PROCEDURE verwijder_klant
29(p_voornaam In klanten.voornaam%TYPE,
30 p_achternaam IN klanten.achternaam%TYPE);
31PROCEDURE controle_zwarte_lijst;
32PROCEDURE niet_populaire_vakhuizen_cur
33(p_parkcode IN vakantiehuizen.parkcode%TYPE);
34FUNCTION meest_verhuurd
35(p_parkcode IN reservaties.parkcode%TYPE)
36RETURN VARCHAR2;
37FUNCTION bereken_bedrag_te_betalen
38(p_resnr IN reservaties.resnr%TYPE,
39 p_bunr IN reservaties.bunr%TYPE)
40 RETURN NUMBER;
41/* vraag 4 */
42e_ouder_niet_gevonden EXCEPTION;
43PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
44/* vraag 5 */
45in de zelfstandige procedure GOEDE_KLANTEN moet je de cursor niet definieren en in het uitvoeringsdeel verwijzen naar de cursor uit de package */
46CURSOR c_goede_klanten
47IS SELECT k.klnr, achternaam, count(r.klnr) aantal
48 FROM klanten k
49 JOIN reservaties r ON r.klnr = k.klnr
50 GROUP BY k.klnr, achternaam
51 HAVING count(r.klnr) > (SELECT AVG(COUNT(klnr))
52 FROM reservaties
53 GROUP BY klnr);
54/* vraag 6 */
55collection voor initialisatiecode. Je had ze ook in de package body kunnen plaatsen */
56TYPE type_rec_res IS RECORD
57(resnr reservaties.resnr%TYPE,
58bunr reservaties.bunr%TYPE,
59voornaam klanten.voornaam%TYPE,
60achternaam klanten.achternaam%TYPE ,
61parkcode reservaties.parkcode%TYPE,
62typenr reservaties.typenr%TYPE,
63hnr reservaties.hnr%TYPE,
64begindat reservaties.begindat%TYPE,
65einddat reservaties.einddat%TYPE);
66TYPE type_tab_res IS TABLE OF type_rec_res
67INDEX BY PLS_INTEGER;
68t_res type_tab_res;
69END;
70
71create or replace PACKAGE BODY VAKANTIE_PKG
72AS
73PROCEDURE voeg_klant_toe
74(p_klnr IN klanten.klnr%TYPE,
75 p_achternaam IN klanten.achternaam%TYPE,
76 p_voornaam IN klanten.voornaam%TYPE,
77 p_straat IN klanten.achternaam%TYPE,
78 p_huisnr IN klanten.huisnr%TYPE,
79 p_postcode IN klanten.postcode%TYPE,
80 p_gemeente IN klanten.gemeente%TYPE,
81 p_status IN klanten.status%TYPE)
82AS
83BEGIN
84 INSERT INTO klanten
85 VALUEs(p_klnr,p_achternaam,p_voornaam,p_straat,p_huisnr,p_postcode,p_gemeente,p_status);
86END;
87PROCEDURE voeg_reservatie_toe
88(p_resnr IN reservaties.resnr%TYPE,
89 p_bunr IN reisburs.bunr%TYPE,
90 p_klnr IN reservaties.klnr%TYPE,
91 p_parkcode IN reservaties.parkcode%TYPE,
92 p_typenr IN reservaties.typenr%TYPE,
93 p_hnr IN reservaties.hnr%TYPE,
94 p_boekingsdatum IN reservaties.boekingsdatum%TYPE DEFAULT SYSDATE,
95 p_begindat IN DATE,
96 p_einddat IN DATE,
97 p_kode IN reservaties.kode%TYPE,
98 p_status IN reservaties.status%TYPE DEFAULT 'OPEN',
99 p_promocode IN reservaties.promocode%TYPE)
100AS
101e_check EXCEPTION;
102PRAGMA EXCEPTION_INIT(e_check,-2290);
103BEGIN
104 INSERT INTO reservaties(resnr,bunr,klnr,parkcode,typenr,hnr,boekingsdatum,begindat,einddat,kode,status,promocode)
105 VALUES(p_resnr,p_bunr,p_klnr,p_parkcode,p_typenr,p_hnr,p_boekingsdatum,p_begindat,p_einddat,p_kode,p_status,p_promocode);
106 COMMIT;
107EXCEPTION
108WHEN e_check
109 THEN IF SQLERRM LIKE '%STATUS%'
110 THEN DBMS_OUTPUT.PUT_LINE('status mag enkel OPEN, BETAALD of GESLOTEN zijn');
111 ELSIF SQLERRM LIKE '%KODE%'
112 THEN DBMS_OUTPUT.PUT_LINE('kode kan enkel 1 of 2 zijn');
113 ELSIF SQLERRM LIKE '%DATUM%'
114 THEN DBMS_OUTPUT.PUT_LINE('einddatum moet groter zijn dan begindatum');
115 ELSIF SQLERRM LIKE '%PARKCODE%'
116 THEN DBMS_OUTPUT.PUT_LINE('Parkcode moet in uppercase');
117 ELSIF SQLERRM LIKE '%TYP%'
118 THEN DBMS_OUTPUT.PUT_LINE('TYPENR moet in uppercase');
119 END IF;
120WHEN e_ouder_niet_gevonden
121THEN IF SQLERRM LIKE '%BUR%'
122 THEN DBMS_OUTPUT.PUT_LINE('bunr bestaat niet');
123 ELSIF SQLERRM LIKE '%KLANT%'
124 THEN DBMS_OUTPUT.PUT_LINE('klant bestaat niet');
125 ELSIF SQLERRM LIKE '%PROMO%'
126 THEN DBMS_OUTPUT.PUT_LINE('promocode bestaat niet');
127 ELSIF SQLERRM LIKE '%VAK%'
128 THEN DBMS_OUTPUT.PUT_LINE('vakantiehuis bestaat niet');
129 END IF;
130WHEN DUP_VAL_ON_INDEX
131THEN DBMS_OUTPUT.PUT_LINE('combinatie resnr,bunr bestaat al');
132END;
133PROCEDURE verwijder_klant
134(p_klnr In klanten.klnr%TYPE)
135AS
136e_klant_bestaat_niet EXCEPTION;
137e_geen_reservaties EXCEPTION;
138e_alles_betaald EXCEPTION;
139e_nog_openstaand EXCEPTION;
140v_aantal NUMBER(2);
141v_betaald NUMBER(2);
142v_klnr klanten.klnr%TYPE;
143BEGIN
144SELECT COUNT(klnr) INTO v_aantal
145FROM klanten
146WHERE klnr = p_klnr;
147IF v_aantal=0
148THEN raise e_klant_bestaat_niet;
149ELSE SELECT COUNT(klnr) INTO v_aantal
150 FROM reservaties
151 WHERE klnr=p_klnr;
152 IF v_aantal=0
153 THEN raise e_geen_reservaties;
154 END IF;
155END IF;
156SELECT COUNT(*) INTO v_aantal
157FROM reservaties
158WHERE klnr=p_klnr
159AND einddat<SYSDATE;
160SELECT COUNT(*) INTO v_betaald
161FROM reservaties
162WHERE klnr=p_klnr
163AND einddat<SYSDATE
164AND status='BETAALD';
165IF v_aantal=v_betaald
166THEN raise e_alles_betaald;
167ELSE raise e_nog_openstaand;
168END IF;
169EXCEPTION
170 WHEN e_klant_bestaat_niet
171 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_klnr||' bestaat niet');
172 WHEN e_geen_reservaties
173 THEN DBMS_OUTPUT.PUT_LINE ('Geen reservaties voor klant '||p_klnr);
174 DELETE FROM klanten where klnr = p_klnr;
175 WHEN e_alles_betaald
176 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_klnr||' betaalde alles');
177 DELETE FROM betalingen
178 WHERE (resnr,bunr) IN (SELECT resnr,bunr
179 FROM reservaties
180 WHERE klnr=p_klnr);
181 DELETE FROM reservaties WHERE klnr=p_klnr;
182 DELETE FROM klanten WHERE klnr=p_klnr;
183 WHEN e_nog_openstaand
184 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_klnr||' wordt opgevolgd');
185
186 DELETE FROM op_te_volgen;
187 INSERT INTO op_te_volgen
188 SELECT resnr,bunr,klnr,parkcode,typenr,hnr,boekingsdatum,begindat,einddat,kode,status,promocode
189 FROM reservaties
190 WHERE klnr=p_klnr;
191 END ;
192/* overloading */
193 PROCEDURE verwijder_klant
194(p_voornaam In klanten.voornaam%TYPE,
195 p_achternaam IN klanten.achternaam%TYPE)
196 AS
197e_klant_bestaat_niet EXCEPTION;
198e_geen_reservaties EXCEPTION;
199e_alles_betaald EXCEPTION;
200e_nog_openstaand EXCEPTION;
201v_aantal NUMBER(2);
202v_betaald NUMBER(2);
203v_klnr klanten.klnr%TYPE;
204BEGIN
205SELECT COUNT(klnr) INTO v_aantal
206FROM klanten
207WHERE UPPER(voornaam)=UPPER(p_voornaam)
208AND UPPER(achternaam)=UPPER(p_achternaam);
209IF v_aantal=0
210THEN raise e_klant_bestaat_niet;
211ELSE SELECT COUNT(klnr) INTO v_aantal
212 FROM reservaties
213 WHERE klnr IN (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
214 AND UPPER(achternaam)=UPPER(p_achternaam));
215 IF v_aantal=0
216 THEN raise e_geen_reservaties;
217 END IF;
218END IF;
219SELECT COUNT(*) INTO v_aantal
220FROM reservaties
221WHERE klnr IN (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
222AND UPPER(achternaam)=UPPER(p_achternaam))
223AND einddat<SYSDATE;
224SELECT COUNT(*) INTO v_betaald
225FROM reservaties
226WHERE klnr IN (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
227AND UPPER(achternaam)=UPPER(p_achternaam))
228AND einddat<SYSDATE
229AND status='BETAALD';
230IF v_aantal=v_betaald
231THEN raise e_alles_betaald;
232ELSE raise e_nog_openstaand;
233END IF;
234EXCEPTION
235 WHEN e_klant_bestaat_niet
236 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_voornaam||' '||p_achternaam||' bestaat niet');
237 WHEN e_geen_reservaties
238 THEN DBMS_OUTPUT.PUT_LINE ('Geen reservaties voor klant '||p_voornaam||' '||p_achternaam);
239 DELETE FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
240 AND UPPER(achternaam)=UPPER(p_achternaam);
241 WHEN e_alles_betaald
242 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_voornaam||' '||p_achternaam||' betaalde alles');
243 DELETE FROM betalingen
244 WHERE (resnr,bunr) IN (SELECT resnr,bunr
245 FROM reservaties
246 WHERE klnr in (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
247 AND UPPER(achternaam)=UPPER(p_achternaam)));
248 DELETE FROM reservaties WHERE klnr in (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
249 AND UPPER(achternaam)=UPPER(p_achternaam));
250 DELETE FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
251 AND UPPER(achternaam)=UPPER(p_achternaam);
252 WHEN e_nog_openstaand
253 THEN DBMS_OUTPUT.PUT_LINE ('klant '||p_voornaam||' '||p_achternaam||' wordt opgevolgd');
254
255 DELETE FROM op_te_volgen;
256 INSERT INTO op_te_volgen
257 SELECT resnr,bunr,klnr,parkcode,typenr,hnr,boekingsdatum,begindat,einddat,kode,status,promocode
258 FROM reservaties
259 WHERE klnr IN (SELECT klnr FROM klanten WHERE UPPER(voornaam)=UPPER(p_voornaam)
260 AND UPPER(achternaam)=UPPER(p_achternaam));
261 END ;
262PROCEDURE controle_zwarte_lijst
263AS
264TYPE type_tab_klant IS TABLE OF klanten%ROWTYPE;
265t_zwarte_lijst type_tab_klant;
266
267BEGIN
268DELETE FROM zwarte_lijst_klanten;
269SELECT DISTINCT k.* BULK COLLECT INTO t_zwarte_lijst
270FROM klanten k
271JOIN reservaties r ON r.klnr = k.klnr
272WHERE r.status = 'OPEN' AND ADD_MONTHS(einddat,1)<SYSDATE
273ORDER BY k.klnr;
274
275FORALL i IN INDICES OF t_zwarte_lijst
276 INSERT INTO zwarte_lijst_klanten
277 VALUES t_zwarte_lijst(i);
278FORALL i IN INDICES OF t_zwarte_lijst
279 UPDATE klanten
280 SET status = 'ZWART'
281 WHERE klnr= t_zwarte_lijst(i).klnr;
282END controle_zwarte_lijst;
283PROCEDURE niet_populaire_vakhuizen_cur
284(p_parkcode IN vakantiehuizen.parkcode%TYPE)
285AS
286CURSOR cur_geen_pop_vakantiehuis
287IS SELECT h.parkcode, h.typenr, h.hnr
288 FROM vakantiehuizen h
289 WHERE NOT EXISTS (SELECT 'x'
290 FROM reservaties
291 WHERE parkcode = h.parkcode
292 AND typenr= h.typenr
293 AND hnr=h.hnr)
294 AND parkcode = p_parkcode
295 ORDER BY parkcode, typenr, hnr;
296BEGIN
297FOR r_geen_pop_vkh IN cur_geen_pop_vakantiehuis
298LOOP
299DBMS_OUTPUT.PUT_LINE
300(r_geen_pop_vkh.parkcode
301||' '||
302r_geen_pop_vkh.typenr
303||' '||
304r_geen_pop_vkh.hnr);
305END LOOP;
306END;
307FUNCTION meest_verhuurd
308(p_parkcode IN reservaties.parkcode%TYPE)
309RETURN VARCHAR2
310AS
311v_output VARCHAR2(15);
312v_typenr reservaties.typenr%TYPE;
313v_hnr reservaties.hnr%TYPE;
314BEGIN
315 SELECT typenr,hnr INTO v_typenr,v_hnr
316 FROM reservaties
317 WHERE parkcode=p_parkcode
318 GROUP BY typenr,hnr
319 HAVING COUNT(*)=(SELECT MAX(COUNT(*))
320 FROM reservaties
321 WHERE parkcode=p_parkcode
322 GROUP BY typenr,hnr);
323 v_output:=v_typenr||' '||v_hnr;
324 RETURN v_output;
325END;
326FUNCTION bereken_bedrag_te_betalen
327(p_resnr IN reservaties.resnr%TYPE,
328 p_bunr IN reservaties.bunr%TYPE)
329RETURN NUMBER
330AS
331v_parkcode reservaties.parkcode%TYPE;
332v_typenr reservaties.typenr%TYPE;
333v_kode reservaties.kode%TYPE;
334v_promocode reservaties.promocode%TYPE;
335v_begindat DATE;
336v_einddat DATE;
337v_prijs NUMBER(6,2);
338v_bedrag NUMBER(6,2);
339v_extra NUMBER(1);
340v_kortingperc promoties.kortingperc%TYPE :=0;
341BEGIN
342 SELECT parkcode,typenr,kode,promocode ,begindat,einddat
343 INTO v_parkcode,v_typenr,v_kode,v_promocode,v_begindat,v_einddat
344 FROM reservaties
345 WHERE resnr=p_resnr
346 AND bunr=p_bunr;
347 IF v_kode=1
348 THEN SELECT prijs_weekend INTO v_prijs
349 FROM type_huis_prijzen
350 JOIN seizoenen ON code=seizoencode
351 WHERE parkcode=v_parkcode
352 AND typenr=v_typenr
353 AND v_begindat >=begindatum
354 AND v_einddat<=einddatum;
355 ELSIF v_kode=2
356 THEN SELECT prijs_midweek INTO v_prijs
357 FROM type_huis_prijzen
358 JOIN seizoenen ON code=seizoencode
359 WHERE parkcode=v_parkcode
360 AND typenr=v_typenr
361 AND v_begindat >=begindatum
362 AND v_einddat<=einddatum;
363 ELSIF TO_CHAR(v_begindat,'dy')='vr'
364 THEN v_extra:=v_einddat-(v_begindat+2);
365 SELECT prijs_weekend + prijs_extra_dag*v_extra INTO v_prijs
366 FROM type_huis_prijzen
367 JOIN seizoenen ON code=seizoencode
368 WHERE parkcode=v_parkcode
369 AND typenr=v_typenr
370 AND v_begindat >=begindatum
371 AND v_einddat<=einddatum;
372 ELSE v_extra:= v_einddat-(v_begindat+4);
373 SELECT prijs_midweek + prijs_extra_dag*v_extra INTO v_prijs
374 FROM type_huis_prijzen
375 JOIN seizoenen ON code=seizoencode
376 WHERE parkcode=v_parkcode
377 AND typenr=v_typenr
378 AND v_begindat >=begindatum
379 AND v_einddat<=einddatum;
380 END IF;
381 IF v_promocode IS NOT NULL
382 THEN SELECT kortingperc INTO v_kortingperc
383 FROM promoties
384 WHERE promocode=v_promocode;
385 END IF;
386 v_bedrag:=v_prijs - (v_prijs*(v_kortingperc/100));
387 RETURN v_bedrag;
388END;
389*/ initialisatiecode */
390BEGIN
391SELECT resnr, bunr, voornaam, achternaam, parkcode, typenr, hnr, begindat, einddat
392BULK COLLECT INTO t_res
393FROM klanten k
394JOIN reservaties r ON k.klnr = r.klnr
395WHERE r.status='BETAALD'
396ORDER BY k.klnr;
397FOR i IN 1..t_res.COUNT
398LOOP
399 DBMS_OUTPUT.PUT_LINE (t_res(i). resnr|| ' ' || t_res(i).bunr || ' ' ||
400t_res(i).voornaam || ' ' || t_res(i).achternaam || ' ' ||t_res(i).parkcode || ' ' || t_res(i).typenr
401|| ' ' || t_res(i).hnr || ' ' || t_res(i).begindat|| ' ' || t_res(i).einddat);
402END LOOP;
403END vakantie_pkg;