/***存储过程名称：p_GroupScore说明：团队业绩统计调用主存储过程:手工调用CALL p_GroupScore('E000100')参数传递:测试在SQL*PLUS下进行VAR r refcursor;Exec p_GroupScore(32,'E000100',:r);print r;原型示例create or replace procedure p_test(p_cur out sys_refcursor)asbeginopen p_cur for select '001' XH,'自动化工程系' DM from DUALUNIONselect '002' XH,'计算机工程系' DM from DUALUNIONselect '003' XH,'机械工程系' DM from DUAL;end p_test;测试在SQL*PLUS下进行VAR r refcursor;Exec p_test(:r);print r;***/CREATE OR REPLACE PROCEDURE p_GroupScore(uISSUE NUMBER,uPID IN VARCHAR2,p_cur out sys_refcursor)ASBEGINIF (uPID = 'E000100') THEN-- 徐桂玲团队OPEN p_cur for SELECT FGRADE,FTYPE,SUM(FCOIN) FROM APP_DETAILS WHERE FISSUE = uISSUE AND FTYPE <> '购物币'ANDFPID IN (SELECT FCPID FROM APP_FCT WHERE FFPID = 'E888888' GROUP BY FCPIDUNIONSELECT 'E000100' FROM DUALUNIONSELECT 'E888888' FROM DUAL)GROUP BY FGRADE,FTYPEUNIONSELECT FGRADE,FTYPE,SUM(FCOIN) FROM APP_DETAILS WHERE FISSUE = uISSUE AND FTYPE = '店补'ANDFPID IN (SELECT FGETMID(FCPID) FROM (SELECT FCPID FROM APP_FCT WHERE FFPID = 'E888888' GROUP BY FCPIDUNIONSELECT 'E000100' FROM DUALUNIONSELECT 'E888888' FROM DUAL))GROUP BY FGRADE,FTYPEORDER BY FGRADE;ELSEOPEN p_cur for SELECT FGRADE,FTYPE,SUM(FCOIN) FROM APP_DETAILS WHERE FISSUE = uISSUE AND FTYPE <> '购物币'ANDFPID IN (SELECT FCPID FROM APP_FCT WHERE FFPID = uPID GROUP BY FCPIDUNIONSELECT uPID FROM DUAL)GROUP BY FGRADE,FTYPEUNIONSELECT FGRADE,FTYPE,SUM(FCOIN) FROM APP_DETAILS WHERE FISSUE = uISSUE AND FTYPE = '店补'ANDFPID IN (SELECT FGETMID(FCPID) FROM (SELECT FCPID FROM APP_FCT WHERE FFPID = uPID GROUP BY FCPIDUNIONSELECT uPID FROM DUAL))GROUP BY FGRADE,FTYPEORDER BY FGRADE;END IF;END p_GroupScore;
