Topic: SQL*Plus and PL/SQL >> Cursor???
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Muks |
Posted: Jul 11, 2007 06:50:26 AM |
Total Post: 47
Joined: Jan, 2007
|
Could someone pls tell me y do we need cursor's .....like for example to get this kind of an o/p
EmployeeS
------------------------
Jason Martin makes $1234.56
Alison Mathews makes $6661.78
James Smith makes $6544.78
Celia Rice makes $2344.78
Robert Black makes $2334.78
Linda Green makes $4322.78
David Larry makes $7897.78
James Cat makes $1232.78
i can write a query which would be way to simple.... then y shud i write cursor for it.
|
|
|
 Nehru |
| Posted: Jul 27, 2007 01:23:54 AM |  |
|
Total Post: 46
Joined: Jul, 2007
|
Hi,
Just am writting some comments on CURSORS in Oracle i Think this will help to understand the usage of CURSORS
Cursor in Oracle ::
What is a Cursor.
When a query is executed in oracle, a result set is produced and stored in the memory. Oracle allows the programmer to access this
result set in the memory through cursors.
Why use a cursor?
Many times, when a query returns more than one row as a result, we might want to go through each row and process the data in different
way for them. Cursor is handy here.
Types of cursors:
Oracle PL/SQL declares a cursor implicitly for all queries and DML statements (including queries that return only one row).
But in most of the cases we don’t use these cursors for queries that return one row. Based on this, there are 2 types of cursors
1. Explicit cursor
2. Implicit cursor
Explicit cursor:
The programmer declares a cursor explicitly to process the result set of query (Mostly the query that returns multiple rows as result).
The following are the steps to use an explicit cursor.
a. Declare the cursor -- This means give the cursor a name and associate the query that is going to return multiple rows.
b. Open the cursor -- execute the query
c. Fetch the cursor -- Get the result set and loop through to process them
d. Close the cursor -- Close cursor processing
Implicit Cursor:
Oracle implicitly opens a cursor to process each sql statement that is not associated with an explicit cursor. An implicit cursor is
opened for all queries (even if it returns only one row as the result set).The most recent implicit cursor (that is, the cursor for the
most recent query ) can be referred to with SQL cursor. Unlike explicit cursors, there is no necessity to use the
OPEN, FETCH and CLOSE statements with these cursors. Instead cursor attributes could be used.
Cursor attributes:
Cursor attributes are variables that take some value about the status of the cursor. These values are automatically set by Oracle and
the programmer can read them not write values for them. There are four cursor attributes. They are
1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT
Take Care
Bye
Nehru
|
|
|
|
|
| Muks |
| Posted: Jul 27, 2007 04:22:44 AM | |
|
Total Post: 47
Joined: Jan, 2007
|
Thanks Nehru that was really helpful
besties
Mukta
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 335 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
