Wednesday, October 31, 2007

Code Style: Functions


Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).

Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.

That said, here is the syntax to create a function in Oracle:

FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
l_age_in_months INTEGER;
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );

SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;

debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );

RETURN l_age_in_months;

WHEN no_data_found THEN
debug( 'no data found' );
--here you need to decide what exactly you want to do. If this is
--used in a SQL statement then you probably don't want to halt
--processing, so returning NULL will work just fine. However, if
--this function is called by another function or procedure and you
--need this value to continue processing, you WOULD want to know
--that the value is not there and you would issue a RAISE after you've
--logged the error
END get_age_in_months;
show errors

The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.

In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.

  1. Instrument your code.

  2. Name the function something descriptive. There is a 30 character limit so don't be lazy.

  3. Name your functions something descriptive. I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.

  4. Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.

  5. If there are more than 1 input parameters, start at the next line.

  6. CREATE OR REPLACE goes on the top line, nothing else

  7. Use spaces liberally

  8. Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.

I tend to name variables thusly (I think I got that from Mr. Kyte):

  • p_ = parameters passed (though you could use i_ for in and o_ for out as well)

  • l_ = local variables

  • g_ = global variables


DomBrooks said...

>If this is used in a SQL statement then you probably don't want to halt processing, so returning NULL will work just fine

It's worth noting that if the function is called in a SQL statement, then that SQL statement will handle a no_data_found exception by returning null (9i at least)

Jeffrey Kemp said...

Generally, I prefer to name functions with a noun or noun phrase, and procedures with a verb or verb phrase. Therefore I've moved away from the "get_" prefix for function names. I prefer x := age_in_months(y); - the verb is unnecessary; it is obvious that we're calling a function to get a value.