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