create or replace procedure GET_SETTLE_NCS_H(PARAM IN VARCHAR2,TOTALPAGES OUT INTEGER,DEBUGSTR OUT VARCHAR2,Or_Replist OUT SYS_REFCURSOR) is
Errcode INTEGER DEFAULT - 20001;
i_Return INTEGER;
s_Msg VARCHAR2(2000);
para varchar2(1000);--输入参数
wherestr varchar2(1000);
strsql varchar2(3000);
countstrsql varchar2(3000);
q_start varchar2(30);
q_limit varchar2(30);
q_sort varchar2(30);
q_dir varchar2(10);
sort_str varchar2(100);
page_str varchar2(100);
sucode varchar2(30); -- 用户编码
sgcode varchar2(30);
ent_c varchar2(30); sshsupid varchar(30);
SHOPID varchar(30);
SHEETID varchar(30);
BEGINDATE varchar(30);
ENDDATE varchar(30);
begin
sucode:='';
totalpages:=0;
para:=param;
q_start:='';
q_limit:='';
q_sort:='';
q_dir:='';
sort_str:='';
page_str:='';
wherestr:=' ';
debugstr:='';
sgcode:='';
ent_c:='';
sshsupid :='';
SHOPID :='';
SHEETID :='';
BEGINDATE :='';
ENDDATE :='';
Sp_Parsepara(Para, i_Return, s_Msg);
IF i_Return <> 1 THEN
Raise_Application_Error(Errcode, s_Msg);
END IF;
BEGIN
SELECT Stp.VALUE
INTO sucode
FROM Sys_Temppara Stp
WHERE Stp.NAME = 'sucode';
EXCEPTION
WHEN No_Data_Found THEN
Debugstr :='没有找到参数sucode';
Raise_Application_Error(Errcode,Debugstr );
END;
FOR r_Para IN (SELECT * FROM Sys_Temppara) LOOP
if r_Para.NAME='sucode' then
sucode:=r_Para.VALUE;
end if;
if r_Para.NAME='sort' then
q_sort:=r_Para.VALUE;
end if;
if r_Para.NAME='dir' then
q_dir:=r_Para.VALUE;
end if;
if r_Para.NAME='start' then
q_start:=r_Para.VALUE;
end if;
if r_Para.NAME='limit' then
q_limit:=r_Para.VALUE;
end if;
if r_Para.NAME='SHEETID' then
SHEETID:=r_Para.VALUE;
end if;
if r_Para.NAME='SHOPID' then
SHOPID:=r_Para.VALUE;
end if;
if r_Para.NAME='BEGINDATE' then
BEGINDATE:=r_Para.VALUE;
end if;
if r_Para.NAME='ENDDATE' then
ENDDATE:=r_Para.VALUE;
end if;
End LOOP;strsql := 'select ins_c from sm_user where su_c= ''' || sucode || '''';
execute immediate strsql into sgcode ;strsql := 'select SU_ENT_C from sm_user where su_c=''' || sucode || '''';
execute immediate strsql into ent_c ;IF (length(trim(q_sort))=0 or q_sort is null) THEN
sort_str:=' order by SHEETID ';
ELSE
sort_str:=' order by ' ||q_sort||' '||q_dir;
END IF;
IF (length(trim(q_limit))=0 or q_limit is null) THEN
page_str :=' where rowno >=0 and rowno<=500 ';
ELSE
select to_number(q_start, '999999') + to_number(q_limit, '999999') into Page_Str from dual;
page_str :=' where rowno >'|| q_start ||' and rowno <= '||Page_Str;
END IF;IF length(trim(sgcode))>0 and sgcode<>'0' THEN
wherestr:=' '||wherestr||'AND a.SGCODE='''||sgcode||'''';
END IF;
IF length(trim(ent_c))>0 and ent_c is not null THEN
wherestr:=' '||wherestr||' AND a.VENDERID= '''||ent_c||'''';
END IF;
IF length(trim(SHEETID))>0 and SHEETID is not null THEN
wherestr:=' '||wherestr||' AND a.SHEETID= '''||SHEETID||'''';
END IF;
IF length(trim(SHOPID))>0 and SHOPID is not null THEN
wherestr:=' '||wherestr||' AND a.SHOPID= '''||SHOPID||'''';
END IF;
IF ((length(trim(BEGINDATE))=0 and length(trim(ENDDATE))=0) or (BEGINDATE is null and ENDDATE is null)) THEN
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN sysdate-2 months AND sysdate ';
ELSE
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN '''||BEGINDATE||''' AND '''||ENDDATE||'''';
END IF;
countstrsql:='SELECT COUNT(*) from BILLHEAD a,INF_SHOP b where a.SGCODE=b.SGCODE and a.SHOPID=b.SHPCODE ';
countstrsql:=countstrsql||wherestr;
execute immediate countstrsql into totalpages;
strsql:=' a.SGCODE,a.SHEETID,a.SHOPID,b.SHPNAME as SSDMFID,a.VENDERID||''-''||c.SUPNAME as VENDERID,a.PAYABLEMONEY,a.KXMONEY,a.KXCASH,a.KXINVOICE,a.PAYABLEAMT,
a.CLOSEVALUE,a.UNJSVALUE,a.UNDQVALUE,a.HAVINVOICE,a.FLAG,a.PAYTYPE,a.BEGINDATE,a.ENDDATE,a.PLANPAYDATE,
a.EDITOR,a.EDITDATE,a.OPERATOR,a.CHECKER,a.CHECKDATE,a.PAYCHECKER,a.PAYCHECKDATE,a.RECEIVER,a.RECEIVDATE,
a.PAYER,a.PAYDATE,a.PAYAMT,a.PAYTAXAMT17,a.PAYTAXAMT13,a.PAYTAXAMT6 ,a.PAYTAXAMT4 ,a.PRINTCOUNT,
a.FPRINTCOUNT,a.NOTE,a.BANKID,a.OPENMONEY,a.CLOSEMONEY,a.BANKVALUE,a.PREMONEY,a.RECONCILER,a.RECONCILEDATE,
a.VSSCHECKED,a.GXTIME, c.SUPBANK,c.SUPACCOUNT,(a.PayableMoney - a.KXMoney + a.KXCash+a.OpenMoney - a.CloseMoney) as PAYMONEY,
(a.KXMoney - a.KXCash) as KXOFFSET,d.PREMONEY as YPREMONEY,(case WHEN a.TEMP1=''Y'' then ''已确认'' else ''未确认'' end) TEMP,a.TEMP1
from BILLHEAD a left join premoney d on a.SGCODE=d.SGCODE AND a.VENDERID=d.VENDERID,INF_SHOP b,INF_SUPINFO c
where a.SGCODE=b.SGCODE and a.SHOPID=b.SHPCODE and a.SGCODE=c.SUPSGCODE
AND a.VENDERID=c.SUPID and a.sheetid in (select DISTINCT sheetid from BILLHEADITEM where PayTypeSortID=''q'') ';
--需要自己修改
strsql:=strsql||wherestr;
strsql:='select * from (select rownum rowno,'
|| strsql
|| ') temp '
||page_str || Sort_Str;
DEBUGSTR := strsql;OPEN Or_Replist FOR strsql;
EXCEPTION
WHEN OTHERS THEN
Debugstr := 'GET_SETTLE_NCS_H 发生异常:' || SQLERRM;
raise_application_error(errcode, Debugstr);
end GET_SETTLE_NCS_H;
缺少关键字,我觉得是IF ((length(trim(BEGINDATE))=0 and length(trim(ENDDATE))=0) or (BEGINDATE is null and ENDDATE is null)) THEN
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN sysdate-2 months AND sysdate ';
ELSE
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN '''||BEGINDATE||''' AND '''||ENDDATE||'''';
END IF;错了... 高手帮忙改下
Errcode INTEGER DEFAULT - 20001;
i_Return INTEGER;
s_Msg VARCHAR2(2000);
para varchar2(1000);--输入参数
wherestr varchar2(1000);
strsql varchar2(3000);
countstrsql varchar2(3000);
q_start varchar2(30);
q_limit varchar2(30);
q_sort varchar2(30);
q_dir varchar2(10);
sort_str varchar2(100);
page_str varchar2(100);
sucode varchar2(30); -- 用户编码
sgcode varchar2(30);
ent_c varchar2(30); sshsupid varchar(30);
SHOPID varchar(30);
SHEETID varchar(30);
BEGINDATE varchar(30);
ENDDATE varchar(30);
begin
sucode:='';
totalpages:=0;
para:=param;
q_start:='';
q_limit:='';
q_sort:='';
q_dir:='';
sort_str:='';
page_str:='';
wherestr:=' ';
debugstr:='';
sgcode:='';
ent_c:='';
sshsupid :='';
SHOPID :='';
SHEETID :='';
BEGINDATE :='';
ENDDATE :='';
Sp_Parsepara(Para, i_Return, s_Msg);
IF i_Return <> 1 THEN
Raise_Application_Error(Errcode, s_Msg);
END IF;
BEGIN
SELECT Stp.VALUE
INTO sucode
FROM Sys_Temppara Stp
WHERE Stp.NAME = 'sucode';
EXCEPTION
WHEN No_Data_Found THEN
Debugstr :='没有找到参数sucode';
Raise_Application_Error(Errcode,Debugstr );
END;
FOR r_Para IN (SELECT * FROM Sys_Temppara) LOOP
if r_Para.NAME='sucode' then
sucode:=r_Para.VALUE;
end if;
if r_Para.NAME='sort' then
q_sort:=r_Para.VALUE;
end if;
if r_Para.NAME='dir' then
q_dir:=r_Para.VALUE;
end if;
if r_Para.NAME='start' then
q_start:=r_Para.VALUE;
end if;
if r_Para.NAME='limit' then
q_limit:=r_Para.VALUE;
end if;
if r_Para.NAME='SHEETID' then
SHEETID:=r_Para.VALUE;
end if;
if r_Para.NAME='SHOPID' then
SHOPID:=r_Para.VALUE;
end if;
if r_Para.NAME='BEGINDATE' then
BEGINDATE:=r_Para.VALUE;
end if;
if r_Para.NAME='ENDDATE' then
ENDDATE:=r_Para.VALUE;
end if;
End LOOP;strsql := 'select ins_c from sm_user where su_c= ''' || sucode || '''';
execute immediate strsql into sgcode ;strsql := 'select SU_ENT_C from sm_user where su_c=''' || sucode || '''';
execute immediate strsql into ent_c ;IF (length(trim(q_sort))=0 or q_sort is null) THEN
sort_str:=' order by SHEETID ';
ELSE
sort_str:=' order by ' ||q_sort||' '||q_dir;
END IF;
IF (length(trim(q_limit))=0 or q_limit is null) THEN
page_str :=' where rowno >=0 and rowno<=500 ';
ELSE
select to_number(q_start, '999999') + to_number(q_limit, '999999') into Page_Str from dual;
page_str :=' where rowno >'|| q_start ||' and rowno <= '||Page_Str;
END IF;IF length(trim(sgcode))>0 and sgcode<>'0' THEN
wherestr:=' '||wherestr||'AND a.SGCODE='''||sgcode||'''';
END IF;
IF length(trim(ent_c))>0 and ent_c is not null THEN
wherestr:=' '||wherestr||' AND a.VENDERID= '''||ent_c||'''';
END IF;
IF length(trim(SHEETID))>0 and SHEETID is not null THEN
wherestr:=' '||wherestr||' AND a.SHEETID= '''||SHEETID||'''';
END IF;
IF length(trim(SHOPID))>0 and SHOPID is not null THEN
wherestr:=' '||wherestr||' AND a.SHOPID= '''||SHOPID||'''';
END IF;
IF ((length(trim(BEGINDATE))=0 and length(trim(ENDDATE))=0) or (BEGINDATE is null and ENDDATE is null)) THEN
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN sysdate-2 months AND sysdate ';
ELSE
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN '''||BEGINDATE||''' AND '''||ENDDATE||'''';
END IF;
countstrsql:='SELECT COUNT(*) from BILLHEAD a,INF_SHOP b where a.SGCODE=b.SGCODE and a.SHOPID=b.SHPCODE ';
countstrsql:=countstrsql||wherestr;
execute immediate countstrsql into totalpages;
strsql:=' a.SGCODE,a.SHEETID,a.SHOPID,b.SHPNAME as SSDMFID,a.VENDERID||''-''||c.SUPNAME as VENDERID,a.PAYABLEMONEY,a.KXMONEY,a.KXCASH,a.KXINVOICE,a.PAYABLEAMT,
a.CLOSEVALUE,a.UNJSVALUE,a.UNDQVALUE,a.HAVINVOICE,a.FLAG,a.PAYTYPE,a.BEGINDATE,a.ENDDATE,a.PLANPAYDATE,
a.EDITOR,a.EDITDATE,a.OPERATOR,a.CHECKER,a.CHECKDATE,a.PAYCHECKER,a.PAYCHECKDATE,a.RECEIVER,a.RECEIVDATE,
a.PAYER,a.PAYDATE,a.PAYAMT,a.PAYTAXAMT17,a.PAYTAXAMT13,a.PAYTAXAMT6 ,a.PAYTAXAMT4 ,a.PRINTCOUNT,
a.FPRINTCOUNT,a.NOTE,a.BANKID,a.OPENMONEY,a.CLOSEMONEY,a.BANKVALUE,a.PREMONEY,a.RECONCILER,a.RECONCILEDATE,
a.VSSCHECKED,a.GXTIME, c.SUPBANK,c.SUPACCOUNT,(a.PayableMoney - a.KXMoney + a.KXCash+a.OpenMoney - a.CloseMoney) as PAYMONEY,
(a.KXMoney - a.KXCash) as KXOFFSET,d.PREMONEY as YPREMONEY,(case WHEN a.TEMP1=''Y'' then ''已确认'' else ''未确认'' end) TEMP,a.TEMP1
from BILLHEAD a left join premoney d on a.SGCODE=d.SGCODE AND a.VENDERID=d.VENDERID,INF_SHOP b,INF_SUPINFO c
where a.SGCODE=b.SGCODE and a.SHOPID=b.SHPCODE and a.SGCODE=c.SUPSGCODE
AND a.VENDERID=c.SUPID and a.sheetid in (select DISTINCT sheetid from BILLHEADITEM where PayTypeSortID=''q'') ';
--需要自己修改
strsql:=strsql||wherestr;
strsql:='select * from (select rownum rowno,'
|| strsql
|| ') temp '
||page_str || Sort_Str;
DEBUGSTR := strsql;OPEN Or_Replist FOR strsql;
EXCEPTION
WHEN OTHERS THEN
Debugstr := 'GET_SETTLE_NCS_H 发生异常:' || SQLERRM;
raise_application_error(errcode, Debugstr);
end GET_SETTLE_NCS_H;
缺少关键字,我觉得是IF ((length(trim(BEGINDATE))=0 and length(trim(ENDDATE))=0) or (BEGINDATE is null and ENDDATE is null)) THEN
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN sysdate-2 months AND sysdate ';
ELSE
wherestr:=' '||wherestr||' AND a.CHECKDATE BETWEEN '''||BEGINDATE||''' AND '''||ENDDATE||'''';
END IF;错了... 高手帮忙改下
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货