Use Cursors (Cursors) to row multiple rows of query results

In Sybase database, the Descriptive database language (SQL) is the most successful and widely used database language, which has become the international standard of relational database language. This mainly benefits from its two major advantages: one is to exempt the user to describe the trouble of the operation process, and pass it on to the system, the system can be based on the requirements of users to determine an effective operation process, the second is the language itself close to the natural language of English, easy to learn and easy to use, very popular with users. However, SQL language is generally limited to the operation of the database, itself is not a fully computational language, such as in the variable type of high-level programming language (such as: C language) like arrays, pointers and other complex variable types; In addition, the language is a collection-oriented language (set_oriented Languase , that is, executing a statement can provide all of the required tuples at once, but in actual programming we need to assign each of these tuples to the relevant variables, individually, according to the conditions. The solution to this problem now is to embed the SQL language into the Advanced programming language, the use of advanced programming language to deal with the advantages of strong data, and SQL language to the database direct operation of the characteristics of the joint completion, this to a certain extent, both to increase the difficulty of writing programs, but also make the whole program cumbersome. Let me introduce you to the new Sybase database, the addition of tool cursors, which makes it easy to implement a row-by-line processing of multiple rows of query results.

First, introduce the function of the cursor, the corresponding operation commands, variables and workflow

The primary function of a cursor (cursor) is to use a SELECT query statement, the resulting multiline data is sent one line to the stored procedure or host program that needs to be invoked, with the initial cursor pointing to the first row of data, sending the row of data to the stored procedure or host program, and then the cursor pointing to the next row of data. This goes on until all the data has been called, and it makes it easy and flexible to modify or delete the data in the row where the cursor resides.

The mechanism of the cursor is accomplished by the following five statements and two global variables.

1, the description statement. A cursor is named and associated with the corresponding query statement.

The syntax is: DECLARE cursor_name cursor for select_statement

Where: select_statement is a SELECT statement that returns multiple rows of result data that does not allow an into clause and a COMPUTE clause to exist.

2, open the cursor sentence. Executes the SQL query statement associated with the cursor and places the results of the query in the cursor.

The syntax is: Open cursor_name

3, take the number of statements. Used to retrieve data from an open cursor and assign that data to a host variable. Each time a FETCH statement is executed, the cursor is first pushed forward one bit, then a value is taken at the cursor's current position, and the corresponding variable is assigned a value.

The syntax is: Fetch cursor_name int host_ vavriable, ...

4, close the cursor sentence. When the count is complete or an error occurs, be sure to close the cursor. Otherwise, the next call to the cursor will be faulted.

The syntax is: Close cursor_name

5. Delete the cursor sentence, which is mainly to release the memory resources invoked by the cursors.

The syntax is: Dellocate cursor_name

Global variables: @ @rowcount, using a FETCH statement to return the number of row values.

@ @sql The status fetch returns the state variable with a value of three:

0 indicates success of the fetch.

1 indicates an error in the fetch number result.

2 indicates that there is no desirable number.

The work flow chart for the cursor is as follows:
Second, the next program is I use the cursor function, write the calculation of the time deposit payable interest rate procedures.

The program is composed of two stored procedures, the need to invoke the time deposit table (T_DQCD) and the Interest rate table (T_LL) data, due to the amount of each deposit, deposit, applicable interest rate, the date of account, in the calculation of time deposit payable interest, can only be made on a per-user basis.

The first stored procedure is the execution of the cursor operation statement, the input parameter "account", and the stored procedure "single account payable interest", the output parameter "single account interest value", and its accumulation, until all accounts payable interest.

The specific procedures are as follows:

print ' Install YFLX ... '

Go

drop proc YFLX

Go

Create proc YFLX

@we Money Output

As

DECLARE c1 cursor FOR/* DESCRIPTION Cursor C1 * *

Select WH from T_DQCD where Zhzt <> ' 1 '

DECLARE @iwh char (9)

DECLARE @wh char (9)

DECLARE @hzs char (12)

DECLARE @ff Money

DECLARE @jj Money

Open C1/* Opening cursor/*

Select @jj = 0.00/* Assign initial value to 0*/

Fetch C1 into @iwh/* Submit cursor data to variable * *

While @ @sqlstatus!= 2

Begin

If @ @sqlstatus =1

Begin

RAISERROR 20001 "Select Fail"

Return

End

exec dhlx @wh = @iwh, @dhlx = @ff output/* Transfer Stored Procedure * *

Select @jj = @jj + @ff

Fetch C1 into @iwh

End

Select @we = @jj/* To pay the total number of totals to the output variable * *

Close c1/* Closing cursor */

deallocate cursor c1/* DELETE cursor * *

Go

The second stored procedure mainly uses SQL query statement based on the account extracted by the cursor, calculates the actual deposit period and the quoted interest rate at that time, and finally calculates the interest payable on the account.

The calculation formula is:

Regular savings deposit not due

Interest payable = Principal x actual days x deposit rate

Overdue Regular savings deposits

Payable interest = principal x deposit period x Deposit Date listing interest + principal x overdue days x current current interest rate

The specific procedures are as follows:

print ' Install DHLX ... '

Go

drop proc DHLX

Go

Create proc DHLX

@wh Char (9),

@dhlx Money Output

As

DECLARE @ts smallint

DECLARE @yflx Money

DECLARE @cts char (9)

DECLARE @hqll float

Declare @bcc char (5)

Declare @zhbcc char (2)

DECLARE @sbcc smallint

DECLARE @khrq smallint

Select @bcc =bcc from T_DQCD where wh= @wh

Select @zhbcc =substring (@bcc, 3,2)

Select @sbcc = convert (int, @zhbcc)

Select @ts = (DateDiff (mm, select QXR from T_DQCD where wh = @wh), GETDATE ()) *30 + datepart (dd,getdate ())-DATEPART (DD, Sele CT QXR from T_DQCD where wh= @wh))-from T_DQCD where wh= @wh/* Calculate actual access days * *

Select @cts = Convert (char, @ts)

Select @hqll =ll from t_ll where bcc= ' 12000 ' and rq= (select Max (RQ) from T_LL)

If @ts < @sbcc *30 * Check whether the deposit is due or not

Select @yflx =round ((select Bjje from T_DQCD where wh= @wh) * (select LL-from-T_ll where bcc= (select Bcc from T_DQCD where wh = @wh) and rq= (select Max (RQ) from T_ll where bcc= (select Bcc from T_DQCD where wh = @wh) and RQ!> (select Khr from T_DQC D where wh= @wh))) * (DateDiff (mm, select QXR from T_DQCD where wh

= @wh), GETDATE ()) *30 + datepart (dd,getdate ())-DATEPART (dd, select QXR from T_DQCD where wh= @wh))/30000), 2) from T_DQCD W Here wh= @wh

Else

Select @yflx =round (((select Bjje from T_DQCD where wh= @wh) * (select LL-from-T_ll where bcc= (select Bcc from T_DQCD where w H= @wh) and rq= (select Max (RQ) from T_ll where bcc= (select Bcc to T_DQCD where wh = @wh) and RQ!> (select QXR from T_DQ CD where wh= @wh)))/1000+ ((select Bjje from T_DQCD where wh= @wh) * @hqll * (@ts-@sbcc *30))/30000), 2 "from T_DQCD where Wh= @wh

Select @dhlx = @yflx

Go

Summary: Because of the row-by-line processing of multiple rows of query results in the program, the result is that the return value is unique when using Sybase database query statements, so that the whole program structure is concise and clear.