· 6 years ago · Nov 18, 2019, 07:46 AM
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 dbo.GetBigAmount
45 @BigAmountCursor CURSOR
46 VARYING OUTPUT
47AS
48 SET @BigAmountCursor = CURSOR
49 FORWARD_ONLY STATIC FOR
50 SELECT OrderId, DateOrder, Tax, Amount
51 FROM OrderTableFirst
52 WHERE ((Amount - Tax) > 500)
53 OPEN @BigAmountCursor
54GO
55
56DECLARE @BigAmountCursor CURSOR
57DECLARE @OrderId int, @DateOrder DATETIME, @Tax Money, @Amount Money
58EXEC GetBigAmount @BigAmountCursor = @BigAmountCursor OUTPUT
59FETCH FROM @BigAmountCursor INTO @OrderId, @DateOrder, @Tax, @Amount
60WHILE (@@FETCH_STATUS = 0)
61BEGIN
62 SELECT @OrderId as OrderId, @DateOrder as DateOrder, @Tax as Tax, @Amount as Amount
63 FETCH NEXT FROM @BigAmountCursor INTO @OrderId, @DateOrder, @Tax, @Amount
64END
65CLOSE @BigAmountCursor
66DEALLOCATE @BigAmountCursor
67GO
68
69-- 2. Модифицировать хранимую процедуру 1. таким образом,
70-- чтобы выборка осуществлялась с формированием столбца,
71-- значение которого формируется пользовательской функцией.
72SELECT 'case2' as task_number
73GO
74
75DROP FUNCTION IF EXISTS dbo.GetAmount
76GO
77
78CREATE FUNCTION dbo.GetAmount (@Tax Money, @Amount Money)
79RETURNS Money
80 AS
81BEGIN
82 RETURN (@Amount - @Tax)
83END
84GO
85
86IF OBJECT_ID ( 'dbo.GetAnotherAmount', 'P' ) IS NOT NULL
87 DROP PROCEDURE dbo.GetAnotherAmount;
88GO
89
90CREATE PROCEDURE dbo.GetAnotherAmount
91 @MainCursor CURSOR VARYING OUTPUT
92AS
93 SET @MainCursor = CURSOR
94 FORWARD_ONLY STATIC FOR
95 SELECT dbo.GetAmount(Tax, Amount), OrderId
96 FROM OrderTableFirst
97 WHERE (dbo.GetAmount(Tax, Amount) > 500)
98 OPEN @MainCursor
99GO
100
101DECLARE @BigAnotherCursor CURSOR
102DECLARE @AmountWithoutTax Money, @OrderId1 int
103EXEC dbo.GetAnotherAmount @MainCursor = @BigAnotherCursor OUTPUT;
104FETCH NEXT FROM @BigAnotherCursor INTO @AmountWithoutTax, @OrderId1
105WHILE (@@FETCH_STATUS = 0)
106BEGIN
107 SELECT
108 @AmountWithoutTax as AmountWithoutTax,
109 @OrderId1 as OrderId
110 FETCH NEXT FROM @BigAnotherCursor INTO @AmountWithoutTax, @OrderId1
111END
112CLOSE @BigAnotherCursor
113DEALLOCATE @BigAnotherCursor
114GO
115
116
117-- 3. Создать хранимую процедуру, вызывающую процедуру
118-- 1., осуществляющую прокрутку возвращаемого
119-- курсора и выводящую сообщения, сформированные из
120-- записей при выполнении условия, заданного еще одной
121-- пользовательской функцией.
122SELECT 'case3' as task_number
123GO
124
125IF OBJECT_ID ( 'dbo.Case3', 'P' ) IS NOT NULL
126 DROP PROCEDURE dbo.Case3;
127GO
128
129CREATE PROCEDURE dbo.Case3
130AS
131 DECLARE @MyCursor3 CURSOR
132 DECLARE @DateOrder3 DATETIME, @OrderId3 int
133 EXEC dbo.GetAnotherAmount @MainCursor = @MyCursor3 OUTPUT
134 FETCH NEXT FROM @MyCursor3 INTO @DateOrder3, @OrderId3
135 WHILE (@@FETCH_STATUS = 0)
136 BEGIN
137 SELECT @DateOrder3 as DateOrder, @OrderId3 as OrderId
138 FETCH NEXT FROM @MyCursor3 INTO @DateOrder3, @OrderId3
139 END
140 CLOSE @MyCursor3
141 DEALLOCATE @MyCursor3
142GO
143
144EXECUTE dbo.Case3
145GO
146
147-- 4. Модифицировать хранимую процедуру 2. таким
148-- образом, чтобы выборка формировалась с помощью
149-- табличной функции.
150SELECT 'case4' as task_number
151GO
152
153IF OBJECT_ID ( 'dbo.Case4', 'P' ) IS NOT NULL
154 DROP PROCEDURE dbo.Case4;
155GO
156
157CREATE PROCEDURE dbo.Case4
158 @Case4 CURSOR VARYING OUTPUT
159AS
160 SET @Case4 = CURSOR
161 --Указывает, что курсор может просматриваться только от первой строки к последней
162 --создает временную копию данных для использования курсором
163 FORWARD_ONLY STATIC FOR
164 SELECT dbo.GetAmount(Tax, Amount), OrderId
165 FROM OrderTableFirst
166 --заполняет результирующий набор
167 -- открытие курсора
168 OPEN @Case4
169GO
170-- использует синтаксис ISO для задания параметров работы курсора.
171DECLARE @Case4 CURSOR
172DECLARE @DateOrder4 DATETIME, @OrderId4 int
173EXEC dbo.Case4 @Case4 = @Case4 OUTPUT
174--возвращает из него строку
175FETCH NEXT FROM @Case4 INTO @DateOrder4, @OrderId4
176WHILE (@@FETCH_STATUS = 0)
177BEGIN
178 SELECT @DateOrder4 as DateOrder, @OrderId4 as OrderId
179 FETCH NEXT FROM @Case4 INTO @DateOrder4, @OrderId4
180END
181--снимает блокировку курсоров для строк
182-- закрытие курсора
183CLOSE @Case4
184-- Удаляет ссылку курсора, чтобы освободить данные захваченные курсором
185DEALLOCATE @Case4
186GO