SQL cursor principle and usage (2) _ MySQL

SQL cursor principle and usage (2) bitsCN.com

Lb_continue = True
Ll_total = 0
Do while lb_continue
FETCH CustomerCur-sor
INTO: ls_acct_no,
: Ls_name,
: Ll_balance;
If sqlca. sqlcode = 0 Then
Ll_total + = ll_balance
Else
Lb_continue = False
End If
LOOP
There are multiple types of cyclic bodies, which are the most common. Some programmers like to place a FETCH statement in front of the loop body, place another FETCH statement in the loop body, and check whether SQLCA. SQLCODE is 100. However, in this case, you need to modify two FETCH statements at the same time during maintenance, which is a little troublesome.

Close cursor
Do not forget to close the cursor at the end of the cursor operation. this is a good programming habit, so that the system releases the resources occupied by the cursor. The statement to close the cursor is simple:
CLOSE CustomerCursor;
Use the Where clause
We can dynamically define the parameters of the Where clause in the cursor. for example, in this example, we directly define that the query province is a record of Beijing, however, we may use a drop-down list box in the application to select the province to be queried. what should we do?
As we mentioned earlier, the DECLARE statement only defines a cursor, which is actually executed in the OPEN statement. After understanding this, we can easily implement this function. add variables to the DECLARE Where clause as parameters, as shown below:
DECLARE mermercursor CURSOR
SELCECT acct_no, name, balance
FROM customer
WHERE province =: ls_province;
Define the value of ls_province
OPEN CustomerCursor;
Cursor type
Like other variables, we can also define the access type of the cursor: global, shared, instance, or local. we recommend that you use the same naming conventions as other variables.
-- Declare a cursor
Declare my_cursor cursor keyset for select * from info
-- Delete a cursor resource
Deallocate my_cursor
-- Open the cursor, which is valid before the cursor is closed or deleted
Open my_cursor
-- Close the cursor
Close my_cursor
-- Declare local variables
Declare @ id int, @ name varchar (20), @ address varchar (20)
-- Locate the record at the specified position
Fetch absolute 56488 from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
-- Locate the relative position record of the current record
Fetch relative-88 from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
-- Locate the previous record
Fetch prior from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
-- Locate the last record
Fetch next from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
-- Locate the first record
Fetch first from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
-- Locate to the end record
Fetch last from my_cursor into @ id, @ name, @ address
Select @ id as id, @ name as name, @ address as address
Instance:
Use database1
Declare my_cursor cursor scroll dynamic
/** // * Scroll indicates that the cursor pointer can be moved freely (otherwise, only forward is allowed). dynamic indicates that the cursor can be read and written (otherwise, the cursor is read-only )*/
For
Select productname from product
Open my_cursor
Declare @ pname sysname
Fetch next from my_cursor into @ pname
While (@ fetch_status = 0)
Begin
Print 'product Name: '+ @ pname
Fetch next from my_cursor into @ pname
End
Fetch first from my_cursor into @ pname
Print @ pname
/** // * Update product set productname = 'zzg' where current of my_cursor */
/** // * Delete from product where current of my_cursor */
Close my_cursor
Deallocate my_cursor

1.4 advanced cursor skills
Although the SQL statement-based background database currently supports roughly the same language, there are some differences in the support for cursors, such as the support for rolling cursors. The so-called scroll cursor means that the programmer can specify the cursor to scroll forward in any direction. For example, in Informix, you can even roll the cursor to the beginning or end of the result set. the statements used are fetch first, fetch last, fetch prior, and fetch next. When a programmer uses a FETCH statement, the default value is fetch next. Since the scroll is implemented in the database background, the scroll cursor provides great convenience for user programming.
The other difference supported by the cursor is that the cursor can be modified. The usage of the cursor above refers to the read-only cursor, while other databases such as Oracle and Sybase support the modification of the cursor. With such a database, you can modify or delete the row where the current cursor is located. For example, to modify the user balance in the row where the current cursor is located, perform the following operations:
UPDATE customer
SET balance = 1000
Where current of mermercursor;
Delete the current row as follows:
Delete from Customer
Where current of mermercursor;
However, if you are currently using Sybase, you must modify the database parameters and set the value of the cursor to 1 to perform the preceding operations. This assignment can be performed before and after the database is connected.
SQLCA. DBParm = "Cursor Update = 1"
The other content is dynamic cursor, that is, you can dynamically form the SELECT statement of the cursor during the running process. Similar to dynamic use of embedded SQL in PowerBuilder, DynamicStagin-gArea and other data types are required, which is beyond the scope of this section.

BitsCN.com