Four cursors : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial






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>








18.2.Declare Cursor
18.2.1.The syntax of the DECLARE CURSOR statement
18.2.2.A DECLARE CURSOR statement that declares a dynamic local cursor
18.2.3.A cursor created in SQL server.
18.2.4.INSENSITIVE Cursor
18.2.5.Cursor on a Single-Column Key
18.2.6.Four cursors
18.2.7.Declare a cursor variable to hold the cursor output variable
18.2.8.Local scroll cursor
18.2.9.DECLARE CURSOR FAST_FORWARD FOR
18.2.10.DECLARE cursor LOCAL
18.2.11.DECLARE CURSOR GLOBAL FOR
18.2.12.Static cursor