CREATE OR REPLACE PROCEDURE
/*-------------------------------------------------
* 存储过程: pGetAllSpell
* 功能: 获得字符串的拼音头和全拼音
* 作者: 李昊
* 参数: strString 待处理的字符串
* strRSpell (输出)拼音头
* strRASpell (输出)全拼音
-------------------------------------------------*/
pGetAllSpell (
strString IN VARCHAR2,
strRSpell OUT VARCHAR2,
strRASpell OUT VARCHAR2
)
AS
TYPE word_spell IS REF CURSOR;
curSpell word_spell;
intLength NUMBER;
intLoop NUMBER;
strSQL VARCHAR2(32767);
strSpell SPELL.SPELL%TYPE;
strASpell SPELL.ASPELL%TYPE;
BEGIN
intLength := LENGTH(strString); -- 如果输入字符串为空,则返回空
IF intLength<=0 THEN
strRSpell := '';
strRASpell := '';
ELSE
-- 拼写查询 SQL 语句
FOR intLoop IN 1..intLength LOOP
IF intLoop=1 THEN
strSQL := 'SELECT ' || TO_CHAR(intLoop) || ' ID,''' || SUBSTR(strString, intLoop, 1) || ''' WORD FROM DUAL';
ELSE
strSQL := strSQL || ' UNION SELECT ' || TO_CHAR(intLoop) || ' ID,''' || SUBSTR(strString, intLoop, 1) || ''' WORD FROM DUAL';
END IF;
END LOOP; strSQL := 'SELECT NVL(s.SPELL,''?'') SPELL, NVL(s.ASPELL,''?'') ASPELL FROM (' || strSQL || ') t,SPELL s WHERE t.WORD=s.WORD(+) ORDER BY t.ID'; -- 打开游标
OPEN curSpell FOR strSQL; LOOP
FETCH curSpell INTO strSpell, strASpell; EXIT WHEN curSpell%NOTFOUND; strRSpell := strRSpell || strSpell;
strRASpell := strRASpell || strASpell;
END LOOP; -- 关闭游标
CLOSE curSpell;
END IF;
END;
/*-------------------------------------------------
* 存储过程: pGetAllSpell
* 功能: 获得字符串的拼音头和全拼音
* 作者: 李昊
* 参数: strString 待处理的字符串
* strRSpell (输出)拼音头
* strRASpell (输出)全拼音
-------------------------------------------------*/
pGetAllSpell (
strString IN VARCHAR2,
strRSpell OUT VARCHAR2,
strRASpell OUT VARCHAR2
)
AS
TYPE word_spell IS REF CURSOR;
curSpell word_spell;
intLength NUMBER;
intLoop NUMBER;
strSQL VARCHAR2(32767);
strSpell SPELL.SPELL%TYPE;
strASpell SPELL.ASPELL%TYPE;
BEGIN
intLength := LENGTH(strString); -- 如果输入字符串为空,则返回空
IF intLength<=0 THEN
strRSpell := '';
strRASpell := '';
ELSE
-- 拼写查询 SQL 语句
FOR intLoop IN 1..intLength LOOP
IF intLoop=1 THEN
strSQL := 'SELECT ' || TO_CHAR(intLoop) || ' ID,''' || SUBSTR(strString, intLoop, 1) || ''' WORD FROM DUAL';
ELSE
strSQL := strSQL || ' UNION SELECT ' || TO_CHAR(intLoop) || ' ID,''' || SUBSTR(strString, intLoop, 1) || ''' WORD FROM DUAL';
END IF;
END LOOP; strSQL := 'SELECT NVL(s.SPELL,''?'') SPELL, NVL(s.ASPELL,''?'') ASPELL FROM (' || strSQL || ') t,SPELL s WHERE t.WORD=s.WORD(+) ORDER BY t.ID'; -- 打开游标
OPEN curSpell FOR strSQL; LOOP
FETCH curSpell INTO strSpell, strASpell; EXIT WHEN curSpell%NOTFOUND; strRSpell := strRSpell || strSpell;
strRASpell := strRASpell || strASpell;
END LOOP; -- 关闭游标
CLOSE curSpell;
END IF;
END;
if object_id('pGetAllSpell','P') is not null
drop proc pGetAllSpell
go/*-------------------------------------------------
* 存储过程: pGetAllSpell
* 功能: 获得字符串的拼音头和全拼音
* 作者: 李昊
* 参数: strString 待处理的字符串
* strRSpell (输出)拼音头
* strRASpell (输出)全拼音
-------------------------------------------------*/create proc pGetAllSpell
(@strString varchar(max),
@strRSpell varchar(max) output,
@strRASpell varchar(max) output)
as
begin
declare @intLength int,@intLoop int=1,@strSQL varchar(max)
select @intLength=len(@strString) if @intLength<=0
select @strRSpell='',@strRASpell=''
else
begin
while(@intLoop<=@intLength)
begin
if @intLoop=1
select @strSQL='SELECT '+cast(@intLoop as varchar)+' ID,'''+substring(@strString,@intLoop,1)+''' WORD '
else
select @strSQL=@strSQL+' UNION SELECT '+cast(@intLoop as varchar)+' ID,'''+substring(@strString,@intLoop,1)+''' WORD '
end
end
select @strSQL='SELECT isnull(s.SPELL,''?'') SPELL, isnull(s.ASPELL,''?'') ASPELL FROM ('+@strSQL+') t left join SPELL s on t.WORD=s.WORD ORDER BY t.ID '
create table #tSpell(SPELL varchar(2000),ASPELL varchar(2000))
insert into #tSpell exec(@strSQL)
select @strRSpell=cast((select SPELL+'' from #tSpell for xml path('')) as varchar(max))
select @strRASpell=cast((select ASPELL+'' from #tSpell for xml path('')) as varchar(max))
drop table #tSpell
end
http://blog.csdn.net/maco_wang/article/details/6260301汉字转拼音函数
http://blog.csdn.net/maco_wang/article/details/6290128