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.
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
- Scope of the parameters are locally
- You can assign default value to a cursor parameter.