如何创建带参数的 SQL 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9708656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I create a SQL function with a parameter?
提问by antonpug
The following code returns one field, a string, based on the input where it says "28".
以下代码根据输入“28”返回一个字段，一个字符串。
SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
FROM (
SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
FROM (
SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
WHERE es.EVENT_ID = 28
)
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;
How can I create a SQL function out of this so that I can pass in whatever number (instead of 28) and the function would return whatever the result of that select turns out to be?
如何从中创建一个 SQL 函数，以便我可以传入任何数字（而不是 28），并且该函数将返回该选择的结果如何？
I tried creating one but I keep getting compilation errors.
我尝试创建一个，但我不断收到编译错误。
Current SQL to create function
创建函数的当前 SQL
create or replace function "DT_SERVICE_STRING" (id in VARCHAR2)
return VARCHAR2 is
begin
DECLARE
result VARCHAR(200);
SELECT data.id, LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
INTO result
FROM (
SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
FROM (
SELECT es.EVENT_ID as id, s.SERVICE_NAME as name FROM DT_SERVICES s
JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
WHERE es.EVENT_ID = id
)
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;
return result;
end;?
Error:
Compilation failed,line 7 (15:22:21)
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior
错误：
编译失败，第 7 行 (15:22:21) PLS-00103：在预期以下情况之一时遇到符号“SELECT”：begin function pragma procedure subtype type current cursor delete exists before
回答by Justin Cave
Assuming that you want a PL/SQL function that is callable from a SQL statement (you cannot have a function defined in SQL), it sounds like you want something like
假设您想要一个可从 SQL 语句调用的 PL/SQL 函数（您不能在 SQL 中定义函数），听起来您想要类似
CREATE OR REPLACE FUNCTION get_conc_names( p_event_id IN dt_event_service.event_id%type )
RETURN VARCHAR2
IS
l_conc_names VARCHAR2(32676);
-- You may want a smaller variable if you know the result will be smaller
BEGIN
SELECT LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
INTO l_conc_names
FROM (
SELECT id, name, ROW_NUMBER() OVER (order by name) rownumber, COUNT(*) OVER () cnt
FROM (SELECT es.EVENT_ID as id, s.SERVICE_NAME as name
FROM DT_SERVICES s
JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
WHERE es.EVENT_ID = p_event_id )
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;
RETURN l_conc_names;
END;
Based on the code you just posted, it appears that you just need to get rid of the DECLAREand to move the declaration of the local variable resultbefore the BEGINand after the IS.
根据您刚刚发布的代码，看来你只需要摆脱的DECLARE和移动的局部变量的声明result之前BEGIN和之后IS。
回答by Beverly
I think the analytic functions need to be inside the inner inline view and then the outer inline view can select them--that's how I've always done it. Try this instead:
我认为分析函数需要位于内部内联视图中，然后外部内联视图可以选择它们——我一直都是这样做的。试试这个：
CREATE OR REPLACE FUNCTION get_conc_names( p_event_id IN dt_event_service.event_id%type )
RETURN VARCHAR2
IS
l_conc_names VARCHAR2(32676);
-- You may want a smaller variable if you know the result will be smaller
BEGIN
SELECT LTRIM(SYS_CONNECT_BY_PATH(name, ', '),',') conc_names
INTO l_conc_names
FROM (
SELECT id, name, rownumber, cnt
FROM (SELECT es.EVENT_ID as id
,s.SERVICE_NAME as name
,ROW_NUMBER() OVER (order by name) as rownumber
,COUNT(*) OVER () as cnt
FROM DT_SERVICES s
JOIN DT_EVENT_SERVICE es ON s.SERVICE_ID = es.SERVICE_ID
WHERE es.EVENT_ID = p_event_id )
) data
WHERE rownumber = cnt
START WITH rownumber = 1
CONNECT BY PRIOR rownumber = rownumber-1;
RETURN l_conc_names;
END;
