oracle 函数判断字符串是否包含图片格式的实例代码

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

RETURN ty_str_split

IS

   j INT := 0;

   i INT := 1;

   len INT := 0;

   len1 INT := 0;

   str VARCHAR2 (4000);

   str_split ty_str_split := ty_str_split ();

BEGIN

   len := LENGTH (p_str);

   len1 := LENGTH (p_delimiter);

   WHILE j < len

   LOOP

     j := INSTR (p_str, p_delimiter, i);

     IF j = 0

     THEN

       j := len;

       str := SUBSTR (p_str, i);

       str_split.EXTEND;

       str_split (str_split. COUNT ) := str;

       IF i >= len

       THEN

         EXIT;

       END IF;

     ELSE

       str := SUBSTR (p_str, i, j - i);

       i := j + len1;

       str_split.EXTEND;

       str_split (str_split. COUNT ) := str;

     END IF;

   END LOOP;

   RETURN str_split;

END fn_split;

create or replace function fn_ispng(

                      strpng  in varchar2

                      ) return number is

  v_num  number;

begin

    WITH t1 AS

   ( select * from table (fn_split(strpng, '.' ))

   )

  select count (1) into v_num from t1 where t1.column_value in ( 'png' , 'jpg' , 'jpeg' , 'gif' , 'bmp' , 'eps' );

  return v_num;

end fn_ispng;

select fn_ispng( 'dfdfd.png' )   from dual;