PL/SQL Parameterized Cursor

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

PL/SQL Parameterized cursor define only datatype of parameter and not need to define it's length.

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

Parameterized cursors are also saying static cursors that can passed parameter value when cursor are opened.

Following example introduce the parameterized cursor. following emp_information table,

EMP_NO EMP_NAME EMP_DEPT EMP_SALARY
1 Forbs ross Web Developer 45k
2 marks jems Program Developer 38k
3 Saulin Program Developer 34k
4 Zenia Sroll Web Developer 42k

Example

Cursor display employee information from emp_information table whose emp_no four (4).

parameter_cursor_demo.sql
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;  
/

Result

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

PL/SQL procedure successfully completed.

Important key point you must remember

  1. Scope of the parameters are locally
  2. You can assign default value to a cursor parameter.