CREATE OR REPLACE PROCEDURE check_in_out
(BEGINDATE IN VARCHAR2,
ENDDATE IN VARCHAR2,
AGENTID IN 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 a.dates as result_01,a.times as result_02,a.logincode as result_03,a.loginorout as result_04 from tb_sso_loginorout a
where a.logincode like "%"|| AGENTID||"%" and
dates >= BEGINDATE
and dates <= ENDDATE order by dates desc
);
limit2:=limit1;
if limit1<0 then
limit2:=COUNTALL;
end if;
--/* */
OPEN CUR_RET FOR
SELECT * FROM (SELECT ROWNUM AS ROW_NUM,WM.* FROM(
select a.dates as result_01,a.times as result_02,a.logincode as result_03,a.loginorout as result_04 from tb_sso_loginorout a
where a.logincode like "%"|| AGENTID||"%" and
dates >= BEGINDATE
and dates <= ENDDATE order by dates desc
)WM ) 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 check_in_out;
but when debugging, it shows
