Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
    • Advanced Topics
    • Cursors
    • Stored Procedures and Functions
    • Triggers
    • Dynamic SQL
    • Call-Level Interface
    • Using SQL to Generate SQL
    • Direct Versus Embedded SQL
    • Summary
    • Q&A
    • Workshop
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Cursors

To most people, a cursor is commonly known as a blinking dot or square that appears on the monitor and indicates where you are in a file or application. That is not the same type of cursor discussed here. An SQL cursor is an area in database memory where the last SQL statement is stored. If the current SQL statement is a database query, a row from the query is also stored in memory. This row is the cursor's current value or current row. The area in memory is named and is available to programs.

A cursor is typically used to retrieve a subset of data from the database. Thereby, each row in the cursor can be evaluated by a program, one row at a time. Cursors are normally used in SQL that is embedded in procedural-type programs. Some cursors are created implicitly by the database server, whereas others are defined by the SQL programmer. Each SQL implementation may define the use of cursors differently.

This section shows syntax examples from two popular implementations: Microsoft SQL Server and Oracle.

The syntax to declare a cursor for Microsoft SQL Server is as follows:

   
DECLARE CURSOR_NAME CURSOR
FOR SELECT_STATEMENT
[ FOR [READ ONLY | UPDATE [ COLUMN_LIST ]}]

The syntax for Oracle is as follows:

   
DECLARE CURSOR CURSOR_NAME
IS {SELECT_STATEMENT}

The following cursor contains the result subset of all records from the EMPLOYEE_TBL table:

   
DECLARE CURSOR EMP_CURSOR IS
SELECT * FROM EMPLOYEE_TBL
{ OTHER PROGRAM STATEMENTS }

According to the ANSI standard, the following operations are used to access a cursor once it has been defined:

Opening a Cursor

When a cursor is opened, the specified cursor's SELECT statement is executed and the results of the query are stored in a staging area in memory.

The syntax to open a cursor in dBASE is as follows:

   
OPEN CURSOR_NAME

The syntax in Oracle is as follows:

   
OPEN CURSOR_NAME [ PARAMETER1 [, PARAMETER2 ]]

To open the EMP_CURSOR:

   
OPEN EMP_CURSOR

Fetching Data from a Cursor

The contents of the cursor (results from the query) can be retrieved through the use of the FETCH statement once a cursor has been opened.

The syntax for the FETCH statement in Microsoft SQL Server is as follows:

   
FETCH CURSOR_NAME [ INTO FETCH_LIST ]

The syntax for Oracle is as follows:

   
FETCH CURSOR_NAME {INTO : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]
[, : HOST_VARIABLE
[[ INDICATOR ] : INDICATOR_VARIABLE ]]
| USING DESCRIPTOR DESCRIPTOR ]

The syntax for dBASE is as follows:

   
FETCH CURSOR_NAME INTO MEMORY_VARIABLES

To fetch the contents of EMP_CURSOR into a variable called EMP_RECORD, your FETCH statement may appear as follows:

   
FETCH EMP_CURSOR INTO EMP_RECORD

Closing a Cursor

You can obviously close a cursor if you can open a cursor. Closing a cursor is quite simple. After it's closed, it is no longer available to user programs.

The Microsoft SQL Server syntax for the closing of a cursor and the deallocation of a cursor is as follows:

   
CLOSE CURSOR_NAME

DEALLOCATE CURSOR CURSOR_NAME

When a cursor is closed in Oracle, the resources and name are released without the DEALLOCATE statement. The syntax for Oracle is as follows:

   
CLOSE CURSOR_NAME

To release the resources in dBASE, the table must be closed and reopened before the resources are released and the name can be reused. The syntax for dBASE is as follows:

   
CLOSE CURSOR_NAME

Share This