1. Write a pl/sql block which declares and uses cursors with the parameters.
In a loop, use a cursor to retrieve the department number and department name from the departments table for a department whose department_id is less than 100. Pass the department number to the another cursor as a parameter to retrieve from the employees table the information of employee last name, job, hire date, and salary of those employees whose employee_id is less than 120 and who work in that department.
a) In the declarative section, declare a cursor dept_cursor to retrieve department_id and department_name for those departments with the department_id less than 100. Order by department_id.
b) Declare another cursor emp_cursor that takes the deparment number as parameter and retrieves last_name, job_id, hire_date, and salary of those employees whose employee_id is less than 120 and who work in that department.
c) Declare variables to contain the values retrieved from each cursor. Use the % type attribute while declaring variables.
d) Open the dept_cursor, use a simple loop, and fetch values in variables declared. Display the department number and department name.
e) For each department, open emp_cursor by passing the current department number as a parameter. Begin another loop and fetch the values of emp_cursor in variables and print all the details retrieved from employees table.
Note: You might desire to print a line after you have displayed the details of each department. Use proper attributes for the exit condition. As well, determine whether a cursor is already open before opening the cursor.
f) Close all the loops and cursors, and then end the executable section. Execute the script.