Code Style: Functions

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:

CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
  l_age_in_months INTEGER;
BEGIN
  --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;

EXCEPTION
  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
    RETURN NULL;
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