w3resource

PL/SQL Cursor Exercises: Print the specifc number of rows from a table

PL/SQL Cursor: Exercise-44 with Solution

Write a block in PL/SQL to print the specifc number of rows from a table.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
  CURSOR emp_cur IS
    SELECT first_name,last_name, salary FROM employees
    WHERE ROWNUM < 8
    ORDER BY first_name;

   emp_fname employees.first_name%TYPE;	
   emp_lname employees.last_name%TYPE;
   emp_sal   employees.salary%TYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO emp_fname,emp_lname, emp_sal;
    IF emp_cur%NOTFOUND THEN 
      EXIT;
    ELSE  
      DBMS_OUTPUT.PUT_LINE
        (rpad('Name: ' || emp_fname||' '|| emp_lname ,30)|| 'salary:  ' || emp_sal);
    END IF;
  END LOOP;
END;
/

Sample Output:

SQL> /
Name: Alexander Hunold        salary:  9000
Name: Bruce Ernst             salary:  6000
Name: David Austin            salary:  4800
Name: Lex De Haan             salary:  17000
Name: Neena Kochhar           salary:  17000
Name: Steven King             salary:  24000
Name: Valli Pataballa         salary:  4800

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: Print the specifc number of rows from a table.

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to print the department name, head of the department, city and number of employees are wroking in that department.
Next: Write a block in PL/SQL to print a dotted line in every 6th line.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.