Objective :
  • Creating Anonymous procedure block
  • Creating explicit cursor to fetch 5 rows of emp table with for loop
  • Cursor equivalent PHP/SQL Script"
    select * from ( select empno, ename, sal from emp order by sal desc ) where ROWNUM <= 5
  • Limiting row output Using SQL statement
  • How RowNum Works:
    Embedded Query First
        Select ---from-- table
           where <where clause>
              group by <columns>
               having <having clause>
                 order by <columns>;
  • Expected out put : Descendent Amount of Salary
Oracle : Scott Account  emp table

Connecting HR

PL/SQL Script:

cursor emp_cur is
select ename, empno, sal
from emp
order by sal DESC;
var_name VARCHAR2 (10);
var_empno NUMBER (4);
var_sal NUMBER (7,2);
OPEN emp_cur;
for i in 1..5 LOOP
fetch emp_cur INTO var_name, var_empno, var_sal;
EXIT WHEN emp_cur%notfound;
dbms_output.put_line (var_name||var_empno||','||var_sal);
end loop;
close emp_cur;

Now click on green / run button.

Note the script out put: an anonymous block is completed

Turn ON DBMS output, FOR THE user scott

Now, click on "RunScript" Button or Press "F" function key


Using SQL* Plus

Which would be very similar to this type of query