What
are cursors? Distinguish between implicit and explict cursors ?
Oracle execute every
query in a seperate memory area called context area and pointer (with
a name) to this area is known as cursor. Implicit cursor are
internally used by oracle engine to run a query. Explicit cursors are
user defined which are declared & open for a select query in
pl/sql code block. They are used when SELECT INTO query return more
than one row and we need to process each row one by one.
What
are the different parts of an Explicit Cursor ?
The different parts
in the process of making an Explicit Cursor are as follows:
1. Declaring the
Cursor
2. Opening the
Cursor
3. Fetching the
Cursor
4. Closing the
Cursor
What
are the cursor attributes used in PL/SQL ?
%Rowcount: checks
the number of rows that are updated, deleted or fetched.
%Isopen: checks
whether the Cursor you want to access is currently open or closed.
%Found: checks if
the Cursor fetched a row. It returns a TRUE if any row is fetched.
%NotFound: checks if
the Cursor fetched any row or not. It returns a TRUE value if any row
is not fetched.
What
is the difference between NO DATA FOUND and %NOTFOUND ?
NO DATA FOUND is a
oracle exception which is thrown if the SELECT INTO statement doesn't
return at least one row. While %NOTFOUND is cursor attribute to check
whether cursor has any row or not.
State
the advantage and disadvantage of Cursor ?
Advantages:
- we can perform row by row processing so we can perform row wise validation or operations on each row.
- Cursors can be faster than a for loop but at the cost of more overhead
Disadvantages:
- occupies memory from your system that may be available for other processes
- Need to be closed properly to avoid ORA-01000: maximum open cursors exceeded
What
is cursor for loop ?
The cursor FOR LOOP
statement implicitly declares its loop index as a record variable of
the row type that a specified cursor returns, and then opens a
cursor. With each iteration, the cursor FOR LOOP statement fetches a
row from the result set into the record. When there are no more rows
to fetch, the cursor FOR LOOP statement closes the cursor. The cursor
also closes if a statement inside the loop transfers control outside
the loop or raises an exception.
Explain
the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF
clause is used along with FOR UPDATE clause. When the session opens a
cursor with the FOR UPDATE clause, all rows in the return set will
hold row-level exclusive locks. Other sessions can only query the
rows, but they cannot update, delete, or select with FOR UPDATE.
The WHERE CURRENT OF
clause is used with UPDATE or DELETE statement to update or delete
the most recent row fetched from the cursor.
Syntax: WHERE
[CURRENT OF cursor_name | search_condition]
What
is ref cursor ?
A REF CURSOR is a
PL/SQL data type whose value is the memory address of a query work
area on the database. In essence, a REF CURSOR is a pointer or a
handle to a result set on the database.
What
are types of Ref cursor ?
There are 2 types of
REF cursors : Strong and weak.
TYPE emp_curtype IS
REF CURSOR RETURN emp%ROWTYPE; --Strong REF cursor
TYPE emp_curtype IS
REF CURSOR; --weak REF cursor
Which
one is better - Strong or weak ?
It depends on your
requirement. In strong cursor, we attach the return ROWTYPE at the
declaration time itself. So, the compiler knows what the cursor
variable should return, making it less error prone. On the other
hand, weak cursors are more flexible because they can be used with
any query, any return ROWTYPE, even different ROWTYPES at different
calls to it.
What
is the difference between cursor & ref cursor ?
A "normal"
plsql cursor is static in defintion while Ref cursors may be
dynamically opened or opened based on logic.
A ref cursor can be
returned to a client -- a cursor cannot be.
A ref cursor can be
passed from subroutine to subroutine -- a cursor cannot be.
A cursor can be
global -- a ref cursor cannot.
What
are the alternative to cursors for better performance ?
Cursors are not bad
for performance. However cursor for loops are bad as they process row
by row. You can use bulk collect with SELECT INTO by making sure
there is a list of collections in the INTO clause. Even in Oracle 10g
and higher Oracle will implicitly perform bulk collects.
What
are the Restrictions on Cursor Variables ?
- You cannot declare a cursor variable in a package specification. That is, a package cannot have a public cursor variable (a cursor variable that can be referenced from outside the package).
- You cannot store the value of a cursor variable in a collection or database column.
- You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
- Using a cursor variable in a server-to-server remote procedure call (RPC) causes an error. However, you can use a cursor variable in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway.
- You cannot use LOB parameters in a server-to-server RPC.
Which
is the Default Cursor in Oracle PL/SQL ?
Implicit Cursors are
the Default Cursor in PL/SQL. These cursors are automatically
activated when DML statements are encountered such as Insert, Delete
or Update.
What
is an Active Set ?
The set of rows that
a Cursor holds at a single point of time is called as an Active Set.
Why
is closing the Cursor required during explicit cursor development ?
It is important
because it will consume system memory while it is in active state and
if it is not closed or terminated then it won’t let the other
things in the memory as memory will be occupied and later on it will
be full. Hence, deletion is necessary. At some point time you may
face error ORA-01000: maximum open cursors exceeded.
What
cursor type do you use to retrieve multiple recordsets ?
Explicit cursors and
Ref cursor if recordset need to be returned to calling program.
No comments:
Post a Comment