Using Multiple Cursors

Test:Quiz:UsingMultipleCursors 1. Assu Assume met tha hat tta tabl ble eBI BIGD GDEP EPTS TSc con onta tain ins s10 100 0ro rows ws, ,an and dta tabl ble eBI BIGE GEMP MPS Sco cont ntai ains ns 1000rows,with10employeesineachdepartme 1000rows,with10em ployeesineachdepartment.Considerthefollowin nt.Considerthefollowingcode: gcode: DECLARE CURSORbigdept_curIS SELECT*FROMbigdepts; CURSORbigemp_curIS SELECT*FROMbigemps; BEGIN FORdept_recINbigdept_curLOOP DBMS_OUTPUT.PUT_LINE(dept_rec.department_name); FORemp_recINbigemp_curLOOP IFemp_rec.department_id=dept_rec.department_id THENDBMS_OUTPUT.PUT_LINE(emp_rec.last_name); ENDIF; ENDLOOP; ENDLOOP; END; Whyisthiscodeinefficient? MarkforReview (1)Points Itlocksbothtablesunnecessarily. Itisusingtwocursorswhenonecursorisenough. ItisdoingaCartesianProduct,joiningeveryemployeewitheverydepa ItisdoingaCartesianProduct,joiningevery employeewitheverydepar r tmentanddisplaying1100linesofoutput. Itreads1000employeerowseverytimeBIGEMP_CURisOPENed,andtheni Itreads1000employeerowseverytimeBIGEMP_ CURisOPENed,andthenig g nores990ofthem.(*) ItisusingcursorFORloops,whicharelessefficientthanOPENingand ItisusingcursorFORloops,whicharelesse fficientthanOPENingand CLOSEingthecursorsexplicitly.

Correct 2. E x a m i n e th e f o l l o w i n g c o d e: DECLARE CURSORregion_curIS SELECT*FROMwf_world_regions; v_region_recregion_cur%ROWTYPE; CURSORcountry_cur(p_region_idNUMBER)IS SELECT*FROMwf_countries WHEREregion_id=p_region_id; v_country_reccountry_cur%ROWTYPE; BEGIN OPENregion_cur; LOOP FETCHregion_curINTOv_region_rec;

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.

EXITWHENregion_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (v_region_rec.region_name); --LineA-- LOOP FETCHcountry_curINTOv_country_rec; EXITWHENcountry_cur%NOTFOUND; ...... WhatwouldyoucodeatLineA? MarkforReview (1)Points OPENcountry_cur(p_region_id); OPENcountry_cur(wf_world_regions.region_id); OPENcountry_cur(v_region_rec.region_id);(*) OPENcountry_cur(region_cur.region_id); OPENcountry_cur;

Correct 3. You Youw wan ant tto top pro rodu duce cea ar rep epor ort twh whic ich hdi disp spla lays yse eac ach hde depa part rtme ment nta and nd (immediatelyaftereachdepartment)alistof (immediatelyafterea chdepartment)alistofemployeeswhoworkinth employeeswhoworkinthatdepart atdepart ment.YoudeclareaDEPARTMENTScursoras: CURSORdept_cursIS SELECT*FROMdepartments ORDERBYdepartment_id; HowcouldyoudeclaretheEMPLOYEEScursor?(Choosetwo). MarkforReview (1)Points (Chooseallcorrectanswers) CURSORemp_cursIS SELECT*FROMemployees; CURSORemp_curs(p_dept_idNUMBER)IS SELECT*FROMemployees

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.

SELECT*FROMemployees ORDERBYdepartment_id; CURSORemp_curs (p_dept_iddepartments.department_id%TYPE)IS SELECT*FROMemployees WHEREdepartment_id=p_dept_id; (*)

CURSORemp_cursIS SELECT*FROMemployees WHEREdepartment_id=departments.department_id;

Incorrect.RefertoSection5Lesson6. 4. Whi Which cho of fth the efo foll llow owin ing gis isa ag goo ood dre reas ason ont to ous use etw two ocu curs rsor ors sin ina as sin ingl gle e PL/SQL PL/SQLbl block ock? ? Markf Markfor orRev Review iew (1)Points Toallowonecursortobeopenedtwiceatthesametime. Whentwotablesarerelatedtoeachother(oft Whentwotablesarere latedtoeachother(oftenbyaforeignkey)and enbyaforeignkey)andw w ewanttoproduceamultilevelreportusingdatafrombothtables.(*) ewanttoproduceamultilevelreportusingda tafrombothtables.(*) ToallowrowstobelockedastheyareFETCHed. TospeeduptheexecutionofthePL/SQLblock. Itistheonlywaytodeclareacursorwithaparameter.

Correct 5. Whi Which cho of fth the efo foll llow owin ing gis isN NOT OTa all llow owed edw whe hen nus usin ing gmu mult ltip iple lec cur urso sors rsw wit ith h para parame mete ters rs? ? Mark Markf for orR Rev evie iew w (1)Points YoucannotusecursorFORloops. YoucannotdeclarethecursorsFORUPDATE.

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.

Noneoftheabove,theyareallallowed.(*)

Correct 6. Ass Assum ume eyo your urs sch chem ema aco cont ntai ains ns2 25 5ta tabl bles es. .Ho How wma many nye exp xpli lici cit tcu curs rsor ors sca can nyo yo udeclareandusewithinasinglePL/SQLblock udeclareandusewith inasinglePL/SQLblock? ? MarkforReview (1)Points Onlyone. Asmanyasyouneed-thereisnolimit.(*) Amaximumofthree. Asmanyasyouneed,butonlyoneofthemcan Asmanyasyouneed,b utonlyoneofthemcanbeopenatanytime. beopenatanytime. Amaximumof25(oneforeachtableinyourschema).

Correct