SQL Cursor

4 Key excerpts on "SQL Cursor"

• 

  eBook - PDF

  SQL: 1999

  Understanding Relational Language Components

  • Jim Melton, Alan R. Simon(Authors)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Cursors Introduction We've mentioned several times that SQL processes data by sets. The primary mechanism used to permit SQL to do row-by-row accessman important facility when SQL is combined with C, COBOL, Ada, or another languagemis the cursor. In this chapter, we explore cursors and how they are used within SQL. In fact, as we implied in section 12.7, The Impedance Mismatch, the principal purpose of cursors is to act as a transformer to compensate for this aspect of the imped- ance mismatch between SQL and other programming languages. Cursors: The Basics Assume that you need to compute the standard deviation of the current sale cost of DVD format movies. Since there is no function to do that in SQL (well, not without the OLAP facilities discussed in Volume 2 of this book), you must retrieve that data into your application program and compute that value there. In other situations, your application may require the ability to display rows of data (formatted in some special way, perhaps) on a screen and allow a user to identify rows for deletion or modification by using a pointer of some sort, such as a cursor. 447 448 Chapter 13 Cursors The languages in which you write your applications (that is, the languages that invoke the SQL code: C, COBOL, Ada, and the others we discussed in the previous chapter) do not support sets or multisets of data. They are capable of dealing with only one or a few pieces of data at a time. This particular difference between SQL and the host languages is part of what we have called an impedance mismatch. SQL has facilities to help resolve this mismatch, and the cursor is among the most important of those facilities. The technique involves an object called a cursor. A cursor is something like a pointer that traverses a collection of rows. If your application has to traverse many rows of data, you cannot retrieve them all at once because your host lan- guage doesn't deal with an arbitrary number of rows.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Database Development and Management

  • Lee Chao(Author)
  • 2006(Publication Date)
  • Auerbach Publications

    (Publisher)

  If you are not clear whether to create a client cursor or a server cursor, you should create the server cursor. To implement a Transact-SQL Cursor, you can use the DECLARE CURSOR statement in Query Analyzer. For example, you can create a cursor called StudentCursor that contains student names and their club duties (see Figure 8.17). The cursor’s structure is created with no data in it. To use the cursor, you need to populate it with data by using the OPEN statement and then fetching the data in the cursor by the FETCH statement. When the OPEN statement is executed, the SQL statement defined in the DECLARE CURSOR statement is processed. The data returned by the SQL statement are used to populate the cursor. The FETCH statement retrieves data in the cursor one record at a time. The example in Figure 8.18 is used to get the data in the cursor Student-Cursor with a WHILE loop. You can see in the output that you fetch one record at a time by using a cursor. For many interactive database applications, it is often desirable to work with one record at a time or a set of specified records at a time. Cursors play an important role in database application development. When a cursor is no longer needed, you can close and deallocate the cursor to free the memory storage used by the cursor (see Figure 8.19). The freed memory can be used by other tasks. The message indicates that the cursor StudentCursor is successfully deallocated. Figure 8.17 Create Transact-SQL Cursor 242 Database Development and Management 8.5 Case Study: Hillcrest Computing The information about the company Hillcrest Computing is given in Chapter 2. The database Hillcrest Computing was created in Chapter 4. The definitions of the tables and the diagram of the tables were also created in Chapter 4. In this section, you will accomplish the following tasks for Hillcrest Computing: Create a view called LABOR_RATE_VIEW that includes computed values.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL in 7 Days

  A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-Side Programming (English Edition)

  • Alex Bolenok(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  As with many other SQL features, cursors are vestiges of the interactive usage pattern. SQL queries were meant to be entered by a human, interactively, from a simple console, with little to no help from other software. Used this way, cursors make a lot of sense. They give the user ability to run a query and page through its results. Early consoles and terminals had scarce memory. Storing the whole resultset anywhere except the server was just not an option.

  Later, SQL's usage pattern shifted from interactivity to meta-programming. SQL queries are now sent by code, not people. Programs do not need to stare at the data and decide what to do next. They know what they want from the database. They read the next chunk as soon as they are done with the previous one. For a program, a cursor is just a convenient way to loop through the records.

  Programs read the query output, record by record, in a loop, and put some decorations around it: some HTML tags or charting system markup. The data is often nested, and so is its processing. A usual data processing pattern is something like: "For each customer, start a new page. For each order of his customer, print its date. For each item within the order …". Nested loops. Sounds familiar, doesn't it?

  It takes quite a mind shift to learn to formulate these things in terms of joins and filters, rather than nested loops. Most programming languages have built-in support for loops but not for joins. Hence, most programmers are comfortable around loops more than they are around joins.

  If you are a user of an interactive console, you learn joins fast. Nested loops are just not an option for you. You will get sick of typing the same query again and again. But one thing computers are good at is doing a simple repetitive task in a loop. The developer types each query just once, inside the loop. The computer is happy to run it as many times as needed. And the easiest way to loop over records is using cursors.

  Running a single query with joins is oftentimes better than running multiple queries in a loop. It takes less time and resources. There are fewer network roundtrips and fewer queries to parse, and the optimizer is likely to come up with a better plan than nested loops.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Mastering PL/SQL Through Illustrations

  From Learning Fundamentals to Developing Efficient PL/SQL Blocks: From Learning Fundamentals to Developing Efficient PL/SQL Blocks (English Edition)

  • Dr. B. Chandra, B Chandra(Authors)
  • 2020(Publication Date)
  • BPB Publications

    (Publisher)

  HAPTER 4

  Cursors in PL/SQL

  Introduction

  PL/SQL Cursors provide a method to carry out row by row (record) processing of Oracle tables. Whenever a SQL statement is processed, a memory area called context area is created. A cursor is nothing but a pointer to this context area. A cursor holds multiple records returned by the SQL statement but can process one record at a time.

  List of topics

  • Explicit cursors
  • Implicit cursors
  • Cursor attributes
  • REF cursor
  • Cursor FOR loop
  • Illustrative PL/SQL blocks/codes using database tables

  Objective

  Explicit cursors are explicitly declared by the user and implicit cursors are automatically created by Oracle whenever a SQL statement is executed. Cursor attributes help the PL/SQL block in determining the state of a cursor. A REF cursor is a variable defined as a cursor type that points to a cursor. Cursor FOR loop is a special kind of FOR loop provided by PL/SQL for iterating repeatedly and fetching records without giving an explicit fetch command.

  4.1 Types of cursors

  There are two types of cursors:

  Figure 4.1

  Explicit cursors are explicitly defined by the user in the DECLARE section of the PL/SQL block and created on a SELECT

  Sign up to read

  Learn more about book