Stored Procedure Exercises

-- cursor is output parameter create OR REPLACE PROCEDURE pro_emp_select (empsal out sys_refcursor) asbegin OPEN empsal for select Empno,sal from EMP;  END Pro_emp_select; -- call declarev_no emp.empno%type;v_sal emp.sal%type;emp_sal sys_refcursor; BEGIN  Pro_emp_select (emp_sal);  LOOP  FETCH emp_sal into v_no,v_sal;  EXIT when emp_sal%NOTFOUND;  Dbms_output.put_line (' number '| | v_no| | ' Salary '| | v_sal);  END LOOP;  CLOSE emp_sal; END;

-- cursor is input parameter create OR REPLACE PROCEDURE pro_emp_in (empsal in sys_refcursor) asv_no emp.empno%type;v_sal Emp.sal%TYPE; BEGIN  LOOP    FETCH empsal into v_no,v_sal;    EXIT when empsal%NOTFOUND;    Dbms_output.put_line (' number '| | v_no| | ' Salary '| | v_sal);  END LOOP; END pro_emp_in;declareemp_sal Sys_refcursor; BEGIN  OPEN emp_sal for SELECT empno,sal from EMP;  Pro_emp_in (emp_sal);  CLOSE emp_sal; END;

Stored Procedure Exercises