In Oracle databases, a cursor is a database object that is used to manipulate and retrieve data from the result set of a statement. Cursors are classified into two types, implicit and explicit.
**Implicit Cursor:**
An implicit cursor is automatically created by Oracle when a SELECT, INSERT, UPDATE, or DELETE statement is executed. The implicit cursor is used to process each row that is returned by the query. The implicit cursor is managed by Oracle, and the programmer does not need to define or manage it explicitly.
For example, if a SELECT statement is executed in a PL/SQL block, a cursor is automatically created to retrieve the result set. The syntax for an implicit cursor is as follows:
SELECT column1, column2, ... columnN
FROM table_name;**Explicit Cursor:**
An explicit cursor is a cursor that is defined and managed explicitly by the programmer. An explicit cursor is used when the programmer needs more control over the cursors, such as when the programmer needs to process multiple cursors in a loop or when the programmer needs to use cursors with DML statements.
The explicit cursor consists of four steps, as follows:
1. Declare the cursor
2. Open the cursor
3. Fetch data from the cursor
4. Close the cursor
The syntax for an explicit cursor is as follows:
DECLARE
CURSOR cursor_name IS
SELECT column1, column2, ... columnN
FROM table_name;
variable_name table_name.column_name%type;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO variable_name;
EXIT WHEN cursor_name%NOTFOUND;
-- business logic here
END LOOP;
CLOSE cursor_name;
END;In summary, an implicit cursor is automatically created by Oracle when a SELECT, INSERT, UPDATE, or DELETE statement is executed, whereas an explicit cursor is explicitly defined and managed by the programmer. Implicit cursors are used for simple queries where no additional control is required, whereas explicit cursors are used when the programmer needs more control over the cursors.