PLSQL_6_6_Practice.docx - www.oracle.com/academy Database...

This preview shows page 1 - 3 out of 12 pages.

The preview shows page 1 - 3 out of 12 pages.
Database Programming with PL/SQL6-6:Using Multiple CursorsPracticeActivitiesVocabularyNo new vocabulary for this lessonTry It / Solve It1.Write and run a PL/SQL block which produces a listing of departments and their employees. Usethe DEPARTMENTS and EMPLOYEES tables. In a cursor FOR loop, retrieve and display thedepartment_id and department_name for each department, and display a second line containing '----------' as a separator. In a nested cursor FOR loop, retrieve and display the first_name,last_name, and salary of each employee in that department, followed by a blank line at the end ofeach department. Order the departments by department_id, and the employees in eachdepartment by last_name.You will need to declare two cursors, one to fetch and display the departments, the second to fetchand display the employees in that department, passing the department_id as a parameter.Your output should look something like this (only the first few departments are shown):10 Administration-----------------------------Jennifer Whalen 440020 Marketing-----------------------------Pat Fay 6000Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of theirrespective owners.
2Michael Hartstein 1300050 Shipping-----------------------------Curtis Davies 3400Randall Matos 2600Kevin Mourgos 5800Trenna Rajs 3500Peter Vargas 2500DECLARECURSOR cur_dep IS SELECT department_id AS id, department_name AS nombreFROM departments;CURSOR cur_emp(p_id_dep NUMBER) IS SELECT employee_id AS id,first_name AS nombre, last_name AS apellido,salary AS salarioFROM employees WHERE department_id=p_id_dep;BEGINFOR reg_dep IN cur_dep LOOPDBMS_OUTPUT.PUT_LINE(reg_dep.id||' '||reg_dep.nombre);DBMS_OUTPUT.PUT_LINE('------------------------');FOR reg_emp IN cur_emp(reg_dep.id) LOOPDBMS_OUTPUT.PUT_LINE('ID:'||reg_emp.id||' '||reg_emp.nombre||' '

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 12 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
JORGE OMAR JASSO LUNA
Tags
Nicaragua, El Salvador, Indigenous peoples of the Americas

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture