求助oracle定义sql函数
create or replace function getTerm(username_new varchar2(32))
return varchar2(10)
as
begin
result varchar2(10);
term number:=1;
yearCode varchar2(10):=substr(username_new,4,2);
semesterCode varchar2(10):=substr(username_new,6,1);
year number:=convert(number,yearCode);
currentdate date(7):=TO_DATE(to_char(getdate(),'YYYY-MM-DD'),'YYYY-MM-DD');
currentyear number:=convert(number,to_char(currentdate,'yyyy'));
lastyear number:=0;
d date(7):=TO_DATE(to_char(currentdate,'YYYY')+'-'+'03-16','YYYY-MM-DD');
newTermStartDate date(7);
lastTermEndDate date(7);
if currentdate<d then
lastyear:=1;
end if;
if lastyear=1 then
currentyear:=currentyear-1;
end if;
term:=(currentyear-year)*2;
newTermStartDate:=TO_DATE(to_char(currentdate,'YYYY')+'-09-16','YYYY-MM-DD');
lastTermEndDate:=TO_DATE(to_char(currentdate,'YYYY')+'-03-16','YYYY-MM-DD');
if currentdate<newTermStartDate then
if currentdate>lastTermEndDate then
term:=term+1;
end if;
else
term:=term+2;
end if;
if semesterCode=2 then
term:=term-1;
end if;
result:=convert(varchar2(10),term);
return result;
end getTerm;
这是我写的sql函数
下面是错误报告：
[Err] ORA-24344: success with compilation error
[Err] create or replace function getTerm(username_new varchar2(32))
return varchar2(10)
as
begin
result varchar2(10);
term number:=1;
yearCode varchar2(10):=substr(username_new,4,2);
semesterCode varchar2(10):=substr(username_new,6,1);
year number:=convert(number,yearCode);
currentdate date(7):=TO_DATE(to_char(getdate(),'YYYY-MM-DD'),'YYYY-MM-DD');
currentyear number:=convert(number,to_char(currentdate,'yyyy'));
lastyear number:=0;
d date(7):=TO_DATE(to_char(currentdate,'YYYY')+'-'+'03-16','YYYY-MM-DD');
newTermStartDate date(7);
lastTermEndDate date(7);
if currentdate<d then
lastyear:=1;
end if;
[Err] ORA-00900: invalid SQL statement
[Err] if lastyear=1 then
currentyear:=currentyear-1
[Err] ORA-00900: invalid SQL statement
[Err] end if
[Err] ORA-00900: invalid SQL statement
[Err] term:=(currentyear-year)*2
[Err] ORA-00900: invalid SQL statement
[Err] newTermStartDate:=TO_DATE(to_char(currentdate,'YYYY')+'-09-16','YYYY-MM-DD')
[Err] ORA-00900: invalid SQL statement
[Err] lastTermEndDate:=TO_DATE(to_char(currentdate,'YYYY')+'-03-16','YYYY-MM-DD')
[Err] ORA-00900: invalid SQL statement
[Err] if currentdate<newTermStartDate then
if currentdate>lastTermEndDate then
term:=term+1
[Err] ORA-00900: invalid SQL statement
[Err] end if
[Err] ORA-00900: invalid SQL statement
[Err] else
term:=term+2
[Err] ORA-00900: invalid SQL statement
[Err] end if
[Err] ORA-00900: invalid SQL statement
[Err] if semesterCode=2 then
term:=term-1
[Err] ORA-00900: invalid SQL statement
[Err] end if
[Err] ORA-00900: invalid SQL statement
[Err] result:=convert(varchar2(10),term)
[Err] ORA-00900: invalid SQL statement
[Err] return result
[Err] ORA-00900: invalid SQL statement
[Err] end getTerm
[Msg] Finished - Unsuccessfully
[解决办法]
CREATE OR REPLACE FUNCTION GETTERM(USERNAME_NEW VARCHAR2) RETURN VARCHAR2 AS
RESULT VARCHAR2(100); --这里需要长度弟弟,我说的是return 后面的不需要长度
TERM NUMBER := 1;
YEARCODE VARCHAR2(100) := SUBSTR(USERNAME_NEW, 4, 2); --这里需要长度弟弟,我说的是return 后面的不需要长度
SEMESTERCODE VARCHAR2(100) := SUBSTR(USERNAME_NEW, 6, 1); --这里需要长度弟弟,我说的是return 后面的不需要长度
YEAR NUMBER := TO_NUMBER(YEARCODE);
CURRENTDATE DATE := TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD');
CURRENTYEAR NUMBER := TO_NUMBER(TO_CHAR(CURRENTDATE, 'yyyy'));
LASTYEAR NUMBER := 0;
D DATE := TO_DATE(TO_CHAR(CURRENTDATE, 'YYYY') + '-' +
'03-16',
'YYYY-MM-DD');
NEWTERMSTARTDATE DATE;
LASTTERMENDDATE DATE;
BEGIN
IF CURRENTDATE < D THEN
LASTYEAR := 1;
END IF;
IF LASTYEAR = 1 THEN
CURRENTYEAR := CURRENTYEAR - 1;
END IF;
TERM := (CURRENTYEAR - YEAR) * 2;
NEWTERMSTARTDATE := TO_DATE(TO_CHAR(CURRENTDATE, 'YYYY') + '-09-16',
'YYYY-MM-DD');
LASTTERMENDDATE := TO_DATE(TO_CHAR(CURRENTDATE, 'YYYY') + '-03-16',
'YYYY-MM-DD');
IF CURRENTDATE < NEWTERMSTARTDATE THEN
IF CURRENTDATE > LASTTERMENDDATE THEN
TERM := TERM + 1;
END IF;
ELSE
TERM := TERM + 2;
END IF;
IF SEMESTERCODE = 2 THEN
TERM := TERM - 1;
END IF;
RESULT := TO_CHAR(TERM);
RETURN RESULT;
END GETTERM;