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:

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:

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.