oracle SQL 函数参数的多个输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17680300/
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
Multiple inputs into oracle SQL function parameters
提问by bawpie
Okay, I have written a basic function in oracle to return the current worker based on the client id and the date specified.
好的，我已经在 oracle 中编写了一个基本函数，用于根据客户端 ID 和指定的日期返回当前工作人员。
It works as intended.
它按预期工作。
FUNCTION get_worker_new_test (p_id IN VARCHAR2, p_date IN DATE, w_type IN
VARCHAR2) RETURN VARCHAR2 IS
CURSOR c1 IS
SELECT
O_RELATIONSHIPS.ID
FROM o_relationships
WHERE rel_source_per_gro_id = p_id
AND rel_rty_code in (w_type)
AND p_date BETWEEN rel_start_date AND NVL(rel_end_date, SYSDATE)
ORDER BY rel_start_date DESC;
l_name VARCHAR2(70) ;
BEGIN
OPEN c1;
FETCH c1 INTO l_name;
CLOSE c1;
RETURN l_name;
END;
So for example, a query like this:
例如，这样的查询：
SELECT
CLIENT,
get_worker_new_test(CLIENT,sysdate,'WORKER')
FROM TABLE
Would return something like:
会返回类似的东西：
Client1 | A WORKER
客户 1 | 一位工人
However, I was wondering if it was possible to ask the function to return the latest worker from a number of different worker types.
但是，我想知道是否可以要求函数从许多不同的工作人员类型中返回最新的工作人员。
So for example, I'd write the query like this:
例如，我会像这样编写查询：
SELECT
CLIENT,
get_worker_new_test(CLIENT,sysdate,'WORKER,CLERK,MANAGER')
FROM TABLE
And it would return:
它会返回：
Client1 | A MANAGER
客户 1 | 经理
if the latest worker type assigned was a manager. I think I need a function that will split the string into seperate entries, but even then I'm not sure if there's a better way of going about it. Any advice or guidance would be appreciated.
如果分配的最新工人类型是经理。我想我需要一个函数来将字符串拆分为单独的条目，但即便如此，我也不确定是否有更好的方法来处理它。任何建议或指导将不胜感激。
Thanks.
谢谢。
回答by suPPLer
Quick and dirty solution #1. Use next query in your function:
快速而肮脏的解决方案#1。在您的函数中使用下一个查询：
SELECT
O_RELATIONSHIPS.ID
FROM o_relationships
WHERE rel_source_per_gro_id = p_id
AND instr( ','||w_type||',', ','||rel_rty_code||',' ) <> 0 --<-- Check comma-quoted worker type to be substring of parameter string
AND p_date BETWEEN rel_start_date AND NVL(rel_end_date, SYSDATE)
ORDER BY rel_start_date DESC;
Quick and dirty solution #2. Create SQL-type:
快速而肮脏的解决方案#2。创建 SQL 类型：
create type uservchartab as table of varchar2(4000);
Replace your function with this code:
用以下代码替换您的函数：
FUNCTION get_worker_new_test
(p_id IN VARCHAR2, p_date IN DATE, w_type IN uservchartab)
RETURN VARCHAR2
IS
CURSOR c1 IS
SELECT O_RELATIONSHIPS.ID
FROM o_relationships
WHERE rel_source_per_gro_id = p_id
AND rel_rty_code member of w_type --<-- Check worker type to be one from the condition
AND p_date BETWEEN rel_start_date AND NVL(rel_end_date, SYSDATE)
ORDER BY rel_start_date DESC;
l_name VARCHAR2(70) ;
BEGIN
OPEN c1;
FETCH c1 INTO l_name;
CLOSE c1;
RETURN l_name;
END;
Use it like this:
像这样使用它：
SELECT CLIENT
, get_worker_new_test( CLIENT, sysdate, uservchartab( 'WORKER', 'CLERK', 'MANAGER' ) )
FROM TABLE
