Cursors in PL/SQL

What are cursors in PL/SQL? Kindly provide me an example to understand the usage and syntax of cursors.

[QUOTE]When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it.[/QUOTE]

For further reference,
[URL="http://www.unix.org.ua/orelly/oracle/langpkt/ch01_09.htm"]http://www.unix.org.ua/orelly/oracle/langpkt/ch01_09.htm[/URL]

[URL="http://www.unix.org.ua/orelly/oracle/prog2/ch06_02.htm"]http://www.unix.org.ua/orelly/oracle/prog2/ch06_02.htm[/URL]

Cheers,
Kalayama

Oracle uses [B]work areas to execute SQL statements and store processing information. [/B]
These work areas are called Cursors which are used to access its stored information.

There are two kinds of cursors: Implicit Cursor and Explicit Cursor.
[B]Implicit Cursors [/B]:- PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.
Implicit Cursors are opened & closed implicitly & it does not require declaration also.

[B]Explicit Cursors [/B]:- Queries that return more than one row needs explicit declaration to process the rows individually.
Explicit Cursors needs four steps to be followed, DECLARE, OPEN, FETCH & CLOSE.
Ex:
DECLARE
CURSOR emp_cur IS
SELECT empno, ename from emp;
BEGIN
OPEN emp_cur;
FETCH emp_cur INTO l_empno, l_ename;
CLOSE emp_cur;
END;
Declare the Cursor - Naming and defining the structure of the query to be performed.
Open the Cursor - The OPEN statement executes the query.
Fetch data from Cursor - Fetches the row data into the variables for further processing of the records.
Close the Cursor - Finally the cursor is closed.

*** Innila ***

Cursors are replacements for multiple select statements..You can fetch record by record using cursors in oracle.
The basic steps in cursor operation is
Declare the cursor
Open the cursor
Fetch the cursor and
Close the cursor.

The fetch statement will fech a list of records from the DB and stores in the cursor area which can be used for retrieval.

hi bro,
Cursor is a private SQL Work Area.
There are two types of cursor
1) Implicit Cursor
2)Explicit Cursor which is user define

EXAMple
Retrive first 10 employees one by one
DECLARE
v_no employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id,last_name
FROM employees;
BEGIN
OPEN emp_cursor
FOR i IN 1...10 LOOP
FETCH emp_cursor INTO v_no,v_name;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_no)|| ' '||v_name);
END LOOP;
END;


REGARD
MIHIR

we can also use parametrized cursor..........
try this-


set serveroutput on
declare
v_id employees.employee_id%type;
v_name employees.last_name%type;
cursor c(v_dept number) is
select employee_id,last_name from employees where department_id=v_dept;
begin
open c(10);
loop
fetch c into v_id,v_name;
exit when c%notfound;
dbms_output.put_line(v_id||v_name);
end loop;
end;