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
We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Example of Shorthand for a row constraint Example: Shorthand for a row constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Mark_in_range CHECK (Mark BETWEEN 0 AND 100);

Great Plains (Microsoft Dynamics) Purchases Report Project Description: I want to build a purchases report that matches the General Ledger. presently, when I join the PM20

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a