БД 8 лаба

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
USE MASTER;
GO
IF db_id (N'lab8') IS NOT NULL
DROP DATABASE lab8;
GO
CREATE DATABASE lab8
ON (NAME = lab8_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab8.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab8_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab8.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab8;
GO
CREATE TABLE table1 (id uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
field1 int,
field2 char (30));
GO
INSERT table1 (field1, field2) VALUES ('1', 'sfs');
INSERT table1 (field1, field2) VALUES ('1', 'werqytr');
INSERT table1 (field1, field2) VALUES ('2', 'fdsf');
INSERT table1 (field1, field2) VALUES ('3', 'sfa');
INSERT table1 (field1, field2) VALUES ('4', 'asd');
GO
CREATE PROCEDURE currency_cursor @currency_cursor CURSOR VARYING OUTPUT AS
SET @currency_cursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT * FROM table1 WHERE field1 NOT IN ('1');
OPEN @currency_cursor;
GO
CREATE FUNCTION func (@word char(30)) RETURNS int AS
BEGIN
RETURN LEN (@word)
END;
GO
CREATE PROCEDURE currency_cursor1 @currency_cursor1 CURSOR VARYING OUTPUT AS
SET @currency_cursor1 = CURSOR FORWARD_ONLY STATIC FOR
SELECT * FROM table1 WHERE dbo.func(table1.field2) NOT IN ('5');
OPEN @currency_cursor1;
GO
CREATE FUNCTION func1 (@word char(30)) RETURNS char(30) AS
BEGIN
DECLARE @buf char(30);
IF (LEN(@word) = 3) SET @buf = @word
ELSE SET @buf = '3';
RETURN @buf;
END;
GO
CREATE PROCEDURE currency_cursor2 AS
DECLARE @MyCursor CURSOR;
DECLARE @id uniqueidentifier;
DECLARE @col int;
DECLARE @word char(30)
EXEC currency_cursor @currency_cursor = @MyCursor OUTPUT;
FETCH NEXT FROM @MyCursor INTO @id, @col, @word;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (dbo.func1(@word) = '3') PRINT @word;
FETCH NEXT FROM @MyCursor INTO @id, @col, @word;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
EXECUTE currency_cursor2;
GO
CREATE FUNCTION func2 () RETURNS table
AS RETURN (SELECT * FROM lab8.dbo.table1);
GO
CREATE PROCEDURE currency_cursor3 @currency_cursor3 CURSOR VARYING OUTPUT AS
SET @currency_cursor3 = CURSOR FORWARD_ONLY STATIC FOR
SELECT * FROM dbo.func2() WHERE field1 NOT IN ('1');
OPEN @currency_cursor3;
GO
DROP PROCEDURE currency_cursor;
DROP FUNCTION func;
DROP PROCEDURE currency_cursor1;
DROP FUNCTION func1;
DROP PROCEDURE currency_cursor2;
DROP PROCEDURE currency_cursor3;
GO