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: jobsjob_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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics