· 6 years ago · Jan 23, 2020, 07:34 PM
1USE BD1d_cz_1B
2GO
3
4DROP FUNCTION IF EXISTS dbo.f_TOTAL_NoParameteres
5GO
6CREATE FUNCTION f_TOTAL_NoParameteres ()
7RETURNS INT
8AS
9BEGIN
10 RETURN (
11 SELECT AVG(Ilość*CenaJednostkowa) FROM PozycjeZamówienia
12 )
13END;
14GO
15SELECT z.IDzamówienia, z.DataZamówienia, SUM(pz.CenaJednostkowa*pz.ilość) [Wartość zamówienia],
16dbo.f_TOTAL_NoParameteres() [Średnia wartość pozycji zmaówienia]
17FROM Zamówienia z JOIN PozycjeZamówienia pz on pz.IDzamówienia=z.IDzamówienia
18GROUP BY z.IDzamówienia,z.DataZamówienia;
19
20--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
21-- Bazy danych 1
22-- Laboratorium nr 14 - st. dzienne
23
24/* Zadania:
25 - funkcje własne użytkownika zwracające:
26 - pojedynczą wartość (f. skalarne)
27 - tabele:
28 - funkcje proste (inline table UDF)
29 - funkcje złożone (multi-statement table valued functions)
30 - procedury składowane
31 - wyzwalacze */
32USE [BD1d_cz_1B]
33
34GO
35
36--https://pastebin.com/............
37
38
39-- Funkcje własne użytkownika zwracające pojedynczą wartość (f. skalarne):
40
41/* 01. Definicja bezparametrycznej funkcji f_TOTAL_NoParameters()
42 obliczającej średnią wartość pozycji zamówienia
43 w tabeli PozycjeZamówienia. */
44 DROP FUNCTION IF EXISTS dbo.f_TOTAL_NoParameters;
45 GO
46
47 CREATE FUNCTION f_TOTAL_NoParameters ()
48 RETURNS INT
49 AS
50 BEGIN
51 RETURN ( SELECT AVG([CenaJednostkowa] * [Ilość])
52 FROM [PozycjeZamówienia]
53 )
54 END;
55 GO
56 -- ***********:
57 SELECT z.IDzamówienia, z.DataZamówienia, SUM(pz.CenaJednostkowa*pz.Ilość) [Wartość zamówienia],
58 dbo.f_TOTAL_NoParameters() [Średnia wartość pozycji zamówienia]
59 FROM Zamówienia z JOIN PozycjeZamówienia pz ON z.IDzamówienia = pz.IDzamówienia
60 GROUP BY z.IDzamówienia, z.DataZamówienia;
61
62/* 02. Definicja parametrycznej funkcji f_Pracownik(imię, nazwisko),
63 zwracającej imię i nazwisko pracownika w zadanym formacie (Imię NAZWISKO)
64 (tabela Pracownicy). */
65 DROP FUNCTION IF EXISTS dbo.f_Pracownik;
66 GO
67
68 CREATE FUNCTION f_Pracownik (@imię NVARCHAR(10), @nazwisko NVARCHAR(20))
69 RETURNS VARCHAR(32)
70 AS
71 BEGIN
72 RETURN (SELECT @imię + SPACE(2) + UPPER(@nazwisko ))
73 END;
74 GO
75 -- ***********:
76 SELECT dbo.f_Pracownik(Imię, Nazwisko) [Imię i NAZWISKO],
77 CAST(DataUrodzenia AS Date) [Data urodzenia]
78 FROM Pracownicy;
79
80/* 03. Definicja parametrycznej funkcji f_Wartosc_Sprzedazy_do_Kraju(kraj odbiorcy)
81 obliczającej sumaryczną wartość sprzedaży do podanego kraju odbiorcy
82 (tabele: Zamówienia i PozycjeZamówienia). */
83 DROP FUNCTION IF EXISTS dbo.Wartosc_Sprzedazy_do_Kraju;
84 GO
85
86 CREATE FUNCTION Wartosc_Sprzedazy_do_Kraju(@kraj NVARCHAR(15))
87 RETURNS MONEY
88 AS
89 BEGIN
90 RETURN ( SELECT SUM(pz.CenaJednostkowa * pz.Ilość) [Wartość sprzedaży]
91 FROM Zamówienia z JOIN PozycjeZamówienia pz
92 ON z.IDzamówienia = pz.IDzamówienia
93 WHERE KrajOdbiorcy = @kraj
94 )
95 END;
96 GO
97 -- ***********:
98 SELECT DISTINCT Kraj, dbo.Wartosc_Sprzedazy_do_Kraju(Kraj) [Wartość zamówień]
99 FROM Klienci;
100
101-- Funkcje własne użytkownika zwracające tabelę (funkcje proste (inline table UDF)):
102
103/* 04. Definicja funkcji f_Klienci_Top10 () zwracającej tabelaryczne zestawienie
104 nazw klientów i liczby złożonych przez nich zamówień
105 (tabele: Klienci, Zamówienia i PozycjeZamówienia). */
106 DROP FUNCTION IF EXISTS dbo.f_Klienci_Top10;
107 GO
108
109 CREATE FUNCTION f_Klienci_Top10 ()
110 RETURNS TABLE
111 AS
112 RETURN ( SELECT TOP (10) k.NazwaFirmy Klient, COUNT(*) [Liczba zamówień]
113 FROM Zamówienia z JOIN Klienci k ON z.IDklienta = k.IDklienta
114 GROUP BY k.NazwaFirmy
115 ORDER BY [Liczba zamówień] DESC
116 );
117 GO
118 -- ***********:
119 SELECT *
120 FROM dbo.f_Klienci_Top10()
121 ORDER BY [Liczba zamówień] DESC;
122
123/* 05. Definicja funkcji f_Dane_Klienta(przedstawiciel) zwracającej
124 dane firmy (nazwa, miasto, kraj, telefon) dla danego przedstawiciela
125 (tabela Klienci). */
126 DROP FUNCTION IF EXISTS dbo.f_Dane_Klienta;
127 GO
128
129 CREATE FUNCTION f_Dane_Klienta(@przedstawiciel NVARCHAR(30))
130 RETURNS TABLE
131 AS
132 RETURN ( SELECT NazwaFirmy Klient, Miasto, Kraj, Telefon
133 FROM Klienci
134 WHERE Przedstawiciel = @przedstawiciel
135 );
136 GO
137 -- ***********:
138 SELECT *
139 FROM dbo.f_Dane_Klienta('Frédérique Citeaux');
140
141/* Funkcje własne użytkownika zwracające tabelę
142 (funkcje złożone (multi-statement table valued functions)): */
143
144/* 06. Definicja funkcji f_Produkty_w_Kategoriach() zwracającej
145 zestawienie: IDproduktu, nazwa produktu, nazwa kategorii, cena jednostkowa,
146 zmodyfikowany stan magazynu oraz kolumnę wycofany.
147 Funkcja modyfikuje stany magazynowe tylko produktów o statusie: Wycofany=1. */
148 DROP FUNCTION IF EXISTS dbo.f_Produkty_w_Kategoriach;
149 GO
150
151 CREATE FUNCTION f_Produkty_w_Kategoriach()
152 RETURNS @produkty_kategorie TABLE
153 (
154 [IDproduktu] [int] NOT NULL,
155 [NazwaProduktu] [nvarchar](40) NOT NULL,
156 [NazwaKategorii] [nvarchar](40) NULL,
157 [CenaJednostkowa] [money] NULL,
158 [StanMagazynu] [smallint] NULL,
159 [Wycofany] [bit] NOT NULL
160 )
161 AS
162 BEGIN
163 INSERT INTO @produkty_kategorie
164 SELECT IDproduktu, NazwaProduktu, NazwaKategorii,
165 CenaJednostkowa, StanMagazynu, Wycofany
166 FROM Produkty p INNER JOIN Kategorie k
167 ON p.IDkategorii = k.IDkategorii
168
169 UPDATE @produkty_kategorie SET [StanMagazynu] = 111
170 WHERE Wycofany = 1
171
172 RETURN
173 END;
174 GO
175 -- ***********:
176 SELECT *
177 FROM dbo.f_Produkty_w_Kategoriach();
178
179/* 07. Definicja funkcji f_Ranking_Sprzedażyh(kraj) zwracającej rankikg pracowników
180 wg malejącej sumarycznej wartości sprzedaży do podanego kraju odbiorcy
181 (tabele:Pracownicy, Zamówienia, PozycjeZamówienia;
182 funkcja: f_Pracownik). */
183 /****** Object: UserDefinedFunction [dbo].[Ranking_Sprzedaży] Script Date: 03.01.2020 01:46:55 ******/
184 DROP FUNCTION IF EXISTS dbo.f_Ranking_Sprzedaży;
185 GO
186
187 SET ANSI_NULLS ON
188 GO
189 SET QUOTED_IDENTIFIER ON
190 GO
191 CREATE FUNCTION dbo.f_Ranking_Sprzedaży (@kraj NVARCHAR(15))
192 RETURNS TABLE
193 AS
194 RETURN (
195 SELECT dbo.f_Pracownik([Imię],[Nazwisko]) AS Pracownik,
196 SUM(pz.Ilość * pz.CenaJednostkowa) [Wartość sprzedazy]
197 FROM Pracownicy p JOIN Zamówienia z
198 ON p.IDpracownika = z.IDpracownika
199 JOIN PozycjeZamówienia pz
200 ON z.IDzamówienia = pz.IDzamówienia
201 WHERE z.KrajOdbiorcy = @kraj
202 GROUP BY dbo.f_Pracownik([Imię],[Nazwisko])
203 );
204 GO
205 -- ***********:
206 DECLARE @kraj NVARCHAR(15) = 'USA';
207 SELECT *
208 FROM dbo.f_Ranking_Sprzedaży (@kraj)
209 ORDER BY [Wartość sprzedazy] DESC;
210 GO
211
212
213-- Procedury
214USE [BD1d_cz_1B]
215GO
216
217/* 08. Definicja procedury składowanej spu_Pobierz_Zamowienia_Klientow,
218 zwracającej zestawienie: ID zamówienia, ID klienta, data zamówienia,
219 dla podanego ID klienta i określonego przedziłu czasu.
220 Jeśli procedura będzie wywołana bez podania daty początkowej,
221 to domyślnie będzie nią 1. stycznia 1980 r., natomiast jeżeli nie zostanie
222 podana data końcowa, to domyślnie będzie to data bieżąca. */
223/****** Object: StoredProcedure [dbo].[spu_Pobierz_Zamowienia_Klientow] Script Date: 03.01.2020 19:03:08 ******/
224 DROP PROCEDURE IF EXISTS dbo.spu_Pobierz_Zamowienia_Klientow ;
225 GO
226
227 SET ANSI_NULLS ON
228 GO
229 SET QUOTED_IDENTIFIER ON
230 GO
231
232 CREATE PROCEDURE dbo.spu_Pobierz_Zamowienia_Klientow
233 @IDKlienta AS NVARCHAR(5),
234 @data_od AS DATE = '19800101',
235 @data_do AS DATE = NULL,
236 @liczba_wierszy AS INT OUTPUT
237 AS
238 BEGIN
239 SET NOCOUNT ON;
240 DECLARE @dd AS DATE;
241 IF @data_do IS NULL
242 BEGIN
243 SET @dd = GETDATE();
244 SET @data_do = CAST(@dd AS DATE);
245 END;
246
247 SELECT IDzamówienia [ID zamówienia], IDklienta [ID klienta],
248 CONVERT(DATE, DataZamówienia) [Data zamówienia]
249 FROM Zamówienia
250 WHERE IDklienta = @IDKlienta
251 AND DataZamówienia >= @data_od
252 AND DataZamówienia < @data_do;
253 SET @liczba_wierszy = @@ROWCOUNT;
254 END;
255 GO
256-- Wywołanie procedury
257 USE [BD1d_cz_1B]
258 GO
259
260 DECLARE @lw AS INT;
261
262 EXEC dbo.spu_Pobierz_Zamowienia_Klientow
263 @IDKlienta = 'ALFKI',
264 --@data_od = '19971013',
265 --@data_do = '19980116',
266 @liczba_wierszy = @lw OUTPUT;
267
268 SELECT @lw AS [Liczba wierszy];
269
270-- Wyzwalacze
271
272-- Zadania do samodzielnego wykonania:
273/* 13.01. Zdefiniować bezparametryczną funkcję f_WTM(), zwracającą sumaryczną wartość
274 produktów w magazynie (tabela Produkty). */
275 DROP FUNCTION IF EXISTS dbo.f_WTM;
276 GO
277 CREATE FUNCTION f_WTM ()
278 RETURNS TABLE
279 AS
280
281 RETURN ( SELECT SUM(StanMagazynu*CenaJednostkowa) Suma
282 FROM Produkty
283 )
284
285 GO
286 --2opcja
287 DROP FUNCTION IF EXISTS dbo.f_WTM;
288 GO
289 CREATE FUNCTION f_WTM ()
290 RETURNS INT
291 AS
292 BEGIN
293 RETURN ( SELECT SUM(StanMagazynu*CenaJednostkowa) Suma
294 FROM Produkty
295 )
296 END;
297 GO
298 -- ***********:
299 SELECT dbo.f_WTM()
300
301
302/* 13.02. Zdefiniować funkcję f_Wiek_Pracownika(), zwracającą aktualny wiek pracownika.
303 (tabela Pracownicy). */
304 DROP FUNCTION IF EXISTS dbo.f_Wiek_Pracownika
305 GO
306 CREATE FUNCTION f_Wiek_Pracownika ()
307 RETURNS TABLE
308 AS
309
310 RETURN ( SELECT IDpracownika,DATEDIFF(yy,DataUrodzenia,getdate()) wiek
311 FROM Pracownicy where DataUrodzenia is not null
312 )
313
314 GO
315 SELECT p.idpracownika, w.wiek from Pracownicy p inner join
316 dbo.f_Wiek_Pracownika() w on w.IDpracownika=p.IDpracownika
317
318/* 13.03. Zdefiniować funkcję f_WPwM(ID), zwracającą sumaryczną wartość
319 produktu o podanym ID (tabela Produkty). */
320 DROP FUNCTION IF EXISTS dbo.f_WPwM;
321 GO
322 CREATE FUNCTION f_WPwM (@ID INT)
323 RETURNS INT
324 AS
325 BEGIN
326 RETURN (
327 SELECT StanMagazynu*CenaJednostkowa FROM Produkty p where IDproduktu=@ID
328 )
329 END;
330 GO
331 SELECT dbo.f_WPwM(3)
332
333/* 13.04. Zdefiniować funkcję f_WZ(ID), zwracającą sumaryczną wartość zamówienia o podanym ID
334 (tabela PozycjeZamówienia). */
335 --SELECT * FROM PozycjeZamówienia
336 DROP FUNCTION IF EXISTS dbo.f_WZ;
337 GO
338 CREATE FUNCTION f_WZ (@ID INT)
339 RETURNS INT
340 AS
341 BEGIN
342 RETURN (
343 SELECT SUM(ilość*CenaJednostkowa) FROM PozycjeZamówienia where IDzamówienia=@ID
344 )
345 END;
346 GO
347 SELECT dbo.f_WZ(10250)
348
349/* 13.05. Zdefiniować funkcję f_WZD(data), zwracającą sumaryczną wartość zamówień przyjętych
350 w podanym dniu (tabele: Zamówienia i PozycjeZamówienia). */
351
352 DROP FUNCTION IF EXISTS dbo.f_WZD;
353 GO
354 CREATE FUNCTION f_WZD (@data DATE)
355 RETURNS INT
356 AS
357 BEGIN
358 RETURN (
359 SELECT SUM(pz.CenaJednostkowa*pz.Ilość) FROM pozycjezamówienia pz inner join Zamówienia z
360 on z.idzamówienia=pz.idzamówienia where z.DataZamówienia=@data
361 )
362 END;
363 GO
364 SELECT dbo.f_WZD('19960704')
365
366/* 13.06. Zdefiniować funkcję f_Wiek_Pracownika(data), obliczającą wiek pracownika
367 w podanym dniu (tabela Pracownicy). */
368
369 DROP FUNCTION IF EXISTS dbo.f_Wiek_Worker;
370 GO
371 CREATE FUNCTION f_Wiek_Worker (@date DATE)
372 RETURNS TABLE
373 AS
374
375 RETURN (
376 SELECT DATEDIFF(yy,DataUrodzenia,@date) wiek FROM Pracownicy where DataUrodzenia is not null
377 )
378
379 GO
380 SELECT *FROM dbo.f_Wiek_Worker('20200101') ORDER BY wiek
381
382/* 13.07. Zdefiniować funkcję (tabele: Zamówienia i PozycjeZamówienia). */
383
384 DROP FUNCTION IF EXISTS dbo.f_funkcja;
385 GO
386 CREATE FUNCTION f_funkcja ()
387 RETURNS TABLE
388 AS
389
390 RETURN (
391 SELECT z.*,pz.CenaJednostkowa,pz.IDproduktu,pz.Ilość,pz.Rabat FROM PozycjeZamówienia pz JOIN Zamówienia z on z.IDzamówienia=pz.IDzamówienia
392 )
393
394 GO
395 SELECT * FROM dbo.f_funkcja()
396
397/* 13.08. Zdefiniować funkcję f_Przedstawiciele(przedstawiciel) zwracającą
398 zestawienie danych firm (nazwa, miasto, kraj, telefon), których przedstawicielami
399 są osoby o określonej pozycji, np. właściciel, przedstawiciel handlowy itp.
400 (tabela Klienci).*/
401 DROP FUNCTION IF EXISTS dbo.f_Przedstawiciele;
402 GO
403 CREATE FUNCTION f_Przedstawiciele (@Przedstawiciel varchar(20))
404 RETURNS TABLE
405 AS
406
407 RETURN (
408 SELECT k.NazwaFirmy,k.Miasto,k.Kraj,k.Telefon FROM Klienci k where k.StanowiskoPrzedstawiciela=@Przedstawiciel
409 )
410
411 GO
412 SELECT *FROM dbo.f_Przedstawiciele('właściciel')
413 SELECT * FROM dbo.f_Przedstawiciele('Przedstawiciel handlowy')
414
415
416
417/* 13.09. Zdefiniować funkcję f_Nowe_Ceny(przyrost) zwracającą
418 zestawienie: IDproduktu, nazwa produktu, nazwa kategorii, cena jednostkowa,
419 zmodyfikowana cena jednostkowa oraz stan magazynu.
420 Funkcja podnosi ceny produktów o podany przyrost procentowy tylko tym produktom,
421 których stan magazynu jest poniżej stanu minimum (tabela Produkty) */
422 DROP FUNCTION IF EXISTS dbo.f_Nowe_Ceny;
423 GO
424 CREATE FUNCTION f_Nowe_Ceny (@Przyrost float)
425 RETURNS TABLE
426 AS
427
428 RETURN (
429 SELECT p.IDproduktu, p.NazwaProduktu,k.NazwaKategorii, p.CenaJednostkowa,ROUND( P.CenaJednostkowa+P.CenaJednostkowa*(@Przyrost/100),2) [Zmodyfikowana] FROM Produkty p inner join Kategorie k
430 on k.IDkategorii=p.IDkategorii where p.StanMagazynu<p.StanMinimum
431 )
432
433 GO
434 SELECT * FROM f_Nowe_Ceny(20.0)