oracle游标fetch_SQL游标@@ FETCH_STATUS函数概述

oracle游标fetch

SQL cursor is one of the most popular database objects. It is used to retrieve data from the result set of an SQL query one row at a time. Even if the cursor is not recommended from a performance perspective, they are still widely used especially when handling a small amount of data.

SQL游标是最流行的数据库对象之一。 它用于一次从一行SQL查询的结果集中检索数据。 即使从性能角度不建议使用游标，它们仍然被广泛使用，尤其是在处理少量数据时。

There are many related objects needed to use a cursor such as @@FETCH_STATUS function. In this article, we will try to describe SQL cursor briefly and then we will explain @@FETCH_STATUS function by providing some examples to clarify each of its returned values.

使用游标需要许多相关的对象，例如@@ FETCH_STATUS函数。 在本文中，我们将尝试简要描述SQL游标，然后通过提供一些示例来阐明@@ FETCH_STATUS函数，以阐明其返回值。

SQL游标概述 (SQL cursor overview)

As mentioned before, a cursor is a database object used to fetch the rows from a result set. There are different phases in a cursor life cycle:

如前所述，游标是用于从结果集中获取行的数据库对象。 游标生命周期有不同的阶段：

宣告游标 (Declaring cursor)

First of all, we need to create a cursor object. Mainly there are 2 required parameters:

首先，我们需要创建一个游标对象。 主要有2个必需参数：

1. Cursor name
   游标名称
2. Source SQL query
   源SQL查询

You must use the following syntax to declare a cursor object:

您必须使用以下语法声明游标对象：

DECLARE <Cursor_name> CURSOR FOR <Source_SQL_Query>

Note that source SQL query should be a SELECT statement, as for example, the following cursor is to read the table schemas and names created in a database:

请注意，源SQL查询应为SELECT语句，例如，以下游标用于读取在数据库中创建的表模式和名称：

DECLARE csrTables CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES

开启游标 (Opening cursor)

Declaring SQL cursor doesn’t mean that you can start reading data from the result set, first, you need to retrieve the source SQL query result which is done using the OPEN command. The command syntax is very simple, just write OPEN keyword plus the name of the cursor as follows:

声明SQL游标并不意味着您可以开始从结果集中读取数据，首先，您需要检索使用OPEN命令完成的源SQL查询结果。 该命令的语法非常简单，只需编写OPEN关键字以及游标的名称，如下所示：

OPEN <Cursor_name>

提取行 (Fetching rows)

After opening the cursor, the source SQL query is executed and the result set is stored in memory. The next step is to fetch rows and to visualize them or to store them within some declared variables. Note that each column in the result set must be mapped to a variable having a relevant data type.

打开游标后，将执行源SQL查询，并将结果集存储在内存中。 下一步是获取行并将其可视化，或将其存储在某些声明的变量中。 请注意，结果集中的每一列都必须映射到具有相关数据类型的变量。

The FETCH command syntax is as follows:

FETCH命令的语法如下：

FETCH NEXT FROM <cursor_name> [ INTO @variable_name [ ,...n ] ]

If you decide to only fetch rows wi