数据库取拼音码的存储过程(Oracle、Sql Server)

最新推荐文章于 2024-06-24 17:53:11 发布

原创最新推荐文章于 2024-06-24 17:53:11 发布 · 304 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL Server #SQL #Oracle #C #C++

本文介绍了一个用于将汉字转换为其拼音形式的SQL函数实现。该函数适用于Oracle和Sql Server数据库，并利用了特定的字符集和排序规则来确保准确的转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle数据库：
Sql代码
CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。　wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;

WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;

SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;

SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;

v_c := CHR (v_n);

IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;

v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;

RETURN v_return;
END fgetpy;

CREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)
RETURN VARCHAR2
AS
v_strlen INT;
v_return VARCHAR2 (500);
v_ii INT;
v_n INT;
v_c VARCHAR2 (2);
v_chn VARCHAR2 (2);
v_rc VARCHAR2 (500);
/*************************************************************************
生成汉字拼音码的函数。　wallimn 2009-06-21
**************************************************************************/
BEGIN
--dbms_output.put_line(v_str);
v_rc := v_str;
v_strlen := LENGTH (v_rc);
v_return := '';
v_ii := 0;

WHILE v_ii < v_strlen
LOOP
v_ii := v_ii + 1;
v_n := 63;

SELECT SUBSTR (v_rc, v_ii, 1)
INTO v_chn
FROM DUAL;

SELECT v_n + MAX (rowsf)
INTO v_n
FROM (SELECT chn, ROWNUM rowsf
FROM (SELECT chn
FROM (SELECT '吖' chn
FROM DUAL
UNION
SELECT '八'
FROM DUAL
UNION ALL
SELECT '嚓'
FROM DUAL
UNION ALL
SELECT '咑'
FROM DUAL
UNION ALL
SELECT '妸'
FROM DUAL
UNION ALL
SELECT '发'
FROM DUAL
UNION ALL
SELECT '旮'
FROM DUAL
UNION ALL
SELECT '铪'
FROM DUAL
UNION ALL
SELECT '丌'
FROM DUAL --because have no 'i'
UNION ALL
SELECT '丌'
FROM DUAL
UNION ALL
SELECT '咔'
FROM DUAL
UNION ALL
SELECT '垃'
FROM DUAL
UNION ALL
SELECT '嘸'
FROM DUAL
UNION ALL
SELECT '拏'
FROM DUAL
UNION ALL
SELECT '噢'
FROM DUAL
UNION ALL
SELECT '妑'
FROM DUAL
UNION ALL
SELECT '七'
FROM DUAL
UNION ALL
SELECT '呥'
FROM DUAL
UNION ALL
SELECT '仨'
FROM DUAL
UNION ALL
SELECT '他'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '屲'
FROM DUAL
UNION ALL
SELECT '夕'
FROM DUAL
UNION ALL
SELECT '丫'
FROM DUAL
UNION ALL
SELECT '帀'
FROM DUAL
UNION ALL
SELECT v_chn
FROM DUAL) a
ORDER BY NLSSORT (chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
WHERE chn = v_chn;

v_c := CHR (v_n);

IF CHR (v_n) = '@'
THEN --英文直接返回
v_c := v_chn;
END IF;

v_return := v_return || v_c;
v_return := lower(v_return);
END LOOP;

RETURN v_return;
END fgetpy;

Sql Server数据库：
Sql代码
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
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 '丌' --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 '帀'
union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
set @return=@return+@c
end
return(@return)
end