KPI考核系统
KPI公式说明
普通公式：公式(100-(R-G)*5)*W，其中R是结果值，G是目标值，W是权重
分段公式：公式>=(R/G,1,1,0.6)*W*100，如上图所示，公式分为三部分。第一部分是‘>=’是标识符,第二部分括号内的‘R/G’，‘1’，‘1’，‘0.6’分别是参数1，参数2，参数3，第三部分是权重(W)和满分分数。其含义是如果R/G>=1则结果为1*W*100,否则结果为0.6*W*100。第二部分中的参数4可以是另一个公式。例如：>=(R/G,1,1,>=(R/G,0.75,0.6,0))*W*100
创建KPI明细考核项
-- auto-generated definition
create table PERFORMANCE_EVALUATION
(
ID NUMBER generated as identity,
KPI_ITME VARCHAR2(100),
KPI_INSTRUCTION VARCHAR2(4000),
W NUMBER,
G NUMBER,
R NUMBER,
KPI_SCORE_FORMULA CLOB,
KPI_GROUP VARCHAR2(50)
)
/
comment on table PERFORMANCE_EVALUATION is '绩效评估项'
/
comment on column PERFORMANCE_EVALUATION.KPI_ITME is '考核项'
/
comment on column PERFORMANCE_EVALUATION.KPI_INSTRUCTION is '指标说明'
/
comment on column PERFORMANCE_EVALUATION.W is '权重'
/
comment on column PERFORMANCE_EVALUATION.G is '目标值'
/
comment on column PERFORMANCE_EVALUATION.R is '结果值'
/
comment on column PERFORMANCE_EVALUATION.KPI_SCORE_FORMULA is '计算公式'
/
comment on column PERFORMANCE_EVALUATION.KPI_GROUP is 'KPI分组'
/
创建计算函数，可以改成触发器形式
分段公式拆解计算
create function apex_cul_spe_kpi(formula varchar)
return number
is
ls_char varchar(10);
ls_formula varchar(200);
ls_parma_1 varchar(20);
ls_param_2 varchar(20);
ls_param_3 varchar(20);
ls_param_4 varchar(100);
ls_res_1 number(10,2);
ls_res_2 number(10,2);
ls_res_3 number(10,2);
ls_res_4 number(10,2);
begin
-- 预处理
select substr(formula, instr(formula, '(') + 1, instr(formula, ')', -1, 1) - instr(formula, '(') - 1)
into ls_formula
from dual;
-- 截取判断条件
select substr(ls_formula, 1, instr(ls_formula, ',') - 1) into ls_parma_1 from dual;
select substr(ls_formula, instr(ls_formula, ',') + 1, instr(ls_formula, ',', 1, 2) - instr(ls_formula, ',') - 1)
into ls_param_2
from dual;
select substr(ls_formula, instr(ls_formula, ',', 1, 2) + 1,
instr(ls_formula, ',', 1, 3) - instr(ls_formula, ',', 1, 2) - 1)
into ls_param_3
from dual;
select substr(ls_formula, instr(ls_formula, ',', 1, 3) + 1) into ls_param_4 from dual;
-- 判断条件计算
select DBMS_AW.EVAL_NUMBER(ls_parma_1) into ls_res_1 from dual;
select DBMS_AW.EVAL_NUMBER(ls_param_2) into ls_res_2 from dual;
select DBMS_AW.EVAL_NUMBER(ls_param_3) into ls_res_3 from dual;
-- 第一个字符判断
select substr(formula, 1, 1) into ls_char from dual;
case ls_char
when '>' then -- 第二个字符判断
select substr(formula, 2, 1) into ls_char from dual;
if ls_char = '=' then
-- 计算分段
if sign(ls_res_1 - ls_res_2) >= 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
else
if sign(ls_res_1 - ls_res_2) > 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
end if;
when '<' then -- 第二个字符判断
select substr(formula, 2, 1) into ls_char from dual;
if ls_char = '=' then
-- 计算分段
if sign(ls_res_1 - ls_res_2) <= 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
else
if sign(ls_res_1 - ls_res_2) < 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
end if;
when '=' then -- 第二个字符判断
select substr(formula, 2, 1) into ls_char from dual;
if ls_char = '>' then
-- 计算分段
if sign(ls_res_1 - ls_res_2) >= 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
else
if sign(ls_res_1 - ls_res_2) <= 0 then
return ls_res_3;
else
select substr(ls_param_4, 1, 1) into ls_char from dual;
if ls_char = '>' or ls_char = '<' or ls_char = '=' then
return apex_cul_spe_kpi(ls_param_4);
else
select DBMS_AW.EVAL_NUMBER(ls_param_4) into ls_res_4 from dual;
return ls_res_4;
end if;
end if;
end if;
else return 0;
end case;
end;
/主计算函数，如果是普通公式直接计算，如果是嵌套执行apex_cul_spe_kpi
create FUNCTION apex_calculate_score(v_formula_a IN VARCHAR2, w IN NUMBER, r IN NUMBER, g IN NUMBER)
RETURN varchar2 IS
V_FORMULA varchar(100);
V_TEMP varchar(100);
V_SCORE number(10);
V_WEIGHT number(10, 2);
V_GOAL number(10,2);
V_CHAR varchar(1);
V_PARAM number(20, 2);
begin
-- 获取参数
V_FORMULA := v_formula_a;
V_GOAL := g;
V_WEIGHT := w;
-- 公式参数替换
select replace(V_FORMULA, 'W', V_WEIGHT) into V_FORMULA from dual;
select replace(V_FORMULA, 'G', V_GOAL) into V_FORMULA from dual;
select replace(V_FORMULA, 'R', r) into V_FORMULA from dual;
select substr(V_FORMULA, 1, 1) into V_CHAR from dual;
if V_CHAR = '>' or V_CHAR = '<' or V_CHAR = '=' then
select substr(V_FORMULA, 1, instr(V_FORMULA, ')',-1)) into V_TEMP from dual;
V_PARAM := APEX_CUL_SPE_KPI(V_TEMP);
select substr(V_FORMULA, instr(V_FORMULA, ')',-1)+1) into V_FORMULA from dual;
V_FORMULA := TO_CHAR(V_PARAM) || V_FORMULA;
end if;
-- return V_PARAM;
-- 解析公式并计算
select dbms_aw.eval_number(V_FORMULA) into V_SCORE from dual;
if V_SCORE < 0 then
V_SCORE := 0;
end if;
if V_SCORE > 100*V_WEIGHT then
V_SCORE := 100*V_WEIGHT;
end if;
RETURN V_SCORE;
end;
/执行运算
SELECT APEX_CALCULATE_SCORE('>=(R,G,W*100,W*100-(G-R))',0.2,80,100) FROM DUAL;