From Oracle FAQ
A cursor is a pointer used to
from a result set. One can think of a cursor as a data structure that describes the results returned from a
SELECT statement. One of the
in this structure is a pointer to the next record to be fetched from the query results.
Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into the : maximum number of open cursors exceeded error.
example opening a cursor and fetching data from it in a loop:
CURSOR c1 IS
SELECT table_name FROM all_
v_table_name all_tables.table_name%TYPE;
INTEGER := 1;
FETCH c1 INTO v_table_
IF c1%notfound OR v_count & 2000 THEN
v_count := v_count + 1;
-- Does cursor need to be closed
IF c1%ISOPEN THEN
-- cursor is open
dbms_output.put_line('Rows processed: '||v_count);
Implement a parameterized cursor:
CURSOR MyCur(p_sal emp.sal%TYPE) IS
SELECT * FROM emp WHERE
FOR MyRow IN MyCur(123) LOOP
dbms_Output.Put_Line(MyRow.eName ||' ' ||MyRow.sal);
Cursor with UPDATE OF and CURRENT OF:
CURSOR abc IS
SELECT a FROM my_seq FOR UPDATE OF
Myvar := 1;
FOR MyRow IN abc LOOP
UPDATE my_seq SET a = Myvar WHERE CURRENT OF
Myvar := Myvar + 1;
- Maximum open cursors exceeded
- Invalid cursor
Navigation menu
This page was last modified on 30 September 2014, at 14:24.}