Write a pl/sql block that declares and uses cursors with parameters.
In a loop, use a cursor to retrieve the department number and the department name from the departments table for a department whose department_id is less than 100. Pass the department number to another cursor as a parameter to retrieve from employees table the details 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 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 hold 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 into the 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. Start another loop and fetch the values of emp_cursor into variables and print all the details retrieved from the employees table.
Note: You may want to print a line after you have displayed the details of each department. Use appropriate attributes for the exit condition. Also, 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.