Oracle PL/SQL Programming: A Developer's Workbook

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 ...