CREATE OR REPLACE FUNCTION
/*-------------------------------------------------
* 函数: fGetSpell
* 作者: 李昊
* 功能: 获得字符串的拼音头
* 参数: strString 待处理的字符串
-------------------------------------------------*/
fGetSpell (
strString IN VARCHAR2
) RETURN VARCHAR2
IS
TYPE word_spell IS REF CURSOR;
curSpell word_spell;
intLength NUMBER;
intLoop NUMBER;
strSQL VARCHAR2(32767);
strSpell SPELL.SPELL%TYPE;
strResult VARCHAR2(300);
BEGIN
intLength := LENGTH(strString); -- 如果输入字符串为空,则返回空
IF intLength<=0 THEN
RETURN('');
END IF; -- 拼写查询 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 FROM (' || strSQL || ') t,SPELL s WHERE t.WORD=s.WORD(+) ORDER BY t.ID'; -- 打开游标
OPEN curSpell FOR strSQL; LOOP
FETCH curSpell INTO strSpell; EXIT WHEN curSpell%NOTFOUND; strResult := strResult || strSpell;
END LOOP; -- 关闭游标
CLOSE curSpell; RETURN(strResult);
END;
/*-------------------------------------------------
* 函数: fGetSpell
* 作者: 李昊
* 功能: 获得字符串的拼音头
* 参数: strString 待处理的字符串
-------------------------------------------------*/
fGetSpell (
strString IN VARCHAR2
) RETURN VARCHAR2
IS
TYPE word_spell IS REF CURSOR;
curSpell word_spell;
intLength NUMBER;
intLoop NUMBER;
strSQL VARCHAR2(32767);
strSpell SPELL.SPELL%TYPE;
strResult VARCHAR2(300);
BEGIN
intLength := LENGTH(strString); -- 如果输入字符串为空,则返回空
IF intLength<=0 THEN
RETURN('');
END IF; -- 拼写查询 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 FROM (' || strSQL || ') t,SPELL s WHERE t.WORD=s.WORD(+) ORDER BY t.ID'; -- 打开游标
OPEN curSpell FOR strSQL; LOOP
FETCH curSpell INTO strSpell; EXIT WHEN curSpell%NOTFOUND; strResult := strResult || strSpell;
END LOOP; -- 关闭游标
CLOSE curSpell; RETURN(strResult);
END;
函数: fn_GetPinyin
描述: 汉字转拼音(无数据表版)
使用: dbo.fn_GetPinyin('中华人民共和国') = zhonghuarenmingongheguo
作者: 流香羽(改编:Tony)
博客: http://hi.baidu.com/流香羽
------------------------------------------------------------- */
http://blog.csdn.net/htl258/archive/2009/09/18/4567350.aspx