CREATE OR REPLACE FUNCTION getyj_zhibiao_value(p_name IN varchar2, p_index IN varchar2)
RETURN NUMBER
IS
-- 定义返回的指标值变量
v_result NUMBER;
-- 定义临时变量来存储查询到的指标值
v_index1 VARCHAR2(50);
v_index2 VARCHAR2(50);
v_index3 VARCHAR2(50);
v_index4 VARCHAR2(50);
v_index5 VARCHAR2(50);
v_index6 VARCHAR2(50);
v_index7 VARCHAR2(50);
v_index8 VARCHAR2(50);
v_index9 VARCHAR2(50);
v_value1 NUMBER;
v_value2 NUMBER;
v_value3 NUMBER;
v_value4 NUMBER;
v_value5 NUMBER;
v_value6 NUMBER;
v_value7 NUMBER;
v_value8 NUMBER;
v_value9 NUMBER;
BEGIN
-- 查询相应的姓名对应的指标和指标值
SELECT INDEX_code1,INDEX_code2,INDEX_code3,INDEX_code4,INDEX_code5,INDEX_code6,
INDEX_code7,INDEX_code8,INDEX_code9,nvl(sum(INDEX_value1),0),nvl(sum(INDEX_value2),0),
nvl(sum(INDEX_value3),0),nvl(sum(INDEX_value4),0),nvl(sum(INDEX_value5),0),nvl(sum(INDEX_value6),0),
nvl(sum(INDEX_value7),0),nvl(sum(INDEX_value8),0),nvl(sum(INDEX_value9),0)
INTO v_index1, v_index2, v_index3,v_index4,v_index5,v_index6,v_index7,v_index8,v_index9, v_value1, v_value2,v_value3
,v_value4,v_value5,v_value6,v_value7,v_value8,v_value9
FROM T_WG_KPI_RELEASE
WHERE GRID_CODE=p_name AND INDEX_CODE1 IS NOT NULL AND CUR_DATE <=to_char(sysdate,'yyyy-MM-dd') AND END_DATE >=to_char(sysdate,'yyyy-MM-dd')
GROUP by INDEX_code1,INDEX_code2,INDEX_code3,INDEX_code4,INDEX_code5,INDEX_code6,
INDEX_code7,INDEX_code8,INDEX_code9;
-- 根据输入的指标判断返回相应的指标值
IF v_index1 = p_index THEN
v_result := v_value1;
ELSIF v_index2 = p_index THEN
v_result := v_value2;
ELSIF v_index3 = p_index THEN
v_result := v_value3;
ELSIF v_index4 = p_index THEN
v_result := v_value4;
ELSIF v_index5 = p_index THEN
v_result := v_value5;
ELSIF v_index6 = p_index THEN
v_result := v_value6;
ELSIF v_index7 = p_index THEN
v_result := v_value7;
ELSIF v_index8 = p_index THEN
v_result := v_value8;
ELSIF v_index9 = p_index THEN
v_result := v_value9;
ELSE
-- 如果没有匹配到相应的指标,返回NULL
v_result := 0;
END IF;
RETURN v_result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- 如果没有找到相应的姓名,返回NULL
RETURN 0;
WHEN OTHERS THEN
-- 处理其他异常
RETURN 0;
END;
一个简单的Oracle函数
最新推荐文章于 2024-07-19 12:06:45 发布