sql语句转化成存储过程 请大家帮我检查一下,看看又没有什么问题 主要是存储过程begin中sum那一块的条件格式,谢谢SQL语句:select co_code,
sum(case when receive_date between <%param("qsrq")%> and <%param("jsrq")%> then revenue_num else 0 end)*100 benyue,
sum(case when receive_date between <%param("sqsrq")%> and <%param("sjsrq")%> then revenue_num else 0 end)*100 sbenyue,
sum(case when receive_date<=<%param("jsrq")%> and fiscal=<%substring(param('jsrq'),1,4) %> then receive_num else 0 end) leijishu,
sum(case when receive_date<=<%param("sjsrq")%> and fiscal=<%substring(param('sjsrq'),1,4) %> then receive_num else 0 end) sleijishu
from v_nt_bill_baobiaoqushu
where co_code in (<%getcode(param('gsdm'))%>)
group by co_code
order by co_code
存储过程:CREATE OR REPLACE PROCEDURE BKA_SP_FSSRCXB
(
AMyFieldName varchar2,
ASourceStr varchar2,
ASplitStr varchar2,
AQSRQ varchar2,
AJSRQ varchar2,
ASQSRQ varchar2,
ASJSRQ varchar2,
AFISCAL varchar2,
ASFISCAL varchar2,
AResult out PKG_ANYIR9.AnyiResultSet
)AS
szTmp VARCHAR2(2000);
mystr varchar2(8000);
BEGIN
mystr :=GetLikeOrStr(AMyFieldName,ASourceStr,ASplitStr);
szTmp :=' select co_code,sum(case when receive_date between ''';
szTmp := szTmp || AQSRQ || ''' and ''' || AJSRQ || ''' then revenue_num else 0 end)*100 benyue,';
szTmp := szTmp || 'sum(case when receive_date between ''';
szTmp := szTmp || ASQSRQ || ''' and ''' || ASJSRQ || ''' then revenue_num else 0 end)*100 sbenyue,';
szTmp := szTmp || 'sum(case when receive_date<=''' || AJSRQ || '''';
szTmp := szTmp || ' and fiscal= ''' || AFISCAL || ''' then revenue_num else 0 end)*100 leijishu,';
szTmp := szTmp || 'sum(case when receive_date<=''' || ASJSRQ || '''';
szTmp := szTmp || ' and fiscal= ''' || ASFISCAL || ''' then revenue_num else 0 end)*100 sleijishu from v_nt_bill_baobiaoqushu '; szTmp := szTmp || ' where (' || mystr || ')';
szTmp := szTmp || ' group by co_code ';
szTmp := szTmp || ' order by co_code ';
AResult := PKG_ANYIR9.GetData(szTmp);
END BKA_SP_FSSRCXB;
sum(case when receive_date between <%param("qsrq")%> and <%param("jsrq")%> then revenue_num else 0 end)*100 benyue,
sum(case when receive_date between <%param("sqsrq")%> and <%param("sjsrq")%> then revenue_num else 0 end)*100 sbenyue,
sum(case when receive_date<=<%param("jsrq")%> and fiscal=<%substring(param('jsrq'),1,4) %> then receive_num else 0 end) leijishu,
sum(case when receive_date<=<%param("sjsrq")%> and fiscal=<%substring(param('sjsrq'),1,4) %> then receive_num else 0 end) sleijishu
from v_nt_bill_baobiaoqushu
where co_code in (<%getcode(param('gsdm'))%>)
group by co_code
order by co_code
存储过程:CREATE OR REPLACE PROCEDURE BKA_SP_FSSRCXB
(
AMyFieldName varchar2,
ASourceStr varchar2,
ASplitStr varchar2,
AQSRQ varchar2,
AJSRQ varchar2,
ASQSRQ varchar2,
ASJSRQ varchar2,
AFISCAL varchar2,
ASFISCAL varchar2,
AResult out PKG_ANYIR9.AnyiResultSet
)AS
szTmp VARCHAR2(2000);
mystr varchar2(8000);
BEGIN
mystr :=GetLikeOrStr(AMyFieldName,ASourceStr,ASplitStr);
szTmp :=' select co_code,sum(case when receive_date between ''';
szTmp := szTmp || AQSRQ || ''' and ''' || AJSRQ || ''' then revenue_num else 0 end)*100 benyue,';
szTmp := szTmp || 'sum(case when receive_date between ''';
szTmp := szTmp || ASQSRQ || ''' and ''' || ASJSRQ || ''' then revenue_num else 0 end)*100 sbenyue,';
szTmp := szTmp || 'sum(case when receive_date<=''' || AJSRQ || '''';
szTmp := szTmp || ' and fiscal= ''' || AFISCAL || ''' then revenue_num else 0 end)*100 leijishu,';
szTmp := szTmp || 'sum(case when receive_date<=''' || ASJSRQ || '''';
szTmp := szTmp || ' and fiscal= ''' || ASFISCAL || ''' then revenue_num else 0 end)*100 sleijishu from v_nt_bill_baobiaoqushu '; szTmp := szTmp || ' where (' || mystr || ')';
szTmp := szTmp || ' group by co_code ';
szTmp := szTmp || ' order by co_code ';
AResult := PKG_ANYIR9.GetData(szTmp);
END BKA_SP_FSSRCXB;
呵呵。
编译通过说明不是语法错误,应该是逻辑错误。
insert into v_nt_bill_baobiaoqushu (FISCAL, CO_CODE, CO_NAME, CHARGE_DATE, BANK_CODE, RECEIVER_BANK, CHARGE_ITEM_CODE, CHARGE_ITEM_NAME, REVENUE_NUM, NT_REVENUE_TYPE, NT_REVENUE_TYPE_NAME, ADMIN_METHOD, ADMIN_METHOD_NAME, PARENT_TYPE, PARENT_TYPE_NAME, RECEIVE_DATE)
values (2009, '025001', '1公安局滨湖分局本级', to_date('21-07-2009', 'dd-mm-yyyy'), '0001', '农行滨湖支行', '1200103', '往来款', 300000.00, 'amb_zck_item', '非固定暂存款', 'Outof_Budget', '纳入预算外专户管理', 'wanglai', '往来款', to_date('19-04-2010', 'dd-mm-yyyy'));insert into v_nt_bill_baobiaoqushu (FISCAL, CO_CODE, CO_NAME, CHARGE_DATE, BANK_CODE, RECEIVER_BANK, CHARGE_ITEM_CODE, CHARGE_ITEM_NAME, REVENUE_NUM, NT_REVENUE_TYPE, NT_REVENUE_TYPE_NAME, ADMIN_METHOD, ADMIN_METHOD_NAME, PARENT_TYPE, PARENT_TYPE_NAME, RECEIVE_DATE)
values (2009, '025001', '1公安局滨湖分局本级', to_date('28-07-2009', 'dd-mm-yyyy'), '0001', '1农业银行滨湖支行', '1200103', '往来款', 126092.00, 'amb_zck_item', '非固定暂存款', 'Outof_Budget', '纳入预算外专户管理', 'wanglai', '往来款', to_date('19-04-2010', 'dd-mm-yyyy'));insert into v_nt_bill_baobiaoqushu (FISCAL, CO_CODE, CO_NAME, CHARGE_DATE, BANK_CODE, RECEIVER_BANK, CHARGE_ITEM_CODE, CHARGE_ITEM_NAME, REVENUE_NUM, NT_REVENUE_TYPE, NT_REVENUE_TYPE_NAME, ADMIN_METHOD, ADMIN_METHOD_NAME, PARENT_TYPE, PARENT_TYPE_NAME, RECEIVE_DATE)
values (2009, '019002', '12环境监测站', to_date('08-08-2009', 'dd-mm-yyyy'), '0001', '1农业银行滨湖支行', '103043505', '环境监测服务费', 1270.00, 'General_Item', '一般行政事业性收费', 'Outof_Budget', '纳入预算外专户管理', 'General', '一般非税收入', to_date('14-09-2010', 'dd-mm-yyyy'));insert into v_nt_bill_baobiaoqushu (FISCAL, CO_CODE, CO_NAME, CHARGE_DATE, BANK_CODE, RECEIVER_BANK, CHARGE_ITEM_CODE, CHARGE_ITEM_NAME, REVENUE_NUM, NT_REVENUE_TYPE, NT_REVENUE_TYPE_NAME, ADMIN_METHOD, ADMIN_METHOD_NAME, PARENT_TYPE, PARENT_TYPE_NAME, RECEIVE_DATE)
values (2009, '020004', '12园林绿化工程管理处', to_date('19-10-2009', 'dd-mm-yyyy'), 'xzyh', '锡州农商行滨湖支行', '1200103', '往来款', 12250.00, 'amb_zck_item', '非固定暂存款', 'Outof_Budget', '纳入预算外专户管理', 'wanglai', '往来款', to_date('21-10-2009', 'dd-mm-yyyy'));