How can I run a query with iteration or as a function in SQL Plus/SQL-PL?

New to SQL Plus and a little confused on how iteration and functions work with it. Hoping someone could show me how it would be done with the following script:

set embedded on
set echo off
set colsep ,
set pagesize 0
set linesize 1000
set numwidth 10
set headsep off
spool <my_file_path>.csv;
select * from (select /*csv*/ row_number() over (order by t.objectid) as id, t.objectid as transformer_id, t.gps_x as lon, t.gps_y as lat, s.electricmeternumber, s.serviceindex, s.accountnumber,
case 
     when t.phasedesignation = 0 then 'unknown'
     when t.phasedesignation = 1 then 'C'
     when t.phasedesignation = 2 then 'B'
     when t.phasedesignation = 3 then 'BC'
     when t.phasedesignation = 4 then 'A'
     when t.phasedesignation = 5 then 'AC'
     when t.phasedesignation = 6 then 'AB'
     when t.phasedesignation = 7 then 'ABC'
     end as phase_designation
from mvtransformer t, vservicepoint_meterinfo s 
where t.objectid = s.transformerobjectid) where id > some value x and id <= some value x;
spool off;

So what I want to do is repeat this script multiple times, varying x and the file path each time. What would be the best way to go about this in SQL Plus?

Thanks!

Solution

You could declare some substitution variables, place your query in a script of its own, and then call that script multiple times having populated the relevant variables.

E.g. something like:

Script to call multiple times:

spool &&filename..csv;

SELECT *
FROM   (SELECT /*csv*/
               row_number() over(ORDER BY t.objectid) AS id,
               t.objectid AS transformer_id,
               t.gps_x AS lon,
               t.gps_y AS lat,
               s.electricmeternumber,
               s.serviceindex,
               s.accountnumber,
               CASE
                 WHEN t.phasedesignation = 0 THEN 'unknown'
                 WHEN t.phasedesignation = 1 THEN 'C'
                 WHEN t.phasedesignation = 2 THEN 'B'
                 WHEN t.phasedesignation = 3 THEN 'BC'
                 WHEN t.phasedesignation = 4 THEN 'A'
                 WHEN t.phasedesignation = 5 THEN 'AC'
                 WHEN t.phasedesignation = 6 THEN 'AB'
                 WHEN t.phasedesignation = 7 THEN 'ABC'
               END  AS phase_designation
              FROM   mvtransformer           t,
                     vservicepoint_meterinfo s
              WHERE  t.objectid = s.transformerobjectid)
WHERE  id > &&val1
AND    id <= &&val2;
spool off;

Calling Script

set embedded on
set echo off
set colsep ,
set pagesize 0
set linesize 1000
set numwidth 10
set headsep off

define val1=10
define val2=20
define filename=your_filename1

@script_to_loop_over

define val1=30
define val2=40
define filename=your_filename2

@script_to_loop_over

...

N.B. . is used to indicate the end of the substitution variable name in SQL*Plus. It looks like you're using SQL Developer/SQLCli, which may or may not treat this the same as SQL*Plus. If it doesn't you'd need to use spool &&filename.csv instead.