Oracle函数将所有名称替换为昵称

Question

我有一个名为昵称的表

Names   nickname
vikram  vik
James   Jim
Robert  Bob
Charles Dick
Richard Dick
Rich    Dick

想要创建一个Oracle函数，将名称替换为输入字符串中表中的昵称

func_nicknames('vikram，James，Rajesh，Robert'(应返回值'vik，Jim，Rajesh，Bob'

CREATE OR REPLACE FUNCTION func_nicknames( in_val varchar2)
RETURN VARCHAR2
IS
O_VAL VARCHAR2(100) := in_val;
BEGIN
SELECT REPLACE(O_VAL,t.name,t.nickname) INTO O_VAL FROM nicknames t;
RETURN(O_VAL);
END func_nicknames;

上面的代码抛出了一个错误。

在SQL Server中，以下代码运行良好：

CREATE OR ALTER FUNCTION getNicknames(@val NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS
BEGIN
DECLARE @result NVARCHAR(MAX);
SET @result = @val;
SELECT @result = REPLACE(@result, name, nickname)
FROM nicknames;
RETURN @result;
END;

我想在Oracle中创建类似的代码。oracle的工作代码：

CREATE OR REPLACE FUNCTION getNicknames(in_val VARCHAR) RETURN VARCHAR IS
ret VARCHAR(2000);
v VARCHAR(2000);
CURSOR cur IS SELECT SRC_VAL_STR, TGT_VAL_STR FROM nicknames;
BEGIN
ret := in_val;
FOR x IN cur
LOOP
SELECT REPLACE(ret, x.name, x.nickname) INTO v FROM DUAL;
ret := v;
END LOOP;
RETURN ret;
END;

Answer 1

首先，您必须将参数转换为表，其余的操作非常简单：

create or replace function getNicknames (names varchar2) return varchar2 is
ret varchar2 (32000);
begin
with names (name) as (
select trim (column_value)
from xmlTable (('"'||replace (names, ',', '","')||'"')) x
)
select 
listagg (coalesce (nickname, na.name), ', ') within group (order by null) into ret 
from names na
left join nicknames ni on ni.name=na.name;
return ret;
end;
/

执行和结果：

exec dbms_output.put_line ('result='||getNicknames ('vikram, James, Rajesh, Robert'));    
result=vik, Jim, Rajesh, Bob

在db<gt；不停摆弄

Answer 2

您的SQL返回多行。这是一个错误。试试这个：

create or replace function f_nicks (pNames in varchar2) 
return varchar2
is
tab dbms_utility.uncl_array;  
nCntElements number := 0;  
vEl varchar2(32000);  
o_nicks varchar2(1000);
BEGIN  
dbms_utility.comma_to_table(pNames, nCntElements, tab);  

select  listagg (nvl( n.nickname, t.listname), ',') nicks into o_nicks
from (select trim(column_value) listname, rownum lp 
from table(tab) ) t , nicknames n   where n.name (+) =  t.listname  ;

return o_nicks;
END;  

---测试功能

select f_nicks ('James, vikram, James,James, Rajesh, Robert') from dual;

db fiddle