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

Controlling autonomous transactions, Controlling Autonomous Transactions ...

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Use triggers to maintain referential integrity, At times, Brewbean's has ch...

At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Unnest operator in sql, UNNEST operator in SQL The inverse operator of...

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

Parameter and keyword description - records, Parameter and Keyword Descript...

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Using extend - collection method, Using EXTEND To enlarge the size of ...

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Using aliases-declarations in sql, Using Aliases The Select-list items f...

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Organic evolution, Organi c Evolution Evolution is a slow continuou...

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

Write sql queries, Write SQL queries to solve the following specifications....

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Data types in sql - timestamp, Data Types in SQL - Timestamp TIMESTAMP...

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Object type in pl/sql, Object Type: The object type is a user-define...

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

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