Example 1: Using the While Statement to Count Substrings

    SQL> DECLARE :SUB_STR CHAR;
    SQL> DECLARE :SRC_STR CHAR(50);
    SQL> BEGIN
    cont>   SET :SUB_STR='l';
    cont>   SET :SRC_STR='The rain in Spain falls mainly on the plain';
    cont> END;
    SQL> SET FLAGS 'TRACE';
    SQL> BEGIN
    cont>-- This procedure counts the occurrence of substrings
    cont>   DECLARE :STR_COUNT INTEGER=0;
    cont>   DECLARE :CUR_POS INTEGER = POSITION (:SUB_STR IN :SRC_STR);
    cont>   WHILE :CUR_POS >0 DO
    cont>      SET :STR_COUNT=:STR_COUNT + 1;
    cont>         SET :CUR_POS = POSITION (:SUB_STR IN :SRC_STR FROM :CUR_POS + 1);
    cont>   END WHILE;
    cont>     TRACE 'FOUND ', :STR_COUNT, ' OCCURRENCES OF "', :SUB_STR, '"';
    cont> END;
    ~Xt: Found 4          occurrences of "l"