How to make a function from a concrete SELECT statement in Oracle SQL? How to replace a concrete value with a variable in Oracle SQL? I have

 select 5, min(id) from my_table where id &gt; 5 --AND ... complex condition
 UNION ALL     
 select 6, min(id) from my_table where id &gt; 6  --AND ... complex condition
  UNION ALL
  ....  

 | 5 | 6 |
 | 6 | 8 |
 ...


How to wrap it in a function executing pseudocode below?

for ( $i in ( select id from my_table)){
    UNION ALL
    select $i, min(id) from my_table where id &gt; $i  --AND ...
 }


To make it clear, I am looking for a general method to turn a select with hard wired values into a function that accepts variables. Note the --AND part.
This is NOT a question about using lead and over but about replacing values with variables.



I am trying to replace START WITH ID=10 with a variable in this query.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = 10
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL &lt;= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;


Apparently I can use this template, but do i have to use a pointer for this?

--creates a function
CREATE OR REPLACE Function dummy_function   ( name_in IN number )
   RETURN number
IS
   cnumber number;
   cursor c1 is
   SELECT weight FROM dummy_links WHERE ID = name_in;
BEGIN
   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;
   close c1;
RETURN cnumber+1; --to have some diversity we add 1

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

--calling a function
SELECT id, weight, dummy_function(id) AS weight_plus_one FROM dummy_links;
If I undferstand correctly, all you need is a bind variable.

SQL&gt; variable sw number
SQL&gt; exec :sw := 10;

PL/SQL procedure successfully completed.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = :sw
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL &lt;= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
        10          3         15          2         27


Then change the value of the variable to whatever you need:

SQL&gt; exec :sw := 9;

PL/SQL procedure successfully completed.

SQL&gt; /

no rows selected

SQL&gt; exec :sw := 8;

PL/SQL procedure successfully completed.

SQL&gt; /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         8          1         12          1         15

SQL&gt; exec :sw := 3;

PL/SQL procedure successfully completed.

SQL&gt; /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         3          1         33          3         30