最近项目中写做报表有一些特殊的需求,用到了function,此处留个笔记。
--判断任务是否有报工
CREATE OR REPLACE FUNCTION WEATHER_DAILY_WORK(TWID IN INTEGER)
RETURN INTEGER IS
OUT_ISEXIT INTEGER;
BEGIN
IF TWID IS NULL THEN
RETURN 0;
END IF;
SELECT COUNT(1)
INTO OUT_ISEXIT
FROM TEST_BR_WORK TB, TEST_WORK_MANAG TW
WHERE TB.ID = TW.TMW_TW_ID
AND TB.TBR_MIANWORKID = TWID;
RETURN OUT_ISEXIT;
END WEATHER_DAILY_WORK;
/
--报表超链接 function
CREATE OR REPLACE FUNCTION GET_GRABTASK_IDS(GROUPNAME IN VARCHAR2,
TASKTYPE IN INTEGER,
STARTTIME IN VARCHAR2,
ENDTIME IN VARCHAR2)
RETURN VARCHAR2 AS
OUT_PUT VARCHAR2(4000);
OUT_NUM VARCHAR2(4000);
BEGIN
OUT_PUT := ' SELECT wm_concat(S.MS_EAPS_ID) FROM SNATCH_TASK S, V_PERSON_GROUP V, ' ||
' TEST_WORK T WHERE S.MS_CREATER = V.LOGINNAME ' ||
' AND S.MS_EAPS_ID = T.ID ';
IF GROUPNAME = 'all' THEN
OUT_PUT := OUT_PUT ||
' AND V.GROUPNAME in (''功能一组'',''功能二组'',''功能三组'') ';
ELSE
OUT_PUT := OUT_PUT || ' AND V.GROUPNAME = ''' || GROUPNAME || '''';
END IF;
IF TASKTYPE = 1 THEN
OUT_PUT := OUT_PUT || ' AND T.WK_PROCESS = ''已投产''';
END IF;
IF TASKTYPE = 2 THEN
OUT_PUT := OUT_PUT || ' AND s.ms_state = ''已认领'' ' ||
' and S.MS_EAPS_ID in (SELECT DISTINCT (TB.TBR_MIANWORKID) FROM TEST_BR_WORK TB, TEST_WORK_MANAG TW ' ||
' WHERE TB.ID = TW.TMW_TW_ID)';
END IF;
IF TASKTYPE = 3 THEN
OUT_PUT := OUT_PUT || ' AND s.ms_state = ''已认领'' ' ||
' and S.MS_EAPS_ID not in (SELECT DISTINCT (TB.TBR_MIANWORKID) FROM TEST_BR_WORK TB, TEST_WORK_MANAG TW ' ||
' WHERE TB.ID = TW.TMW_TW_ID)';
END IF;
IF TASKTYPE = 4 THEN
OUT_PUT := OUT_PUT || ' AND s.ms_state = ''已召回''';
END IF;
IF TASKTYPE = 5 THEN
OUT_PUT := OUT_PUT || ' AND s.ms_state = ''未开始''';
END IF;
IF TASKTYPE = 6 THEN
OUT_PUT := OUT_PUT || ' AND s.ms_state = ''已撤除''';
END IF;
IF STARTTIME IS NOT NULL THEN
OUT_PUT := OUT_PUT ||
' AND TO_CHAR(S.MS_PRIVATE_TIME, ''yyyy-mm-dd'') >= ''' ||
STARTTIME || '''';
END IF;
IF ENDTIME IS NOT NULL THEN
OUT_PUT := OUT_PUT ||
' AND TO_CHAR(S.MS_PRIVATE_TIME, ''yyyy-mm-dd'') <= ''' ||
ENDTIME || '''';
END IF;
EXECUTE IMMEDIATE OUT_PUT
INTO OUT_NUM;
RETURN OUT_NUM;
END GET_GRABTASK_IDS;
/
--调用函数返回至 并用逗号分成列
SELECT REGEXP_SUBSTR(C.A, '[^,]+', 1, LEVEL)
FROM (SELECT MAX(GET_GRABTASK_IDS(?,?,?,?)) A
FROM DUAL) C
CONNECT BY LEVEL < LENGTH(C.A)
注:在linux sqlplus中执行function时,一定记得在最后写 / 不然是无法执行的!!!