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