New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Cursors


    A database cursor is similar to the cursor on a word processor screen. As you press the Down Arrow key, the cursor scrolls down through the text one line at a time. Pressing the Up Arrow key scrolls your cursor up one line at a time. Hitting other keys such as Page Up and Page Down results in a leap of several lines in either direction. Database cursors operate in the same way.

    Database cursors enable you to select a group of data, scroll through the group of records (often called a recordset), and examine each individual line of data as the cursor points to it. You can use a combination of local variables and a cursor to individually examine each record and perform any external operation needed before moving on to the next record.


    One other common use of cursors is to save a query's results for later use. A cursor's result set is created from the result set of a SELECT query. If your application or procedure requires the repeated use of a set of records, it is faster to create a cursor once and reuse it several times than to repeatedly query the database. (And you have the added advantage of being able to scroll through the query's result set with a cursor.)

    Follow these steps to create, use, and close a database cursor:

1.Create the cursor.

2.Open the cursor for use within the procedure or application.

3.Fetch a record's data one row at a time until the end of the cursor's records.

4.Close the cursor when you are finished with it.

5.Deallocate the cursor to completely discard it.

Creating a Cursor


    To create a cursor using Transact-SQL, issue the following syntax:

SYNTAX:

declare cursor-name cursor for select-statement
[for {read only | update [of column-name-list]}]

    The Oracle7 SQL syntax used to create a cursor looks like this:

SYNTAX:

DECLARE cursor-name CURSOR FOR {SELECT command | statement-name | block-name}

    By executing the DECLARE cursor-name CURSOR statement, you have defined the cursor result set that will be used for all your cursor operations. A cursor has two important parts: the cursor result set and the cursor position.

    The following statement creates a cursor based on the ARTISTS table:

INPUT:

create Artists-Cursor cursor for select * from ARTISTS go

ANALYSIS:

    You now have a simple cursor object named Artists-Cursor that contains all the records in the ARTISTS table. But first you must open the cursor.

Opening a Cursor


    The simple command to open a cursor for use is

SYNTAX:

open cursor-name

    Executing the following statement opens Artists-Cursor for use:

open Artists-Cursor go

    Now you can use the cursor to scroll through the result set.

Scrolling a Cursor


    To scroll through the cursor's result set, Transact-SQL provides the following FETCH command.

SYNTAX:

fetch cursor-name [into fetch-target-list]

    Oracle SQL provides the following syntax:

FETCH cursor-name {INTO : host-variable [[INDICATOR] : indicator-variable]
[, : host-variable [[INDICATOR] : indicator-variable] ]... |
USING DESCRIPTOR descriptor }

    Each time the FETCH command is executed, the cursor pointer advances through the result set one row at a time. If desired, data from each row can be fetched into the fetch-target-list variables.


NOTE:Transact-SQL enables the programmer to advance more than one row at a time by using the following command: set cursor rows number for cursor-name. This command cannot be used with the INTO clause, however. It is useful only to jump forward a known number of rows instead of repeatedly executing the FETCH statement.


    The following statements fetch the data from the Artists-Cursor result set and return the data to the program variables:

INPUT:

declare @name char(30)
declare @homebase char(40)
declare @style char(20)
declare @artist-id int
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
print @name
print @homebase
print @style
print char(@artist-id) go

    You can use the WHILE to loop through the entire result set. But how do you know when you have reached the end of the records?

Testing a Cursor's Status


    Transact-SQL enables you to check the status of the cursor at any time through the maintenance of two global variables: @@sqlstatus and @@rowcount.

    The @@sqlstatus variable returns status information concerning the last executed FETCH statement. (The Transact-SQL documentation states that no command other than the FETCH statement can modify the @@sqlstatus variable.) This variable contains one of three values. The following table appears in the Transact-SQL reference manuals:
Status Meaning
0 Successful completion of the FETCH statement.
1 The FETCH statement resulted in an error.
2 There is no more data in the result set.

    The @@rowcount variable contains the number of rows returned from the cursor's result set up to the previous fetch. You can use this number to determine the number of records in a cursor's result set.

    The following code extends the statements executed during the discussion of the FETCH statement. You now use the WHILE loop with the @@sqlstatus variable to scroll the cursor:

INPUT:

declare @name char(30)
declare @homebase char(40)
declare @style char(20)
declare @artist-id int
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
while (@@sqlstatus = 0)
begin
print @name
print @homebase
print @style
print char(@artist-id)
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
end go

ANALYSIS:

    Now you have a fully functioning cursor! The only step left is to close the cursor.

Closing a Cursor


    Closing a cursor is a very simple matter. The statement to close a cursor is as follows:

SYNTAX:

close cursor-name

    This cursor still exists; however, it must be reopened. Closing a cursor essentially closes out its result set, not its entire existence. When you are completely finished with a cursor, the DEALLOCATE command frees the memory associated with a cursor and frees the cursor name for reuse. The DEALLOCATE statement syntax is as follows:

SYNTAX:

deallocate cursor cursor-name

    The complete process of creating a cursor, using it, and then closing it, using Transact-SQL.

INPUT:

declare @name char(30)
declare @homebase char(40)
declare @style char(20)
declare @artist-id int
create Artists-Cursor cursor
for select * from ARTISTS
open Artists-Cursor
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
while (@@sqlstatus = 0)
begin
print @name
print @homebase
print @style
print char(@artist-id)
fetch Artists-Cursor into @name, @homebase, @style, @artist-id
end
close Artists-Cursor
deallocate cursor
Artists-Cursor go

Cursor without parameters (simplest)


SYNTAX:

CURSOR cursor-name IS SELECT-statement;

For example, you could define a cursor called c1 as below.

CURSOR c1
IS
SELECT course-number from courses-tbl
where course-name = name-in;

    The result set of this cursor is all course-numbers whose course-name matches the variable called name-in. Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCours ( name-in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course-number
from courses-tbl
where course-name = name-in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;

Cursor with parameters


SYNTAX:

CURSOR cursor-name (parameter-list)
IS
SELECT-statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject-id-in IN varchar2)
IS
SELECT course-number from courses-tbl
where subject-id = subject-id-in;

    The result set of this cursor is all course-numbers whose subject-id matches the subject-id passed to the cursor via the parameter.

Cursor with return clause


SYNTAX:

CURSOR cursor-name
RETURN field%ROWTYPE
IS
SELECT-statement;

For example, you could define a cursor called c3 as below.

CURSOR c3
RETURN courses-tbl%ROWTYPE
IS
SELECT * from courses-tbl where subject = 'Mathematics';

Open Cursor


Once you've declared your cursor, the next step is to open the cursor.

SYNTAX:

OPEN cursor-name;

For example, you could open a cursor called c1 with the following command:

OPEN c1;

Fetch


    The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.

SYNTAX:

FETCH cursor-name INTO <list of variables>;

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course-number into the variable called cnumber;

CLOSE


The final step of working with cursors is to close the cursor once you have finished using it.

SYNTAX:

CLOSE cursor-name;

For example, you could close a cursor called c1 with the following command:

CLOSE c1;

Cursor Attributes


    While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute Explanation
%IsOpen - Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%Found - Returns INVALID-CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns NULL if cursor is open, but fetch has not been executed.
- Returns TRUE if a successful fetch has been executed.
- Returns FALSE if no row was returned.
%Notfound - Returns INVALID-CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Return NULL if cursor is open, but fetch has not been executed.
- Returns FALSE if a successful fetch has been executed.
- Returns TRUE if no row was returned.
%Rowcount - Returns INVALID-CURSOR if cursor is declared, but not open; or if cursor has been closed.
- Returns the number of rows fetched.

SELECT FOR UPDATE Statement


    The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

SYNTAX:

CURSOR cursor-name
IS
select-statement FOR UPDATE [of column-list] [NOWAIT];

CURSOR c1
IS
SELECT course-number, instructor from courses-tbl
FOR UPDATE of instructor;

    If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

WHERE CURRENT OF Statement


    If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

SYNTAX:

UPDATE table-name SET set-clause WHERE CURRENT OF cursor-name;

DELETE FROM table-name WHERE CURRENT OF cursor-name;

    The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Cursor within a cursor


    In this example, we have a cursor called get-tables that retrieves the owner and table-name values. These values are then used in a second cursor called get-columns.

create or replace procedure MULTIPLE-CURSORS-PROC is
v-owner varchar2(40);
v-table-name varchar2(40);
v-column-name varchar2(100);
/* First cursor */
cursor get-tables is
select distinct tbl.owner, tbl.table-name
from all-tables tbl
where tbl.owner = 'SYSTEM';
/* Second cursor */
cursor get-columns is
select distinct col.column-name
from all-tab-columns col
where col.owner = v-owner
and col.table-name = v-table-name;
begin
-- Open first cursor
open get-tables;
loop
fetch get-tables into v-owner, v-table-name;
open get-columns;
loop
fetch get-columns into v-column-name;
end loop;
close get-columns;
end loop;
close get-tables;
EXCEPTION
WHEN OTHERS THEN
raise-application-error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE-CURSORS-PROC;

    The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Cursor with variable in an "IN CLAUSE"


Assumptions & declarations:

1. Ref-cursor is of type REF CURSOR declared in Package

2. I will to pass a comma separated Numbers as a string

3. This should be used in the query in the IN Clause

4. Execute the Query and Return the Output as REF Cursor

    Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

Create or Replace Function func-name
Return PackageName.ref-cursor
As
out-cursor PackageName.Ref-cursor;
v1 varchar(2);
v2 varchar(2);
v3 varchar(2);
Begin
v1 := '1';
v2 := '2';
v3 := '3';
Open out-cursor
For Select * from Table-name
where column-name in (v1, v2, v3);
Return out-cursor;
End;

The Scope of Cursors


    Unlike tables, indexes, and other objects such as triggers and stored procedures, cursors do not exist as database objects after they are created. Instead, cursors have a limited scope of use.

WARNING: Remember, however, that memory remains allocated for the cursor, even though its name may no longer exist. Before going outside the cursor's scope, the cursor should always be closed and deallocated.


    A cursor can be created within three regions:

In a session--A session begins when a user logs on. If the user logged on to an SQL Server and then created a cursor, then cursor-name would exist until the user logged off. The user would not be able to reuse cursor-name during the current session.

Stored procedure--A cursor created inside a stored procedure is good only during the execution of the stored procedure. As soon as the stored procedure exits, cursor-name is no longer valid.

Trigger--A cursor created inside a trigger has the same restrictions as one created inside a stored procedure.