1.SQL语句:直接利用SQL语句创建DB2函数
SET SCHEMA DB2INFO;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2INFO";
CREATE FUNCTION "DB2INFO"."CONVERTYMD"
("SSRC" VARCHAR(30),
"SMOD" VARCHAR(30),
"ICYCLE" INTEGER,
"STYPE" VARCHAR(2)
)
RETURNS VARCHAR(30)
SPECIFIC "DB2INFO"."SQL100205164226500"
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE i_pos INTEGER;
DECLARE s_res VARCHAR(30);
DECLARE s_mods VARCHAR(30);
DECLARE s_date VARCHAR(30);
DECLARE S_DAY VARCHAR(2);
DECLARE S_LAST_DAY VARCHAR(2);
DECLARE S_YYYYMM VARCHAR(30);
SET S_RES = UCASE(RTRIM(SSRC));
SET S_MODS = UCASE(RTRIM(SMOD));
SET I_POS = LOCATE(S_MODS,S_RES);
IF I_POS=0 THEN
RETURN NULL;
END IF;
IF UCASE(RTRIM(STYPE))=W THEN
SET S_DATE = RTRIM(CHAR(CURRENT DATE - (ICYCLE*7) DAYS,ISO));
RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
END IF;
IF UCASE(RTRIM(STYPE))=D THEN
SET S_DATE = RTRIM(CHAR(CURRENT DATE - ICYCLE DAYS,ISO));
RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
END IF;
IF(UCASE(RTRIM(STYPE))=H) THEN
SET S_DATE = RTRIM(CHAR(date(CURRENT TIMESTAMP - ICYCLE HOURS),ISO))||
|| RTRIM(CHAR(time(CURRENT TIMESTAMP - ICYCLE HOURS)));
RETURN REPLACE(S_RES,S_MODS,SUBSTR(S_DATE,1,LENGTH(S_MODS)));
END IF;
IF UCASE(RTRIM(STYPE))=M THEN
SET S_DAY = RTRIM(SUBSTR(S_RES,LENGTH(S_MODS)+2,2));
SET S_LAST_DAY = RTRIM(CHAR(DAY(CURRENT DATE - (ICYCLE-1) MONT
