Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:
29-13.	(c). The PLS_INTEGER datatype utilizes the machine’s native arithmetic functions.
Q:
29-14.	Snippet (b) is faster because it exits as soon as the condition is met. In snippet (a), all 2045 conditions are always checked.
Q:
29-15.	Trigger (a) is better because the WHEN and UPDATE OF clauses cause it to fire only when the desired conditions are met; Trigger (b) fires regardless of what happens. For example, suppose you update on the dept columns. Trigger (a) doesn’t do anything; Trigger (b) still performs the test for each row.
Q:
29-16.	One possible way to improve the code is to avoid repeating unnecessary calculations inside the loop: DECLARE v_today CONSTANT VARCHAR2(10) := TO_CHAR (SYSDATE, 'MM/DD/YYYY'); CURSOR emp_cur IS SELECT SUBSTR (last_name, 1, 20) last_name FROM employee; BEGIN FOR rec IN emp_cur LOOP process_employee_history (rec.last_name, v_today); END LOOP; END;
Q:
29-17.	One of the best ways to simplify maintenance and minimize the number of times a SQL statement must be parsed is to replace literals with bind variables. You can almost always improve a cursor simply by replacing hardcoded values with bind variables: CURSOR name_cur (dept IN INTEGER) IS SELECT last_name FROM employee WHERE department_id = dept; BEGIN OPEN marketing_cur(20);
Q:
29-18.	You can get a performance boost by replacing repetitive, cursor-based PL/SQL loops with “pure” SQL statements: UPDATE emp SET sal = sal * 1.01;
Q:
29-19.	Correlated subqueries and complex multiple joins can result in excessive ...