declare
v_1 date := TO_DATE('20250301', 'YYYYMMDD');
v_start date;
v_end date := trunc(sysdate);
v_current date;
v_sql varchar2(5000);
begin
v_start :=trunc(v_1);
for i in 0..(v_end-v_start) loop
v_current := v_start+i;
v_sql :=
'merge into PURE_AVG_AMOUNT dest using
(with t1 as
(SELECT a.*,CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3 / 24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3 / 24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8 / 24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED=1
and a.IN_FORCE_DATE is not null),
t2 as (select distinct phone,country_code,NEW_DATE from t1
where TRUNC(NEW_DATE)=TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')and user_type_product=0),
t3 as (
select t1.NEW_DATE,t1.REPAYMENT_AMOUNT
from t2
left join t1
on t2.phone=t1.phone
and t2.country_code=t1.country_code
and t1.NEW_DATE>=t2.NEW_DATE
and t1.new_date is not null),
t4 as(
select week_num,TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') as pure_in_force_date,sum(REPAYMENT_AMOUNT/100)/count(1) AS avg_AMOUNT
from
(SELECT
REPAYMENT_AMOUNT,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7)+1 AS week_num,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 AS week_start,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 + 6 AS week_end
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'') AND TRUNC(ADD_MONTHS(SYSDATE, 12), "YYYY") - 1)
GROUP BY
week_num
ORDER BY week_num),
t5 as(select * from t4
pivot(sum(round(avg_AMOUNT,6)) for week_num IN
(1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周", 11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周", 21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周", 31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周", 41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周")))
select t5.* from t5) src on (dest.PURE_IN_FORCE_DATE=src.pure_in_force_date and dest.COUNTRY_CODE=src.COUNTRY_CODE WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",dest."第二周" = src."第二周",dest."第三周" = src."第三周",dest."第四周" = src."第四周",dest."第五周" = src."第五周",dest."第六周" = src."第六周",dest."第七周" = src."第七周",dest."第八周" = src."第八周",dest."第九周" = src."第九周",dest."第十周" = src."第十周",dest."第十一周" = src."第十一周",dest."第十二周" = src."第十二周",dest."第十三周" = src."第十三周",dest."第十四周" = src."第十四周",dest."第十五周" = src."第十五周",dest."第十六周" = src."第十六周",dest."第十七周" = src."第十七周",dest."第十八周" = src."第十八周",dest."第十九周" = src."第十九周",dest."第二十周" = src."第二十周",dest."第二十一周" = src."第二十一周",dest."第二十二周" = src."第二十二周",dest."第二十三周" = src."第二十三周",dest."第二十四周" = src."第二十四周",dest."第二十五周" = src."第二十五周",dest."第二十六周" = src."第二十六周",dest."第二十七周" = src."第二十七周",dest."第二十八周" = src."第二十八周",dest."第二十九周" = src."第二十九周",dest."第三十周" = src."第三十周",dest."第三十一周" = src."第三十一周",dest."第三十二周" = src."第三十二周",dest."第三十三周" = src."第三十三周",dest."第三十四周" = src."第三十四周",dest."第三十五周" = src."第三十五周",dest."第三十六周" = src."第三十六周",dest."第三十七周" = src."第三十七周",dest."第三十八周" = src."第三十八周",dest."第三十九周" = src."第三十九周",dest."第四十周" = src."第四十周",dest."第四十一周" = src."第四十一周",dest."第四十二周" = src."第四十二周",dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周","第二周","第三周","第四周","第五周","第六周","第七周","第八周","第九周","第十周","第十一周","第十二周","第十三周","第十四周","第十五周","第十六周","第十七周","第十八周","第十九周","第二十周","第二十一周","第二十二周","第二十三周","第二十四周","第二十五周","第二十六周","第二十七周","第二十八周","第二十九周","第三十周","第三十一周","第三十二周","第三十三周","第三十四周","第三十五周","第三十六周","第三十七周","第三十八周","第三十九周","第四十周","第四十一周","第四十二周","第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周",src."第二周",src."第三周",src."第四周",src."第五周",src."第六周",src."第七周",src."第八周",src."第九周",src."第十周",src."第十一周",src."第十二周",src."第十三周",src."第十四周",src."第十五周",src."第十六周",src."第十七周",src."第十八周",src."第十九周",src."第二十周",src."第二十一周",src."第二十二周",src."第二十三周",src."第二十四周",src."第二十五周",src."第二十六周",src."第二十七周",src."第二十八周",src."第二十九周",src."第三十周",src."第三十一周",src."第三十二周",src."第三十三周",src."第三十四周",src."第三十五周",src."第三十六周",src."第三十七周",src."第三十八周",src."第三十九周",src."第四十周",src."第四十一周",src."第四十二周",src."第四十三周"
))';
EXECUTE IMMEDIATE v_sql;
END LOOP;
end;
报[65000][6502] ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在 line 11 https://docs.oracle.com/error-help/db/ora-065 ...
最新发布
本文解释了在PLSQL中使用SELECT current_date FROM dual时，所获取的时间实际上是Oracle数据库服务器所在机器的时间，而非客户端本地时间的原因。
扫一扫
688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
