How to make a function from a concrete SELECT statement in Oracle SQL?

Question

How to replace a concrete value with a variable in Oracle SQL? I have

 select 5, min(id) from my_table where id > 5 --AND ... complex condition
 UNION ALL 
 select 6, min(id) from my_table where id > 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 > $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 <= 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;

• Stepan: @mustaccio Actual problem is stated elsewhere: dba.stackexchange.com/questions/221945/… . I got an answer for a concrete case, but can't figure out a way to select across all rows

Answer 1

If I undferstand correctly, all you need is a bind variable.

SQL> variable sw number
SQL> 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 <= 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> exec :sw := 9;
PL/SQL procedure successfully completed.
SQL> /
no rows selected
SQL> exec :sw := 8;
PL/SQL procedure successfully completed.
SQL> /
 ORIG_ID IN_COUNT IN_SUM OUT_COUNT OUT_SUM
---------- ---------- ---------- ---------- ----------
 8 1 12 1 15
SQL> exec :sw := 3;
PL/SQL procedure successfully completed.
SQL> /
 ORIG_ID IN_COUNT IN_SUM OUT_COUNT OUT_SUM
---------- ---------- ---------- ---------- ----------
 3 1 33 3 30

• Stepan: Almost. Now I need to glue exec :sw := 3; into select t.*, blob.in_count,blob.in_sum from t, blob where blob.id = t.id where 'blob' is the fing starting at WITH t AS and ending at GROUP BY orig_id; I need a table of rows for each id. Function might work better.