What is Simple Cursor? and What is Parametrized cursor?

Every SQL statement executed by Oracle has a Private SQL area that contains info about SQL statement and the set of data returned.

In PL/SQL, a cursor is a name assigned to a specific
private SQL area of a specific SQL Statement.
There can be either, Static Cursor whose SQL statements is determined at compile time, or Dynamic Cursor, whose SQL statement is determined at runtime.

Note: Dynamic Cursors are implemented using Oracle built in package DBMS_SQL.

Implicit Cursors:
----------------
Any SQL statement that is executed directly or in an PL/SQL block i.e. execution section or in exception section, during which it is associated with a work area in memory of oracle (SGA). This is done using implicit cursor by Oracle.

We do not need to declare implicit cursor hence not be
opened, fetched and closed.

Explicit Cursors:
-----------------

They are the SELECT statement that is declared explicitly in the declaration section of current block or in a package
specification.

Further we can use open, fetch and close in execution section or exception section of the block or program to utilize declared cursor.

To use an explicit cursor we need to declare it in
declaration section of block or package specification.

There are three sub types of explicit cursors:

* Simple Cursor (without parameter)

CURSOR emp_cur IS
SELECT emp_id
FROM emp;

* Parameterized Cursor - That accepts arguments

CURSOR emp_cur (dept_in [IN] NUMBER) IS
SELECT emp_id
FROM emp
WHERE dept = dept_in ;
* Returning Cursor - A cursor header that contains
a return clause

CURSOR emp_cur (dept_in [IN] NUMBER) IS
RETURN emp%ROWTYPE
SELECT *
FROM emp;


Hope so, above paragraphs explain cursors very well and in details.

A cursor is a private sql area used to stored intermediate data from the sql statements.whenever an sql statement is fired an cursor get open indirectly which is called as implicit cursor and whenever an user want to process data based on some sql statements result he has to explicitly define a cursor known as explicit cursor, A simple cursor is where no parameters are used where as a parametrized cursor is a cursor where the query result are based explicitly on the parameters passed to the cursor

Code
DECLARE
 CURSOR C1(ENO NUMBER) IS
   SELECT * FROM EMP
   WHERE EMPNO=ENO;
BEGIN
 
  FOR I IN C1(&ENO) LOOP
   DBMS_OUTPUT.PUT_LINE(ENAME);
 END LOOP;
END;
 
 
 

The difference between simple and parameterized cursor is as follows:

Simple Cursor - A cursor having a simple or a complex SQL query and which will have conditions based on given variables or hard-coded.

Parameterized Cursor - such types of cursors will accept parameters i.e. values of the where clause in the cursor query will depend on the passed parameters.

Cursors having some private area.which is used for store the data from sql statements.Normal cursors we don't have use any parameters.but parametrized cursors are used the parameters,which is executed based upon the parameter.