Expert
10-18. | What is the output from the following code snippet? BEGIN UPDATE employee SET salary = salary; FOR v_rec IN ( SELECT employee_id FROM employee ) LOOP IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('TRUE - ' || SQL%ROWCOUNT); ELSE DBMS_OUTPUT.PUT_LINE('FALSE - ' || SQL%ROWCOUNT); END IF; END LOOP; END; / |
10-19. | What is the output from the following code snippet? DECLARE CURSOR cur IS SELECT employee_id FROM employee; BEGIN FOR rec IN cur LOOP IF cur%ROWCOUNT = 1 THEN IF cur%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END IF; END LOOP; END; |
10-20. | How would you change the following block of code to make better use of the FOR UPDATE clause and also improve the program’s performance? DECLARE CURSOR upd_all_cur IS SELECT * FROM employee FOR UPDATE; BEGIN /* || Double everyone's commisson */ FOR rec IN upd_all_cur LOOP IF rec.commission IS NOT NULL THEN UPDATE employee SET commission = commission * 2 WHERE employee_id = rec.employee_id; END IF; END LOOP; END; |
10-21. | Which row in the employee table is locked when this block is executed? DECLARE CURSOR upd_of_sal_cur IS SELECT * FROM employee FOR UPDATE OF salary; BEGIN FOR rec IN upd_of_sal_cur LOOP IF rec.commission IS NOT NULL THEN UPDATE employee SET commission = commission * 2 WHERE CURRENT OF upd_of_sal_cur; END IF; END LOOP; END; |
10-22. | Suppose you create the following package; it contains three cursors (door_number_one, door_number_two and door_number_three) that have the same query but are not ... |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.