a12HhHz8

1/* Удаление базы данных. */
2USE master;
3
4DROP DATABASE IF EXISTS lab8
5GO
6
7CREATE DATABASE lab8
8GO
9
10USE lab8
11GO 
12
13--  1. Создать хранимую процедуру, 
14-- производящую выборку из некоторой таблицы и
15-- возвращающую результат выборки в виде курсора.
16SELECT 'case1' as task_number
17GO
18
19DROP TABLE IF EXISTS OrderTableFirst
20GO
21
22CREATE TABLE OrderTableFirst (
23	OrderId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
24    DateOrder DATETIME NOT NULL,
25    Tax Money NOT NULL,
26    Amount Money NOT NULL
27)
28INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
29VALUES ('2011-04-12T00:00:00.000', 13, 2300);
30INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
31VALUES ('2013-04-12T00:00:00.001', 15, 2860);
32INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
33VALUES ('2017-04-12T00:00:00.102', 1, 45);
34INSERT INTO OrderTableFirst(DateOrder, Tax, Amount)
35VALUES ('2018-04-12T00:00:01.112', 10, 950);
36GO
37
38Select* from OrderTableFirst;
39GO
40
41DROP PROCEDURE IF EXISTS GetBigAmount
42GO
43
44CREATE PROCEDURE GetBigAmount @BigAmountCursor CURSOR VARYING OUTPUT AS
45	SET @BigAmountCursor = CURSOR FORWARD_ONLY STATIC FOR
46	SELECT OrderId, DateOrder, Tax, Amount FROM OrderTableFirst WHERE ((Amount - Tax) >  500)
47	OPEN @BigAmountCursor
48GO
49
50DECLARE @BigAmountCursor CURSOR
51EXEC GetBigAmount @BigAmountCursor = @BigAmountCursor OUTPUT
52DECLARE @OrderId int, @DateOrder DATETIME, @Tax Money, @Amount Money
53FETCH NEXT FROM @BigAmountCursor INTO @OrderId, @DateOrder, @Tax, @Amount
54WHILE (@@FETCH_STATUS = 0) BEGIN
55	SELECT @OrderId as OrderId, @DateOrder as DateOrder, @Tax as Tax, @Amount as Amount
56	FETCH NEXT FROM @BigAmountCursor INTO @OrderId, @DateOrder, @Tax, @Amount
57END
58CLOSE @BigAmountCursor
59DEALLOCATE @BigAmountCursor
60GO
61
62--  2. Модифицировать хранимую процедуру 1. таким образом,
63-- чтобы выборка осуществлялась с формированием столбца,
64-- значение которого формируется пользовательской функцией. 
65SELECT 'case2' as task_number
66GO
67
68DROP FUNCTION IF EXISTS dbo.GetAmount
69GO
70
71CREATE FUNCTION dbo.GetAmount (@Tax Money, @Amount Money) RETURNS Money AS BEGIN
72	RETURN (@Amount - @Tax)
73END
74GO
75
76ALTER PROCEDURE GetBigAmount @BigAmountCursor CURSOR VARYING OUTPUT AS
77	SET @BigAmountCursor = CURSOR FORWARD_ONLY STATIC FOR
78	SELECT OrderId, DateOrder, Tax, Amount, dbo.GetAmount(Tax, Amount) AS fullAmount FROM OrderTableFirst
79	WHERE (dbo.GetAmount(Tax, Amount) >  500)
80	OPEN @BigAmountCursor
81GO
82
83DECLARE @BigAmountCursor CURSOR
84EXEC GetBigAmount @BigAmountCursor = @BigAmountCursor OUTPUT
85-- FETCH NEXT FROM @BigAmountCursor
86-- WHILE (@@FETCH_STATUS = 0) BEGIN 
87-- 	FETCH NEXT FROM @BigAmountCursor
88-- END
89CLOSE @BigAmountCursor
90DEALLOCATE @BigAmountCursor
91GO
92
93
94-- 3. Создать хранимую процедуру, вызывающую процедуру
95-- 1., осуществляющую прокрутку возвращаемого
96-- курсора и выводящую сообщения, сформированные из
97-- записей при выполнении условия, заданного еще одной
98-- пользовательской функцией. 
99SELECT 'case3' as task_number
100GO
101
102CREATE FUNCTION dbo.IsDateTimeForOld(@DateOrder DATETIME) RETURNS BIT AS BEGIN
103	DECLARE @oh_yeah BIT SET @oh_yeah = 0
104	IF @DateOrder < 2005 SET @oh_yeah = 1
105	RETURN @oh_yeah
106END
107GO
108
109ALTER PROCEDURE GetBigAmount @BigAmountCursor CURSOR VARYING OUTPUT AS
110	SET @BigAmountCursor = CURSOR FORWARD_ONLY STATIC FOR
111	SELECT OrderId, DateOrder FROM OrderTableFirst
112	WHERE (dbo.GetAmount(Tax, Amount) >  500)
113	OPEN BigAmountCursor
114GO
115
116
117CREATE PROCEDURE GetBigAmountForOldFags AS
118	DECLARE @OrderId int
119	DECLARE @DateOrder DATETIME
120	DECLARE @BigAmountCursor CURSOR
121	EXEC GetBigAmount @BigAmountCursor = @BigAmountCursor OUTPUT
122	FETCH NEXT FROM @BigAmountCursor INTO @OrderId, @DateOrder
123	WHILE (@@FETCH_STATUS = 0) BEGIN
124		if dbo.IsDateTimeForOld(@DateOrder) = 1 SELECT @OrderId AS OrderId
125		FETCH NEXT FROM @BigAmountCursor INTO @OrderId, @DateOrder
126	END
127	CLOSE @BigAmountCursor
128	DEALLOCATE @BigAmountCursor
129GO
130
131EXEC GetBigAmountForOldFags
132GO
133
134-- 4. Модифицировать хранимую процедуру 2. таким
135-- образом, чтобы выборка формировалась с помощью
136-- табличной функции.
137SELECT 'case4' as task_number
138GO
139
140CREATE FUNCTION dbo.GetBigAmountFunc() RETURNS TABLE AS
141	RETURN (SELECT * FROM OrderTableFirst WHERE (dbo.GetAmount(Tax, Amount) > 500));
142GO
143
144ALTER PROCEDURE GetBigAmount @BigAmountCursor CURSOR VARYING OUTPUT AS
145	SET @BigAmountCursor = CURSOR FORWARD_ONLY STATIC FOR
146	SELECT * FROM dbo.GetBigAmountFunc()
147	OPEN @BigAmountCursor
148GO
149
150DECLARE @BigAmountCursor CURSOR
151EXEC GetBigAmount @BigAmountCursor = @BigAmountCursor OUTPUT
152FETCH NEXT FROM @BigAmountCursor
153WHILE (@@FETCH_STATUS = 0) BEGIN 
154	FETCH NEXT FROM @BigAmountCursor
155END
156CLOSE @BigAmountCursor
157DEALLOCATE @BigAmountCursor
158GO