Pl sql code to declare cursors with parameter, PL-SQL Programming

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.

Posted Date: 3/20/2013 2:37:36 AM | Location : United States

Related Discussions:- Pl sql code to declare cursors with parameter, Assignment Help, Ask Question on Pl sql code to declare cursors with parameter, Get Answer, Expert's Help, Pl sql code to declare cursors with parameter Discussions

Write discussion on Pl sql code to declare cursors with parameter
Your posts are moderated
Related Questions

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders that have not been shipped within a day of the order

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

Using raise_application_error The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustra

COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a