A cursor with parameters is similar to a stored procedure and a function. You can pass parameters to the cursor and use them in the query. It is usually used to open a cursor under certain conditions.
Unlike stored procedures, a cursor can only accept passed values, but cannot return values. The parameter only defines the data type and has no size.
Example:
DECLARE
CURSOR c_dept is select * from dept order by deptno;
CURSOR c_emp (p_dept VARCHAR2)
Is select ename, sal from emp where deptno = p_dept order by ename;
R_dept DEPT % ROWTYPE;
V_ename EMP. ENAME % TYPE;
V_salary EMP. SAL % TYPE;
V_total_salary v_salary % TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
Exit when c_dept % NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('department: '| r_dept.DEPTNO |'-'| r_dept.DNAME );
V_total_salary: = 0;
OPEN c_emp (r_dept.DEPTNO );
LOOP
FETCH c_emp INTO v_ename, v_salary;
Exit when c_emp % NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('name: '| v_ename | 'salary:' | v_salary );
V_total_salary: = v_total_salary + v_salary;
End loop;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE ('total salary of depart: '| v_total_salary );
End loop;
CLOSE c_dept;
END;
Url: http://greatverve.cnblogs.com/archive/2011/07/12/oracle-cursor.html