0

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
2

How about a function that returns ref cursor? I've used your QUERY 3 without any changes (line 7 onwards), encapsulated it into ref cursor function stuff. Have a look.

SQL> create or replace function f_rc (name_in in number)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for
  7    WITH
  8       dummy_links as (
  9             SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
 10             SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
 11             SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
 12             SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
 13             SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
 14             SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
 15             SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
 16             SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
 17             SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
 18             SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
 19       res AS (SELECT ID,
 20              in_node,
 21              out_node,
 22              weight,
 23              MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
 24              MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
 25              MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
 26              CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
 27                 ELSE 'out'
 28              END direction_from_orig_node,
 29              LEAST(in_node, out_node) node1,
 30              GREATEST(in_node, out_node) node2,
 31              row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
 32           FROM   dummy_links
 33           START WITH ID = name_in
 34           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
 35                    OR PRIOR in_node IN (in_node, out_node))
 36                    AND LEVEL <= 2)
 37    SELECT orig_id,
 38         COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
 39         nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
 40         COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
 41         nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
 42    FROM   res
 43    WHERE  rn = 1
 44    AND    ID != orig_id
 45    GROUP BY orig_id;
 46
 47    return l_rc;
 48  end f_rc;
 49  /

Function created.

SQL>
SQL>   select f_rc(1) from dual;

F_RC(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         1          4         35          0          0


SQL>
  • thank you!. Can you look at this question too/ Getting values from the cursor is the last missing piece of info for me. – Stepan Nov 13 '18 at 21:20
  • Please look at this follow up question. dba.stackexchange.com/questions/222469/… – Stepan Nov 13 '18 at 21:53
  • You're welcome; I posted an answer in the follow up question; have a look, please. – Littlefoot Nov 14 '18 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.