Microsoft SQL Server 2000 supports keywords Global and Local definitions for Declare Cursor statements. Global specifies that the cursor is global in one connection. Local specifies that the cursor name is part in the stored procedure, trigger, and contains a batch statement containing the Declare Cursor. Microsoft SQL Server 7.0 Previously, in one connection, the cursor name is global. You must first execute a stored procedure for creating a cursor, and then perform another stored procedure to read records from the cursor. Such as: Use Pubsgocreate Procedure OpenCRSR AS
DECLE SAMPLECRSR CURSOR FORSELECT AU_LNAMEFROM Authorswhere Au_lname Like 'S%'
Open SampleCrsRGO
Create Procedure ReadcrSr asfetch next from samplecrwhile (@@ fetch_status <> -1) Begin Fetch Next from SamplecrSrendgo
Exec OpenCRSR / * Declaration and open the cursor SamplecrSr. * / GoEExec ReadCRSR / * Reads records from the cursor SampleCRSR. * / GoClose SampleCrsRGodeAllocate SamplesRGO
The local cursor plays an important protective role in the stored procedure and triggers. Global cursors can be accessed outside the stored procedures or triggers defined. Therefore, they may be changed outside the stored procedures and triggers when they are inadvertent. The local cursor is more secure because they do not have changed outside the stored procedures and triggers unless the special output cursor parameters are passed to the caller. Because global cursors can be referenced outside the stored procedures and triggers, they may have unpredictable impact on other statements. For example: A stored procedure creates a global cursor XYZ, and the program XYZ is still open after the process runs. If the other parts of the program, if you want to declare a global cursor and name XYZ, a repetitive definition error occurs. The global and partial cursors have different namespaces, so two global and local cursors with the same name can appear at the same time. Transact-SQL syntax supports cursor parameters, and also supports the use of Global identification cursors. If a jam name also represents a global cursor and a local cursor, this cursor name will refer to local cursors when there is no Global. Database Options Default To Local Cursor Controls the default scope of the cursor created by the Declare Cursor statement that does not specify the Global and local options. If the Default to Local Cursor option is true, it is a local cursor, which is against the whole. In SQL Server 2000, the Default To Local Cursors option defaults to False to keep the previous version consistent. Declare and open the stored procedure of the local cursor, you can pass these cursors to the stored procedures, triggers, and batch statements that call it. This can be implemented by a CURSOR VARYING data type defined as an output parameter. When the stored procedure is executed, the cursor must be opened to return by output parameters. We can reference it with a local variable defined as a Cursor type. Use Pubsgo / * Create a Procedure with a cursor output parameter. * / Create procedure OpenCRSR @outcrsr Cursor Varying Output As
Set @outcrsr = Cursor Forselect au_lnamefrom authorswhere au_lname like 's%' open @outcrsRGO
/ * Declaration partial cursor. * / Declare @crsrvar Cursor
/ * Associate the previous vernier to the local variable. * / Exec opencrsr @outcrsr = @crsrvar Output
/ * Use @crsrvar to read records. * / Fetch next from @crsrvarwhile (@@ fetch_status <> -1) begin fetch next from @crsrvarend
Close @crsrvar
The DEAALLOCATE @crsrvargo database API does not support the stored procedure of the output cursor parameters. A stored procedure containing output cursor parameters cannot be executed directly by the database API. These stored procedures can be performed by other stored procedures, triggers, transact-sql batch, script calls. The global cursor has been valid until it is explicitly released or connected. The local cursor default will be released at the end of the stored procedure, trigger, and batch unless it is output as an output parameter. That partial cursor will also be released at the end of the reference.
