Small examples of using cursors, dynamic SQL, and bound variables in PL/SQL

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