CREATE OR REPLACE PROCEDURE QD_MYDTJ
(BEGINDATE VARCHAR2,
ENDDATE VARCHAR2,
xz_name VARCHAR2,
xz_number VARCHAR2,
--BEGININTEVAL VARCHAR2,
--ENDINTEVAL VARCHAR2,
start1 IN NUMBER,
limit1 IN NUMBER,
AN_O_RET_CODE OUT NUMBER,
AC_O_RET_MSG OUT VARCHAR2,
COUNTALL OUT NUMBER,
CUR_RET OUT PKG_PUB_UTILS.REFCURSOR
)
as
limit2 NUMBER := 0;
BEGIN
AN_O_RET_CODE := 0;
AC_O_RET_MSG := "";
select COUNT (*) INTO COUNTALL
from (select count(userid) from tb_agent_satisfaction where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE group by userid);
limit2:=limit1;
if limit1<0 then
limit2:=COUNTALL+1;
end if;
--/* */
OPEN CUR_RET FOR
SELECT * FROM ( SELECT ROWNUM AS ROW_NUM,TT.* FROM (
select a.userid as RESULT_01,
e.name as RESULT_02,
to_char(NVL(a.count,0)) as RESULT_03,
to_char(NVL(b.count,0))as RESULT_04,
case to_char(b.count) when "0" then "0.00%" else to_char((NVL(b.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_05,
to_char(NVL(c.count,0)) as RESULT_06,
case to_char(c.count) when "0" then "0.00%" else to_char((NVL(c.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_07,
to_char(NVL(d.count,0)) as RESULT_08,
case to_char(d.count) when "0" then "0.00%" else to_char((NVL(d.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_09
from (select userid,count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE group by userid) a
left join (select userid,count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("1","2") group by userid) b on a.userid=b.userid
left join (select userid,count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("3","4") group by userid) c on a.userid=c.userid
left join (select userid,count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("5","6") group by userid) d on a.userid=d.userid
left join TB_SSO_OPER e on e.LOGINCODE = a.USERID and a.userid =xz_number and e.name= xz_name
union all
select "" as RESULT_01,
"" as RESULT_02,
to_char(NVL(a.count,0)) as RESULT_03,
to_char(NVL(b.count,0))as RESULT_04,
case to_char(b.count) when "0" then "0.00%" else to_char((NVL(b.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_05,
to_char(NVL(c.count,0)) as RESULT_06,
case to_char(c.count) when "0" then "0.00%" else to_char((NVL(c.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_07,
to_char(NVL(d.count,0)) as RESULT_08,
case to_char(d.count) when "0" then "0.00%" else to_char((NVL(d.count,0)/NVL(a.count,0))*100,"9990.99")||"%"end as RESULT_09
from (select count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE ) a,
(select count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("1","2") ) b ,
(select count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("3","4") ) c ,
(select count(*) as count from TB_AGENT_SATISFACTION where to_char(appraisetime, "yyyymmdd") between BEGINDATE and ENDDATE and satisfaction in ("5","6") ) d
) TT ) WHERE ROW_NUM BETWEEN start1 AND limit2 ;
EXCEPTION
WHEN OTHERS THEN
AN_O_RET_CODE := -1;
AC_O_RET_MSG := ":" || SQLCODE || CHR(13) || ":" || SQLERRM;
END QD_MYDTJ;
<!-- -->
<parameterMap id="parameter_report_QD_mydtj" class="java.util.HashMap" >
<parameter property="BEGIN_DATE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="END_DATE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="start" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="limit" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="zx_name" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="zx_number" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="ret_code" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="ret_message" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="countall" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
<parameter property="cur_ret" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="result_report_QD_mydtj"/>
</parameterMap>
<procedure id="report_QD_mydtj" resultClass="java.util.HashMap" parameterMap="parameter_report_QD_mydtj">
{call QD_MYDTJ(?,?,?,?,?,?,?,?,?,?) }
</procedure >
ActionServlet................................
action=========================[com.sunyard.csr.action.GetReportAction@31b75e54]
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>{END_DATE=20180629, zx_number=12, limit=10, zx_name=2, BEGIN_DATE=20180530, IS_EXPORT_TYPE=3, session=no, start=0, REPORT_TYPE=report_QD_mydtj, small=0, large=0.6}
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/sunyard/csr/dao/xml/UserCalReport.xml.
--- The error occurred while applying a parameter map.
--- Check the UserCalReport.parameter_report_QD_mydtj.
--- Check the statement (update procedure failed).
--- Cause: java.sql.SQLException: ORA-06550: 1 , 7 :
PLS-00905: WEBAGENT.QD_MYDTJ
ORA-06550: 1 , 7 :
PL/SQL: Statement ignored
xz_name and zx_number
have been added to
xml. The two variables xz_name VARCHAR2,
xz_number VARCHAR2, have been added to the stored procedure, and nothing else has changed from the sql, of and a.userid = xz_number and e.name = xz_name