Reply from Howler_Fish on Apr 17 at 7:33 PM If the variables are singular, then the state record would be the best place to put them. That way the database sees the SQL as UPDATE TABLE SET FIELD = :1 WHERE KEY_1 = :2 AND KEY_2 = :3
instead of
UDPATE TABLE SET FIELD = (SELECT FIRST_VAR FROM MY_TEMP WHERE MY_TEMP.PROCESS_INSTANCE = :1) WHERE KEY_1 = (SELECT SECOND_VAR FROM MY_TEMP WHERE MY_TEMP.PROCESS_INSTANCE = :1
Temp tables are there to emulate arrays / cubes.
If the stored procedure involves lots of branching statements ( IF ELSE / EVALUATE / CASE) then you might want to consider moving into PeopleCode.within the AppEngine.
Whilst AppEngine's can do IF / ELSE / EVALUATE via SQL and dynamic section calls, PeopleCode does it in a format that is far easier to read. Just try to avoid a nested action structure like
Do-Select / Do-While
PeopleCode
Move the loop inside PeopleCode so that you are only calling the PeopleCode action once.
| | | ---------------Original Message---------------
From: Ken Duncan
Sent: Tuesday, April 17, 2012 5:29 PM
Subject: Stored proc -> App Engine and Temp Tables: Right Strategy?
I am converting a stored procedure with multiple parts and variables defined to an Application Engine program. It looks to me like a good approach would be to make the parts of the stored procedure each a SQL step and the variable definitions should be turned into temporary tables. Does this sound like a good approach? Thanks.
Ken |
| Reply to this email to post your response. __.____._ | In the Spotlight Become a blogger at Toolbox.com and share your expertise with the community. Start today.   _.____.__ |
No comments:
Post a Comment