Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

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,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page