|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
One way to do a FOR Loop is by using a separate recursive script (a script that calls itself), in which the recursive call at the end of the script is bypassed at the end of the loop using a generated begin-comment-block marker as seen in the Pseudo-IF description. In our payroll voucher script, we wanted to be able to enter a variable number of adjustment periods, prompting for year, payroll ID, payroll number, sequence number, and optional SSN's for each adjustment period entered, and build a character string list of all of those periods entered to match against. The resulting recursive script, payroll_voucher_adjs.sql, prompts for the year first, and, if no year is entered, skips the rest of the script. If a year is entered, the remaining prompts are processed, the entered values are concatenated to the list, and the script is called again. The relevant code snippets in the script to do this recursion are shown below. Be aware that there is a limit to the number of recursions that can be done, depending on the size of the internal call stack maintained by Oracle, so, you probably couldn't use this technique in an application that required looping thousands of times (you would probably use an unnamed PL/SQL block for that, but those can't contain "accept" statements - see Sequential Numbering of Records).
-- Script: payroll_voucher_adjs.sql
...
--
-- Get the year for the next set of adjustments, or blank if no more to enter.
--
accept adjyear char prompt 'Enter adjustment year for &adjnext set of adjustments, if any: '
--
-- If there are no more adjustments (year is blank), execute a begin-comment-
-- block marker to bypass the rest of this script.
--
spool payvoudo.sql
select decode('&adjyear','','/*','') from dual;
spool off
@payvoudo.sql
... Other prompts and processing go here ...
--
-- Rerun this script to get the next adjustment set. Note: This rerun and
-- the commands above will be bypassed if no adjustment year was entered.
--
@payroll_voucher_adjs.sql
--
-- Define the end-comment-block marker. It is in a commented line to prevent
-- error messages about unmatched comment block markers, but it will be seen
-- and matched against if Oracle is looking for the end of the comment block.
--
-- */
Then, in the main payroll voucher script, we just initialize the variable containing the list of adjustment periods and call the recursive script:
define adjs=';'
@payroll_voucher_adjs.sql
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
