Oracle
Oracle PL SQl 中函式的自定義返回類型
我有一個查詢，我想把它包裝成一個函式。目前查詢返回
in_count,in_sum,out_count,out_sum. 我只知道如何返回“原始類型”。所以目前我有四個不同的函式，帶select in_count from /* query 3*/,select in_sum from /* query 3*/等。即使查詢 3 返回所有必要的欄位。我結合在一個聲明中。這是多餘的，我應該能夠從單個函式返回in_count,in_sum,out_count, 。out_sum我有一個查詢，它返回給定硬編碼段的附近段數和權重（參見查詢 3）。我想創建一個函式 f(id) 從參數執行查詢 3 的 id 。到目前為止，我能夠創建四個函式，每個函式都返回一個 FIELD（參見查詢 1）。然後查詢 2 可以呼叫這些我們的函式來獲取包含每個段 ID 的統計數據行的表。我不喜歡這個解決方案中的幾件事： (a) 我執行四次基本相同的程式碼以返回四個數字。我希望我可以執行一次程式碼並返回一個元組（int，int，int，int）。(b) 任何關於使用者定義函式的討論都是從“指針不好”開始的。在這裡，我似乎使用了指針。問題：創建查詢 2 生成的表的正確方法是什麼？我懷疑 Oracle 允許返回類型 row(int,int,int,int), 但是語法是什麼？在哪裡可以找到使用者定義函式的範例？Oracle 文件僅在其文件中顯示如何返回數字或二進制整數（那是什麼？）。 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
返回參考游標的函式怎麼樣？我已經使用您的
QUERY 3沒有任何更改（從第 7 行開始），將其封裝到ref 游標函式的東西中。看一看。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>