Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL

PL/SQL Parameterized Cursor



PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query.

Default values is assigned to the Cursor parameters. and scope of the parameters is locally.

Parameterized Explicit Cursor Example

following one emp_information table:


EMP_NO EMP_NAME EMP_DEPT EMP_SALARY
1 Jay Web Developer 45k
2 Ajay Program Developer 38k
3 Bhavesh Program Developer 34k
4 Jigar Web Developer 42k

Example Code:

SQL>set serveroutput on
SQL>edit parameter_cursor_demo
DECLARE
	cursor c(no number) is select * from emp_information
	where emp_no = no;
	tmp emp_information%rowtype;
BEGIN 
	OPEN c(4);
	FOR tmp IN c(4) LOOP
	dbms_output.put_line('EMP_No:    '||tmp.emp_no);
	dbms_output.put_line('EMP_Name:  '||tmp.emp_name);
	dbms_output.put_line('EMP_Dept:  '||tmp.emp_dept);
	dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);			
	END Loop;
CLOSE c;
END;	
/
SAVE: parameter_cursor_demo.sql

Example Result:

SQL>@parameter_cursor_demo
EMP_No:    4
EMP_Name:  Jigar
EMP_Dept:  Web Developer
EMP_Salary:42k

PL/SQL procedure successfully completed.