Native Compilation, CASE, and Dynamic Bulk Binding
Pages: 1, 2, 3, 4

About CASE statement syntax

The CASE statement begins with the keyword CASE. The keyword is followed by a selector. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

So, basically, you can use CASE statements with scalar values, like strings, dates, Booleans, intervals, and so on.

The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed.

The ELSE clause works similarly to the ELSE clause in an IF statement. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;

If the CASE statement selects the implicit ELSE clause, PL/SQL raises the predefined exception CASE_NOT_FOUND. So there's always a default action, even when you omit the ELSE clause.

The keywords END CASE terminate the CASE statement. These two keywords must be separated by a space.

Here's another example of a typical CASE statement:

CREATE OR REPLACE FUNCTION 
   grade_translator (grade_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   retval VARCHAR2(100);
BEGIN   
   CASE
      WHEN grade_in = 'A' 
         THEN retval := 'Excellent';
      WHEN grade_in = 'B' 
         THEN retval := 'Very Good';
      WHEN grade_in = 'C' 
         THEN retval := 'Good';
      WHEN grade_in = 'D' 
         THEN retval := 'Fair';
      WHEN grade_in = 'F' 
         THEN retval := 'Poor';
      ELSE retval := 'No such grade';
   END CASE;
   RETURN retval;
END;

The CASE Expression

The CASE expression is a fragment of a statement that returns a value. Its syntax is similar to that of a CASE statement with the following differences:

• Each WHEN clause does not end with a semicolon (it's all part of a single expression, whereas a semicolon indicates the end of a logical statement).

• The END clause does not include the CASE keyword. You simply END the expression.

• You don't perform assignments within the WHEN clauses. Instead, you simply provide the value that you want to be assigned after the CASE expression is evaluated.

Here's a rewrite of the previous grade translator using the expression syntax:

CREATE OR REPLACE FUNCTION 
   grade_translator (grade_in IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN   
   RETURN 
    CASE
      WHEN grade_in = 'A' 
         THEN 'Excellent'
      WHEN grade_in = 'B' 
         THEN 'Very Good'
      WHEN grade_in = 'C' 
         THEN 'Good'
      WHEN grade_in = 'D' 
         THEN 'Fair'
      WHEN grade_in = 'F' 
         THEN 'Poor'
      ELSE 'No such grade'
   END;
END;

Notice that, in this case, we don't assign the expression to a variable. We RETURN it directly from the function.

Searched CASE Statements and Expressions

With a "searched CASE" statement or expression, you don't provide a selector value for the CASE statement. Instead, the WHEN clauses contain search conditions that evaluate to a Boolean value. Use this form when you aren't checking simply for matching values, but you need to evaluate arbitrarily complex Boolean expressions.

Here are examples of searched CASE statements and expressions:

CASE
  WHEN n = 1  THEN Action1;
  WHEN n = 2  THEN Action2;
  WHEN n = 3  THEN Action3;
  WHEN ( n > 3 and n < 8 ) 
    THEN Action4through7;
  ELSE ActionOther;
END CASE;

and:

text := 
   CASE
     WHEN n = 1 THEN one
     WHEN n = 2 THEN two
     WHEN n = 3 THEN three
     WHEN ( n > 3 and n < 8 ) 
       THEN four_through_seven
     ELSE other
   END;

Example 4 offers one more example of a searched CASE expression, this time using the new Oracle 9i INTERVAL datatype to determine whether I'm too old or too young to be forced to work!

Example 4. Searched CASE expression with INTERVALs.

DECLARE -- Example of CASE searched expression
   cant_play_now BOOLEAN;
   how_young INTERVAL YEAR TO MONTH :=
      (SYSDATE - TO_DATE ('09-23-1958', 'MM-DD-YYYY')) YEAR TO MONTH;
   max_age CONSTANT INTERVAL YEAR TO MONTH := INTERVAL '16' YEAR;
   min_age CONSTANT INTERVAL YEAR TO MONTH := INTERVAL '70' YEAR;
BEGIN
   -- Notice: no semi-colons between WHEN clauses.
   cant_play_now :=
      CASE 
       WHEN how_young < min_age THEN FALSE
        WHEN how_young > max_age THEN FALSE
        ELSE TRUE
      END;
    
   IF cant_play_now 
   THEN
      must_go_to_work;
   END IF;
END;

Bulk Binding in Native Dynamic SQL

One of the most exciting new features in Oracle8i was the native dynamic SQL: an implementation of dynamic SQL that relies on two native statements (EXECUTE IMMEDIATE and OPEN FOR) to run dynamically constructed SQL statements at runtime. NDS is dramatically easier to use than DBMS_SQL and, in most cases, is noticeably faster. But NDS wasn't the only new feature for improving SQL performance and flexibility. Oracle also introduced "bulk binding" for DML and queries.

And never the twain could meet. In other words, we couldn't use bulk binding to run a dynamic SQL statement. In Oracle 9i, all that's changed. We can now take advantage of these two great features together. This section refreshes your knowledge of bulk binding and then shows how to use it with dynamic SQL, finishing up with an explanation of improved error handling for bulk-bind operations.

Suppose we need to write a program to populate elements of a PL/SQL collection from a SELECT query. Our first pre-Oracle8i inclination would be to write what you see in Example 5.

Example 5. Using a cursor FOR loop to populate a collection.

DECLARE
   TYPE employee_ids_t IS 
      TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;

   employee_ids   employee_ids_t;
   n              INTEGER        := 0;
BEGIN
   FOR j IN  (SELECT employee_id
                FROM employees
               WHERE salary < 3000)
   LOOP
      n :=  n + 1;
      employee_ids (n) := j.employee_id;
   END LOOP;
END;

Using this approach, each explicit row-by-row assignment of the collection element to the cursor component causes a context switch between the PL/SQL engine and the SQL engine resulting in performance overhead. With Oracle8i and above, we can move away from these context switches (and all that code) by performing a BULK COLLECTION, as shown here:

BEGIN
   SELECT employee_id
     BULK COLLECT INTO employee_ids
     FROM employees
    WHERE salary < 3000;
   ...
END;

Wow! That's quite a change in code volume. And you'll see substantial improvements in performance, as well, by minimizing the number of context switches required to execute the block.

But what if you want to execute this same query (and then process the rows) for one of any number of different employee tables, segregated by location?

In Oracle 9i, we can take the preceding query and transform it into a dynamic SQL query that will handle this additional complexity. There are many application implementation situations that require dynamic SQL. Native dynamic SQL (execute immediate and related constructs) is usually preferred over Dbms_Sql because it's easier to write and proofread and executes faster. However, pre-Oracle9i, only Dbms_Sql could be used for dynamic bulk binding. Oracle 9i introduces the following syntax for bulk binding in native dynamic SQL:

CREATE OR REPLACE PROCEDURE
   process_employees (loc_in IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE 
     'SELECT employee_id FROM '
     || loc_in || '_employees'
     BULK COLLECT INTO employee_ids;
   ...
END;

It's so nice to see the fairly arbitrary restrictions in the PL/SQL language falling away with each new release! Let's take a look at how you implement "in-binding" (binding variables into the dynamic SQL), "out-binding" (extracting values from the dynamic SQL), and error handling when using bulk operations on dynamic SQL statements.

Pages: 1, 2, 3, 4

Next Page