On 5/5/2014 5:15 PM, Booth Martin wrote:
Buck wrote:
I'd still write stored procedures to isolate
the DB layer from the UI layer. You'll
love result sets. (Seriously.)
Stored procedure? Result sets? My bet is that those terms do not mean
what I thought they meant.
A stored procedure is like a subroutine for the database. Imagine you
have an RPG program that uses embedded SQL. An example might help.
Say you're reading invoice history, and the invoice files have a header
and detail, and that you need to get the customer name and phone number
from the customer master file, and the product description and weight
from the item master file. That's probably a multiline JOIN. There's
nothing wrong with that, unless you're going to have your web developers
read this information. Now you need to explain which files have what
keys and whether to LEFT JOIN or not. Oh, and be sure to let them know
when you change any of those tables. Icky, right?
If you were to put that logic into an SP, the only thing the web folk
would need to know is that if they need invoice history, they CALL
INVOICE_HISTORY (:CustID); If you refactor your database, say breaking
the customer phone number into a separate table (home land line, home
mobile, work land line, work cell, work FAX, etc.) you don't need to
tell the web people about the new table; change the SP and they never
know the database changed under them.
And how does the SP return the data? Wouldn't invoice history be like
an array of invoices? Yes it would, and that array would look
suspiciously like a DDS subfile. It has column definitions - customer
ID, item ID, item description, etc. and a varying number of rows
depending on which customer we're looking at. In SQL terminology that's
called a result set. (There is another way to do that called a User
Defined Table Function; more research-y fun!) Virtually every
programming language that is capable of using SQL can use result sets.
SPs are part of the database even though they can (and usually do)
contain business logic. In fact, that's one of the best parts about
stored procedures: instead of forcing every consumer who FETCHes data to
include the business logic in their code, they can all CALL the SP and
the business logic is handled for them.
SPs on i have a feature that is absent on other platforms; it makes them
wicked powerful: We can write our SP logic using an HLL like RPG. This
is called an external stored procedure (vs an SQL stored procedure like
Chuck demonstrated). An external SP is registered to DB2 the same as an
SQL SP: CREATE PROCEDURE, and it is invoked the same way: CALL.
Is there a simple example somewhere that we can look at? Simple.
Something that demonstrates the process to which you are pointing? Did I
mention simple? (If you have ever looked at the examples on my web site
you realize I like simple and overly obvious examples.)
I'll try to update the wiki with some examples but I haven't the time at
the moment. I have only a skeleton at the moment:
http://wiki.midrange.com/index.php/Stored_procedure
Because SPs are part of modern databases, they are a feature of DB2 for
LUW, MySQL, SQL Server, Oracle and others. Tutorials like Youtube
videos for those platforms will have many commonalities with DB2.
Jon and Susan on RPG external SPs:
http://ibmsystemsmag.com/ibmi/developer/rpg/rpg_stored_procedures/
2006, but someone on a different list found value in it:
http://www.redbooks.ibm.com/abstracts/sg246503.html
--buck
midrange.com
