PL/SQL Parameterized Cursor with Shortcut Method

Below is the Syntax:

CURSOR CursorName(FormalParameter DataType)

IS

Select Statement

Where ColumnName = FormalParameterName;

For IndexVariableName IN CursorName(ActualValue)

LOOP

Executable Statements;

END LOOP;

Parameterized Cursor With Shortcut Method(Example)

DECLARE

CURSOR C1(p_deptno number) is select * from emp where deptno = p_deptno;

BEGIN

FOR I in C1(10)

LOOP

Dbms_output.put_line(i.ename||’ ’||i.sal||’ ‘||i.deptno);

END LOOP;

END;

Write a program to display the employees as ‘CLERK’ or ‘ANALYST’ from EMP table, based on job as a parameter and also display the report statically using cursor for loops.

DECALRE

CURSOR C1(p_job varchar2) is select * from emp when job = p_job;

BEGIN

Dbms_output.put_line(‘Employees working as analyst’);

FOR I in C1(‘ANALYST’)

LOOP

Dbms_output.put_line(i.ename||’ ‘||i.job);

END LOOP;

Dbms_output.put_line(‘Employees working as CLERK’);

FOR I in C1(‘CLERK’)

LOOP

Dbms_output.put_line(i.ename||’ ||i.job);

END LOOP:

END;

NOTE: In oracle when we are defining number of cursor in PL/SQL block and also one cursor values passed into another cursor, then receiving cursor must be an parameterized cursor.

Write a PL/SQL program to retrieve all deptno from dept table using a static cursor and also pass these deptno from this cursor into another parameterized cursor, which returns employee details from emp table based on passed deptno.

DECLARE

CURSOR C1 is select * from dept;

CURSOR C2(p_deptno number) is select * from emp where deptno = p_deptno;

BEGIN

FOR I in C1

LOOP

Dbms_output.put_line(‘Department Number’||’ ‘||i.deptno);

FOR J IN C2(i.deptno)

LOOP

Dbms_output.put_line(‘Employee Name’||’ ||J.ename);

END LOOP;

END LOOP;

END;

Write a PL/SQL manager  Number and display the employees who are working under manager.

DECLARE

CURSOR C1 is select distinct(mgr)  from emp where mgr is not null;

CURSOR C2(p_mgr number) is select * from emp where mgr  p_mgr;

BEGIN

FOR I in C1

LOOP

Dbms_output.put_line(‘Manager Number’||’ ‘||i.mgr);

FOR J in C2(i.mgr)

LOOP

Dbms_output.put_line(‘Employee Names’||’ ‘||J.ename);

END LOOP;

END LOOP;

END;

NOTE: We can also pass default values in parameterized cursor using ‘DEFAULT’ or ‘ := ‘ operator because by default cursor parameters are ‘ IN ’ parameter, Below is the syntax

ParameterName DataType Default ActualValue

ParameterName DataType := ActualValue

First preference is not actual value and second preference is default value        

DECLARE

CURSOR C1(p_deptno Number Default 20) IS select * from emp where deptno = p_deptno;

BEGIN

FOR I in c1()

LOOP

Dbms_output.put_line(i.ename||’ ‘||i.depnto);

END LOOP;

END;

Write a PL/SQL program to modify salaries of the employee in emp table based on following condition

If JOB = ‘CLERK’ then increment by 100

If JOB = ‘SALESMAN’ then decrement by 200

If JOB = ‘ANALYST’ then increment by 100

DECLARE

CURSOR C1 is select * from emp;

I emp%rowtype;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT when C1%NOTFOUND;

If  i.job = ‘CLERK’ then

Update emp set sal = i.sal+100 where empno = i.empno;

Elsif i.job = ‘SALESMAN’ then

Update emp set sal = i.sal-200 where empno = iempno;

Elsif i.job = ‘ANALYST’ then

Update emp set sal = i.sal+100 where empno = i.empno;

END IF;

END LOOP;

CLOSE C1;

END;