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
Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t