Need a bit of help with function basics.. [message #357531] |
Wed, 05 November 2008 15:43  |
Meeples
Messages: 2 Registered: November 2008
|
Junior Member |
|
|
I'm quite new to working with PL/SQL, and am having issues crafting up a certain function here.
This function's intent is to compare rows in one table with a couple of others, checking for rows that match certain criteria. It is then meant to return the number of these rows found.
As noted, I'm quite new, as in 'this is my first function'. I had figured on using a SELECT INTO to gather up the criteria-fitting rows into a new table, and then COUNT that, but as SELECT INTO with PL/SQL functions can only take a single row, I cannot see how to get that to work.
Can someone offer suggestions to me here as to how I should go about properly doing this? Thank you!
|
|
|
|
|
Re: Need a bit of help with function basics.. [message #357744 is a reply to message #357531] |
Thu, 06 November 2008 07:49  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Glad to help. Now that you see how PL/SQL can do what you want I would add the following:
PL/SQL cursors of any form process the data one row at a time. TOM KYTE of ASKTOMHOME fame coined the phrase to desrbie row by row processing as "SLOW BY SLOW" processing. And it is. You are always better trying to find an all sql solution to your problem.
I would suggest that you first write the PL/SQL solution to get what you want. This will give you a working product that lets you learn plsql, and from which results can be compared to the ALL SQL alternative.
Then you should think hard about it and craft an all sql solution to your problem. This does several things:
Quote: | 1) if both alternatives give the same answer then they are likely both correct.
2) you will see that it is no more difficult to write a sql only solution as compared to a pl/sql solution.
3) if the datasets are large enought, you will see how much faster a sql only solution can run.
|
Good luck, Kevin
|
|
|