create or replace function getdatestr(indate in date,num in number)
return varchar2
as
i number;
begin
select to_char(indate,'D') into i from dual;
if i>=num then
return to_char(indate+(i-num),'mm-dd');
else
return to_char(indate+(num-i),'mm-dd');
end if;
end;SQL> select getdatestr(to_date('2003-10-10','yyyy-mm-dd'),3) from dual;GETDATESTR(TO_DATE('2003-10-10
--------------------------------------------------------------------------------
10-13
return varchar2
as
i number;
begin
select to_char(indate,'D') into i from dual;
if i>=num then
return to_char(indate+(i-num),'mm-dd');
else
return to_char(indate+(num-i),'mm-dd');
end if;
end;SQL> select getdatestr(to_date('2003-10-10','yyyy-mm-dd'),3) from dual;GETDATESTR(TO_DATE('2003-10-10
--------------------------------------------------------------------------------
10-13
已连接。
SQL> create function add_f(p1 in number,p2 in number)
2 return number is
3 rNumber number;
4 begin
5 rNumber := p1+p2;
6 return(rNumber);
7 end;
8 /函数已创建。SQL> select add_f(1,2)"a" from dual; a
----------
3
--D_TIME INTEGER;
RESULT INTEGER;
F_YEAR INTEGER;
F_MONTH INTEGER;
N_YEAR INTEGER;
N_MONTH INTEGER;
N_NUM INTEGER;
O_MONTH INTEGER;
BEGIN
--D_TIME:=TO_CHAR(D_TIME1,'YYYYMMDD');
F_YEAR:=SUBSTR(D_TIME,1,4);
F_MONTH:=SUBSTR(D_TIME,5,2);
IF (F_MONTH+D_NUM)>12 THEN
N_MONTH:=MOD((F_MONTH+D_NUM),12);
N_NUM:=TRUNC((F_MONTH+D_NUM)/12);
N_YEAR:=F_YEAR+N_NUM;
ELSIF (F_MONTH+D_NUM)>0 THEN
N_MONTH:=F_MONTH+D_NUM;
N_NUM:=0;
N_YEAR:=F_YEAR+N_NUM;
ELSIF (F_MONTH+D_NUM)<0 THEN
O_MONTH:=-F_MONTH-D_NUM;
N_MONTH:=TRIM(TO_CHAR((12-MOD(O_MONTH,12)),'00'));
N_YEAR:=TO_CHAR(F_YEAR-TRUNC(O_MONTH/12)-1,'0000');
ELSIF (F_MONTH+D_NUM)=0 THEN
N_MONTH:=12;
N_YEAR:=TO_CHAR(F_YEAR-1,'0000');
END IF;
RESULT:=TO_NUMBER(TO_CHAR(N_YEAR)||TRIM(TO_CHAR(N_MONTH,'00')));
RETURN(RESULT);
END ADDMONTH;