What is cursor
Answers were Sorted based on User's Feedback
In order to process a sql statements oracle will allocate an
area of memory known as the context area. The context area
contains information necessary to complete the processing,
including the number of rows processed by the select
statement, a pointer to the parsed representation of the
statement, and in the case of a query, the active set,
which is the set of rows returned by the query.
A cursor is a handle or pointer to the context area.
Sql cursor attributes are:
1. SQL%ROWCOUNT: number of rows affected by the most recent
sql statement (an integer value).
2. SQL%FOUND: Boolean attribute that evaluates to TRUE if
the most recent SQL statement affects one or more rows.
3. SQL%NOTFOUND: Boolean attribute that evaluates to true if
the most recent SQL statement does not affects any rows.
4. SQL%ISOPEN: always evaluates to false because pl/sql
closes implicit cursors immediately after they are executed.
Cursor Types:
1. Implicit cursor
2. Explicit Cursor
3. REF cursor
4. Parametrized cursor
5. FOR LOOP Cursor
Implicit cursor:
a.Are opened implicitly by oracle whenever a DML or select
statement is executed.
b.Opened, fetched, closes internally.
c.Un-named cursors
d.Attributes: sql%isopen, sql%found, sql%notfound, sql%rowcount.
Explicit cursor
•Are declared and opened explicitly by developers to
manipulate multiple rows returned by queries one by one.
•Manually we have to declare, open, fetch, and close it.
•Name given to a context area.
•Attributes: cur_name%isopen, cur_name%found,
ur_name%notfound, cur_name%rowcount.
REF CURSOR EXAMPLE
declare
type t1 is ref cursor;
v1 t1;
begin
open v1 for select * from inv;
open v1 for select * from inv2;
end;
Parameterized cursor:
We can pass parameters for cursor as like procedures and
functions.
Syntax: cursor cursor_name[parameter_name datatype] is
select statement;
the advantage of parameterized cursor is, a single cursor
can be opened and closed several times in a block, returning
different active set in each occasion.
Note: formal parameters should not be mentioned with data type
Thanks,
Tripti
Is This Answer Correct ? | 10 Yes | 0 No |
Answer / ramdeep garg
A cursor is a SELECT statement that is defined within the
declaration section of your PLSQL code. We'll take a look
at three different syntaxes for cursors.
Cursor without parameters (simplest)
The basic syntax for a cursor without parameters is:
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 FindCourse
( 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;
Is This Answer Correct ? | 7 Yes | 1 No |
Answer / vipin kumar ( m.c.a)
Cursor is a private SQL work area to perform an action using
PL/SQL statement. DBA allocate the memory space to the
objects that is area is called the cursor.
Cursor has two types-
1. Implicit cursor:- means predefine cursor.
2. Explicit cursor:- means created by the user or programmer.
Is This Answer Correct ? | 2 Yes | 1 No |
CURSOR is a temporary workspace area used to hold
transaction data.
It is valid in PLSQL only.
It is not stored in database.
It cannot be reuse.
Is This Answer Correct ? | 1 Yes | 3 No |
Query to retrieve record for a many to many relationship ?
What is a procedure in oracle?
What is index-organized table in Oracle?
How to define a specific record type?
Hi Every One , What is the table Space in Oracle Database ? waiting for answere and thankyou
How to convert the date format from dd/mm/yyyy to mm/dd/yyyy. (for eg). 22/05/2008 to 05/22/2008. when u use to_char(), it tells the month is invalid. how to resolve this problem.tel with eg.
how can get like this result table A col1 col2 --- ----- a A b B C C .. .. .. ... .. ... wants result like for a single column col1 ------ a,b,c,........n of data and another is col1 ------- A B C D ........ a b b d ........
What are the differences between interval year to month and interval day to second?
What is the purpose of tables, private synonyms and public synonyms in Oracle?
The join defined by the default data link is an outer join yes or no ?
How to insert a record into a table?
When a form is invoked with call_form, Does oracle forms issues a save point ?