Mega Code Archive

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>