Cursores en SQL (2)

 

FETCH NEXT FROM Employee_Cursor

 

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Employee_Cursor

END

CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

 

'Abrir un cursor e imprimir su contenido

SET NOCOUNT ON

DECLARE

@au_id varchar(11),

@au_fname varchar(20),

@au_lname varchar(40),

@message varchar(80),

@title varchar(80)

 

PRINT "-------- Utah Authors report --------"

 

DECLARE authors_cursor CURSOR FOR

SELECT au_id, au_fname, au_lname

FROM authors

WHERE state = "UT"

ORDER BY au_id

 

OPEN authors_cursor

FETCH NEXT FROM authors_cursor

INTO @au_id, @au_fname, @au_lname

 

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT " "

SELECT

@message = "----- Books by Author: " +

@au_fname + " " + @au_lname

PRINT @message

 

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

 

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

 

FETCH NEXT FROM authors_cursor

INTO @au_id, @au_fname, @au_lname

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

GO

 

 

'Recorrer un cursor

USE pubs

GO

DECLARE authors_cursor CURSOR FOR

SELECT au_lname

FROM authors

WHERE au_lname LIKE "B%"

ORDER BY au_lname

 

OPEN authors_cursor

FETCH NEXT FROM authors_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM authors_cursor

END

CLOSE authors_cursor

DEALLOCATE authors_cursor

 

 

Usuarios que han visto este tema también han visto...

- Bases de Datos Externas en SQL
- Crear Base de Datos en SQL
- Cursores en SQL
- La Cláusula Procedure en SQL
- Actualizar un registro SQL

- Versión imprimible de este documento

- Enviar por e-mail este documento