ExZzF6pY

1use master;
2go
3
4drop database if exists lab8;
5go
6
7create database lab8;
8go
9
10use lab8;
11go
12
13/* 1 */
14create table Application
15(
16    ID            int identity (1,1) primary key not null,
17    InsuranceID   int unique                     not null,
18    BankChequeEUR money                          not null,
19    Area          char(15)                       not null default 'Schengen'
20);
21go
22
23insert into Application(BankChequeEUR, InsuranceID)
24values (850, 485);
25
26insert into Application(BankChequeEUR, InsuranceID, Area)
27values (1920, 100, 'USA');
28
29insert into Application(BankChequeEUR, InsuranceID)
30values (590, 792);
31go
32
33
34create procedure CheckMoneyProc @MoneyCursor cursor varying output
35as
36    set @MoneyCursor = cursor
37        forward_only for
38        select InsuranceID, Area
39        from Application
40        where BankChequeEUR > 700
41    open @MoneyCursor;
42go
43
44
45declare @Cursor cursor
46declare @InsuranceID int, @Area char(15)
47exec CheckMoneyProc @MoneyCursor = @Cursor output
48
49fetch from @Cursor into @InsuranceID, @Area
50while (@@fetch_status = 0)
51    begin
52        select @InsuranceID as InsuranceID, @Area as Area
53        fetch next from @Cursor into @InsuranceID, @Area
54    end
55close @Cursor
56deallocate @Cursor;
57go
58
59
60
61/* 2 */
62create function CheckAreaFunc(@Area char(15))
63    returns int
64as
65begin
66    if @Area = 'USA'
67        begin
68            return 1
69        end
70
71    return 0
72end;
73go
74
75create procedure CheckAreaProc @AreaCursor cursor varying output
76as
77    set @AreaCursor = cursor
78        forward_only for
79        select ID, InsuranceID
80        from Application
81        where dbo.CheckAreaFunc(Area) = 1
82    open @AreaCursor;
83go
84
85declare @Cursor cursor
86declare @ID int, @InsuranceID int
87exec CheckAreaProc @AreaCursor = @Cursor output
88
89fetch next from @Cursor into @ID, @InsuranceID
90while (@@fetch_status = 0)
91    begin
92        select @ID as ID, @InsuranceID as InsuranceID
93        fetch next from @Cursor into @ID, @InsuranceID
94    end
95close @Cursor
96deallocate @Cursor;
97go
98
99
100
101/* 3 */
102create procedure CheckProc @AreaC cursor varying output
103as
104    set @AreaC = cursor
105        forward_only for
106        select ID, InsuranceID, Area
107        from Application
108    open @AreaC;
109go
110
111declare @Cursor cursor
112declare @ID int, @InsuranceID int, @Area char(15)
113exec CheckProc @AreaC = @Cursor output
114
115fetch next from @Cursor into @ID, @InsuranceID, @Area
116while (@@fetch_status = 0)
117    begin
118        if (dbo.CheckAreaFunc(@Area) = 0)
119            select @ID as ID, @InsuranceID as InsuranceID, @Area as Area
120        fetch next from @Cursor into @ID, @InsuranceID, @Area
121    end
122close @Cursor
123deallocate @Cursor;
124go
125
126
127
128/* 4 */
129create function Func4()
130    returns @Table table
131                   (
132                       ID   int      not null,
133                       Area char(15) not null
134                   )
135as
136begin
137    insert @Table select ID, Area from Application
138    return
139end
140go
141
142create procedure CheckAreaProcNew @AreaCursor cursor varying output
143as
144    set @AreaCursor = cursor
145        forward_only for
146        select ID, Area
147        from dbo.Func4()
148        where dbo.CheckAreaFunc(Area) = 0
149    open @AreaCursor;
150go
151
152declare @Cursor cursor
153declare @ID int, @Area char(15)
154exec CheckAreaProcNew @AreaCursor = @Cursor output
155fetch next from @Cursor into @ID, @Area
156while (@@fetch_status = 0)
157    begin
158        select @ID as ID, @Area as Area
159        fetch next from @Cursor into @ID, @Area
160    end
161close @Cursor
162deallocate @Cursor;
163go