I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different functions with select in_count from /* query 3*/, select in_sum from /* query 3*/, etc. Even though query 3 returns all necessary fields. That I combine in one statement. This is redundant and I should be able to return in_count, in_sum, out_count, out_sum from a single function.
I have a query that returns nearby segments count and weight for a given hardcoded segment (see query 3). I want to make a function f(id) that executes query 3 for id from the argument. So far I was able to create four functions, each returning A FIELD (see query 1). Query 2 can then call these our functions to get a table containing rows of stats for each segment ID. I don't like several things in this solution: (a) I execute essentially the same code four times to return four numbers. I wish I can execute code once and return a tuple (int, int, int, int). (b) Any discussion on user defined functions starts with "pointers are bad". Here I seem to use pointers. Question: What is the right way to create a table produced by query 2? I suspect Oracle allows to return type row(int,int,int,int), but what would be the syntax? Where can I find examples of user defined functions? Oracle docs only show how to return number or binary integer (what is that?) in their docs. https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm#i2153260
--(Query 1) creates a function
CREATE OR REPLACE Function dummy_function ( name_in IN number )
RETURN number
IS
cnumber number;
cursor c1 is
select rt.in_count from ( --also do rt.in_sum, rt.out_count, rt.out_sum as three additional functions
--COMPLEX_QUERY
) rt ;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 0;
end if;
close c1;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
-- (Query 2)
SELECT id, dummy_function(t.id) AS func_in , dummy_function1(t.id) AS func_in_sum, dummy_function2(t.id) AS func_out, dummy_function3(t.id) AS func_out_sum , t.* FROM dummy_links t;
-- (Query 3) -- COMPLEX_QUERY: -- note START WITH ID = name_in: here name_in is an argument (id=in range 1..10 works)
WITH
dummy_links 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 dummy_links
START WITH ID = name_in
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
