可滚动游标Scrollable Cursors

在基于新式屏幕的应用程序中,用户向后滚动数据。In modern screen-based applications, the user scrolls backward and forward through the data. 对于此类应用程序,返回之前提取的行是一个问题。For such applications, returning to a previously fetched row is a problem. 一种可能的做法是先关闭再重新打开游标,然后提取行,直到光标到达所需的行。One possibility is to close and reopen the cursor and then fetch rows until the cursor reaches the required row. 另一种可能的方法是读取结果集,将其缓存在本地,并在应用程序中实现滚动。Another possibility is to read the result set, cache it locally, and implement scrolling in the application. 这两种可能性仅适用于较小的结果集,而后一种可能性很难实现。Both possibilities work well only with small result sets, and the latter possibility is difficult to implement. 更好的解决方案是使用可 滚动的游标,该游标 可以在结果集中向后和向前移动。A better solution is to use a scrollable cursor, which can move backward and forward in the result set.

滚动的游标 通常用于基于新式屏幕的应用程序中,用户可以在这些应用程序中来回滚动数据。A scrollable cursor is commonly used in modern screen-based applications in which the user scrolls back and forth through the data. 但是,仅当只进游标不执行此作业时,应用程序才应使用可滚动游标,因为可滚动游标的开销通常比只进游标更昂贵。However, applications should use scrollable cursors only when forward-only cursors will not do the job, as scrollable cursors are generally more expensive than forward-only cursors.

向后移动的功能引发了不适用于只进游标的问题:可滚动的游标是否检测对以前提取的行所做的更改?The ability to move backward raises a question not applicable to forward-only cursors: Should a scrollable cursor detect changes made to rows previously fetched? 也就是说,是否应检测更新、删除和新插入的行?That is, should it detect updated, deleted, and newly inserted rows?

出现这一问题的原因在于,结果集的定义(一组符合特定条件的行)在检查行是否与该条件匹配时不会出现问题,也不指出每次提取行时是否必须包含相同的数据。This question arises because the definition of a result set - the set of rows that matches certain criteria - does not state when rows are checked to see whether they match that criteria, nor does it state whether rows must contain the same data each time they are fetched. 以前的省略使可滚动游标能够检测行是否已插入或删除,而后者则可以使它们检测更新的数据。The former omission makes it possible for scrollable cursors to detect whether rows have been inserted or deleted, while the latter makes it possible for them to detect updated data.

检测更改的功能有时很有用。The ability to detect changes is sometimes useful, sometimes not. 例如,会计应用程序需要一个忽略所有更改的游标;如果光标显示最新更改,则无法平衡书籍。For example, an accounting application needs a cursor that ignores all changes; balancing books is impossible if the cursor shows the latest changes. 另一方面,航空公司预订系统需要一个显示数据的最新更改的光标;如果没有此类游标,它必须不断地重新查询数据库以显示最新的航班可用性。On the other hand, an airline reservation system needs a cursor that shows the latest changes to the data; without such a cursor, it must continually requery the database to show the most up-to-date flight availability.

为了满足不同应用程序的需求,ODBC 定义了四种不同类型的可滚动游标。To cover the needs of different applications, ODBC defines four different types of scrollable cursors. 这些游标的支出和检测结果集更改的能力会有所不同。These cursors vary both in expense and in their ability to detect changes to the result set. 请注意,如果可滚动游标可以检测对行所做的更改,则在尝试重新提取这些行时,它只能检测它们;数据源无法向游标通知当前提取行的更改。Note that if a scrollable cursor can detect changes to rows, it can only detect them when it attempts to refetch those rows; there is no way for the data source to notify the cursor of changes to the currently fetched rows. 请注意,更改的可见性也由事务隔离级别控制;有关详细信息,请参阅 事务隔离Note as well that visibility of changes is also controlled by the transaction isolation level; for more information, see Transaction Isolation.

本部分包含以下主题。This section contains the following topics.