oracle创建函数时报success with compilation error

Question

CREATE OR REPLACE 

FUNCTION "fn_CompareAge" (in_age1 IN VARCHAR2, in_age2 IN VARCHAR2)

RETURN INTEGER

AS

v_mark1 INTEGER := 1;

  v_mark2 INTEGER := 1;

v_num1 INTEGER;

  v_num2 INTEGER;

  v_result Integer := 0;

BEGIN

IF "INSTR"(in_age1, 'D') > 0 THEN

v_mark1 := 3;

ELSIF INSTR(in_age1, 'M') > 0 THEN

v_mark1 := 2;

  END IF;

IF INSTR(in_age2, 'D') > 0 THEN

v_mark2 := 3;

ELSIF INSTR(in_age2, 'M') > 0 THEN

v_mark2 := 2;

  END IF;

IF v_mark1 <= v_mark2 THEN 

IF v_mark1 = 3 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'D')+1, LENGTH(in_age1)));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'D')+1, LENGTH(in_age2)));

END IF;

END IF;

ELSIF v_mark1 = 2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

  

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, LENGTH(in_age1)));

IF (v_mark2 = 3) THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

ELSIF v_mark2 = 2 THEN

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, LENGTH(in_age2)));

      END IF;

END IF;

ELSIF v_mark1 = 1 THEN 

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, LENGTH(in_age1)));

      IF (v_mark2 = 2 or v_mark2 = 3) THEN

        v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

ELSIF v_mark2 = 1 THEN

        v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, LENGTH(in_age2)));

      END IF;

 END IF;

ELSIF v_mark1 > v_mark2 THEN

IF v_mark2 = 2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, INSTR(in_age2, 'M')-2));

  

IF v_num1 = v_num2 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, INSTR(in_age1, 'M')+1, INSTR(in_age1, 'D')-INSTR(in_age1, 'M')-1));

v_num2 := TO_NUMBER(SUBSTR(in_age2, INSTR(in_age2, 'M')+1, LENGTH(in_age2)));

END IF;

ELSIF v_mark2 = 1 THEN

v_num1 := TO_NUMBER(SUBSTR(in_age1, 2, INSTR(in_age1, 'M')-2));

v_num2 := TO_NUMBER(SUBSTR(in_age2, 2, LENGTH(in_age2)));

 END IF;

  END IF;

  

  IF v_num1 >= v_num2 THEN

    v_result := 1;

  END IF;

RETURN v_result;

END;

上面是我想要创建的函数，可我每次在navicat的查询编辑里执行时都出现：

[SQL]CREATE OR REPLACE 

FUNCTION "fn_CompareAge" (in_age1 IN VARCHAR2, in_age2 IN VARCHAR2)

RETURN INTEGER

AS

v_mark1 INTEGER := 1;

  v_mark2 INTEGER := 1;

v_num1 INTEGER;

  v_num2 INTEGER;

  v_result Integer := 0;

BEGIN

IF "INSTR"(in_age1, 'D') > 0 THEN

v_mark1 := 3;

ELSIF INSTR(in_age1, 'M') > 0 THEN

v_mark1 := 2;

  END IF;

[Err] ORA-24344: success with compilation error

每次都是在第一个end if 处截断了， 函数里只保存到end if 以上的内容，end if 下面的内容就丢失了；

可当我把完整的函数创建语句拿到函数界面(如下图)里直接保存却是可以正常保存的，且无任何编译错误。

 数据库是oracle 11g的，不明白为什么创建的时候会报错？

Answer 1

看错了！！！