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
Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Using the FORALL Statement The keyword FORALL instruct the PL/SQL engine to bulk-bind input collections before sending them all to the SQL engine. Though the FORALL statement

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Question 1 . Compare SQL and PL/SQL Question 2 . Write a database trigger to implement the following check condition                          Given the following table

to write a heap sort program usin pl-sql

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=