CREATE OR REPLACE FUNCTION My_Add_Months(
in_Date IN DATE,
in_Balance IN INTEGER
) RETURN DATE
/* 实现ORACLE函数Add_Month功能
*/
AS v_Str_Date VARCHAR2(14);
v_Year VARCHAR2(4);
v_Month VARCHAR2(2);
v_Day VARCHAR2(2);
v_Time VARCHAR2(6);
v_Balance INTEGER;
BEGIN
v_Balance := TRUNC(in_Balance);
v_Str_Date := TO_CHAR(in_Date,'YYYYMMDDHH24MISS');
v_Year := SUBSTR(v_Str_Date,1,4);
v_Month := SUBSTR(v_Str_Date,5,2);
v_Day := SUBSTR(v_Str_Date,7,2);
v_Time := SUBSTR(v_Str_Date,9,14);
-- 处理年、月
IF v_Month + v_Balance > 12 THEN -- 加月跨年
v_Year := v_Year + FLOOR((v_Month + v_Balance)/12);
v_Month := MOD(v_Month + v_Balance,12);
ELSIF v_Month + v_Balance <= 0 THEN -- 减月跨年 v_Year := v_Year + FLOOR((v_Month + v_Balance)/12);
v_Month := MOD(v_Month + v_Balance,12) + 12;
ELSE
v_Month := v_Month + v_Balance;
END IF;
SELECT DECODE(LENGTH(v_Month),1,'0'||v_Month,v_Month) INTO v_Month FROM dual;
-- 处理天
IF v_Month IN ('04','06','09','11') AND v_Day = '31' THEN
v_Day := 30;
ELSIF v_Month = '02' AND v_Day IN ('29','30','31') THEN
IF (MOD(v_Year,4) = 0 AND MOD(v_Year,100) <> 0) OR MOD(v_Year,400) = 0 THEN
v_Day := '29';
ELSE
v_Day := '28';
END IF;
END IF;
v_Str_Date := v_Year||v_Month||v_Day||v_Time;
RETURN TO_DATE(v_Str_Date,'YYYY-MM-DD HH24:MI:SS');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' , '||SUBSTR(SQLERRM,1,200));
RETURN NULL;
END My_Add_Months;
in_Date IN DATE,
in_Balance IN INTEGER
) RETURN DATE
/* 实现ORACLE函数Add_Month功能
*/
AS v_Str_Date VARCHAR2(14);
v_Year VARCHAR2(4);
v_Month VARCHAR2(2);
v_Day VARCHAR2(2);
v_Time VARCHAR2(6);
v_Balance INTEGER;
BEGIN
v_Balance := TRUNC(in_Balance);
v_Str_Date := TO_CHAR(in_Date,'YYYYMMDDHH24MISS');
v_Year := SUBSTR(v_Str_Date,1,4);
v_Month := SUBSTR(v_Str_Date,5,2);
v_Day := SUBSTR(v_Str_Date,7,2);
v_Time := SUBSTR(v_Str_Date,9,14);
-- 处理年、月
IF v_Month + v_Balance > 12 THEN -- 加月跨年
v_Year := v_Year + FLOOR((v_Month + v_Balance)/12);
v_Month := MOD(v_Month + v_Balance,12);
ELSIF v_Month + v_Balance <= 0 THEN -- 减月跨年 v_Year := v_Year + FLOOR((v_Month + v_Balance)/12);
v_Month := MOD(v_Month + v_Balance,12) + 12;
ELSE
v_Month := v_Month + v_Balance;
END IF;
SELECT DECODE(LENGTH(v_Month),1,'0'||v_Month,v_Month) INTO v_Month FROM dual;
-- 处理天
IF v_Month IN ('04','06','09','11') AND v_Day = '31' THEN
v_Day := 30;
ELSIF v_Month = '02' AND v_Day IN ('29','30','31') THEN
IF (MOD(v_Year,4) = 0 AND MOD(v_Year,100) <> 0) OR MOD(v_Year,400) = 0 THEN
v_Day := '29';
ELSE
v_Day := '28';
END IF;
END IF;
v_Str_Date := v_Year||v_Month||v_Day||v_Time;
RETURN TO_DATE(v_Str_Date,'YYYY-MM-DD HH24:MI:SS');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' , '||SUBSTR(SQLERRM,1,200));
RETURN NULL;
END My_Add_Months;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货