· 6 years ago · Dec 05, 2019, 05:58 AM
1USE master
2GO
3
4IF DB_ID (N'lab11') IS NOT NULL
5DROP DATABASE lab11;
6GO
7
8CREATE DATABASE lab11
9 ON PRIMARY(
10 NAME = lab11_prim_dat,
11 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab11_prim_dat.mdf',
12 SIZE = 10MB,
13 MAXSIZE = 100MB,
14 FILEGROWTH = 10MB )
15 LOG ON (
16 NAME = lab11_prim_log,
17 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab11_prim_log.ldf',
18 SIZE = 5MB,
19 MAXSIZE = 25MB,
20 FILEGROWTH = 5MB );
21GO
22
23USE lab11;
24GO
25
26IF OBJECT_ID(N'kind_detail') IS NOT NULL
27 DROP TABLE kind_detail;
28GO
29CREATE TABLE kind_detail (
30 id int IDENTITY(1,1) NOT NULL,
31 title nvarchar(255) NOT NULL CONSTRAINT UQ_title_detail UNIQUE,
32 specification nvarchar(255) NULL,
33 num_det int NOT NULL CONSTRAINT DF_num_det DEFAULT (1),
34 num_par int NOT NULL CONSTRAINT DF_num_par DEFAULT (1),
35 CONSTRAINT PK_id_kind_detail PRIMARY KEY (id));
36GO
37
38IF OBJECT_ID(N'kind_detail_rec') IS NOT NULL
39 DROP TABLE kind_detail_rec;
40GO
41CREATE TABLE kind_detail_rec (
42 id_p int NOT NULL CONSTRAINT FK_kind_detail_rec_p REFERENCES kind_detail (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
43 id_c int NOT NULL CONSTRAINT FK_kind_detail_rec_c REFERENCES kind_detail (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
44 [count] int NULL,
45 CONSTRAINT PK_id_kind_detail_rec PRIMARY KEY (id_p, id_c));
46GO
47
48IF OBJECT_ID(N'party') IS NOT NULL
49 DROP TABLE party;
50GO
51CREATE TABLE party (
52 id int NOT NULL CONSTRAINT FK_par_to_kind_detail REFERENCES kind_detail (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
53 number int NOT NULL,
54 stat int NOT NULL CONSTRAINT DF_party_status DEFAULT (0),
55 date_begin date NOT NULL,
56 date_finish date NOT NULL,
57 date_end date NULL,
58 [count] int NOT NULL,
59 CONSTRAINT CK_par_date CHECK (date_begin <= date_finish),
60 CONSTRAINT PK_party PRIMARY KEY (id, number));
61GO
62
63IF OBJECT_ID(N'party_rec') IS NOT NULL
64 DROP TABLE party_rec;
65GO
66CREATE TABLE party_rec (
67 id_c int NOT NULL,
68 number_c int NOT NULL,
69 id_p int NOT NULL,
70 number_p int NOT NULL,
71 CONSTRAINT FK_party_rec_c FOREIGN KEY (id_c, number_c) REFERENCES party (id, number) ON DELETE NO ACTION ON UPDATE NO ACTION,
72 CONSTRAINT FK_party_rec_p FOREIGN KEY (id_p, number_p) REFERENCES party (id, number) ON DELETE NO ACTION ON UPDATE NO ACTION,
73 CONSTRAINT PK_party_rec PRIMARY KEY (id_c, number_c, id_p, number_p));
74GO
75
76IF OBJECT_ID(N'detail') IS NOT NULL
77 DROP TABLE detail;
78GO
79CREATE TABLE detail (
80 id int NOT NULL CONSTRAINT FK_det_to_kind_detail REFERENCES kind_detail (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
81 number int NOT NULL,
82 stat int NOT NULL CONSTRAINT DF_detail_status DEFAULT (0),
83 id_p int NULL,
84 number_p int NULL,
85 id_par int NOT NULL,
86 number_par int NOT NULL,
87 CONSTRAINT FK_detail_rec FOREIGN KEY (id_p, number_p) REFERENCES detail (id, number) ON DELETE NO ACTION ON UPDATE NO ACTION,
88 CONSTRAINT FK_detail_party FOREIGN KEY (id_par, number_par) REFERENCES party (id, number) ON DELETE NO ACTION ON UPDATE NO ACTION,
89 CONSTRAINT PK_detail PRIMARY KEY (id, number));
90GO
91
92IF OBJECT_ID(N'operation_k_det') IS NOT NULL
93 DROP TABLE operation_k_det;
94GO
95
96CREATE TABLE operation_k_det (
97 id int NOT NULL CONSTRAINT FK_operation_to_kind_detail REFERENCES kind_detail (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
98 number int NOT NULL,
99 title nvarchar(255) NOT NULL,
100 duration float NOT NULL,
101 [count] int NOT NULL,
102 CONSTRAINT PK_operation_k_det PRIMARY KEY (id, number));
103GO
104
105IF OBJECT_ID(N'operation_par') IS NOT NULL
106 DROP TABLE operation_par;
107GO
108CREATE TABLE operation_par (
109 id_p int NOT NULL,
110 number_p int NOT NULL,
111 id_o int NOT NULL,
112 number_o int NOT NULL,
113 time_start datetime NOT NULL,
114 time_finish datetime NOT NULL,
115 CONSTRAINT FK_operation_par_p FOREIGN KEY (id_p, number_p) REFERENCES party (id, number) ON DELETE NO ACTION ON UPDATE NO ACTION,
116 CONSTRAINT FK_operation_par_o FOREIGN KEY (id_o, number_o) REFERENCES operation_k_det (id, number) ON DELETE CASCADE ON UPDATE CASCADE,
117 CONSTRAINT CK_operation_par_id CHECK (id_p = id_o),
118 CONSTRAINT CK_operation_par_time CHECK (time_start < time_finish),
119 CONSTRAINT PK_operation_par PRIMARY KEY (id_p, number_p, number_o));
120GO
121
122
123CREATE TRIGGER del_k_det ON kind_detail INSTEAD OF DELETE AS
124BEGIN
125 DELETE kind_detail_rec WHERE EXISTS(SELECT * FROM deleted
126 WHERE (kind_detail_rec.id_c = deleted.id OR kind_detail_rec.id_p = deleted.id));
127 DELETE party_rec WHERE EXISTS(SELECT * FROM deleted
128 WHERE (party_rec.id_p = deleted.id OR party_rec.id_c = deleted.id));
129 DELETE operation_par WHERE EXISTS(SELECT * FROM deleted
130 WHERE (operation_par.id_p = deleted.id OR operation_par.id_o = deleted.id));
131 DELETE operation_k_det WHERE EXISTS(SELECT * FROM deleted WHERE (operation_k_det.id = deleted.id));
132 DELETE detail WHERE EXISTS(SELECT * FROM deleted WHERE (detail.id = deleted.id));
133 DELETE party WHERE EXISTS(SELECT * FROM deleted WHERE (party.id = deleted.id));
134 DELETE kind_detail WHERE EXISTS(SELECT * FROM deleted WHERE (kind_detail.id = deleted.id));
135END
136GO
137
138CREATE TRIGGER del_det ON detail INSTEAD OF DELETE AS
139BEGIN
140 UPDATE detail SET id_p = NULL, number_p = NULL WHERE (EXISTS(SELECT * FROM deleted
141 WHERE (detail.id_p = deleted.id AND detail.number_p = deleted.number)));
142 UPDATE party SET [count] = -1 WHERE EXISTS(SELECT * FROM (SELECT DISTINCT id_par, number_par FROM deleted) AS d
143 WHERE (party.id = d.id_par AND party.number = d.number_par));
144 DELETE detail WHERE (EXISTS (SELECT * FROM deleted WHERE (detail.id = deleted.id AND detail.number = deleted.number)));
145END
146GO
147
148CREATE TRIGGER del_par ON party INSTEAD OF DELETE AS
149BEGIN
150 DELETE operation_par WHERE EXISTS(SELECT * FROM deleted
151 WHERE (operation_par.id_p = deleted.id AND operation_par.number_p = deleted.number));
152 DELETE party_rec WHERE EXISTS(SELECT * FROM deleted
153 WHERE ((party_rec.id_p = deleted.id AND party_rec.number_p = deleted.number)
154 OR (party_rec.id_c = deleted.id AND party_rec.number_c = deleted.number)));
155 DELETE detail WHERE EXISTS(SELECT * FROM deleted WHERE (detail.id_par = deleted.id AND detail.number_par = deleted.number));
156 DELETE party WHERE EXISTS(SELECT * FROM deleted WHERE (party.id = deleted.id AND party.number = deleted.number));
157END
158GO
159--Можно сделать по другому
160CREATE TRIGGER upd_par ON party INSTEAD OF UPDATE AS
161BEGIN
162 IF (UPDATE(id))
163 BEGIN
164 RAISERROR(N'You can not update id', 18, 10);
165 ROLLBACK;
166 END
167
168 DECLARE @id_i int;
169 DECLARE @number_i int;
170 DECLARE @status_i int;
171 DECLARE @date_begin_i date;
172 DECLARE @date_finish_i date;
173 DECLARE @date_end_i date;
174 DECLARE @count_i int;
175 DECLARE @cursor_i CURSOR;
176
177 DECLARE @id_d int;
178 DECLARE @number_d int;
179 DECLARE @cursor_d CURSOR;
180
181 SET @cursor_i = CURSOR FORWARD_ONLY STATIC FOR SELECT id, number, stat, date_begin, date_finish, date_end, [count] FROM inserted;
182 SET @cursor_d = CURSOR FORWARD_ONLY STATIC FOR SELECT id, number FROM deleted;
183 OPEN @cursor_i;
184 OPEN @cursor_d;
185 FETCH NEXT FROM @cursor_i INTO @id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i;
186 FETCH NEXT FROM @cursor_d INTO @id_d, @number_d;
187
188 WHILE (@@FETCH_STATUS = 0)
189 BEGIN
190 SET @count_i = (SELECT COUNT(*) FROM detail WHERE (id_par = @id_d AND number_par = @number_d));
191 IF (@count_i = 0)
192 BEGIN
193 DELETE operation_par WHERE EXISTS(SELECT * FROM deleted
194 WHERE (operation_par.id_p = deleted.id AND operation_par.number_p = deleted.number));
195 DELETE party_rec WHERE EXISTS(SELECT * FROM deleted
196 WHERE ((party_rec.id_p = deleted.id AND party_rec.number_p = deleted.number)
197 OR (party_rec.id_c = deleted.id AND party_rec.number_c = deleted.number)));
198 DELETE party WHERE (id = @id_d AND number = @number_d);
199 END
200 ELSE
201 BEGIN
202 IF (@number_i <> @number_d AND @number_i IS NOT NULL AND @number_d IS NOT NULL)
203 BEGIN
204 IF (EXISTS(SELECT * FROM party WHERE (id = @id_d AND number = @number_i)))
205 BEGIN
206 RAISERROR(N'The id and number exists in party', 18, 10);
207 ROLLBACK;
208 END
209 INSERT party (id, number, stat, date_begin, date_finish, date_end, [count])
210 VALUES (@id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i);
211 UPDATE party_rec SET number_c = @number_i WHERE (id_c = @id_d AND number_c = @number_d);
212 UPDATE party_rec SET number_p = @number_i WHERE (id_p = @id_d AND number_p = @number_d);
213 UPDATE operation_par SET number_p = @number_i WHERE (id_p = @id_d AND number_p = @number_d);
214 UPDATE detail SET number_par = @number_i WHERE (id_par = @id_d AND number_par = @number_d);
215 DELETE party WHERE (id = @id_d AND number = @number_d);
216 END
217 ELSE UPDATE party SET stat = @status_i, date_begin = @date_begin_i, date_finish = @date_finish_i,
218 date_end = @date_end_i, [count] = @count_i WHERE (id = @id_d AND number = @number_d);
219 END
220 FETCH NEXT FROM @cursor_i INTO @id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i;
221 FETCH NEXT FROM @cursor_d INTO @id_d, @number_d;
222 END
223 CLOSE @cursor_i;
224 DEALLOCATE @cursor_i;
225 CLOSE @cursor_d;
226 DEALLOCATE @cursor_d;
227END
228GO
229
230
231CREATE TRIGGER upd_det ON detail INSTEAD OF UPDATE AS
232BEGIN
233 IF (UPDATE(id))
234 BEGIN
235 RAISERROR(N'You can not update id', 18, 10);
236 ROLLBACK
237 END
238 DECLARE @ins table (
239 k int IDENTITY(1,1),
240 id int,
241 number int);
242 INSERT @ins (id, number) SELECT id, number FROM inserted;
243 DECLARE @del table (
244 k int IDENTITY(1,1),
245 id int,
246 number int);
247 INSERT @del (id, number) SELECT id, number FROM deleted;
248 DECLARE @upd table (
249 k int,
250 id int,
251 number_d int,
252 number_i int);
253 INSERT @upd (k, id, number_d, number_i)
254 SELECT d.k, d.id, d.number, i.number FROM @del AS d
255 INNER JOIN @ins AS i ON (i.k = d.k);
256 UPDATE detail SET id_p = (SELECT id_p FROM inserted WHERE (detail.id = inserted.id AND detail.number = inserted.number)),
257 number_p = (SELECT number_p FROM inserted WHERE (detail.id = inserted.id AND detail.number = inserted.number)),
258 id_par = (SELECT id_par FROM inserted WHERE (detail.id = inserted.id AND detail.number = inserted.number)),
259 number_par = (SELECT number_par FROM inserted WHERE (detail.id = inserted.id AND detail.number = inserted.number)),
260 stat = (SELECT stat FROM inserted WHERE (detail.id = inserted.id AND detail.number = inserted.number))
261 WHERE (EXISTS(SELECT * FROM @upd AS u WHERE (detail.id = u.id AND detail.number = u.number_i AND u.number_d = u.number_i)));
262 IF (UPDATE(number))
263 BEGIN
264 INSERT detail (id, number, id_p, number_p, id_par, number_par, stat)
265 SELECT id, number, id_p, number_p, id_par, number_par, stat FROM inserted
266 WHERE (NOT EXISTS(SELECT * FROM detail
267 WHERE (inserted.id IS NOT NULL AND detail.id = inserted.id AND detail.number = inserted.number)));
268 UPDATE detail SET number_p = (SELECT number_i FROM @upd AS u WHERE (detail.number_p = u.number_d AND detail.id_p = u.id))
269 WHERE (EXISTS(SELECT * FROM @upd AS u
270 WHERE (detail.number_p = u.number_d AND detail.id_p = u.id AND u.number_d <> u.number_i)));
271 DELETE detail WHERE (EXISTS(SELECT * FROM @upd AS u
272 WHERE(detail.id = u.id AND detail.number = u.number_d AND detail.number <> u.number_i)));
273 END
274
275 IF (UPDATE(id_par) OR UPDATE(number_par))
276 BEGIN
277 UPDATE party SET [count] = -1 WHERE (EXISTS(SELECT * FROM (SELECT DISTINCT id_par, number_par FROM deleted) AS d
278 WHERE (party.id = d.id_par AND party.number = d.number_par)))
279 UPDATE party SET [count] = -1 WHERE (EXISTS(SELECT * FROM (SELECT DISTINCT id_par, number_par FROM inserted) AS d
280 WHERE (party.id = d.id_par AND party.number = d.number_par)))
281 END
282END
283GO
284
285
286CREATE TRIGGER ins_det ON detail FOR INSERT AS
287BEGIN
288 UPDATE party SET [count] = -1 WHERE EXISTS(SELECT * FROM (SELECT DISTINCT id_par, number_par FROM inserted) AS d
289 WHERE (party.id = d.id_par AND party.number = d.number_par));
290END
291GO
292
293CREATE TRIGGER ins_par ON party INSTEAD OF INSERT AS
294BEGIN
295 DECLARE @id_i int;
296 DECLARE @number_i int;
297 DECLARE @count_i int;
298 DECLARE @status_i int;
299 DECLARE @date_begin_i date;
300 DECLARE @date_finish_i date;
301 DECLARE @date_end_i date;
302 DECLARE @cursor_i CURSOR;
303
304 DECLARE @i int;
305 DECLARE @num_det int;
306
307 SET @cursor_i = CURSOR FORWARD_ONLY STATIC FOR SELECT id, number, stat, date_begin, date_finish, date_end, [count] FROM inserted;
308 OPEN @cursor_i;
309 FETCH NEXT FROM @cursor_i INTO @id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i;
310
311 WHILE (@@FETCH_STATUS = 0)
312 BEGIN
313 SET @num_det = (SELECT num_det FROM kind_detail WHERE (kind_detail.id = @id_i));
314 IF (@number_i IS NULL)
315 BEGIN
316 SET @number_i = (SELECT num_par FROM kind_detail WHERE (kind_detail.id = @id_i));
317 UPDATE kind_detail SET num_par = @number_i + 1, num_det = @num_det + @count_i WHERE (kind_detail.id = @id_i);
318 END
319 ELSE
320 UPDATE kind_detail SET num_det = @num_det + @count_i WHERE (kind_detail.id = @id_i);
321 INSERT party (id, number, stat, date_begin, date_finish, date_end, [count])
322 VALUES (@id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i);
323 SET @i = 0;
324 WHILE (@i < @count_i)
325 BEGIN
326 INSERT detail (id, number, id_par, number_par) VALUES (@id_i, @num_det + @i, @id_i, @number_i);
327 SET @i = @i + 1;
328 END
329 FETCH NEXT FROM @cursor_i INTO @id_i, @number_i, @status_i, @date_begin_i, @date_finish_i, @date_end_i, @count_i;
330 END
331 CLOSE @cursor_i;
332 DEALLOCATE @cursor_i;
333END
334GO
335
336CREATE TRIGGER upd_k_det ON kind_detail FOR UPDATE AS
337BEGIN
338 IF (UPDATE(id))
339 BEGIN
340 RAISERROR(N'You can not update id', 18, 10);
341 ROLLBACK;
342 END
343 IF (UPDATE(num_det) OR UPDATE(num_par))
344 BEGIN
345 DECLARE @id_i int;
346 DECLARE @num_det_i int;
347 DECLARE @num_par_i int;
348 DECLARE @cursor_i CURSOR;
349
350 DECLARE @id_d int;
351 DECLARE @num_det_d int;
352 DECLARE @num_par_d int;
353 DECLARE @cursor_d CURSOR;
354
355 SET @cursor_i = CURSOR FORWARD_ONLY STATIC FOR SELECT id, num_det, num_par FROM inserted;
356 SET @cursor_d = CURSOR FORWARD_ONLY STATIC FOR SELECT id, num_det, num_par FROM deleted;
357 OPEN @cursor_i;
358 OPEN @cursor_d;
359 FETCH NEXT FROM @cursor_i INTO @id_i, @num_det_i, @num_par_i;
360 FETCH NEXT FROM @cursor_d INTO @id_d, @num_det_d, @num_par_d;
361
362 WHILE (@@FETCH_STATUS = 0)
363 BEGIN
364 SET @num_det_d = (SELECT MAX(detail.number) FROM detail WHERE (detail.id = @id_d));
365 IF (@num_det_d > @num_det_i)
366 BEGIN
367 SET @num_det_i = @num_det_d;
368 UPDATE kind_detail SET num_det = @num_det_i WHERE (kind_detail.id = @id_d);
369 END
370 SET @num_par_d = (SELECT MAX(party.number) FROM party WHERE (party.id = @id_d));
371
372 IF (@num_par_d > @num_par_i)
373 BEGIN
374 SET @num_par_i = @num_par_d;
375 UPDATE kind_detail SET num_par = @num_par_i WHERE (kind_detail.id = @id_d);
376 END
377 FETCH NEXT FROM @cursor_i INTO @id_i, @num_det_i, @num_par_i;
378 FETCH NEXT FROM @cursor_d INTO @id_d, @num_det_d, @num_par_d;
379 END
380 CLOSE @cursor_i;
381 DEALLOCATE @cursor_i;
382 CLOSE @cursor_d;
383 DEALLOCATE @cursor_d;
384 END
385END
386GO
387
388IF OBJECT_ID(N'k_detail_entirely') IS NOT NULL
389 DROP VIEW k_detail_entirely;
390GO
391CREATE VIEW k_detail_entirely AS
392SELECT p.title_p AS title_p, p.[count] AS [count], c.title AS title_c
393FROM (
394 SELECT k.title AS title_p, r.[count] AS [count], r.id_c AS id_c
395 FROM kind_detail_rec AS r
396 RIGHT JOIN kind_detail AS k ON (r.id_p = k.id)) AS p
397FULL OUTER JOIN kind_detail AS c ON (c.id = p.id_c);
398GO
399
400CREATE TRIGGER ins_k_detail_entirely ON k_detail_entirely INSTEAD OF INSERT AS
401BEGIN
402 INSERT kind_detail (title) SELECT DISTINCT title_c FROM inserted
403 WHERE NOT EXISTS(SELECT * FROM kind_detail WHERE kind_detail.title = inserted.title_c);
404 INSERT kind_detail (title) SELECT DISTINCT title_p FROM inserted
405 WHERE NOT EXISTS(SELECT * FROM kind_detail WHERE kind_detail.title = inserted.title_p);
406 INSERT kind_detail_rec (id_p, id_c, [count]) SELECT r.id_p, c.id, r.[count] FROM (
407 SELECT k.id AS id_p, i.[count] AS [count], i.title_c AS title_c FROM inserted AS i
408 JOIN kind_detail AS k ON (i.title_p IS NOT NULL AND i.title_c IS NOT NULL AND i.title_p = k.title)) AS r
409 JOIN kind_detail AS c ON (r.title_c = c.title);
410END
411GO
412
413CREATE TRIGGER del_k_detail_entirely ON k_detail_entirely INSTEAD OF DELETE AS
414BEGIN
415 DELETE kind_detail_rec WHERE EXISTS(SELECT * FROM (
416 SELECT c.id_c AS id_c, id_p AS id_p FROM
417 (SELECT kind_detail.id AS id_c, deleted.title_p AS title_p FROM deleted
418 JOIN kind_detail ON (kind_detail.title = deleted.title_c)) AS c
419 JOIN kind_detail ON (kind_detail.title = c.title_p)) AS q
420 WHERE (kind_detail_rec.id_c = q.id_c AND kind_detail_rec.id_p = q.id_p));
421END
422GO
423
424CREATE TRIGGER upd_k_detail_entirely ON k_detail_entirely INSTEAD OF UPDATE AS
425BEGIN
426 DELETE kind_detail_rec WHERE EXISTS(SELECT * FROM (
427 SELECT c.id_c AS id_c, id_p AS id_p FROM
428 (SELECT kind_detail.id AS id_c, deleted.title_p AS title_p FROM deleted
429 JOIN kind_detail ON (kind_detail.title = deleted.title_c)) AS c
430 JOIN kind_detail ON (kind_detail.title = c.title_p)) AS q
431 WHERE (kind_detail_rec.id_c = q.id_c AND kind_detail_rec.id_p = q.id_p));
432
433 INSERT kind_detail (title) SELECT DISTINCT title_c FROM inserted
434 WHERE NOT EXISTS(SELECT * FROM kind_detail WHERE kind_detail.title = inserted.title_c);
435 INSERT kind_detail (title) SELECT DISTINCT title_p FROM inserted
436 WHERE NOT EXISTS(SELECT * FROM kind_detail WHERE kind_detail.title = inserted.title_p);
437 INSERT kind_detail_rec (id_p, id_c, [count]) SELECT r.id_p, c.id, r.[count] FROM (
438 SELECT k.id AS id_p, i.[count] AS [count], i.title_c AS title_c FROM inserted AS i
439 JOIN kind_detail AS k ON (i.title_p IS NOT NULL AND i.title_c IS NOT NULL AND i.title_p = k.title)) AS r
440 JOIN kind_detail AS c ON (r.title_c = c.title);
441END
442GO
443
444IF OBJECT_ID(N'Ostatok') IS NOT NULL
445 DROP FUNCTION Ostatok;
446GO
447
448CREATE FUNCTION Ostatok (@a int, @b int) RETURNS int AS
449BEGIN
450 DECLARE @c int = @a%@b;
451 IF (@c = 0)
452 SET @c = @b;
453 RETURN @c;
454END
455GO
456
457IF OBJECT_ID(N'proc_parents_is_null') IS NOT NULL
458 DROP PROCEDURE proc_parents_is_null;
459GO
460
461CREATE PROCEDURE proc_parents_is_null
462 @d_cursor CURSOR VARYING OUTPUT
463AS
464 SET @d_cursor = CURSOR
465 FORWARD_ONLY STATIC FOR
466 SELECT * FROM detail WHERE id_p IS NULL ORDER BY id ASC;
467OPEN @d_cursor
468GO
469
470
471
472
473
474
475SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
476GO
477
478BEGIN TRANSACTION
479 INSERT kind_detail (title) VALUES (N'Плата0');
480 INSERT party (id, number, [count], date_begin, date_finish) VALUES (1, 0, 2, '2017-07-28', '2017-08-28');
481 INSERT party (id, [count], date_begin, date_finish) VALUES (1, 3, '2017-08-28', '2017-09-28');
482 INSERT kind_detail (title) VALUES (N'Резистор0'), (N'Конденсатор0');
483 INSERT party (id, [count], date_begin, date_finish, date_end) VALUES (2, 15, '2017-07-28', '2017-07-28', '2017-07-28'),
484 (2, 10, '2017-08-28', '2017-08-28', '2017-08-28'), (3, 20, '2017-07-28', '2017-07-28', '2017-07-28');
485 INSERT kind_detail_rec (id_p, id_c, [count]) VALUES (1, 2, 5), (1, 3, 4);
486 INSERT party_rec (id_p, number_p, id_c, number_c) VALUES (1, 0, 2, 1), (1, 1, 2, 1), (1, 0, 3, 1), (1, 1, 2, 2), (1, 1, 3, 1);
487
488 INSERT operation_k_det (id, number, title, duration, [count]) VALUES (1, 5, N'Приварить резисторы', 2.5, 1),
489 (1, 10, N'Приварить конденсаторы', 1.6, 1);
490 INSERT operation_par (id_p, number_p, id_o, number_o, time_start, time_finish) VALUES
491 (1, 0, 1, 5, '2017-07-28T21:34:55.6', '2017-07-29T15:04:50.6'), (1, 0, 1, 10, '2017-07-28T21:34:55.6', '2017-08-01T09:34:22.6'),
492 (1, 1, 1, 5, '2017-08-28T21:34:55.6', '2017-08-29T15:04:50.6'), (1, 1, 1, 10, '2017-08-28T21:34:55.6', '2017-09-01T09:34:22.6');
493
494 UPDATE detail SET id_p = 1, number_p = dbo.Ostatok(detail.number, 5) WHERE (id = 3);
495 UPDATE detail SET id_p = 1, number_p = dbo.Ostatok(detail.number, 3) WHERE (id = 2 AND number BETWEEN 0 AND 15);
496 UPDATE detail SET id_p = 1, number_p = dbo.Ostatok(detail.number, 2)+3 WHERE (id = 2 AND number BETWEEN 16 AND 25);
497 --UPDATE detail SET number = -1, stat = 1 WHERE (number = 1);
498 --UPDATE detail SET number_par = 1 WHERE (number_par = 2);
499COMMIT
500GO
501
502BEGIN TRANSACTION
503 INSERT kind_detail (title, specification) VALUES (N'Плата1', N'ГОСТ 1234');
504 INSERT party (id, [count], date_begin, date_finish) VALUES
505 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), 4, '2017-08-05', '2017-10-20'),
506 ((SELECT id FROM kind_detail WHERE (title = N'Конденсатор0')), 40, '2017-08-05', '2017-08-05'),
507 ((SELECT id FROM kind_detail WHERE (title = N'Резистор0')), 20, '2017-08-05', '2017-08-05');
508 INSERT kind_detail_rec (id_p, id_c, [count]) VALUES
509 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), (SELECT id FROM kind_detail WHERE (title = N'Конденсатор0')), 7),
510 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), (SELECT id FROM kind_detail WHERE (title = N'Резистор0')), 1);
511 INSERT party_rec (id_p, number_p, id_c, number_c) VALUES
512 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), 1, (SELECT id FROM kind_detail WHERE (title = N'Конденсатор0')), 2),
513 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), 1, (SELECT id FROM kind_detail WHERE (title = N'Резистор0')), 3);
514 INSERT operation_k_det (id, number, title, duration, [count]) VALUES
515 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), 5, N'Приварить резисторы', 0.5, 1),
516 ((SELECT id FROM kind_detail WHERE (title = N'Плата1')), 10, N'Приварить конденсаторы', 14.7, 1);
517 UPDATE detail SET id_p = (SELECT id FROM kind_detail WHERE (title = N'Плата1')), number_p = dbo.Ostatok(detail.number, 4)
518 WHERE (id = (SELECT id FROM kind_detail WHERE (title = N'Конденсатор0')) AND number BETWEEN 21 AND 48);
519 UPDATE detail SET id_p = (SELECT id FROM kind_detail WHERE (title = N'Плата1')), number_p = dbo.Ostatok(detail.number, 4)
520 WHERE (id = (SELECT id FROM kind_detail WHERE (title = N'Резистор0')) AND number BETWEEN 26 AND 29);
521COMMIT TRANSACTION
522GO
523INSERT kind_detail (title, specification) VALUES (N'Проволока медная', N'ГОСТ 1568');
524--DELETE kind_detail WHERE (title = N'Плата0');
525GO
526
527SELECT * FROM kind_detail;
528SELECT * FROM party;
529SELECT * FROM detail;
530SELECT * FROM kind_detail_rec;
531SELECT * FROM party_rec;
532GO
533
534SELECT 'primery';
535GO
536DECLARE @d_Gcursor CURSOR
537EXEC proc_parents_is_null @d_cursor = @d_Gcursor OUTPUT
538
539FETCH NEXT FROM @d_Gcursor
540WHILE (@@FETCH_STATUS = 0)
541BEGIN
542 FETCH NEXT FROM @d_Gcursor
543END
544CLOSE @d_Gcursor
545DEALLOCATE @d_Gcursor
546GO
547SELECT * FROM kind_detail WHERE (title LIKE N'П%');
548SELECT * FROM kind_detail WHERE (title IN (N'Конденсатор0', N'Резистор0', N'Проволока медная'));
549SELECT * FROM kind_detail WHERE (id IN (SELECT DISTINCT id_p FROM kind_detail_rec));
550SELECT k.title, p.number FROM kind_detail AS k LEFT JOIN party AS p ON (k.id = p.id);
551SELECT a.title, b.number FROM kind_detail AS a, party AS b WHERE (a.id = b.id);
552SELECT * FROM party ORDER BY date_begin ASC;
553SELECT * FROM party ORDER BY date_begin DESC;
554GO
555SELECT 'GROUP BY';
556SELECT d.id, MAX(d.number) AS [max] FROM detail AS d GROUP BY d.id;
557SELECT d.id, MIN(d.number) AS [min] FROM detail AS d GROUP BY d.id;
558SELECT d.id, MIN(d.number) AS [min] FROM party AS d GROUP BY d.id;
559SELECT d.id, SUM(d.duration) AS [sum] FROM operation_k_det AS d GROUP BY d.id
560SELECT d.id, AVG(d.duration) AS [avg] FROM operation_k_det AS d GROUP BY d.id
561SELECT d.id, MIN(d.number) AS [min] FROM detail AS d WHERE (d.id_p IS NULL) GROUP BY (d.id) HAVING COUNT(d.number) > 10;
562GO
563SELECT 'UNION';
564SELECT title FROM kind_detail
565UNION ALL
566SELECT title FROM kind_detail WHERE (title LIKE N'П%')
567GO
568SELECT title FROM kind_detail
569UNION
570SELECT title FROM kind_detail WHERE (title LIKE N'П%')
571GO
572SELECT title FROM kind_detail
573EXCEPT
574SELECT title FROM kind_detail WHERE (title LIKE N'П%')
575GO
576SELECT title FROM kind_detail
577INTERSECT
578SELECT title FROM kind_detail WHERE (title LIKE N'П%')
579GO