游标的使用例子
DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lnameFROM authorsWHERE state = "UT"ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title END CLOSE titles_cursor DEALLOCATE titles_cursor -- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameEND
DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lnameFROM authorsWHERE state = "UT"ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title END CLOSE titles_cursor DEALLOCATE titles_cursor -- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameEND
我希望的是,整个SQL语句都是我动态创建的,因为关键是FROM的问题,如果没有这个
OBJECT对象的话,该SQL语句是会报错的
我希望的是For语句都是我动态拼出来的