w3resource

PL/SQL Cursor Exercises: Print a dotted line in every 6th line

PL/SQL Cursor: Exercise-45 with Solution

Write a block in PL/SQL to print a dotted line in every 6th line.

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 FROM employees
    WHERE ROWNUM < 15
    ORDER BY first_name;
  emp_fname employees.first_name%TYPE;
  emp_lname employees.last_name%TYPE;
  i number:=1;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO emp_fname,emp_lname;
    EXIT WHEN emp_cur%NOTFOUND OR emp_cur%NOTFOUND IS NULL;
    DBMS_OUTPUT.PUT_LINE(rpad(emp_cur%ROWCOUNT || '. ',10)|| emp_fname ||' '|| emp_lname);
    IF emp_cur%ROWCOUNT = 6*i THEN
       DBMS_OUTPUT.PUT_LINE('--------------------------------');
	   i:=i+1;
    END IF;
  END LOOP;
  CLOSE emp_cur;
END;
/

Sample Output:

SQL> /
1.        Alexis Bull
2.        Amit Banda
3.        Anthony Cabrio
4.        David Bernstein
5.        David Austin
6.        Elizabeth Bates
--------------------------------
7.        Ellen Abel
8.        Harrison Bloom
9.        Hermann Baer
10.       Laura Bissot
11.       Mozhe Atkinson
12.       Sarah Bell
--------------------------------
13.       Shelli Baida
14.       Sundar Ande

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: Print a dotted line in every 6th line.

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to print the specifc number of rows from a table.
Next: Write a block in PL/SQL to display the first department with more than five employees.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.