定义包头接口
CREATE OR REPLACE PACKAGE WB_YGKH_PCK_TEST
AS
-- Package header
PROCEDURE TEST(myName IN varchar2);
PROCEDURE testIfElse(age IN number);
END WB_YGKH_PCK_TEST;定义包体实现
CREATE OR REPLACE PACKAGE BODY WB_YGKH_PCK_TEST
AS
-- Package body
PROCEDURE TEST(myName IN varchar2) IS
BEGIN
dbms_output.put_line('我的名字叫'||myName);
END ;
PROCEDURE testIfElse(age IN number) IS
BEGIN
IF(age> 30) THEN
dbms_output.put_line ('我已经超过30岁了');
ELSE
if(age < 10) THEN
dbms_output.put_line('我还是个小学生');
elsif (age = 18 or age = 19) then
dbms_output.put_line('这两个年龄你可能会遇到人生中最重要的人');
elsif (age >= 10 and age < 20) THEN
dbms_output.put_line ('10-20岁时人生最关键的时候');
else
dbms_output.put_line('大学毕业出来参加工作了');
END IF;
END IF;
END ;
END WB_YGKH_PCK_TEST;
CREATE OR REPLACE PACKAGE BODY CMES_RESULT_OA_PCK_JXP IS
/******************************************************************************
名称 CMES_MONTHREPORT_MGER_SCORE
创建日期：2022-09-06
创建作者：蒋学屏
程序功能：公司客户经理评价得分汇总表（单客户经理）月报汇总报表生成
******************************************************************************/
PROCEDURE CMES_MONTHREPORT_MGER_SCORE(I_RPT_DATE IN VARCHAR2,
O_RETCODE OUT VARCHAR2,
O_RETMSG OUT VARCHAR2
) IS
v_last_rpt_date varchar(8);-- 上次评分日期
v_temp_date DATE; -- 校验日期格式
BEGIN
O_RETCODE := 0;
O_RETMSG := '执行成功!';
-- 参数校验
SELECT TO_DATE(I_RPT_DATE,'yyyymmdd') INTO v_temp_date FROM dual;
IF v_temp_date IS NOT NULL then
select max(rpt_date) INTO v_last_rpt_date FROM CMES_EVLT_MGER_SCORE WHERE rpt_date < I_RPT_DATE;
--删除重新跑数当月的数据
DELETE from CMES_EVLT_MGER_SCORE T WHERE T.RPT_DATE = I_RPT_DATE;
INSERT INTO STATIST.CMES_EVLT_MGER_SCORE
(RPT_DATE, USER_NAME, USER_ID, USER_TYPE, MBRNO, MBRNAME, BRNO, BRNAME,
SCORE, SCORE_YLNL, SCORE_KPI, SCORE_ZLZXL, SORT_ZH,SORT_ZH_BSQ,SORT_FH,SORT_FH_BSQ)
SELECT I_RPT_DATE AS RPT_DATE ,t3.userName,t3.userId,'1' AS USER_TYPE ,t3.bankno,t3.bankname,t3.brno,t3.brname,
t1.score,t2.SCORE_YLNL,t2.SCORE_KPI,t2.SCORE_ZLZXL,
t1.SORT_ZH,
CASE WHEN t4.sort_zh_bsq IS NULL THEN t1.sort_zh ELSE t4.sort_zh_bsq-t1.sort_zh END AS sort_zh_bsq,
t1.SORT_FH,
CASE WHEN t4.sort_fh_bsq IS NULL THEN t1.sort_fh ELSE t4.sort_fh_bsq-t1.sort_fh END AS sort_fh_bsq
FROM (
select dcms.mbrno,ghid,sum(score) AS SCORE,
row_number() over (order by sum(score) desc) AS SORT_FH,
row_number() OVER (Partition By dcms.mbrno Order By Sum(score) Desc) AS SORT_ZH
FROM dgyj_cust_mger_score dcms where dcms.report_id ='1'
group BY dcms.mbrno,dcms.ghid
) t1
LEFT JOIN (
select ghid,
-- 盈利能力得分
sum (CASE WHEN index_id >=100001 AND index_id <=100004 then nvl(score,0) ELSE 0 END) AS SCORE_YLNL,
-- KPI得分
sum (CASE WHEN index_id >=100005 AND index_id <=100027 then nvl(score,0) ELSE 0 END) AS SCORE_KPI,
-- 战略执行力得分 (指标参数缺失)
sum (CASE WHEN index_id = 100001 then nvl(score,0) ELSE 0 END) AS SCORE_ZLZXL
FROM dgyj_cust_mger_score dcms where dcms.report_id ='1'
GROUP BY ghid
) t2
ON t1.ghid = t2.ghid
LEFT JOIN (
SELECT mg.userId,mg.userName,mg.bankno,mg.brno,mg.bankname,mg.brname,mg.title FROM cust_mger_msg mg
) t3
ON t1.ghid = t3.userId
LEFT JOIN (
select user_id ,sort_zh AS sort_zh_bsq ,sort_fh AS sort_fh_bsq FROM CMES_EVLT_MGER_SCORE WHERE rpt_date = v_last_rpt_date
) t4 ON t1.ghid = t4.user_id
where t1.mbrno IS NOT NULL
ORDER BY t1.ghid ;
ELSE
O_RETCODE := '-1';
O_RETMSG := '执行失败,日期参数不合法！';
END IF;
EXCEPTION WHEN OTHERS THEN
O_RETCODE := '-1';
O_RETMSG := '执行失败';
END;
PROCEDURE CMES_MONTHREPORT_MGER_SCORE_YH(I_RPT_DATE IN VARCHAR2,
O_RETCODE OUT VARCHAR2,
O_RETMSG OUT VARCHAR2
) IS
V_RPT_DATE varchar(8); -- 任务跑批日期
MAX_RPT_DATE varchar(8); -- 原始数据日期
V_SCORE NUMBER; -- 总分
V_YLNL NUMBER; -- 盈利能力
YLNL_GJS NUMBER; -- 盈利能力计算每个管户对应的管家
V_SCORE_YLNL NUMBER; -- 盈利能力得分
V_SCORE_KPI NUMBER; -- KPI得分
V_SCORE_ZLZXL NUMBER; -- 战略执行力得分
V_SORT_FH_BSQ NUMBER; -- 分行比上期
V_SORT_ZH_BSQ NUMBER; -- 支行比上期
V_USER_NAME VARCHAR2(200); -- 用户名
V_USER_ID VARCHAR2(100); -- 统一认证号
V_MBRNO VARCHAR2(500); -- 支行号
V_MBRNAME VARCHAR2(500); -- 支行名称
V_BRNO VARCHAR2(500); -- 网点号
V_BRNAME VARCHAR2(500); -- 网点名称
V_COUNT VARCHAR2(500);
BEGIN
O_RETCODE := 0;
O_RETMSG := '执行成功!';
IF (I_RPT_DATE IS NOT NULL) THEN
V_RPT_DATE := I_RPT_DATE;
ELSE
SELECT to_char(sysdate,'yyyymmdd') into V_RPT_DATE FROM dual;
END IF;
-- 找到最近任务跑批原始日期
SELECT MAX(RPT_DATE) INTO MAX_RPT_DATE FROM dgyj_cust_mger_score WHERE REPORT_ID = 1 AND RPT_DATE <= V_RPT_DATE;
--删除重新跑数当月的数据
DELETE from CMES_EVLT_MGER_SCORE T WHERE T.RPT_DATE = I_RPT_DATE;
-- 按人生成数据
FOR CUR_LIST IN (SELECT distinct t.ghid AS GHID FROM dgyj_cust_mger_score t WHERE t.rpt_date = MAX_RPT_DATE ) loop
V_USER_ID:= '';
V_USER_NAME := '';
V_MBRNO := '';
V_MBRNAME:= '';
V_BRNO := '';
V_BRNAME := '';
select
nvl(sum (CASE WHEN index_id >=100001 AND index_id <=100004 then nvl(score,0) ELSE 0 END),0) AS SCORE_YLNL,
nvl(sum (CASE WHEN index_id >=100005 AND index_id <=100027 then nvl(score,0) ELSE 0 END),0) AS SCORE_KPI,
nvl(sum (CASE WHEN index_id = 100001 then nvl(score,0) ELSE 0 END),0) AS SCORE_ZLZXL
into V_SCORE_YLNL,V_SCORE_KPI,V_SCORE_ZLZXL
FROM dgyj_cust_mger_score dcms where dcms.report_id ='1' AND rpt_date = MAX_RPT_DATE
AND GHID = CUR_LIST.GHID;
-- 总分
V_SCORE := V_SCORE_YLNL+V_SCORE_KPI+V_SCORE_ZLZXL;
V_YLNL := 0;
--盈利能力 管家对应管户的盈利总收入
for CUR_CUST_LIST IN (SELECT dir.CINO as cino ,NVL(SUM(NVL(dir.VALUE,0)),0) AS zl FROM DGYJ_INDEX_RLT dir WHERE DIR.index_name
IN ('BIZ_INCOME','CK_CONTRI','DK_CONTRI','MID_INCOME')
AND dir.CINO IN (
SELECT CINO FROM CUST_MGER_LIST cml WHERE cml.GHID = CUR_LIST.GHID
)GROUP BY dir.CINO) LOOP
-- 查找管户对应的管家数
SELECT COUNT(1) into YLNL_GJS FROM CUST_MGER_LIST cml WHERE cml.CINO = CUR_CUST_LIST.cino;
-- 管户总量/管家数
V_YLNL := V_YLNL+CUR_CUST_LIST.ZL/YLNL_GJS;
END LOOP;
-- 获取管家信息
SELECT COUNT(1) into V_COUNT FROM CUST_MGER_MSG cmm WHERE USERID = CUR_LIST.GHID;
IF (V_COUNT > 0) THEN
SELECT USERID,USERNAME,BANKNO,BANKNAME,BRNO,BRNAME into
V_USER_ID,V_USER_NAME,V_MBRNO,V_MBRNAME,V_BRNO,V_BRNAME
FROM CUST_MGER_MSG cmm WHERE USERID = CUR_LIST.GHID ;
END IF;
-- 生成得分统计数据
insert into cmes_evlt_mger_score
(rpt_date, user_name, user_id, user_type, mbrno, mbrname, brno, brname, score,
score_ylnl, ylnl, score_kpi, score_zlzxl)
values
(v_rpt_date, v_user_name, v_user_id, '1', v_mbrno, v_mbrname, v_brno, v_brname, v_score,
v_score_ylnl, v_ylnl, v_score_kpi, v_score_zlzxl);
END LOOP;
-- 更新本次分行和支行排名信息
for CUR_SCORE_LIST IN (SELECT cems.USER_ID,CEMS.rpt_date,SCORE,
row_number() over (order by score desc) AS SORT_FH,
row_number() OVER (Partition By cems.mbrno Order By score Desc) AS SORT_ZH
FROM CMES_EVLT_MGER_SCORE cems WHERE RPT_DATE = v_rpt_date)LOOP
-- 上次跑数排名信息
V_SORT_FH_BSQ := 0;
V_SORT_ZH_BSQ := 0;
SELECT count(1) into V_COUNT FROM CMES_EVLT_MGER_SCORE WHERE user_id=CUR_SCORE_LIST.user_id
AND RPT_DATE = (SELECT MAX(s.RPT_DATE)
FROM CMES_EVLT_MGER_SCORE s WHERE s.RPT_DATE < V_RPT_DATE);
IF(V_COUNT > 0) THEN
SELECT SORT_FH,SORT_ZH into V_SORT_FH_BSQ,V_SORT_ZH_BSQ FROM CMES_EVLT_MGER_SCORE WHERE user_id=CUR_SCORE_LIST.user_id
AND RPT_DATE = (SELECT MAX(s.RPT_DATE) FROM CMES_EVLT_MGER_SCORE s WHERE s.RPT_DATE < V_RPT_DATE);
-- 更新排名信息 (上期排名-本期排名)
UPDATE CMES_EVLT_MGER_SCORE SET SORT_FH =CUR_SCORE_LIST.SORT_FH,SORT_ZH = CUR_SCORE_LIST.SORT_ZH,
SORT_FH_BSQ = V_SORT_FH_BSQ-CUR_SCORE_LIST.SORT_FH,
SORT_ZH_BSQ = V_SORT_ZH_BSQ-CUR_SCORE_LIST.SORT_ZH
WHERE rpt_date =CUR_SCORE_LIST.rpt_date AND user_id =CUR_SCORE_LIST.user_id
AND score = CUR_SCORE_LIST.score;
else
-- 更新排名信息 （无上期排名）
UPDATE CMES_EVLT_MGER_SCORE SET SORT_FH =CUR_SCORE_LIST.SORT_FH,SORT_ZH = CUR_SCORE_LIST.SORT_ZH,
SORT_FH_BSQ = CUR_SCORE_LIST.SORT_FH,
SORT_ZH_BSQ = CUR_SCORE_LIST.SORT_ZH
WHERE rpt_date =CUR_SCORE_LIST.rpt_date AND user_id =CUR_SCORE_LIST.user_id
AND score = CUR_SCORE_LIST.score;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
O_RETCODE := '-1';
O_RETMSG := '执行失败' || SQLERRM;
END;
END CMES_RESULT_OA_PCK_JXP;
CREATE OR REPLACE PACKAGE CMES_RESULT_OA_PCK_JXP IS
TYPE REF_CS IS REF CURSOR;
/******************************************************************************
名称 CMES_MONTHREPORT_MGER_SCORE
创建日期：2022-09-06
创建作者：蒋学屏
程序功能：公司客户经理评价得分汇总表（单客户经理）月报汇总报表生成
******************************************************************************/
PROCEDURE CMES_MONTHREPORT_MGER_SCORE(I_RPT_DATE IN VARCHAR2,
O_RETCODE OUT VARCHAR2,
O_RETMSG OUT VARCHAR2
);
PROCEDURE CMES_MONTHREPORT_MGER_SCORE_YH(I_RPT_DATE IN VARCHAR2,
O_RETCODE OUT VARCHAR2,
O_RETMSG OUT VARCHAR2
);
END CMES_RESULT_OA_PCK_JXP;
oracle 分割字符串，以逗号分割返回列表
SELECT
regexp_substr('aaa,bbb,ccc', '[^,]+', 1, LEVEL) NAME_VAL
FROM
dual
CONNECT BY
LEVEL <= LENGTH('aaa,bbb,ccc')-LENGTH(REPLACE('aaa,bbb,ccc', ','))+ 1;
-- demo
-- 逗号分割字符串，拼接in查询条件
BEGIN
V_IN_CONDITION := '(';
FOR INDEX_LIST IN (SELECT regexp_substr(i_index_id, '[^,]+', 1, LEVEL) AS NAME_VAL FROM dual CONNECT BY
LEVEL <= LENGTH(i_index_id)-LENGTH(REPLACE(i_index_id, ','))+ 1) LOOP
IF LENGTH(V_IN_CONDITION)>1 THEN
V_IN_CONDITION := V_IN_CONDITION ||','||'''' || INDEX_LIST.NAME_VAL || '''';
ELSE
V_IN_CONDITION := V_IN_CONDITION || '''' || INDEX_LIST.NAME_VAL || '''';
END IF;
END LOOP;
V_IN_CONDITION := V_IN_CONDITION ||')';
END; Oracle 存过分页编写
PROCEDURE PROC_TURNPAGE(I_OPERNAME IN VARCHAR2, --操作名称
I_PAGESIZES IN NUMBER, --每页显示记录数
I_PAGENOW IN NUMBER, --当前页数
I_QUERY_SQL IN VARCHAR2, --查询语句
O_RETCODE OUT VARCHAR2, --返回标志
O_RETMSG OUT VARCHAR2, --返回信息
O_ROWNUMS OUT NUMBER, --总记录数
O_PAGENUM OUT NUMBER, --总页数
O_RESULTLIST OUT SYS_REFCURSOR --返回记录结果
) IS
V_SQLSTR VARCHAR2(9999);
--V_SQLSTR long;
V_BEGNUM NUMBER;
V_ENDNUM NUMBER;
BEGIN
--计算总记录数
EXECUTE IMMEDIATE 'select count(1) from (' || I_QUERY_SQL || ' ) '
INTO O_ROWNUMS;
IF I_PAGESIZES IS NOT NULL AND I_PAGENOW IS NOT NULL THEN
--计算每页的开始行
V_BEGNUM := (I_PAGENOW - 1) * I_PAGESIZES + 1;
--计算每页的结束行
V_ENDNUM := V_BEGNUM + I_PAGESIZES - 1;
--计算总页数
O_PAGENUM := CEIL(NVL(O_ROWNUMS, 0) / NVL(I_PAGESIZES, 0));
V_SQLSTR := 'select tmp1.*
from (select rownum rn,tmp.*
from(' || I_QUERY_SQL ||
' ) tmp) tmp1 where rn between ' || V_BEGNUM || ' and ' ||
V_ENDNUM;
ELSE
V_SQLSTR := I_QUERY_SQL;
END IF;
OPEN O_RESULTLIST FOR V_SQLSTR;
O_RETCODE := 0;
O_RETMSG := I_OPERNAME || '成功';
EXCEPTION
WHEN OTHERS THEN
O_RETCODE := 1;
O_RETMSG := I_OPERNAME || '失败，sqlcode=' || SQLCODE || '，sqlerrm=' ||
SUBSTRB(SQLERRM, 1, 500);
END;
--分页，每页展示几条,当前页数作为入参，总条数作为出参
PROC_TURNPAGE('待审批列表查询',
--操作名称
I_PAGESIZES,
--每页显示记录数
I_PAGENOW,
--当前页数
V_QUERY,
--查询语句
O_RETCODE,
--返回标志
O_RETMSG,
--返回信息
O_ROWNUMS,
--总记录数
O_PAGENUM,
--总页数
O_RESULTLIST
--返回记录结果
);
PROCEDURE PROC_SZKQ_LOG( /*i_message In Varchar2 ,*/ --日志信息
I_PROCNAME IN VARCHAR2,
I_RETCODE IN VARCHAR2,
I_RETMSG IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SZKQ_LOG
(WORKDATE, WORKTIME, PROCNAME, RETCODE, RETMSG)
VALUES
(TO_CHAR(SYSDATE, 'yyyymmdd'),
TO_CHAR(SYSDATE, 'hh24miss'),
I_PROCNAME,
I_RETCODE,
SUBSTR(I_RETMSG, 1, 4000));
COMMIT;
RETURN;
END;
1283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
