Intermediate

29-13.

What is the most efficient integer datatype for numbers in the 231 range?

  1. NUMBER

  2. BINARY_INTEGER

  3. PLS_INTEGER

  4. INT

  5. POSITIVE

29-14.

Explain why Snippet (a) is superior to Snippet (b):

  1. 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;
  2. 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):

  1. 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;
  2. 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 ...

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.