What is a cursor?

What is a cursor?

I. Tutorial Purpose

1. Master the methods and steps for creating a cursor;

2. Learn how to use the cursor;

Ii. experiment content

1. Create a cursor;

2. How to Use the cursor.

Iii. Experiment steps

1. Create a cursor.

1) create a stored procedure-sp_CURSOR1 using the S, C, and SC tables in the S_C database. This stored procedure is used to display all course information. If the score is greater than or equal to 90, the score itself is displayed. If the score is greater than or equal to 80, the score is displayed. If the score is greater than or equal to 70, the score is displayed; score> = 60 show pass; score> = 0 show fail; if no score is displayed, no score is displayed. The student ID, name, course, and score are displayed in the following format: Student ID --- name --- course --- score, as shown in 1.

The cursor technology is required to implement the above requirements, and the Print statement is used to display the data.

 

Figure 1 Result Display format

Creation Statement of sp_CURSOR1:

If exists (SELECT name FROM sysobjects WHERE name = 'SP _ CURSOR1 'AND type = 'P ')

Drop procedure sp_CURSOR1

GO

Create procedure sp_CURSOR1

AS

BEGIN

DECLARE @ sno char (10)

DECLARE @ name char (10)

DECLARE @ cno char (10)

DECLARE @ grade int

DECLARE S_CURSOR CURSOR

FOR

Select s. sno, S. sname, C. cname, SC. grade from SC, S, C

Where s. sno = SC. sno and SC. cno = C. cno

OPEN S_CURSOR

Fetch next from S_CURSOR INTO @ sno, @ name, @ cno, @ grade

WHILE @ FETCH_STATUS = 0

BEGIN

IF @ grade IS NULL

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '--- no score'

Else if @ grade> = 90

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '---' + CONVERT (VARCHAR (20 ), @ grade)

Else if @ grade> = 80

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '--- liang'

Else if @ grade> = 70

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '--- 中'

Else if @ grade> = 60

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '--- pass'

Else if @ grade> = 0

Print rtrim (@ sno) + '---' + RTRIM (@ name) + '---' + RTRIM (@ cno) + '--- fail'

Fetch next from S_CURSOR INTO @ sno, @ name, @ cno, @ grade

END

CLOSE S_CURSOR

DEALLOCATE S_CURSOR

END

Result description:

 

2. Use of the cursor.

Calculate the bank interest. One depositor account is 0001. The Bank access record is as follows:

Transaction date	Transaction amount	Account Balance	Remarks
2010-10	10000	10000	Deposit Account
2010-12-20	20000	30000
2011-02-03	-5000	25000
2011-05-01	-3000	22000
2011-10-10	6000	28000

The data table of the access record is named fundlist, the field name is id (id, int), cid (depositor account, varchar (20), ex-date (Transaction date, datetime ), ex-money (transaction amount, money), balance (account balance, money ). According to the relevant provisions, the deposit interest of a bank is: Before (including) The annual interest of 2%, and after (not including) The annual interest of 3%, compile a stored procedure sp_cal_interest to calculate the current interest of the depositor, which must be implemented using a cursor. Note: currently, getdate () is used to indicate that the annual interest rate is 2% x 2%/365 x the number of days it is stored.

If exists (SELECT name FROM sysobjects WHERE name = 'SP _ cal_interest 'AND type = 'P ')

Drop procedure sp_cal_interest

GO

Create procedure sp_cal_interest

AS

DECLARE @ time datetime

DECLARE @ money

DECLARE @ pretime datetime

DECLARE @ all money

DECLARE @ lixi money

DECLARE @ T datetime

BEGIN

SET @ T = CAST ('2017-04-01 'AS datetime)

DECLARE f_cursor CURSOR

SELECT ex_date, balance FROM fundlist

OPEN f_cursor

Fetch next from f_cursor INTO @ time, @ money

SET @ pretime = @ time

SET @ all = @ money

WHILE @ FETCH_STATUS = 0

BEGIN

IF @ time <= @ T

SET @ lixi = @ all * DATEDIFF (DAY, @ pretime, @ time) * 0.02/365

Else if @ time> @ t and @ pretime <= @ T

SET @ lixi = @ all * DATEDIFF (DAY, @ pretime, @ T) * 0.02/365

+ @ All * DATEDIFF (DAY, @t, @ time) * 0.03/365

ELSE

SET @ lixi = @ all * DATEDIFF (DAY, @ pretime, @ time) * 0.03/365

SET @ all = @ all + @ lixi + @ money

SET @ pretime = @ time

Fetch next from f_cursor INTO @ time, @ money

END

PRINT @ lixi

CLOSE f_cursor

DEALLOCATE f_cursor

END

Iv. Experiment Summary