Sybase database technology: Cursors updatable with for read only/for update

Do not specify a for update when defining a cursor or for read only,ase to check to see if the cursor is updatable;

If a cursor query statement contains an ORDER BY clause, the ASE defines the cursor as read-only, and in other cases it is defined as an updatable cursor;

If you do not involve updating or deleting table data, we recommend that you include the for Read Only option in the cursor definition so that the ASE defines the cursor as read-only;

Table customer has a unique index on the C_custkey column that queries the first 10 rows of the table (all fields are stitched together into a single string).

If you define a cursor for read-only:

Declare cur_hash cursor FOR select top ten convert (varchar,c_custkey) +coalesce (Nullif (IsNull (C_name, '), '), ') +co Alesce (Nullif (IsNull (c_address, '), '), ') +convert (Varchar,c_nationkey) +coalesce (Nullif (IsNull (C_phone, '), ") , ') +convert (Varchar,c_acctbal) +coalesce (Nullif (IsNull (C_mktsegment, '), '), ') +coalesce (Nullif (IsNull (c_ Comment, '), '), ') from the customer for read only

The table scan is used to return the first 10 rows of data;

If you define a cursor for update or do not specify a for read only/for update:

The ASE scans the table data using a unique index on the C_custkey column, and then returns the first 10 rows of data according to the C_custkey key sequence;

For this query statement: Select Top ... from customer, the cursor is also defined as updatable even if you do not specify a for update,ase;

If you do not specify a FOR UPDATE clause, the ASE selects any unique index, or it can use a different index or table scan if the specified table column does not have a unique index.

However, if you specify a FOR UPDATE clause, the ASE must scan the base table with a unique index defined for one or more columns. If it does not exist, an error is returned.

You can use an abstract plan to see if a unique index is used when the cursor scans the table data:

To view cursor information declared outside a stored procedure:

Sp_cursorinfo null, cursor_name

To view cursor information declared within a stored procedure:

Join in stored procedure: EXEC sp_cursorinfo

The stored procedure Sp_cursorinfo also displays abstract schedule information for cursor query statements in addition to displaying the state information of the cursor.

The state of the cursor is different from the output, which is typically output:

1, the cursor defines, compiles, scans the data to use the isolation level;

2, whether the cursor can be scrolled;

3, the current position of the cursor;

4. The number of rows that the cursor has read/updated/deleted;

5. Whether the cursor will remain open after the transaction commits or rolls back;

6. Number of rows per fetch;

7, the cursor is read-only or updatable;

8, the amount of memory consumed by the cursor;

9, the number of columns of the result set returned by the cursor;

10, the column information of the cursor result set (column type, column width, whether it can be updated);

About options: Set close on Endtran

If the current transaction is committed or rolled back, the cursor remains open by default; output: The cursor would remain open when a transaction is committed or rolled back.

If set close on Endtran on then the cursor is closed when the current transaction is committed or rolled back, output: The cursor will be closed if a transaction is committed or rolled .

Sybase database technology: Cursors updatable with for read only/for update