Cursor, if it was before to tell me this concept, my brain has two ideas: 1, you cow; 2, I will not
Not not a reason, not an excuse, so we have to learn, I attribute like to see Code, do not like reading people, so, the text is not attractive to me, gossip less ah, to provide you with an example of my writing, first let everyone use up, and know how to use Ah, want to go deep, do not ask me ah, to read the book
cursors, as Java staff, the best understanding is the list in Java, but the database is a little lazy, not too much management to him, to open, close, release resources
Demo1:
Declare cursor for -- -DECLARE CURSOR mycur Select from T_stu Open Mycur--- opening cursor --action content close mycur -- -Close Cursors deallocate mycur--- Releasing Resources
The top of the demo is just to tell you some basic instructions of the cursor, in fact, there is no use, in the function, the use of cursors in the stored procedure is really useful class, next, see Demo2:
The main function of this function is to pass in the table name, return all the columns of the table, and spell the string back.
Create function [dbo].[Getcols]( @table_name as varchar( -) )RETURNS varchar( -) asBEGIN DECLARE @colname VARCHAR( -) DECLARE @typename VARCHAR( -) DECLARE @result varchar( -) Set @result = "' DECLAREMycurCURSOR for ---Define Cursors SelectC.name asColumnname,ty.name asTypeName fromsys.columns CInner JoinSys.tables T onT.object_id=C.object_id Inner JoinSys.types Ty onty.system_type_id=c.system_type_idwhereT.name= @table_name andTy.name!='sysname' ---Incoming parameter @table_name Order byt.name,c.column_idOPENMycur---Open Cursor FETCH NEXT fromMycur into ----Get the next piece of data @colname,@typename ----Save the acquired data to a variable while @ @FETCH_STATUS = 0 ----Returns the state of the last fetch command, 0: Success-1: Failed-2: The fetched row does not exist BEGIN if @result = NULL or @result = "' begin SET @result = @colname End Else begin SET @result = @result + ',' + @colname End FETCH NEXT fromMycur into ---Get the next piece of data @colname,@typename END CLOSEMycur---Close Cursors deallocateMycur----Release resources RETURN @resultEND
Use instances of cursors (SQL Server version)