w3resource

PL/SQL Program: Display job ids, titles, and minimum salaries of all jobs

PL/SQL While Loop: Exercise-7 with Solution

Write a PL/SQL program to display the job IDs, titles, and minimum salaries of all jobs.

Sample Solution:

Table: jobs
job_id				varchar(25)
job_title			varchar(50)
min_salary			integer
max_salary			integer

PL/SQL Code:

DECLARE
  CURSOR job_cursor IS
    SELECT job_id, job_title, min_salary
    FROM jobs;
job_recordjob_cursor%ROWTYPE;
BEGIN
  OPEN job_cursor;
  FETCH job_cursor INTO job_record;
  WHILE job_cursor%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('Job ID: ' || job_record.job_id);
    DBMS_OUTPUT.PUT_LINE('Job Title: ' || job_record.job_title);
    DBMS_OUTPUT.PUT_LINE('Minimum Salary: ' || job_record.min_salary);
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    FETCH job_cursor INTO job_record;
  END LOOP;
  CLOSE job_cursor;
END;
/

Sample Output:

Job ID: AD_PRES
Job Title: President
Minimum Salary: 20000
-------------------------
Job ID: AD_VP
Job Title: Administration Vice President
Minimum Salary: 15000
-------------------------
Job ID: AD_ASST
Job Title: Administration Assistant
Minimum Salary: 3000
-------------------------
Job ID: FI_MGR
Job Title: Finance Manager
Minimum Salary: 8200
-------------------------
Job ID: FI_ACCOUNT
Job Title: Accountant
Minimum Salary: 4200
-------------------------
Job ID: AC_MGR
Job Title: Accounting Manager
Minimum Salary: 8200
-------------------------
Job ID: AC_ACCOUNT
Job Title: Public Accountant
Minimum Salary: 4200
-------------------------
Job ID: SA_MAN
Job Title: Sales Manager
Minimum Salary: 10000
-------------------------

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves and display the job details, including their IDs, titles, and minimum salaries.

The cursor job_cursor is defined to select the job_id, job_title, and min_salary columns from the jobs table and then declares a variable job_record of type job_cursor, which store each fetched row.

The FETCH statement fetches the first row from the job_cursor into the job_record variable and the WHILE loop checks whether there are more rows to fetch from the cursor.

Within the loop, the DBMS_OUTPUT.PUT_LINE displays the job ID, job title, and minimum salary for each job.

The loop continues until there are no more rows to fetch.

Flowchart:

Flowchart: PL/SQL While Loop Exercises - Display job ids, titles, and minimum salaries of all jobs.

Improve this sample solution and post your code through Disqus

Previous: Display employee ids, names, and manager names.
Next: Employee data retrieval using cursor in PL/SQL.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/while-loop/plsql-while-loop-exercise-7.php