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
Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

WHEN or THEN Key Constraints Suppose a table has two columns representing a period of time throughout which the information conveyed by the other columns is recorded as having