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

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

Package specification in pl/sql , Package Specification The package sp...

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Using rename in combination with join - sql, Using RENAME in combination wi...

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

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

Initializing objects in pl sql, Initializing Objects: Till you initiali...

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Advantages of subprograms, Advantages of Subprograms The Subprograms g...

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Effects of null for unique specification - sql, Effects of NULL for UNIQUE ...

Effects of NULL for UNIQUE Specification When a UNIQUE specification u for base table t includes a column c that is not subject to a NOT NULL constraint, the appearance of sev

Existential quantification - sql, Existential Quantification - SQL Ex...

Existential Quantification - SQL Existential quantification-stating that something is true of at least one object under consideration-can be expressed by OR(r,c), meaning tha

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va...

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

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