31 Oct Sql server tutorial 16 Using SQL Cursors
Key Skills & Concepts
- Understand SQL Cursors
- Declare a Cursor
- Open and Close a Cursor
- Retrieve Data from a Cursor
Use Positioned UPDATE and DELETE Statements
s we have looked at different aspects of SQL throughout this book, we have used
direct invocation to create and access various data objects. Direct invocation, or
interactive SQL, is a type of data access method that supports the ad hoc execution of
SQL statements, usually through some sort of client application. For example, you can use
SQL Server Management Studio, Oracle’s SQL Developer, or MySQL Workbench to
interact directly with your SQL database. However, direct invocation generally represents
only a small percentage of all database use. A far more common method used to access
SQL databases is embedded SQL, a data access model in which SQL statements are
embedded in an application programming language, such as C, Java, and COBOL. To
support embedded SQL, the SQL standard allows you to declare cursors that act as
pointers to specific rows of data in your query results. This chapter explains why cursors
are used and how cursors can be declared, opened, and closed within an SQL session.
You’ll also learn how to retrieve data using the cursor so that your programming language
can work with SQL data in a format that the application can process.
Understand SQL Cursors
One of the defining characteristics of SQL is the fact that data in an SQL database is
managed in sets. In fact, query results returned by SELECT statements are often referred
to as result sets. These result sets are each made up of one or more rows extracted from
one or more tables.
When working with SQL data interactively, having data returned in sets rarely presents
a problem because you can normally scroll through the query results to find the
information you need. If the size of the results is too great to easily skim through, you can
narrow the focus of your query expression to return a more manageable result set.
However, most data access is through means other than direct invocation (despite the fact
that we access data interactively throughout the book). One of the most common methods,
embedded SQL, accesses data through SQL statements embedded in an application
impedance mismatch exists between SQL and the programming languages. Impedance
mismatch refers to differences between SQL and other programming languages. As you
might recall from Chapter 3, one example of impedance mismatch is the way in which
SQL data types differ from data types in other programming languages. These differences
can lead to the loss of information when an application extracts data from an SQL
database. Another example of impedance mismatch is the fact that SQL returns data in
sets but other programming languages cannot handle sets. Generally, they can process
only a few pieces of data (a single record) at the same time. The way in which SQL deals
with this type of impedance mismatch is through the use of cursors.
A cursor serves as a pointer that allows the application programming language to deal
with query results one row at a time, much like the way these programming languages
handle records from traditional (flat) data files. Although the cursor can traverse all the
rows of a query result, it focuses on only one row at a time.
You can also use cursors in SQL client modules, which are sets of SQL statements that can be called from within
an application programming language. Client modules, along with embedded SQL and interactive SQL, provide
one more method to invoke SQL statements. Because client modules are not implemented as widely as embedded
SQL, I focus on using cursors in embedded SQL. For more information about SQL client modules, see Chapter 18.
Although declaring a cursor is pivotal in using that cursor in your application, the
declaration alone is not enough to extract data from an SQL database. In fact, full cursor
functionality is supported through the use of four SQL statements, each of which are
embedded in the application programming language, or host language. The following
descriptions provide an overview of these four statements:
DECLARE CURSOR Declares the SQL cursor by defining the cursor name, the
cursor’s characteristics, and a query expression that is invoked when the cursor is
OPEN Opens the cursor and invokes the query expression, making the query results
available to FETCH statements.
FETCH Retrieves data into variables that pass the data to the host programming
language or to other embedded SQL statements.
CLOSE Closes the cursor. Once the cursor is closed, data cannot be retrieved from
the cursor’s query results.
The four statements are called from within the host language. Figure 16-2 illustrates
how the cursor-related statements are used. The embedded SQL statements are shown in
the boxes that are shaded gray.