Requirements: Query and output employee information for Department 30th
Mode one: Use Loop...fetch
1 SETServeroutput on;2 DECLARE3 CURSORC_emp is 4 SELECT * fromEmpWHEREDeptno= -;5V_emp EMP%RowType; 6 BEGIN7 OPENc_emp;8 Loop9 FetchC_emp intov_emp;Ten Exit whenC_emp%NotFound; OneDbms_output.put_line (v_emp.empno||' '||V_emp.ename||' '||V_emp.job||' '||V_emp.mgr||' '||V_emp.hiredate||' '||V_emp.sal||' '||V_emp.comm||' '||V_emp.deptno); A EndLoop; - Closec_emp; - END; the /
Mode two: Using a cursor for loop
1 SETServeroutput on;2 DECLARE3 CURSORC_emp is4 SELECT * fromEmpWHEREDeptno= -;5V_emp EMP%RowType; 6 BEGIN7 forEinchC_emp Loop8Dbms_output.put_line (e.empno||' '||E.ename||' '||E.job||' '||E.mgr||' '||E.hiredate||' '||E.sal||' '||E.comm||' '||E.deptno);9 EndLoop;Ten END; One /
Method Three: Using dynamic SQL and binding variables
1 SETServeroutput on;2 DECLARE3TYPE Emp_cursor isREFCURSOR;4 c_emp emp_cursor;5V_emp EMP%RowType;6 BEGIN7 OPENC_emp for 'SELECT * from emp where Deptno =:n'USING -;8 Loop9 FetchC_emp intov_emp;Ten Exit whenC_emp%NotFound; OneDbms_output.put_line (v_emp.empno||' '||V_emp.ename||' '||V_emp.job||' '||V_emp.mgr||' '||V_emp.hiredate||' '||V_emp.sal||' '||V_emp.comm||' '||V_emp.deptno); A ENDLoop; - Closec_emp; - END; the /
Small examples of using cursors, dynamic SQL, and bound variables in PL/SQL