Using Cursors for Update

Test:Quiz:UsingCursorsForUpdate 1. You Youwa want ntt to ofe fetc tch hro rows wsf fro rom mth the eEM EMPL PLOY OYEE EES Sta tabl ble. e.Y You ouw wan ant tto tol loc ock kth the efe fe tchedrows,topreventotherusersfromupdati tchedrows,toprevent otherusersfromupdatingthem.Youdeclarethe ngthem.Youdeclarethefollowing following cursor: CURSORemp_cursIS SELECTemployee_id,last_name,salary FROMemployees --LineA--; WhatshouldyoucodeatLineA? WhatshouldyoucodeatLineA? MarkforReview (1)Points

FORLOCK

FORUPDATEOFemployees

FORUPDATE(*)

FORUPDATE(employees)

Correct 2. Youh ouhav ave ede decl cla ared redth the efo foll llow owin ing gcu curs rsor or: : CURSORcountry_cursIS SELECTcountry_id,country_name FROMwf_countries FORUPDATEWAIT10; AnotheruserupdatesarowinWF_COUNTRIESbutdoesnotCOMMITtheupda AnotheruserupdatesarowinWF_COUNTRIESbut doesnotCOMMITtheupdate.What te.What willhappenwhenyouOPENcountry_curs;? MarkforReview (1)Points

ALOCKED_ROWSexceptionisraisedimmediately.

Theotheruser'stransactionisautomaticallyrolledback.

YoursessionwaitsindefinitelyuntiltheotheruserCOMMITs.

Yoursessionwaitsfor10seconds,andthenreturnscontroltoyourblo Yoursessionwaitsfor10seconds,andthenre turnscontroltoyourbloc c ksothatitcancontinuetoexecute.(*)

Yourblockfailsbecauseyoushouldhavecoded Yourblockfailsbecau seyoushouldhavecoded:FORUPDATEWAIT(10); :FORUPDATEWAIT(10);

Correct 3. Youdeclareacursorasajoinoftwotables: CURSORemp_dept_cursIS SELECTlast_name,salary,department_name FROMemployeese,departmentsd WHEREe.department_id=d.department_id --PointA--; YouwanttolockfetchedrowsfromEMPLOYEES,butNOTlockfetchedrowsfromDEP ARTMENTS. WhichofthefollowingiscorrectatPointA? MarkforReview (1)Points

FORUPDATE

FORUPDATEofsalary(*)

FORUPDATEOFemployees

FORUPDATE(last_name)

Correct 4. WhencanweusetheWHERECURRENTOFclause? (1)Points

MarkforReview

OnlywithanUPDATE,notwithaDELETE.

OnlywithaDELETE,notwithanUPDATE.

WhenthecursorisdeclaredasSELECT...FORUPDATE...;(*)

Whenthecursorisbasedonasingletable(notonajoin).

WhenthecursorhasnotbeenOPENed.

Correct 5. Whatisthedifferencebetweenthefollowingtwoblocksofcode? --BlockA

DECLARE CURSORemp_cursorIS SELECTemployee_id,last_name FROMemployees WHEREdepartment_id=80 FORUPDATEOFsalary; --BlockB DECLARE CURSORemp_cursorIS SELECTemployee_id,last_name FROMemployees WHEREdepartment_id=80 FORUPDATEOFsalary NOWAIT; MarkforReview (1)Points

Thereisnodifference;theprogramsbehaveexactlythesameway.

InBlockA,theprogramwaitsindefinitelyuntiltherowsareavailable. InBlockB,theprogramreturnscontrolimmediatelysothatitcandootherwor k.(*)

InBlockA,theprogramwaitsindefinitelyuntiltherowsareavailable. InBlockB,controlisreturnedtoyourprogramafter5secondssothatitcan dootherwork.

Correct 6. YouhavedeclaredacursorasSELECT....FORUPDATE;YouhaveOPENedth ecursorandlockedtheFETCHedrows.Whenaretheserowlocksreleased? MarkforReview (1)Points

WhenanUPDATE...WHERECURRENTOFcursor_name;isexecuted.

WhenyouCLOSEthecursor.

Whenyourblockfinishesexecuting.

WhenyouexplicitlyCOMMITorROLLBACKyourtransaction.(*)

WhenanotherusertriestoSELECTtherows.

Correct 7.

IftherowsyouattempttoreserveusingFORUPDATEhavealreadybeenl

ockedbyanothersessionandyouusetheNOWAIToption,whatistheoutcome? MarkforReview (1)Points

Theblockexecutessuccessfullywithnoerrors.

Theserverwillwaituntilthelockshavebeenreleasedbytheotheruse r.

AnOracleservererroroccurs.(*)

Yourrowswilloverridetheotherusers'lockandyourblockwillexecut esuccessfully.

Correct