GeekInterview.com
Series: Subject:
Question: 182 of 196

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

Asked by: Interview Candidate | Asked on: Feb 7th, 2006
Showing Answers 1 - 11 of 11 Answers
Jawwad Malvi

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
Divesh

Answered On : 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

  
Login to rate this answer.
KiranKW

Answered On : May 2nd, 2007

View all answers by KiranKW

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 ...... ;

  
Login to rate this answer.
jiten

Answered On : 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.

  
Login to rate this answer.
chetanpunjabi

Answered On : Jun 27th, 2008

View all answers by chetanpunjabi

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.

  
Login to rate this answer.

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
luv2manish

Answered On : Mar 27th, 2010

View all answers by luv2manish

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)

  
Login to rate this answer.
michael

Answered On : 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

  
Login to rate this answer.
z_ashwini

Answered On : Jan 13th, 2012

View all answers by z_ashwini

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;

  
Login to rate this answer.
Vidya Khamkar

Answered On : 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.

  
Login to rate this answer.

set serveroutput on;
begin
delete from employ where empid = 1;
commit;
dbms_output.put_line(NO of records deleted||sql%rowcount);
end;

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.