Author: David Eulerdate: 2004/09 / 28Email: de_euler-david@yahoo.com.cn
If you have any questions, please contact me :) In the database test, you often need to fill or mark the table in the database, you can implement the operation of each query record through the cursor, using the RAND () function. Get the random number, insert the random number into the table, you can update or populate the data table. Here, the use of the cursor is generally required to use the cursor. FETCH CURSOR (one value, the cursor is automatically moved) 4. The loop reads the cursor and processes each record. Fetch is equivalent to fetch next. 5. Close and release the cursor, Close Cursor, DEAllocate Cursor. Hereinafter, a bulk change database is recorded in the database. This example is updated between the price of all the materials in the price list. The price of all the materials in the original price is 0, and after updating. Price is random number between 0 and 100: Use Guruerp
- Define Cursor MyTestCursor: Declare MyTestCursor Cursorfor Select PGI_ITM_CODE, PGI_ListPrice from TBLPRICELISTGROUPITEM / * Select two fields from the table * // * Table TBLPRICELISTGROUPITEM PGI_ITM_CODE is Unique Key * /
- Open the cursor mytestcursor: Open mytestcursor
Declare @PGI_ITM_CODE CHAR (28) Declare @pgi_listprice float
- FETCH Remove the record referred to in the cursor and deposits the record results into the variable: fetch from mytestcursor INTO @ PGI_ITM_CODE, @ PGI_ListPrice
/ **************** BEGIN of loop ************************************** ** / while @@ FETCH_STATUS = 0Begin update TBLPRICELISTGROUPITEM set PGI_ListPrice = floor (100 * rand ()) where PGI_ITM_CODE = @ PGI_ITM_CODEfetch next from MyTestCursor into @ PGI_ITM_CODE, @ PGI_ListPriceEnd / ************* **** End of loop ****************************** /
SELECT @PGI_ITM_CODE As Code, @ PGI_ListPrice As Price
/ *********** Close the cursor, release the cursor: ************** / close mytestcursordeallocate mytestcursor
Repeat, use the cursor to change or populate the database, which is generally five steps from Declare, Open, Fetch, Loop Fetch, Close and Deallocate. Remark 1: The While cycle starts in BeGin, ending with END, continues when the condition is true, ending note 2: @@ fetch_status is a variable in SQL Server, the following is the explanation on SQL Server Books Online: Returns The Status of the Last Cursor Fetch Statement Issued Against Any Cursor Currently Opened by The Connection.
Return ValuedScription0Fetch Statement Was Successful. -1Fetch Statement Failed or the Row Was Beyond The Result Set. -2Row Fetched is Missing.
Examples
This Example Uses @@ fetch_status to control cursoor activities in a while loop.
Declare Employee_Cursor Cursor for
Select lastname, firstname from northwind.dbo.employees
Open Employee_Cursor
Fetch next from Employee_cursor
While @@ fetch_status = 0
Begin
Fetch next from Employee_cursor
End
Close Employee_cursor
Deallocate Employee_Cursor
