CREATE OR REPLACE
FUNCTION "INDEXLX3YKLS"(guidStr IN VARCHAR2)
RETURN NUMBER
AS
V_SQL varchar2(500);-- sql语句
V_CNT NUMBER; -- 记录总条数
minDate DATE; -- 最小日期
maxDate DATE; -- 最大日期
months NUMBER;-- 【最小日期下月第一天】与【最大日期下月第一天】的月份差,即时间段个数
result NUMBER:=0;-- 单个时间段内(每3个月)最大开立数量
--定义游标存放结果集
--cursor cursor_data is
-- select t.ENTNAME,t.ESDATE from EDS_GS_FDDBRDWTZXX t where t.guid=guidStr
-- UNION ALL
-- select t2.ENTNAME,t2.ESDATE from EDS_GS_FDDBRZQTQYRZXX t2 where t2.guid=guidStr;
BEGIN
-- 功能:关联企业历史连续三月内最多开立数
-- 有问题请联系: zhuDH
-- 是否有记录,若无返回0
V_SQL := 'SELECT COUNT(1) FROM DUAL WHERE EXISTS ('
||'select 1 from EDS_GS_FDDBRDWTZXX t where t.guid=' ||guidStr
||' union all '
||'select 1 from EDS_GS_FDDBRZQTQYRZXX t2 where t2.guid='||guidStr
|| ')';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT = 0 THEN return 0; END IF;
-- 有记录
-- 获取最大和最小日期
SELECT to_date(max(p.ESDATE),'yyyy-MM-dd'),to_date(min(p.ESDATE),'yyyy-MM-dd') into maxDate,minDate from (
select t.ESDATE from EDS_GS_FDDBRDWTZXX t where t.guid=guidStr
UNION ALL
select t2.ESDATE from EDS_GS_FDDBRZQTQYRZXX t2 where t2.guid=guidStr
) p;
dbms_output.put_line(maxDate||'--'||minDate);
-- 获取【最小日期下月第一天】与【最大日期下月第一天】的月份差
SELECT last_day(maxDate)+1,last_day(minDate)+1 INTO maxDate,minDate from dual;
SELECT MONTHS_BETWEEN(maxDate,minDate) INTO months FROM dual;
dbms_output.put_line(maxDate||'--'||minDate||' 共:'||months);
-- 计算连续3月内最大投资企业开立数
SELECT max(count(1)) INTO result FROM
(SELECT
ROWNUM rum,
minDate + numtoyminterval(rownum - 3, 'MONTH') a1,
minDate + numtoyminterval(rownum, 'MONTH') a2
FROM DUAL
CONNECT BY ROWNUM <= months) z
JOIN (SELECT te.ENTNAME,TE.ESDATE from EDS_GS_FDDBRDWTZXX te where te.GUID=guidStr
UNION
SELECT te.ENTNAME,TE.ESDATE from EDS_GS_FDDBRZQTQYRZXX te where te.GUID=guidStr
) p1 ON to_date(p1.ESDATE,'yyyy-MM-dd') > z.a1 AND to_date(p1.ESDATE,'yyyy-MM-dd') <= z.a2
GROUP BY z.rum;
--for v_value in cursor_data loop
-- dbms_output.put_line(v_value.ENTNAME);
-- end loop;
return result;
END;