/* Cursor */

/* Cursor */
　　
-- A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records.
　　
/* The Role Of The cursor */
-- Allows specific rows in the result set to be located.
-- Retrieve one or more rows of data from the current location of the result set.
-- The row at the current position in the result set can be modified.
　　
/* Create a cursor */
Declare cursor_name [insensitive] [scroll] cursor for select_statement [For {read only | update [of column_name [,... n]}]
/* Cursor_name
Is the name of the defined Transact-SQL cursor.
Insensitive
Define a cursor to create a temporary copy of the data that will be used by the cursor. All requests to the cursor are answered from the temporary table in tempdb;
Therefore, modification to the base table is not reflected in the data returned when the cursor is extracted, and the cursor cannot be modified. When using the SQL-92 syntax,
If insensitive is omitted, the deletion and update of the base table submitted by (any user) are reflected in the subsequent extraction.
Scroll
All extraction options (first, last, Prior, next, relative, and absolute) can be used.
If scroll is not specified in the SQL-92 declare cursor, next is the only supported extraction option.
If scroll is specified, fast_forward cannot be specified.
Select_statement
Is a standard SELECT statement that defines the cursor result set. The keywords compute, compute by, and into are not allowed in the select_statement declared by the cursor.
*/
　　
/* Cursor operation */
+ Expand sourceview plaincopy to clipboardprint?
Declare Cr cursor
For
Select * from authors
-- Define a cursor
-- Scroll is not specified for the cursor and can only be used for next

Open cr
-- Open the cursor

Fetch next from Cr
-- Push cursor

Close cr
-- Close the cursor

Deallocate cr
-- Delete a cursor

Create Table EMP
(
Eid int primary key,
Ename varchar (10 ),
Sal money
)

Insert into EMP select 1001, 'Rose ', 1234
Union select 1002, 'jack', 2564
Union select 1003, 'will, 245
Union select 1004, 'bucket', 456


-- The result of the first promotion is the first

/* @ Fetch_status */

-- Return the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.
-- 0 fetch statement is successful.
---1 The fetch statement fails or this row is not in the result set. (When fetch fails)
---2 The extracted row does not exist.
Fetch next from Cr
While @ fetch_status = 0
Begin
Fetch next from Cr
End
-- If no advance is made, the value of the global variable has no reference value for whether the cycle can be started.
-- The value of fetch_status in the last row is 0.
-- The next one is-1.

/* Scroll cursor */
Declare Cr cursor scroll for select * from EMP
-- Scroll keywords required
Open cr
Fetch next from Cr
Close cr
Deallocate cr
Declare Cr cursor
For
Select * from authors
-- Define a cursor
-- Scroll is not specified for the cursor and can only be used for next
　　
Open cr
-- Open the cursor
　　
Fetch next from Cr
-- Push cursor
　　
Close cr
-- Close the cursor
　　
Deallocate cr
-- Delete a cursor
　　
Create Table EMP
(
Eid int primary key,
Ename varchar (10 ),
Sal money
)
　　
Insert into EMP select 1001, 'Rose ', 1234
Union select 1002, 'jack', 2564
Union select 1003, 'will, 245
Union select 1004, 'bucket', 456
　　
　　
-- The result of the first promotion is the first
　　
/* @ Fetch_status */
　　
-- Return the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.
-- 0 fetch statement is successful.
---1 The fetch statement fails or this row is not in the result set. (When fetch fails)
---2 The extracted row does not exist.
Fetch next from Cr
While @ fetch_status = 0
Begin
Fetch next from Cr
End
-- If no advance is made, the value of the global variable has no reference value for whether the cycle can be started.
-- The value of fetch_status in the last row is 0.
-- The next one is-1.
　　
/* Scroll cursor */
Declare Cr cursor scroll for select * from EMP
-- Scroll keywords required
Open cr
Fetch next from Cr
Close cr
Deallocate cr
　　　　
　　
-- Fetch first: extract the first row of the cursor.
-- Fetch next: extract the next row of the last extracted row.
-- Fetch prior: extract the first row of the last extracted row.
-- Fetch last: extract the last row in the cursor.
-- Fetch absolute N:
-- If n is a positive integer, the nth row in the cursor is extracted.
-- If n is a negative integer, the nth row before the last row of the cursor is extracted.
-- If n is 0, no rows are extracted.
-- Fetch relative N:
-- If n is positive, the nth row after the last extracted row is extracted.
-- If n is negative, the nth row before the extracted row is extracted.
-- If n is 0, the same row is extracted again.
　　
/* @ Cursor_rows */
-- Returns the number of rows that meet the conditions in the currently opened cursor.
　　
/* Cursor instance (update )*/
Begin Tran tr
Declare Cr cursor for select * from EMP for update of ename
-- Only the ename column can be updated through this cursor
-- Multiple columns are separated by commas (,).
Open cr
　　
Fetch next from Cr
Update EMP set ename = 'log' where current of Cr
-- Current of Cr through cursor
-- Update only one row
Close cr
Deallocate cr
Rollback Tran tr
　　
/* Delete the row pointed to by the cursor */
Delete from EMP where current of Cr
/* Stored Procedure */
　　
/* Retrieve records */
Create proc P1 @ No int
As select * from EMP where eid = @ No
-- As is required
　　
Exec P1 2004
-- Execute the Stored Procedure
　　
/* Addition */
Create proc P1 @ A int, @ B INT
As return @ A + @ B
Declare @ Res Int
Set @ res = exec P1 20, 30
Print @ res
-- Drop proc p1
-- Execution of stored procedures with return values
　　
/* Output by address */
Create proc P1 @ A int output, @ B INT output
As
Begin
Declare @ temp int
Set @ temp = @
Set @ A = @ B
Set @ B = @ temp
End
　　
Declare @ A int, @ B INT
Set @ A = 90
Set @ B = 890
Exec P1 @ A output, @ B output
Print @
Print @ B

Examples of SQL-based Random Functions and cursors

View plaincopy to clipboardprint?
Create Table EMP
(
Eid varchar (10)
)
Go -- note that go must be added here.
-- Drop table EMP
Create proc prand
As
Begin
Declare @ I int
Set @ I = 0
While @ I <100
Begin
Insert into EMP select floor (RAND () * 100000)
-- The value range of rand () * 100000 is 1--99999.
Set @ I = @ I + 1
-- Insert 100 random numbers cyclically
End

Declare CRL scroll cursor for select * from EMP
-- Define a cursor
Open CRL
-- Open the cursor
-- Fetch first from CRL
Declare @ Max int, @ min int, @ temp int
-- @ Max maximum, @ min minimum, @ temp Temporary Variable
Fetch next from CRL into @ Max
-- The cursor is promoted for the first time. 'into @ Max 'assigns the result to @ Max. For more information about the usage of the into clause, see the books online...
Set @ min = @ Max
-- Assign this initial value to the minimum value
While @ fetch_status = 0
Begin

Fetch next from CRL into @ temp
If @ temp> @ Max
Begin
Set @ max = @ temp
End

If @ temp <@ min
Begin
Set @ min = @ temp
End

End
Print 'maximum value is '+ convert (varchar, @ max)
Print 'minimum value: '+ convert (varchar, @ min)
-- Output result, which must be forcibly converted
Close CRL
-- Close the cursor
Deallocate CRL
-- Delete a cursor
End
-- Drop proc prand

Exec prand
Create Table EMP
(
Eid varchar (10)
)
Go -- note that go must be added here.
-- Drop table EMP
Create proc prand
As
Begin
Declare @ I int
Set @ I = 0
While @ I <100
Begin
Insert into EMP select floor (RAND () * 100000)
-- The value range of rand () * 100000 is 1--99999.
Set @ I = @ I + 1
-- Insert 100 random numbers cyclically
End

Declare CRL scroll cursor for select * from EMP
-- Define a cursor
Open CRL
-- Open the cursor
-- Fetch first from CRL
Declare @ Max int, @ min int, @ temp int
-- @ Max maximum, @ min minimum, @ temp Temporary Variable
Fetch next from CRL into @ Max
-- The cursor is promoted for the first time. 'into @ Max 'assigns the result to @ Max. For more information about the usage of the into clause, see the books online...
Set @ min = @ Max
-- Assign this initial value to the minimum value
While @ fetch_status = 0
Begin

Fetch next from CRL into @ temp
If @ temp> @ Max
Begin
Set @ max = @ temp
End

If @ temp <@ min
Begin
Set @ min = @ temp
End

End
Print 'maximum value is '+ convert (varchar, @ max)
Print 'minimum value: '+ convert (varchar, @ min)
-- Output result, which must be forcibly converted
Close CRL
-- Close the cursor
Deallocate CRL
-- Delete a cursor
End
-- Drop proc prand

Exec prand

Go indicates that a batch of T-SQL statements end, the T-SQL statement after go falls into another batch processing range, and a go is defaulted at the end of all the statements in the T-SQL. However, go is not a T-SQL statement, but a command that can be identified by the SQL Server Utility

Use [beifeng trade]

Set ansi_nulls on

Set quoted_identifier on

Alter procedure [DBO]. [testprocedure]
@ ID number nvarchar (18)
As
Select * From DBO. Score where ID card number = @ ID card number
In the preceding example, an error occurs: 'create/alter procedure 'must be the first statement in the query batch.

Should be changed

 

Use [beifeng trade]
Go
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Alter procedure [DBO]. [testprocedure]
@ ID number nvarchar (18)
As
Select * From DBO. Score where ID card number = @ ID card number

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/ztj111/archive/2009/04/28/4133122.aspx