What is cursor? Explain the types of cursor and write one implicit cursor program to check how many records deleted from table?

Showing Answers 1 - 11 of 11 Answers

Jawwad Malvi

  • Feb 13th, 2006
 

The Cursor is a handle (name or a pointer) for the memory associated with a specific statement.  A cursor is basically an Area alocated by Oracle for executing the Sql Statements.  Oracle Uses an Implicit Cursor statement for a single row query and Explicit Cursor for a multi row query.

Types of Cursor :

I) Implicit

II) Explicit (Explicit cursor has three sub-types)

1)  Simple Cursor

2)  Parameterised Cursor

3) Ref Cursor

Divesh

  • Apr 23rd, 2006
 

declare num number(10);begin delete from ; num := sql%rowcount; dbms_output.put_line('Number of rows deleted arr : ' || num );end;please free to contact me on dibansal@cisco.com if you have any queries

  Was this answer useful?  Yes

KiranKW

  • May 2nd, 2007
 

Hi

   Hi,

   Please find the explanation below....

CURSOR

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.

There are different types of cursors but broadly classified into two :

 Implicit cursor 
 Explicit cursor.

   Every query that we issue on a database is an implicit cursor for eg. a select, an update, a delete etc.

  On the otherhand explicit cursor is a user defined cursor eg: cursor explicit_cur as ...... ;

  Was this answer useful?  Yes

jiten

  • Aug 27th, 2007
 

Cursor is a private SQL memory area. Generally it is used to improve to performance of the system.

Implicit cursor name is "SQL".

If you can used any DML statement in your plsql code.
At last you use SQL cursor name with its attributes so that you will meaning full information.

like

SQL%ROWCOUNT which gives how many rows are affected by this plsql block.

  Was this answer useful?  Yes

Implicit and Explicit.

Whenever you issue a SQL statement, the Oracle server opens an area of memory in which the command is parsed and executed. This area is called a cursor.
When the executable part of a block issues a SQL statement, PL/SQL creates an implicit cursor, which PL/SQL manages automatically. The programmer explicitly declares and names an explicit cursor.

  Was this answer useful?  Yes

A cursor is a handle (pointer) in memory for a DML operation (Select , Update).

There are mainly 2 types of cursors .
1) Implicit Cursor.
2) Explicit Cursor.

Implicit cursor: Oracle will implicitly creates an area for the DML operations. Programmer will not have control on implicit cursors. The only useful attribute on this implicit cursor is SQL%ROWCOUNT , it will give the number of rows affected by the recent DML operation.

The only Implicit cursor is SQL.

Explicit Cursor:
Explicit cursors are created by the programmer and programmer have control on it
Programmer can

1) Open

2) Close
3) Fetch

and do some manipulations on the values

Explicit Cursors are classified into

1) Normal cursor
2) Parameterized cursor
3) Cursor For Loops and
4) REF cursors

REF Cursors:

Normally when we create a normal cursor , we cant change the select query associated to that query (the query which is given at the time of definition)

But using REF cursors , we can change the cursor statement also.


These REF cursors are useful when we are sending data from one environment to another environment.


Thanks And Regards

Nagaraja.Musturi

luv2manish

  • Mar 27th, 2010
 

Cursor refers to a private work area assigned by Oracle to process the SQL / PLS SQL. Stetament

Types: Implicit & Explicit

Implicit: Whenever we are using any SQL queries Oracle assigns a private work area to statement, after processing the work area is lost, user cannot access the Implicit Cursor area. But if you want to check how many records got effected with your statment youcan use built in function %ROWCOUNT.
 
Explicit: When we are declering a cursor, open it, fetch the data one row at a time, or we can say whit the explicit cursor we are assigning a private work area to our statement when Oracle process the statement,  we can apply different cursor function ISOPEN, FOUND etc to access the details of statement.

Explicit cursor can be paramaterized
  
When we are talking about refcursors, its a cursor varible which is efficient then cursors.
types of refcursors are
Strong (Refcursor with return type)
Weak (Refcursor without return type)

  Was this answer useful?  Yes

michael

  • Aug 22nd, 2011
 

when we execute the sql statement from pl/sql it assigns the private work area for that statement.that contain all the information in it.in simplest form cursor is like a pointer in database table

  Was this answer useful?  Yes

z_ashwini

  • Jan 13th, 2012
 

Cursors when exected temporary allot spaces in DB.
There are 2 types of Cursors:
(1) Implicit
(2) Explicit
(3) Ref cursors
Implicit types of cursors are those cursors that are internally used by the DB and DB temporarily allocates space for processing.
Explicit cursors are the cursors that are defined and declared by the user.
Ref cursors are the cursors that are mainly used for collection of data.
You can use the same cursors number of times in the code.
E.G of Implicit cursor:
DECLARE
v_count number;
BEGIN
Delete from emp
where dept_id =101;
--Implicit Cursor
Select count(1) into v_count
from emp;
EXCEPTION
when no_data_found then
dbms_output.put_line(No data present in EMP);
when others then
dbms_oupput.put_line(ERROR :::sqlerrm);
END;

  Was this answer useful?  Yes

Vidya Khamkar

  • Sep 26th, 2012
 

Cursor - A cursor is like a virtual table, with row and columns specified by the query. A cursor also has a nation of current row, which is essentially a pointer to a row in the virtual table.
Types of cursors-
1) Static
2) Dynamic
3) Forwardonly
4) Keyset-driven.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions