SQL Server/T-SQL/Cursor/Declare CURSOR — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:19, 26 мая 2010

Cursor declaration syntax

 

DECLARE <cursor name> CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR <SELECT statement>
[FOR UPDATE [OF <column name >[,...n]]]



Declare a cursor

17> create table employee(
18>     ID          int,
19>     name        nvarchar (10),
20>     salary      int,
21>     start_date  datetime,
22>     city        nvarchar (10),
23>     region      char (1))
24> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
1>
2> DECLARE curEmployee INSENSITIVE CURSOR
3> FOR SELECT ID, Name FROM Employee
4> DECLARE @ID Int
5> DECLARE @Name VarChar(100)
6>
7> OPEN curEmployee
8> FETCH NEXT FROM curEmployee INTO @ID, @Name
9> WHILE @@Fetch_Status = 0
10>    BEGIN
11>       PRINT @Name
12>       FETCH NEXT FROM curEmployee INTO @ID, @Name
13>    END
14> CLOSE curEmployee
15>
16> DEALLOCATE curEmployee
17> GO
Jason
Robert
Celia
Linda
David
James
Alison
Chris
Mary
1>
2>
3> drop table employee
4> GO
1>



DECLARE CURSOR FOR

 

3> CREATE TABLE authors(
4>    au_id          varchar(11),
5>    au_lname       varchar(40)       NOT NULL,
6>    au_fname       varchar(20)       NOT NULL,
7>    phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
8>    address        varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL,
12>    contract       bit               NOT NULL
13> )
14> GO
1> insert authors values("1",  "Joe",   "Abra",   "111 111-1111", "6 St.", "Berkeley",  "CA", "11111", 1)
2> insert authors values("2",  "Jack",  "Majo",   "222 222-2222", "3 St.", "Oakland" ,  "CA", "22222", 1)
3> insert authors values("3",  "Pink",  "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4",  "Blue",  "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5",  "Red",   "Anne",   "555 555-5555", "6 Av.", "Regina",    "SK", "55555", 1)
6> insert authors values("6",  "Black", "Michel", "666 666-6666", "3 Pl.", "Regina",    "SK", "66666", 1)
7> insert authors values("7",  "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8",  "Yellow","Heather","888 888-8888", "3 Pu",  "Vacaville", "CA", "88888", 0)
9> insert authors values("9",  "Gold",  "Dep",    "999 999-9999", "5 Av.", "Oakland",   "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean",   "000 000-0000", "4 Av.", "Oakland",   "CA", "00000", 1)
11> GO
1>
2>
3>
4> CREATE TABLE titles(
5>    title_id       varchar(20),
6>    title          varchar(80)       NOT NULL,
7>    type           char(12)          NOT NULL,
8>    pub_id         char(4)               NULL,
9>    price          money                 NULL,
10>    advance        money                 NULL,
11>    royalty        int                   NULL,
12>    ytd_sales      int                   NULL,
13>    notes          varchar(200)          NULL,
14>    pubdate        datetime          NOT NULL
15> )
16> GO
1>
2> insert titles values ("1", "Secrets",   "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The",       "business",     "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology",   "0736", $7.99,  $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology",   "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With",      "business",     "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley",    "mod_cook",     "0877", $19.99, $0.00,    12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?",      "trad_cook",    "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty",     "trad_cook",    "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
1>
2> CREATE TABLE titleauthor(
3>    au_id          varchar(20),
4>    title_id       varchar(20),
5>    au_ord         tinyint               NULL,
6>    royaltyper     int                   NULL
7> )
8> GO
1>
2> insert titleauthor values("1", "2", 1, 60)
3> insert titleauthor values("2", "3", 1, 100)
4> insert titleauthor values("3", "4", 1, 100)
5> insert titleauthor values("4", "5", 1, 100)
6> insert titleauthor values("5", "6", 1, 100)
7> insert titleauthor values("6", "7", 2, 40)
8> insert titleauthor values("7", "8", 1, 100)
9> insert titleauthor values("8", "9", 1, 100)
10> GO
1>
2>
3> DECLARE @au_id char(11), @au_lname varchar(40), @title_id char(6),
4>     @au_id2 char(11), @title_id2 char(6), @title varchar(80)
5>
6> DECLARE au_cursor CURSOR FOR
7>     SELECT au_id, au_lname FROM authors ORDER BY au_id
8>
9> DECLARE au_titles CURSOR FOR
10>     SELECT au_id, title_id FROM titleauthor ORDER BY au_id
11>
12> DECLARE titles_cursor CURSOR FOR
13>     SELECT title_id, title FROM titles ORDER BY title
14>
15> OPEN au_cursor
16> FETCH NEXT FROM au_cursor INTO @au_id, @au_lname
17>
18> WHILE (@@FETCH_STATUS=0)
19>     BEGIN
20>     OPEN au_titles
21>     FETCH NEXT FROM au_titles INTO @au_id2, @title_id
22>
23>
24>     WHILE (@@FETCH_STATUS=0)
25>         BEGIN
26>         
27>         
28>         IF (@au_id=@au_id2)
29>             BEGIN
30>             OPEN titles_cursor
31>             FETCH NEXT FROM titles_cursor INTO
32>                 @title_id2, @title
33>
34>             WHILE (@@FETCH_STATUS=0)
35>                 BEGIN
36>                 
37>                 IF (@title_id=@title_id2)
38>                     SELECT @au_id, @au_lname, @title
39>
40>                 FETCH NEXT FROM titles_cursor INTO
41>                     @title_id2, @title
42>                 END
43>                 CLOSE titles_cursor
44>             END
45>         FETCH NEXT FROM au_titles INTO @au_id2, @title_id
46>         END
47>
48>     CLOSE au_titles
49>     FETCH NEXT FROM au_cursor INTO @au_id, @au_lname
50>     END
51> CLOSE au_cursor
52>
53> DEALLOCATE titles_cursor
54> DEALLOCATE au_titles
55> DEALLOCATE au_cursor
56> GO
----------- ---------------------------------------- --------------------------------------------------------------------------------
1           Joe                                      The
----------- ---------------------------------------- --------------------------------------------------------------------------------
1           Joe                                      The
----------- ---------------------------------------- --------------------------------------------------------------------------------
1           Joe                                      The
----------- ---------------------------------------- --------------------------------------------------------------------------------
2           Jack                                     Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
2           Jack                                     Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
2           Jack                                     Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
3           Pink                                     Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
3           Pink                                     Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
3           Pink                                     Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
4           Blue                                     With
----------- ---------------------------------------- --------------------------------------------------------------------------------
4           Blue                                     With
----------- ---------------------------------------- --------------------------------------------------------------------------------
4           Blue                                     With
----------- ---------------------------------------- --------------------------------------------------------------------------------
5           Red                                      Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
5           Red                                      Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
5           Red                                      Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
6           Black                                    Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
6           Black                                    Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
6           Black                                    Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
7           White                                    Fifty
----------- ---------------------------------------- --------------------------------------------------------------------------------
7           White                                    Fifty
----------- ---------------------------------------- --------------------------------------------------------------------------------
7           White                                    Fifty
1>