Sqlserver如何创建语言辅助函数_[SQL Server教程]
context('userenv','lang') and num = n;
return l_word;
exception
when no_data_found then
return null;
end;
--
function cardinal(n number) return varchar2
is
p number; -- power
t varchar2(30); -- template
v number; -- lower portion
l_word numwords.word%type;
begin
if n < 0 then
l_word := get_word(-1);
if l_word is null then
return null;
end if;
return l_word||' '||cardinal(-n);
end if;
l_word := get_word(n);
if l_word is not null then
return l_word;
end if;
for row in
(
select * from numrules
where lang = sys_context('userenv','lang')
order by seq
)
loop
if length(n) <= row.p1 + row.p2 then
p := power(10,row.p2);
v := mod(n,p);
if row.seq = 0 then
if n < 20 then
return replace(row.temp0,'~2',cardinal(v));
end if;
else
if v = 0 then
return replace(row.temp0,'~1',cardinal(n/p));
else
return replace(replace(nvl(row.temp,'~1 ~2'),
'~1',cardinal(n-v)),
'~2',cardinal(v));
end if;
end if;
end if;
end loop;
return 'NUMBER TOO LARGE';
end cardinal;
end genword;
/
show errors;
最后，这里是我为英语和德语收集的一些数据。我还将数据从美国英语拷贝到英国英语中并使用术语“thousand million”和“million million”代替“billion”和“trillion”（美国用法），在美国之外这两个短语通常是混淆的来源。这些数据对生成-999,999,999,999到999,999,999,999之间所有整数（包括零）的拼写版本已经足够了。
REM -- create a table of base words and exceptions
create or replace package genword
as
function get_word(n number) return varchar2;
function cardinal(n number) return varchar2;
end genword;
/
show errors;
create or replace package body genword
as
function get_word(n number) return varchar2
is
l_wordnumwords.word%type;
begin
select word into l_word from numwords
where lang = sys_context('userenv','lang') and num = n;
return l_word;
exception
when no_data_found then
return null;
end;
--
function cardinal(n number) return varchar2
is
p number; -- power
t varchar2(30); -- template
v number; -- lower portion
l_word numwords.word%type;
begin
if n < 0 then
l_word := get_word(-1);
if l_word is null then
return null;
end if;
return l_word||' '||cardinal(-n);
end if;
l_word := get_word(n);
if l_word is not null then
return l_word;
end if;
for row in
(
select * from numrules
where lang = sys_context('userenv','lang')
order by seq
)
loop
if length(n) <= row.p1 + row.p2 then
p := power(10,row.p2);
v := mod(n,p);
if row.seq = 0 then
if n < 20 then
return replace(row.temp0,'~2',cardinal(v));
end if;
else
if v = 0 then
return replace(row.temp0,'~1',cardinal(n/p));
else
return replace(replace(nvl(row.temp,'~1 ~2'),
'~1',cardinal(n-v)),
'~2',cardinal(v));
end if;
end if;
end if;
end loop;
return 'NUMBER TOO LARGE';
end cardinal;
end genword;
/
show errors;
下面是一些简单的 SQL 语句，这些语句使用了前面提供到函数和数据。你可以试一下将语言设成‘GERMAN’，或‘ENGLISH’来测试其它两组数据：
SQL> alter session set nls_language = 'AMERICAN';
SQL> select genword.cardinal(123456789) from dual;
one hundred twenty-three million four hundred fifty-six thousand seven hundred
eighty-nine
来源:网络
