Implicit Cursor & Explicit Cursor

Implicit Cursor are declared and used by the oracle internally oracle automatically opens  and closes the cursor .Where as the explicit cursors are declared and used by the user to .process multi row select statement.

Implicit CURSOR Example
--CREATE TABLE employees (empid  NUMBER,empname varchar2(256));

CREATE OR REPLACE PROCEDURE test_implict_cursor
AS
BEGIN
  INSERT INTO employees
  VALUES ('1', 'GEORGE'); --INSERT
 
  INSERT INTO employees
  VALUES ('2', 'THOMOS'); --INSERT
 
  INSERT INTO employees
  VALUES ('3', 'DAVID'); --INSERT
 
  FOR items IN (SELECT empid, empname
                FROM employees)
    LOOP
    DBMS_OUTPUT.put_line ( 'empid= '
                           || items.empid
                           || ', empname = '
                           || items.empname
                           );
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_IMPLICT_CURSOR;
Output:

empid = 1, empname = GEORGE
empid = 2, empname = THOMOS
empid = 3, empname = DAVID

Explicit CURSOR example 1

--CREATE TABLE employees (empid  NUMBER,empname varchar2(256));

CREATE OR REPLACE PROCEDURE test_explict_cursor
AS
CURSOR cursor1
IS
SELECT empid, empname
FROM employees;
BEGIN
  INSERT INTO employees
  VALUES ('1', 'GEORGE'); --INSERT
 
  INSERT INTO employees
  VALUES ('2', 'THOMOS'); --INSERT
 
  INSERT INTO employees
  VALUES ('3', 'DAVID'); --INSERT
 
  FOR items IN cursor1
    LOOP
    DBMS_OUTPUT.put_line ( 'empid= '
                           || items.empid
                           || ', empname = '
                           || items.empname
                           );
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_EXPLICT_CURSOR;

Output:

empid = 1, empname = GEORGE
empid = 2, empname = THOMOS
empid = 3, empname = DAVID



Explicit CURSOR example 2
CREATE TABLE employees (empid NUMBER, empname VARCHAR2(256));
/
INSERT INTO employees
VALUES ('1', 'GEORGE')
/
INSERT INTO employees
VALUES ('2', 'THOMOS')
/
INSERT INTO employees
VALUES ('3', 'DAVID')
/

CREATE OR REPLACE PROCEDURE test_explict_cursor
AS
CURSOR cursor1
IS
SELECT empid, empname
FROM employees;

empid employees.empid%TYPE;
empname employees.empname%TYPE;
employees_details employees%ROWTYPE;
BEGIN
  OPEN cursor1;
 
  LOOP
    FETCH cursor1
    INTO employees_details; --
   
    DBMS_OUTPUT.put_line ( 'empid= '
                           || employees_details.empid
                           || ' empname = '
                           || employees_details.empname
                           );
    EXIT WHEN cursor1%NOTFOUND;
-- process data record
  END LOOP;
END;
/

SHOW ERROR;
SET SERVEROUTPUT ON;

EXEC TEST_EXPLICT_CURSOR;