oracle 存储过程转 sql server 2005存储过程

[fw0011]

CREATE OR REPLACE PROCEDURE
/*-------------------------------------------------
* 存储过程: pGetASpell
* 功能: 获得字符串的全拼音
* 作者： 李昊
* 参数: strString 待处理的字符串
* strResult (输出)拼音头
-------------------------------------------------*/
pGetASpell (
strString IN VARCHAR2,
strResult OUT VARCHAR2
)
AS
TYPE word_spell IS REF CURSOR;
curSpell word_spell;
intLength NUMBER;
intLoop NUMBER;
strSQL VARCHAR2(32767);
strSpell SPELL.ASPELL%TYPE;
BEGIN
intLength := LENGTH(strString);

-- 如果输入字符串为空，则返回空
IF intLength<=0 THEN
strResult := '';
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.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;

EXIT WHEN curSpell%NOTFOUND;

strResult := strResult || strSpell;
END LOOP;

-- 关闭游标
CLOSE curSpell;
END IF;
END;

[jyh070207]

重复的发了...