oracle创建函数时报success with compilation error

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的，不明白为什么创建的时候会报错？

羅宇城_

浏览 8800回答 1

oracle创建函数时报success with compilation error

1回答