TT Ads

Cursors give us a choice of how we move through the tuples of the relation. The default, and most common choice is to start at the beginning and fetch the tuples in order, until the end. On the other hand, there are other orders in which tuples may be fetched, and tuples could be scanned many times before the cursor is closed. To take advantage of these options, we need to do two things.

1. When declaring the cursor, put the keyword SCROLL before the keyword CURSOR. This change tells the SQL system that the cursor may be used in a manner other than moving forward in the order of tuples.

2. In a FETCH statement, follow the keyword FETCH by one of various options that tell where to find the desired tuple. These options are:

(a) NEXT or PRIOR to get the next or previous tuple in the order. Remember that these tuples are relative to the current position of the cursor. NEXT is the default if no option is specified, and is the usual choice.

(b) FIRST or LAST to get the first or last tuple in the order.

(c) RELATIVE followed by a positive or negative integer, which indicates how many tuples to move forward (if the integer is positive) or backward (if negative) in the order. For example, RELATIVE 1 is a synonym for NEXT, and RELATIVE -1 is a synonym for PRIOR.

(d) ABSOLUTE followed by a positive or negative integer, which indicates the position of the desired tuple counting from the front (if positive) or back (if negative). For instance, ABSOLUTE 1 is a synonym for FIRST and ABSOLUTE -1 is a synonym for LAST.

Example 1:  Let us rewrite the function of “Modifications by Cursor” Figure 1 to begin at the last tuple and move backward through the list of tuples. First, we need to declare cursor execCursor to be scrollable, which we do by adding the keyword SCROLL in line (6), as:

6) EXEC SQL DECLARE execCursor SCROLL CURSOR FOR MovieExec;

Also, we need to initialize the fetching of tuples with a FETCH LAST statement, and in the loop we use FETCH PRIOR. The loop that was lines (8) through (14) in “Modifications by Cursor” Figure 1 is rewritten in “Dynamic SQL” Figure 1. The reader should not assume that there is any advantage to reading tuples in the reverse of the order in which they are stored in MovieExec.

Tags

tuples
integer
sql system
loop
Dynamic SQL
Protecting Against Concurrent Updates
Modifications by Cursor
Cursors
Triggers in SQL
Schema-Level Constraints and Triggers
Tuple-Based CHECK Constraints
Keys Declared With UNIQUE
Constraints and Triggers
Modifying Views
View Definitions
Introduction to Selection of Indexes
Default Values / Indexes
Simple Table Declarations
Defining a Relation Schema in SQL
Deletion / Updates
Database Modifications
Grouping / HAVING Clauses
Full-Relation Operations
Union, Intersection, and Difference of Queries
Tuple Variables
Disambiguating Attributes
Queries Involving More Than One Relation
Null Values and Comparisons Involving NULL
Projection in SQL
The Database Language SQL
Additional Constraint Examples
Extending the Projection Operator
Grouping
Extended Operators of Relational Algebra
Selection on Bags / Product of Bags / Joins of Bags
Union, Intersection, and Difference of Bags
Relational Operations on Bags
Dependent and Independent Operations
Selection / Cartesian Product
Set Operations on Relations
An Algebra of Relational Operations
Relational Algebra
Information Integration Via Semistructured Data
Nested Relations
The Object-Relational Model
Representing ODL Relationships
Representing Other Type Constructors
Nonatomic Attributes in Classes
Relationships in ODL / Inverse Relationships
Reasoning About Multivalued Dependencies
Definition of Multivalued Dependencies
Multivalued Dependencies
Boyce-Codd Normal Form
Decomposing Relations
Why the Closure Algorithm Works
Trivial Functional Dependencies
Rules About Functional Dependencies
Keys of Relations
Using Null Values to Combine Relations – Comparison of Approaches
Converting Subclass Structures to Relations
From E/R Diagrams to Relational Designs
Relation Instances
Equivalent Representations of a Relation
Tuples / Domains
Converting Multiway Relationships to Binary
Multiway Relationships
Database System Implementation
Database Design
Multimedia Data
Relational Database Systems

TT Ads

Post not found !

Leave a Reply

Your email address will not be published.