哪位大哥能帮忙把下面2个简单的sqlserver下的函数转成oracle下面的函数

[herofour444]

在插入新数据的时候，取出该字符串对应的字母

用户定义函数A
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63

select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end



用户定义函数B

CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(50) )
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(20), @e VARCHAR(20), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=20 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END




调用方法
CREATE TRIGGER aa ON dbo.表A
FOR INSERT, UPDATE, DELETE
AS
update 表A set pinyin=dbo.F_GetHelpCode(b.item_name)
from 表A a,inserted b
where a.item_code=b.item_code

[herofour444]

自己从
CREATE OR REPLACE FUNCTION "MDM"."F_CH2PY" (chn in varchar2)
return varchar2 is
n float :=63;
c varchar2;
begin
select n = n +1, c = case chn when chn then to_char(n) else c end
from ( select * from ( select chn ='吖' union all select'八' union all select'嚓' union all select'咑' union all select'妸' union all select'发' union all select'旮' union all select'铪' union all select'丌' union all select
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select
'屲' union all select
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select chn) as a where rownum < 28
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return c;
end;

[herofour444]

上面是自己写的，出现以下错误
行号=6 列号=10 错误文本=pl/sql: ora-00923: from keyword not found where expect
行号=6 列号=1 错误文本 = pl/sql: sql statement ignored

[Hangfeng]

CREATE OR REPLACE FUNCTION F_GetHelpCode
(p_cName VARCHAR2)
RETURN VARCHAR2
AS
p_i SMALLINT;
p_L SMALLINT;
p_cHelpCode VARCHAR2(20);
p_e VARCHAR2(20);
p_iAscii SMALLINT;
BEGIN
p_i := 1;
p_L := 0;
p_cHelpCode := '';
while (p_L <=20 AND p_i <=length(p_cName)) loop
p_e := lower(substr(p_cname, p_i, 1));
p_iAscii := ASCII(p_e);

IF (p_iAscii >=48 AND p_iAscii <=57) OR (p_iAscii>=97 AND p_iAscii <=122) or (p_iAscii=95) then
p_cHelpCode := p_cHelpCode + p_e;
ELSE
IF p_iAscii>=176 AND p_iAscii <=247 then
p_cHelpCode := p_cHelpCode + f_ch2py(p_e);
ELSE
p_L := p_L -1;
END IF;
p_i := p_i+1;
p_L := p_L+1;
END IF;

end loop;
RETURN p_cHelpCode;
END;
/

[Hangfeng]

在p_iAscii > =48处，把> =中间的空格去掉。

[herofour444]

谢谢阿
现在第二个函数也编译成功了，
现在测试了下，没有反应，没有转换成功，
是不是oracle字符集的问题啊

[herofour444]

特别着急，可是没人帮忙

[Hangfeng]

CREATE OR REPLACE function f_ch2py(p_chn varchar2)
return varchar2
as
p_n int;
p_c char(1);
begin
p_n := 63;
select p_n+1,(case chn when p_chn then to_char(p_n) else p_c end) into p_n, p_c
from(
select * from (
select '吖' chn from dual union all
select '八' chn from dual union all
select '嚓' chn from dual union all
select '咑' chn from dual union all
select '妸' chn from dual union all
select '发' chn from dual union all
select '旮' chn from dual union all
select '铪' chn from dual union all
select '丌' chn from dual union all --because have no 'i'
select '丌' chn from dual union all
select '咔' chn from dual union all
select '垃' chn from dual union all
select '嘸' chn from dual union all
select '拏' chn from dual union all
select '噢' chn from dual union all
select '妑' chn from dual union all
select '七' chn from dual union all
select '呥' chn from dual union all
select '仨' chn from dual union all
select '他' chn from dual union all
select '屲' chn from dual union all --no 'u'
select '屲' chn from dual union all --no 'v'
select '屲' chn from dual union all
select '夕' chn from dual union all
select '丫' chn from dual union all
select '帀' chn from dual union all
select p_chn chn from dual) a
order by chn
) b where rownum <= 27
;
return(p_c);
end;
/

[Hangfeng]

CREATE OR REPLACE FUNCTION F_GetHelpCode
(p_cName VARCHAR2)
RETURN VARCHAR2
AS
p_i SMALLINT;
p_L SMALLINT;
p_cHelpCode VARCHAR2(20);
p_e VARCHAR2(20);
p_iAscii SMALLINT;
BEGIN
p_i := 1;
p_L := 0;
p_cHelpCode := '';
while (p_L <=20 AND p_i <=length(p_cName)) loop
p_e := lower(substr(p_cname, p_i, 1));
p_iAscii := ASCII(p_e);

IF (p_iAscii >=48 AND p_iAscii <=57) OR (p_iAscii>=97 AND p_iAscii <=122) or (p_iAscii=95) then
p_cHelpCode := p_cHelpCode + p_e;
ELSE
IF p_iAscii>=176 AND p_iAscii <=247 then
p_cHelpCode := p_cHelpCode + f_ch2py(p_e);
ELSE
p_L := p_L -1;
END IF;
p_i := p_i+1;
p_L := p_L+1;
END IF;

end loop;
RETURN p_cHelpCode;
END;
/

[herofour444]

谢谢阿，特别感谢，
第一个函数执行成功，第二个还存在编译错误

行号=18 列号=28 错误文本 = pLS-00103: encounterd the symbol "=" when expecting one of the following: (-+all case mod new null < an identifier> < a double-quoted delimited-identifier> <a bin variable> any avg cout current max min prior some sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification > < a number> <a single-quoted sql string > pipe
第21，26，28行业出现错误
21 encounterd the symbol "then" when expecting one of the following : * & - + : / at mod...
26 encouted the symbol "p_i"
encouted the symbol ":" when expecting one of the following :).....
28 encountered the symbol "if" when expecting one of the following : ; <an identifier>