Oracle PL/SQL Best Practices

Name

SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.

Synopsis

Always put your single-row query inside a function, and then call the function to return the information you need (whether it’s a scalar value, an entire record, or even a collection) through the RETURN clause.

Example

Instead of writing code like this:

BEGIN
   SELECT title INTO l_title   -- HARD-CODED
     FROM book                 -- QUERY...
    WHERE isbn =isbn_in;       -- BAD IDEA!

you should create a function, ideally within a “table encapsulation package”:

PACKAGE te_book
IS
   FUNCTION title (isbn_in IN book.isbn%TYPE)
      RETURN book.title%TYPE;

Now your application code looks like this:

BEGIN
   l_title := te_book.title (isbn_in);

Benefits

Optimal performance: The query is written once, presumably by the developer who best knows how to write it. Since there is a single physical representation of the query in code, the parsed version of the cursor is cached and used repeatedly.

Easy maintenance: If you need to change the query, you only have to do it in one place.

Consistent error handling: Individual developers don’t have to remember to write handlers for NO_DATA_FOUND and TOO_MANY_ROWS.

Challenges

Discipline is required in a multi-person team environment to ensure that the team has at least one person overseeing this type of encapsulation and that the whole team adheres to this standard.

There will be a larger volume of code to write and manage (your DBA must size the System Global Area accordingly). Explore the possibilities ...