Support Where Current Of

Availability

This feature is available since MogDB 5.0.0.

Introduction

When a cursor is positioned on a table row, it can be updated or deleted using that cursor to identify the row.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

Cursor query is a basic simple single-table query statement, for complex statements may be the case of error reporting, especially grouping statements, others also include multi-table join, related subqueries and so on. This feature supports the use of "CURRENT OF cursor" for the WHERE condition of UPDATE or DELETE statement.

Constraints

• The CURRENT OF statement cannot be used in conjunction with any other condition, i.e., the arithmetic condition can only be used in conjunction with this condition.
• In Oracle compatibility mode the cursor must specify "for update".
• When the cursor contains join, Oracle may not be able to delete or update the data (and no error is reported), but MogDB can be modified normally.

Syntax Description

CURRENT OF cursor_name

Example

-- Prepare data
CREATE TABLE uctest(f1 int, f2 text);
INSERT INTO uctest VALUES (1, 'one'), (2, 'two'), (3, 'three');

-- DELETE WHERE CURRENT OF
START TRANSACTION;
CURSOR c1 FOR SELECT * FROM uctest FOR UPDATE;
FETCH 2 FROM c1;
DELETE FROM uctest WHERE CURRENT OF c1;
COMMIT;

-- Check UPDATE WHERE CURRENT
START TRANSACTION;
CURSOR c1 FOR SELECT * FROM uctest FOR UPDATE;
FETCH c1;
UPDATE uctest SET f1 = 8 WHERE CURRENT OF c1;
COMMIT;