May 2000
Intermediate to advanced
594 pages
11h 32m
English
Content preview from Oracle PL/SQL Programming: A Developer's Workbook
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
Start your free trial
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 ... |