Further explanation
October 26, 2009 - 9am Central time zone
Reviewer: Analyst from Melbourne
We are trying to efficiently pass result sets between functions. Perhaps ref cursor was the wrong
concept to which to refer.
We have the following code conceptually:
Procedure A (inputParamter)
{
(several inline views)
Return a set called foo
}
Procedure B (ResultSet foo)
{
select ...
from foo
... (more SQL code)
Return result set called foo2
}
The result set foo2 is returned to the user.
Now, certainly we could create a single procedure that contains the SQL code from Procedure A and
Procedure B. However, this is the wrong approach - of course. So, we would like to have one
function that performs the work of Procedure A and returns a set, and another function that
performs the work of Procedure B. The latter function would accept at input, the set produced from
Procedure A, and return a result set.
I would like to understand the best way to implement this solution.
Followup October 26, 2009 - 2pm Central time zone:
what is your concept of a result set then? give me a pared down snippet of actual code to review. I don't know what you are doing.
I don't know why A would be needed to procedure a set. If the set is the result of a sql query - A is not necessary or desired, so therefore, I think A must include procedural code and builds a collection - an array - but I don't really know what you mean.
RE: single procedure that contains the SQL code from Procedure A and Procedure B.
October 26, 2009 - 12pm Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
Why not a single query that contains the SQL from Procedure A and Procedure B?
That's the mantra!
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185#6758131003460
56247
A rather involved process
October 26, 2009 - 2pm Central time zone
Reviewer: Analyst from Melbourne
""Why not a single query that contains the SQL from Procedure A and Procedure B?
That's the mantra! "
Of course. Where practical, all SQL code should be stored in one routine. However, my question is
from the perspective of code maintenance.
If I were to amalgamate the code from all of the associated procedures that comprise the process in
question, the procedure would contain no less that 5 or so pages of SQL code. There is NO
procedural code. Now, I would rather not have a procedure of five pages.
Perhaps I was wrong - happy to admit so - in my use of the term procedure. Simply, I have a
process. This process involves about 5 pages of code. The process can be said to comprise three key
steps. I would like these steps to be maintained, ideally, as three routines. I would like to
follow the same approach that you would in modular coding.
Followup October 26, 2009 - 3pm Central time zone:
... the procedure would contain no less that 5 or
so pages of SQL code. ...
that is dandy. comment it well (and buy a bigger monitor - I have a really tall one I use)
You can also use views if approach to "modularize"
or with subquery factoring
with procedureA
as
(select ......),
procedureB
as
(select ..... ),
procedureC
as
(select .... ),
select from procedurea, procedureb, procedurec
and stick a form feed in a comment between each with.
That's a tall order...
October 27, 2009 - 7am Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
While SQL is almost second nature to me, and my mother "tongue" of FORTRAN is but a wisp of memory, most of my annoyance is due not to SQL itself, but the number of columns on table.
Tall tables require a "tall monitor", if you're adhering to the convention of one-column-per-line, e.g.
SELECT ASL
, ASL_DESCRIPTION
, ADMITTED_STATUS
, STATE_CD
, COASTAL_PROXIMITY_TIER_NBR
, period AS CURRENT_PERIOD
, ALPHA_SORT
, COASTAL_ASSESSABILITY_CD
, measure_name AS MEASUREMENT_NAME
, total_measure_amt AS TOTAL_MEASURE
, assessed_measure_amt AS ASSESSED_MEASURE
, nonassessed_measure_amt AS NONASD_MEASURE
FROM COASTAL_EXP_HIST
WHERE period IN
(ADD_MONTHS (TRUNC (SYSDATE, 'q'), -1)
, ADD_MONTHS (TRUNC (SYSDATE, 'q'), -13)
)
As Tom suggests, modularizing using "WITH subqueries" where possible, and comments where necessary, is the best approach I've found.
For example, here's just one section of a query I wrote to populate a reporting/summary table with 105 columns:
-- ****************************************************
-- Prior Current Month and Current Growth
-- ****************************************************
, SUM ( CASE WHEN Periods.CURRENT_FLAG = '12'
THEN exPERience.EARNED_PREMIUM
END ) AS pcurr_EP
, SUM ( CASE WHEN Periods.CURRENT_FLAG = '12'
THEN exPERience.INCURRED_LOSS
+ exPERience.EARNED_PREMIUM
* (Agents.OVERHEAD_PCT / 100 )
END ) AS pcurr_IL -- Incurred_Loss_with_ULAE
, SUM ( CASE WHEN exPERience.NEW_RENEWAL_CD = 0
AND Periods.CURRENT_FLAG = '12'
THEN exPERience.NET_WRITTEN
ELSE 0
END ) AS pCurr_New_WP
, SUM ( CASE WHEN exPERience.NEW_RENEWAL_CD = 0
AND Periods.CURRENT_FLAG = 'Current'
THEN exPERience.NET_WRITTEN
END )
/ NULLIF (
SUM ( CASE WHEN exPERience.NEW_RENEWAL_CD = 0
AND Periods.CURRENT_FLAG = '12'
THEN exPERience.NET_WRITTEN
END )
, 0 )
- 1 AS curr_new_WP_growth
, SUM ( CASE WHEN ( Periods.CURRENT_FLAG = '12' )
THEN exPERience.NET_WRITTEN
ELSE 0
END ) AS pCurr_total_WP
, SUM ( CASE WHEN Periods.CURRENT_FLAG = 'Current'
THEN exPERience.NET_WRITTEN
END )
/ NULLIF (
SUM ( CASE WHEN Periods.CURRENT_FLAG = '12'
THEN exPERience.NET_WRITTEN
END )
, 0 )
- 1 AS curr_total_WP_growth
105 columns? Yep, I wrote about it here:
http://it.toolbox.com/blogs/data-ruminations/the-absolutely-stunningly-beautiful-table-25899
Each section of SQL is "cohesive" (in the computer science sense)
http://en.wikipedia.org/wiki/Cohesion_%28computer_science%29
and still fits on an ordinary monitor page.
|