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

FBI and Others...

I did have a job when I got back into town. Unfortunately I let the news of the FBI raid ruin the remainder of my vacation.

There still isn't a whole lot of information available, most of it is just speculation of course, but it seems now everyone is piling on.

Class Action Lawsuit Against WellCare Health Plans
WellCare Says SEC Joins Probe

On Monday of last week the stock price hit an all time high of over 128 dollars. It closed at $28.62 today. Um, yikes. We had purchased a few shares at around the $100 mark and lost about $200 dollars. If there's a bright side to all this it's that we can actually afford to buy more than a couple of shares now...

Looking for an Oracle Developer?

So I am on vacation (well, I wouldn't necessarily call being a pack mule vacation) at Disney World this week.

Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.

FBI raid shutters Medicare insurer

FBI Raids Tampa WellCare

I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.

I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...

So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.

Code Style: Package Specification

Package Specification

CREATE OR REPLACE
PACKAGE p_foo
AS
  FUNCTION get_something
    ( p_id IN NUMBER,
      p_thing_im_looking_for VARCHAR2 ) RETURN NUMBER;
  PROCEDURE do_something( p_id IN NUMBER );
  PROCEDURE insert_something
    ( p_first_name IN PEOPLE_TAB.FIRST_NAME%TYPE,
      p_last_name IN PEOPLE_TAB.LAST_NAME%TYPE,
      p_email_address IN PEOPLE_TAB.EMAIL_ADDRESS%TYPE,
      p_middle_initial IN PEOPLE_TAB.MIDDLE_INITIAL%TYPE DEFAULT NULL,
      p_prefix IN PEOPLE_TAB.PREFIX%TYPE DEFAULT NULL );
  PROCEDURE update_something
    ( p_id IN NUMBER,
      p_first_name IN VARCHAR2,
      p_last_name IN VARCHAR2 );
  PROCEDURE delete_something( p_id IN NUMBER );
END p_foo;
/
show errors

1. Name your package something descriptive. There is a 30 character limit so don't be lazy.


2. Name your procedures and functions something descriptive. There is a 30 character limit so don't be lazy. UPDATE this or INSERT that. Try to give a small clue as to what it does. If it's a function, I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.


3. 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.


4. Use the package name after the END statement.


5. If there are more than 1 input parameter, 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.


9. Declare parameters using %TYPE. If the parameter doesn't map to a table column declare SUBTYPEs somewhere.

Code Style: Index

My ongoing contribution to...pretty much myself. Perhaps you can find this useful, or amusing, whichever.

• General Principles


• Tables


• Functions


• Package Specification

Code Style: Tables

Tables are easy.

CREATE TABLE t
(
  col1 NUMBER(10,0)
    CONSTRAINT  pk_col1 PRIMARY KEY,
  col2 VARCHAR2(32)
    CONSTRAINT nn_col2_t NOT NULL
    CONSTRAINT uq_col2_t UNIQUE,
  col3 VARCHAR2(400),
  col4 VARCHAR2(1) DEFAULT 'N'
    CONSTRAINT ck_yorn_col4_t CHECK ( col4 IN ( 'Y', 'N' ) )
    CONSTRAINT nn_col4_t NOT NULL
);

Remember to always name your constraints. While I am at, use constraints as much as humanly possible, at least in your OLTP systems. You'll be able to reduce the amount of code you need to write and actually let the database do it's job. I'd much rather let the database do it than rely on code to maintain my data integrity.

For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.

For child tables:

CREATE TABLE s
(
  col5 NUMBER(10,0)
    CONSTRAINT pk_col5 PRIMARY KEY,
  col1
    CONSTRAINT fk_col1_s REFERENCES t( col1 )
    CONSTRAINT nn_col1_s NOT NULL,
  col6 VARCHAR2(30)
);

For Foreign Key constraints, you do not have to declare the type as it will be inherited from the parent table.

This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).

As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).

To recap:

• Use constraints as much as possible


• Always name your constraints


• Work with your DBA for table options


• Always name your constraints

Code Style: General Principles

I have always wanted to put together my personal code style guide. Now that I have a blog, I can do so rather easily.

This will be the first among many that details my approach to coding style. Yes, it is very particular to me. No, I don't expect anyone in their right mind to follow it. Though perhaps someone can use it as a general guideline.

I am fairly fanatical about style.

Parenthesis go on separate lines, always (I'll eventually contradict myself I'm sure). None of this my line starts with a comma crap. My only concession is that it makes commenting out lines easier. Otherwise, I can't stand it. It's ugly to me and I don't like ugly.

Tabs = 2 spaces

CREATE OR REPLACE is the top line followed by the particular object on the second line (I don't know why, it's my style though).

CREATE OR REPLACE
PACKAGE p_package
IS
...

Always put "show errors" at the bottom of procedure/function/package scripts.

Always end your procedure or function with the name after END.

CREATE OR REPLACE
PACKAGE p_package
IS
...
END p_package;
/
show errors

Align SELECT statements left, not right:

Evil

SELECT blah, t.id, s.x
  FROM t, s
 WHERE t.id = s.id
   AND t.x = s.x; 

Correct

SELECT 
  blah,
  t.id,
  s.x
FROM 
  t, 
  s
WHERE t.id = s.id
  AND t.x = s.x; 

I believe but can't confirm that Toad Formatter is the main perpetrator of this travesty.

Instrumentation. Use lots of it. Home grown or Mr. Kyte's debug routine. You can also use the Oracle supplied package DBMS_APPLICATION_INFO. I have gotten into the habit of using set_session_longops and it helps tremendously in monitoring those pesky long running datawarehouse programs.

Be liberal in your use of spaces: ADD_MONTHS( TRUNC( SYSDATE, 'MONTH' ), 2 )
Capitalize keywords like SELECT, INSERT, UPDATE and DELETE.
Standard functions should also be capitalized.

I'm sure I've got more and I'll add them here as time goes on.

To Test or Not to Test?

OK, I'll stop with the silly Shakespearean silliness.

Why do we test everything?

I'm talking about one line of code like this:

Before

WHERE line_of_business IN ( 'XYZ', 'ABC' )

After

WHERE line_of_business IN ( 'XYZ', 'ABC', '123' )

(this was the cause of my first screwup)

I added an extra filter on the IN list. Does that really require the complete testing of the entire (say 8 hour) process?

I've been in IT for 5+ years now and I understand the importance of testing, but this seems a tad ridiculous to me.

Is there a better way? Unit testing performed by another developer? Visual inspection by the DBA or group of peers?