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

Assignment Help:

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.


Related Discussions:- Pl sql code to declare cursors with parameter

Theory of panspermia - origin of life, THEO R Y OF PANSPERMIA - Arrh...

THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Max and min operator in sql, MAX and MIN operator in SQL Example: ...

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

Creating a table, Creating a Table Syantax: CREATE TABLE ENROL...

Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Use of count in sql, Use of COUNT in SQL It describes and discusses va...

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Query, ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quan...

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Example of when or then constraints - sql, Example of WHEN or THEN Constrai...

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Keyword and parameter description - goto statement, Keyword and Parameter D...

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

Differentiate between snowflake schema and star schema, Problem: (a) De...

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

I want customer management program, This is a Customer Management project. ...

This is a Customer Management project. Customer data is presented in a text file. The program will load this text data into its DB columns. The data mapping is user definable. User

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd