Cursors definition and types explained

Question

Accepted Answer

Cursors are used to retrieve and manipulate data from a database in PL/SQL.

Cursors are like pointers to a result set, allowing us to fetch and process rows one by one.
There are two types of cursors: implicit and explicit.
Implicit cursors are automatically created by Oracle when executing a SQL statement.
Explicit cursors are declared and used by the programmer.
Explicit cursors provide more control and flexibility compared to implicit cursors.
Cursors can be used to fetch data from single or multiple tables.
Cursor variables can be used to pass cursors as parameters to procedures or functions.
Cursor attributes like %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN can be used to check the status of a cursor.
Cursor FOR LOOP is a convenient way to process all rows returned by a cursor.
Example: DECLARE CURSOR c_emp IS SELECT * FROM employees; emp_rec employees%ROWTYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO emp_rec; EXIT WHEN c_emp%NOTFOUND; -- process the row END LOOP; CLOSE c_emp; END;
Example: DECLARE CURSOR c_dept IS SELECT department_name FROM departments WHERE location_id = 1700; dept_name departments.department_name%TYPE; BEGIN OPEN c_dept; FETCH c_dept INTO dept_name; IF c_dept%FOUND THEN -- process the row ELSE -- handle no rows found END IF; CLOSE c_dept; END;

Accepted Answer

Cursor is a sql private work area. Cursor can execute only one row at a time

Implicit cursor - oracle will automatically open and fetch and close the cursor

Explicit cursor - developer need to open and fetch and close the cursor

Top TCS Plsql Developer interview questions & answers