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