Oracle網站上的每周一帖(Tip for Week of December 13, 2004)

                                                            計算一個字符串在另外一個字符串裡出現的次數
                                                                    Aui de la Vega,菲律賓
CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)
RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)
AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN
COUNTER := COUNTER+1;
END IF;
NEXT_INDEX := NEXT_INDEX+1;
END LOOP;
RETURN COUNTER;
END;
代碼分析：
該代碼不是很復雜，利用NEXT_INDEX加一遞增，對INSTRING做遍歷，從INSTRING字符串的第NEXT_INDEX個字符開始截取
LENGTH(PATTERN)長度的字符串，如果截取的字符串等於PATTERN,則計數器COUNTER+1,在PL/SQL Developer+Oracle10+WINNT
調試成功。