Oracle 自定义function笔记

最近项目中写做报表有一些特殊的需求，用到了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时，一定记得在最后写 / 不然是无法执行的！！！