Breaking News

Sqlrpgle Update Cursor Examples

Main Menu

Sqlrpgle Update Cursor Examples

среда 25 марта admin 74

The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. IBM calls this type of update a positioned update. There is a way cooler, big data selection that we could do updating the whole set-at-a-time also called a searched update.

Summary: in this tutorial, you will learn how to use the SQL Server cursor to process a result set, one row at a time.

SQL works based on set e.g., SELECT statement returns a set of rows which is called a result set. However, sometimes, you may want to process a data set on a row by row basis. This is where cursors come into play.

What is a database cursor

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

SQL Server cursor life cycle

These are steps for using a cursor:

First, declare a cursor.

2
FORselect_statement;

To declare a cursor, you specify its name after the DECLARE keyword with the CURSOR data type and provide a SELECT statement that defines the result set for the cursor.

Next, open and populate the cursor by executing the SELECT statement:

Then, fetch a row from the cursor into one or more variables:

SQL Server provides the @@FETCHSTATUS function that returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful. You can use the WHILE statement to fetch all rows from the cursor as shown in the following code:

2
4
BEGIN
END;

After that, close the cursor:

Finally, deallocate the cursor:

SQL Server cursor example

Examples

We’ll use the prodution.products table from the sample database to show you how to use a cursor:

First, declare two variables to hold product name and list price, and a cursor to hold the result of a query that retrieves product name and list price from the production.products table:

2
4
6
8
10
@product_nameVARCHAR(MAX),
FORSELECT
list_price
production.products;

Next, open the cursor:

Then, fetch each row from the cursor and print out the product name and list price:

2
4
6
8
10
@product_name,
BEGIN
FETCHNEXTFROMcursor_productINTO
@list_price;

Install aspupload windows 2008. After that, close the cursor:

Finally, deallocate the cursor to release it.

The following code snippets put everything together:

2
4
6
8
10
12
14
16
18
20
22
24
26
28
@product_nameVARCHAR(MAX),
FORSELECT
list_price
production.products;
OPENcursor_product;
FETCHNEXTFROMcursor_productINTO
@list_price;
WHILE@@FETCH_STATUS=0
PRINT@product_name+CAST(@list_priceASvarchar);
@product_name,
END;
CLOSEcursor_product;
DEALLOCATEcursor_product;

Here is the partial output:

In practice, you will rarely use the cursor to process a result set in a row-by-row manner.

In this tutorial, you have learned how to use the SQL Server cursor to process a result set, each row at a time.

This Oracle tutorial explains how to use the Oracle/PLSQL WHERE CURRENT OF statement with syntax and examples.

Description

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

Syntax

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

OR

Note

  • The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

Example

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the WHERE CURRENT OF Statement:

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the WHERE CURRENT OF Statement: