Oracle Fundamentals - a little bit more

Since I wrote the last column on Oracle (What’s a database? And what is Oracle?) a number people have said to me that they actually found it useful! This is kind of the reason I write these things so I thought I might expand a bit further on what other things a database, and in particular Oracle, can do.

The previous column focused on storage and how databases like Oracle manage information to make it accessible to many users at once. Principally this is all you need a database to do and other products take care of other functions such as data manipulation.

Procedures & Functions

There could be cases, however, where you want data manipulation to be done within a database. For instance if you wanted to run an invoicing routine against your massive sales table you wouldn’t want to leave a client PC logged in running that routine you would want to start it off and walk away, or perhaps have it run automatically each night!

Many databases have the ability to run code for you within the database using, in Oracle’s case, either a function or a procedure. These two things are more or less the same with the only real difference being that one tells you how it did and the other doesn’t! In our example of invoicing a function could let you know it had finished and a procedure wouldn’t. Each have their place and it’s up to the programmer involved how they are implemented.

It is not that easy to explain why you might want to use a database procedure without an example so lets create a “pseudo-code” version and explain it’s use…

To generate invoices we could have to work out a number of factors before we actually bill someone. So in the case of a car rental it could be factors such as time rented, options taken, discounts applied, car rented. This could be something like:

Pseudo-Code:
1.Retrieve customer details from database
2.Retrieve car rentals from database
3.Retrieve discounts from database
4.Calculate the time elapsed from last invoice run
5.Generate invoice line from factors above

Pseudo-Code:
Procedure RunInvoicing is
  1.Retrieve customer details from database
  2.Retrieve car rentals from database
  3.Retrieve discounts from database
  4.Calculate the time elapsed from last invoice run
  5.Generate invoice line from factors above
End Procedure

The procedure, having been defined, is then stored as code within the database. So now to run all of those commands we type into and Oracle sql session:

SQL: Exec RunInvoicing;

That executes the procedure we have already stored in the database and runs all the commands in it that generate our invoices. There are some important additions to this which are much more about business logic and privacy than they are about programming:

• It is possible to restrict the viewing, editing and execution of a procedure to different logins increasing security and privacy
• It is possible to encrypt a procedure so that no-one can read the code stored within
• A procedure can be run internally by Oracle on a timer so it runs the same time every night if required (using DBMS_JOB)

A quick note on Packages; these are a collection of functions/procedures put inside a wrapper to make everyone’s administrative lives easier. It’s analogous to a having a car - yes there are lots of wires and switches under the hood but all you are shown is a steering wheel and some pedals a Package does the same and only shows you the steering wheel and pedals yet there are many procedures and functions that are actually running.

Triggers

You might not want to run a procedure or function each night and you might want something automatic to happen each time a specific buisness event happens. For instance you might want to send a welcome email to every new customer once their account is created. A Trigger on the customer table can be setup to “fire” each time a new customer is created calling a procedure or function which, in turn, emails the customer.

Triggers can be used to a variety of things from deletion, update or addition events. The database also protects against triggers that loop (call each other) and will let you know with a lovely error message :)

Views

A view is much like a shortcut in that it shortens a commonly used request to a similar term. If you worked in a company and the CEO wanted the whole company he or she might say “get me the finance department, the development department, the ground staff, reception, the motor pool and IT which is the whole company” (wierd company). You now know what compromises the entire company so if he or she says “get me the whole company” you know what departments to go a get. So in pseudo code we want to get all the Jill’s in the company:

Pseduo-SQL: select all the Jill's from the finance department, the development department, the ground staff, reception, the motor pool and IT

Which is dull, especially when you now need to run the same command for Alan, David, Brian and Samantha. So we create a view of that instead:

Pseduo-SQL: Create View AllCompany as select everyone  from the finance department, the development department, the ground staff, reception, the motor pool and IT

So now when we want all the Jill’s we can do:

Pseudo-SQL: Select all the Jill's from AllCompany

Much easier :)

The last word

Oracle has many MANY more functions than just this and they are adding new features all the time. These are some pretty basic functions that lots of other database products also incorporate and are extremely important for database programming. Some software houses like their products to be database agnostic but that removes the option to leverage some of the great functionality built into the database products.

When developing I made extensive use of functions and procedures to do simple things like check a login or apply a VAT calculation all of which can be a bit sensitive. Understanding what those things mean will make any conversation with a developer a lot easier to have and help you make good decisions.