Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

29-13.	What is the most efficient integer datatype for numbers in the 231 range? NUMBER BINARY_INTEGER PLS_INTEGER INT POSITIVE
29-14.	Explain why Snippet (a) is superior to Snippet (b): PROCEDURE exec_line_proc (line IN INTEGER) IS BEGIN IF line = 1 THEN exec_line1; END IF; IF line = 2 THEN exec_line2; END IF; IF line = 3 THEN exec_line3; END IF; ... IF line = 2045 THEN exec_line2045; END IF; END; PROCEDURE exec_line_proc (line IN INTEGER) IS BEGIN IF indx = 1 THEN exec_line1; ELSIF indx = 2 THEN exec_line2; ELSIF indx = 3 THEN exec_line3; ... ELSIF indx = 2045 THEN exec_line2045; END IF; END;
29-15.	Explain why Trigger (a) is better than Trigger (b): CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE OF salary, commission ON employee FOR EACH ROW WHEN (NVL (OLD.salary, -1) != NVL (NEW.salary, -1) OR NVL (OLD.commission, -1) != NVL (NEW.commission, -1)) BEGIN compensation_pkg.alert(old.emp_id); END; CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE ON employee FOR EACH ROW BEGIN IF NVL (old.salary, -1) != NVL (new.salary, -1) OR NVL (old.commission, -1) != NVL (new.commission, -1) THEN compensation_pkg.alert(old.emp_id); END IF; END;
29-16.	Improve the performance of the following code (assume that process_employee_history has been optimized): DECLARE CURSOR emp_cur IS SELECT last_name, TO_CHAR (SYSDATE, 'MM/DD/YYYY') today FROM employee; BEGIN FOR rec IN emp_cur LOOP IF LENGTH (rec.last_name) > 20 THEN rec.last_name := SUBSTR (rec.last_name, 20); END IF; process_employee_history ...