· 6 years ago · Jan 28, 2020, 02:38 PM
1-- Übungsserie 6
2-- Userdefined functions and procedures
3-- 2 Teile
4
5/************************************ Übung Teil 1 ******************************************************************************/
6
7-- Beispiel aus Übung 5 als Funktion
8use SW1920
9go
10
11
12/** Aufgabe 1
13Schreiben Sie die Funktion fn_EmpVac, die für einen Mitarbeiter (Übergabe der Personalnummer) die Anzahl der Urlaubstage
14in Abhängigkeit von der Abteilung ermittelt und ausgibt. Folgende Regeln sollen gelten:
15Alle Mitarbeiter aller Sales-Abteilungen erhalten 20 Tage Urlaub,
16die Mitarbeiter von Operations erhalten 25 Tage,
17die Mitarbeiter von Research erhalten 28 Tage,
18die Mitarbeiter aller anderen Abteilungen erhalten 30 Tage Urlaub.
19Falls die Personalnummer unbekannt ist, soll -1 zurückgegeben werden.
20
21Testen Sie mit
221. 7401 --> 20
232. 7839 --> 30
243. 7788 --> 28
253. 7902 --> 25
263. 1111 --> -1
27*/
28
29if exists (select * from sys.objects where name = 'fn_EmpVac' and type = 'FN')
30drop function fn_EmpVac
31go
32create function fn_EmpVac (@empno int)
33returns int
34as
35begin
36 declare
37 @urlaub int, @abt varchar(50)
38
39 if not exists(select * from employee where empno=@empno)
40 begin
41 set @urlaub = -1
42 end
43 else
44 begin
45 select @abt=dname
46 from departments d join employee e on d.deptno=e.deptno
47 where empno=@empno
48
49 if @abt like 'Sales%'
50 begin
51 set @urlaub = 20
52 end
53 else if @abt = 'Operations'
54 begin
55 set @urlaub = 25
56 end
57 else if @abt = 'Research'
58 begin
59 set @urlaub = 28
60 end
61 else
62 begin
63 set @urlaub = 30
64 end
65 end
66 return @urlaub
67end
68GO
69
70
71--Testen
72select dbo.fn_EmpVac (7401)
73select dbo.fn_EmpVac (7839)
74select dbo.fn_EmpVac (7788)
75select dbo.fn_EmpVac (7902)
76select dbo.fn_EmpVac (1111)
77go
78
79/* Aufgabe 2
80Schreiben Sie die Prozedur p_UpdVacTime, die die Spalte VACTIME in der Tabelle employee füllt
81Der jeweilige Urlaubsanspruch des Mitarbeiters soll mittels deer Gunktion fn_EmpVac ermittelt werden
82*/
83
84if exists (select * from sys.objects where name = 'p_UpdVacTime' and type = 'P')
85drop procedure p_UpdVacTime
86go
87create procedure p_UpdVacTime
88as
89begin
90 declare
91 empCur cursor
92 scroll
93 for select empno from employee for update
94
95 declare
96 @empno int
97
98 open empCur
99
100 fetch first from empCur into @empno
101
102 while @@FETCH_STATUS=0
103 begin
104 update employee
105 set vactime = dbo.fn_EmpVac(empno)
106 where current of empCur
107 fetch next from empCur into @empno
108 end
109 close empCur
110 deallocate empCur
111end
112go
113
114-- Ausführen der Prozedur
115exec p_UpdVacTime
116
117/* * Aufgabe 3
118Schreiben Sie die Funktion fn_SalesComm, die als Eingabe eine Personalnummer erwartet.
119Die Funktion soll für einen bestimmten Kundenbetreuer die zu zahlende Provision ermitteln.
120Diese soll jeweils 1% des Gesamtumsatzes aller betreuten Kunden dieses Mitarbeiters betragen
121und als Dezimalzahl mit 2 Nachkommastellen ausgegeben werden. Provisionen liegen unter 10000$
122Testen Sie mit
1237401 -->339.04
1247602 -->78.16
125*/
126
127
128if exists (select * from sys.objects where name = 'fn_SalesComm' and type = 'FN')
129drop function fn_SalesComm
130go
131create function fn_SalesComm (@empno int)
132returns decimal(6,2)
133as
134begin
135 declare
136 @comm decimal(6,2)
137
138 if not exists(select * from employee where empno=@empno)
139 begin
140 set @comm = -1
141 end
142 else
143 begin
144 select @comm = sum(qty*actualprice)*0.01
145 from customer c
146 join orders o on c.custid=o.custid
147 join orderitems oi on o.ordid=oi.ordid
148 where repid= @empno
149 end
150 return @comm
151end
152GO
153
154-- Test
155select dbo.fn_SalesComm(7401)
156select dbo.fn_SalesComm(7602)
157select dbo.fn_SalesComm(1111)
158
159
160/* * Aufgabe 4
161Schreiben Sie die Prozedur p_SalesComm, mittels deren alle Provisionen in die Tabelle Employee eingetragen werden sollen.
162Schauen Sie sich die Werte für COMM in der Tabelle Employee VOR und NACH dem Ausführen der Prozedur an.
163Warum gibt es plötzlich NULL-Werte? Ist das korrekt?
164Falls nein, wie könnte man das verhindern?
165*/
166
167
168if exists (select * from sys.objects where name = 'p_SalesComm' and type = 'P')
169drop procedure p_SalesComm
170go
171create procedure p_SalesComm
172as
173begin
174 declare
175 empCur cursor
176 scroll
177 for select empno from employee for update
178
179 declare
180 @empno int
181
182 open empCur
183
184 fetch first from empCur into @empno
185
186 while @@FETCH_STATUS=0
187 begin
188 update employee
189 set comm = dbo.fn_SalesComm(empno)
190 where current of empCur
191 fetch next from empCur into @empno
192 end
193 close empCur
194 deallocate empCur
195end
196go
197
198--Tabelle aktualisieren
199exec p_SalesComm
200
201
202/* * Aufgabe 5
203Schreiben Sie die Funktion fn_EmpTime, mit der Sie für einen bestimmten Mitarbeiter (Übergabe der Personalnummer) dessen bzw. deren Dauer der Betriebszugehörigkeit in Jahren ermitteln und zurückgeben.
204Falls die Personalnummer unbekannt ist, soll -1 zurückgegeben werden.
205Testen Sie mit
2061. 7401 --> 39
2072. 7839 --> 40
2083. 1111 --> -1
209Hinweis: Funktion datediff(intervall,startdatum, enddatum) verwenden
210*/
211
212if exists (select * from sys.objects where name = 'fn_EmpTime' and type = 'FN')
213drop function fn_EmpTime
214go
215create function fn_EmpTime (@empno int)
216returns int
217as
218 begin
219 declare
220 @dauer int
221
222 if not exists(select * from employee where empno=@empno)
223 begin
224 set @dauer = -1
225 end
226 else
227 begin
228 select @dauer=DATEDIFF(yy,hiredate,getdate())
229 from employee
230 where empno = @empno
231 end
232
233 return @dauer
234 end
235go
236
237--Test
238
239select dbo.fn_EmpTime (7401)
240select dbo.fn_EmpTime (7839)
241select dbo.fn_EmpTime (1111)
242
243/* * Aufgabe 6 (Ausgabe: Ace Tennis Net)
244Schreiben Sie die Funktion fn_Topseller(ohne Parameter), mit der Sie das umsatzstärkste Produkt ausgeben können .
245*/
246
247if exists (select * from sys.objects where name = 'fn_Topseller' and type = 'FN')
248drop function fn_Topseller
249go
250create function fn_Topseller ()
251returns varchar(50)
252as
253 begin
254 declare
255 @topprod varchar(50), @umsatz money
256
257 select top 1 @topprod= descrip, @umsatz=SUM(qty*actualprice)
258 from orderitems oi
259 join products p on oi.prodid=p.prodid
260 join orders o on oi.ordid=o.ordid
261 group by descrip
262 order by SUM(qty*actualprice) desc
263
264 return @topprod
265 end
266go
267
268--Testen
269select dbo.fn_Topseller ()
270
271/* * Zusatzaufgabe
272Schreiben Sie die Funktion fn_Sunday, an die ein Datum übergeben wird und von der Funktion zurückgegeben wird,ob es sich bei dem Datum um einen Sonntag handelt.
273Testen Sie, indem Sie die Funktion in einer Schleife aufrufen, die alle Datumswerte im Januar 2020 durchläuft.
274*/
275
276if exists (select * from sys.objects where name = 'fn_Sunday' and type = 'FN')
277drop function fn_Sunday
278go
279create function fn_Sunday(@tag smalldatetime)
280returns bit
281as
282 BEGIN
283 declare @rueck bit
284 IF datename(dw,@tag) = 'Sonntag'
285 begin
286 set @rueck= 1
287 end
288 else
289 begin
290 set @rueck=0
291 end
292 return @rueck
293 END
294GO
295
296--Testen
297
298begin
299 declare @start date ='01.01.2020', @end date ='31.01.2020'
300 while @start <=@end
301 begin
302 if dbo.fn_Sunday(@start)=1
303 begin
304 print concat (@start,' ist ein Sonntag')
305 end
306 set @start=dateadd(dd,1,@start)
307 end
308end
309go
310
311/************************************ Übung Teil 2 ******************************************************************************/
312
313/** Aufgabe 7
314Schreiben Sie die Funktion fn_extractFName, die als Eingabe den Mitarbeiternamen erwartet und aus diesem
315den Vornamen herauslösen und ausgeben soll.
316Hinweis:Der existierende Bestand ist einheitlich VornameLeerzeichenFamilienname
317*/
318go
319create or alter function fn_extractFName(@Mitarbeitername varchar(50))
320 returns varchar(50)
321 as
322 begin
323 declare
324 @name varchar(50)
325 set @name = substring(@Mitarbeitername,1,CHARINDEX(' ',@Mitarbeitername)-1)
326 return @name;
327end
328go
329
330--testen
331select dbo.fn_extractFName(ename)
332from EMPLOYEE
333
334
335
336/** Aufgabe 8
337Schreiben Sie die Funktion fn_extractLName, die als Eingabe den Mitarbeiternamen erwartet und aus diesem
338den Familiennamen herauslösen und ausgeben soll.
339Hinweis:Der existierende Bestand ist einheitlich VornameLeerzeichenFamilienname
340*/
341go
342create or alter function fn_extractLName(@Mitarbeitername varchar(50))
343 returns varchar(50)
344 as
345 begin
346 declare
347 @name varchar(50)
348 set @name = substring(@Mitarbeitername,CHARINDEX(' ',@Mitarbeitername)+1,LEN(@mitarbeitername))
349 return @name;
350end
351go
352
353select dbo.fn_extractLName(ename)
354from EMPLOYEE
355
356
357/** Aufgabe 9
358Schreiben Sie die Funktion fn_NewMail, die aus Vorname, Familienname sowie Abteilungsname
359und einem Standardsuffix die Mailadresse generiert.
360An die Funktion übergeben werden soll die Personalnummer.
361Die Bildungsvorschrift lautet: vorname.familienname@sw-abteilungsname.com
362
363Existiert die Personalnummer nicht, soll -1 ausgegeben werden.
364
365Testen Sie die Funktion mit folgenden Werten:
3661. Einzeltest: 7401 --> amanda.scott@sw-sales_north.com
3672. Nicht existente Personalnummer 1111 --> -1
3683. Anwendung auf alle Mitarbeiter und Überprüfung der Werte!
369*/
370go
371create or alter function fn_NewMail(@empno int)
372 returns int
373 as
374 begin
375 declare
376 @email varchar (50)
377 begin
378 select @email=CONCAT(fname,'.',lname
379 from EMPLOYEE
380 where
381 end
382
383
384
385 return @email
386 end
387
388
389/** Aufgabe 10
390Schreiben Sie die Funktion fn_EmpLogin, das für einen Mitarbeiter aus dem Vornamen, dem Familiennamen, dem Eintrittsjahr sowie der Abteilungsnummer
391einen Benutzernamen generiert und diesen an die aufrufende Umgebung zurückgibt.
392An die Funktion übergeben werden soll die Personalnummer.
393Die Bildungsvorschrift lautet: InitialenBindestrichAbteilungsnummerBindestrichEintrittsjahr
394
395Existiert die Personalnummer nicht, soll -1 ausgegeben werden.
396
397Testen Sie die Funktion mit folgenden Werten:
3981. Einzeltest: 7401 --> AS-32-1981
3992. Nicht existente Personalnummer 1111 --> -1
4003. Anwendung auf alle Mitarbeiter und Überprüfung der Werte!
401*/
402
403
404
405/** Aufgabe 11
406Erstellen Sie zunächst vier neue Spalten in der Tabelle employee mittels Befehl.
407FNAME varchar(30), LNAME varchar(50), MAIL varchar(100), USERNAME varchar(20)
408
409Erstellen Sie die Prozedur p_UpdEmp mittels deren Sie diese neuen Spalten füllen.
410Überprüfen Sie nach Ausführung der Prozedur die Werte!
411*/
412go
413create or alter procedure p_UpdEmp
414as
415 begin
416 declare
417 EmpCursor cursor
418 scroll
419 for select empno, ename from EMPLOYEE
420 for update
421
422 declare
423 @empno int, @ename varchar (50)
424
425 open EmpCursor
426
427 fetch first from EmpCursor into @empno, @ename
428
429 while @@FETCH_STATUS=0
430 begin
431 --1.Aktion: Tabelle employee aktualisieren
432 update EMPLOYEE
433 set
434 FNAME =dbo.fn_extractFName(@ename)
435 ,LNAME =dbo.fn_extractLName(@ename)
436 --,MAIL =dbo.fn_NewMail(@empno) --mail = 'no mail'
437 --,USERNAME =no login(@empno) --mail = 'no login'
438
439 where current of EmpCursor
440 --2.Holen der jeweils nächsten Zeile aus der Cursor-Variablen
441 fetch next from EmpCursor into @empno, @ename
442
443 end
444
445 close EmpCursor
446 deallocate EmpCursor
447
448 end
449go
450
451exec p_UpdEmp
452
453--alter table employee
454--add
455 --FNAME varchar(30)
456 --,LNAME varchar(50)
457 --,MAIL varchar(100)
458 --,USERNAME varchar(20)
459--go