oracle存储过程,字符串sql语句执行与update返回值示例CREATE OR REPLACE PROCEDURE SP_BalanceLargeKind(
vFieldName in varchar, --大类结算表中字段名
vCompanyId in varchar, --区域或公司id
vCheckMonth in varchar,--结账月
vFilterStr in varchar, --附加筛选条件
retCnt out number
) is
s_sql varchar(5000) :='';
/**********************************************
update大类结算表
author: dingzh@
date: 2009-09-29
***********************************************/
begin
---------------------------------------
s_sql :='
update MM_LARGE_KIND_BALANCE B
set '||vFieldName ||' = (
select theMoney from (
select OB.STORE_CODE,PANYID,F_GetProductTopKindI d(PC.PRODUCT_KIND_ID) as TOP_KIND_ID,
S.MATERIAL_TYPE,S.TERMINAL_TYPE,S.PHASE_TYPE,S. MATERIAL_SOURCE,sum(OD.PRODUCT_MONEY) as theMoney
from MM_OPERATION_BILL OB,MM_OPERATION_DETAIL OD,MM _STORAGE S,MM_CATALOG_PRODUCCTS_REL PC
where OB.OPERATION_ID=OD.OPERATION_ID and OD.N_STOR AGE_ID=S.N_STORAGE_ID and S.PRODUCT_ID=PC.PRODUCT_ID
and PC.PRODUCT_CATLOG_TYPE=''1''
and OB.CHECK_MONTH='''||vCheckMonth ||''' '||vFilterSt r ||' '||
--根据要更新的大类结算表字段，取得相关状态与类型的料单' and exists(select 1 from MM_OPER_TYPE_RELATION R
where nvl(RGE_BALANCE_FIELD,'' '') like ''%['||vFieldName ||']%''
and R.OPER_TYPE=OB.OPER_TYPE and R.OPER_STATE=O B.OPER_STATE) '||
--根据公司取出所有相关仓库
' and exists(select 1 from MM_STORES_INFO where COMPAN
Y_ID like '''||vCompanyId ||'%''
and STORE_KIND=''1'' and STORE_BELONG=''1'' and ST ORE_STATE=1 and STORE_CODE=OB.STORE_CODE)
group by OB.STORE_CODE,PANYID,F_GetProductTopKin dId(PC.PRODUCT_KIND_ID),
S.MATERIAL_TYPE,S.TERMINAL_TYPE,S.PHASE_TYPE,S.MATER IAL_SOURCE
) H
where PANYID=PANYID and H.STORE_CODE=B.STO RE_CODE and H.TOP_KIND_ID=B.KIND_ID
and H.MATERIAL_TYPE=B.MATERIAL_TYPE and H.TERMINAL_TYPE =B.TERMINAL_TYPE
and H.PHASE_TYPE=B.PHASE_TYPE and H.MATERIAL_SOURCE=B. MATERIAL_SOURCE)
where exists(select 1 from MM_STORES_INFO where COMPANY_ID like '''||vCompanyId ||'%''
and STORE_KIND=''1'' and STORE_BELONG=''1'' and STORE_STATE =1 and STORE_CODE=B.STORE_CODE)
and B.CHECKOUT_MONTH='''||vCheckMonth ||'''
';
dbms_output.put_line(s_sql);
execute immediate s_sql;
retCnt:=sql%rowcount;
dbms_output.put_line(retCnt);
----------------------------------------
end SP_BalanceLargeKind;。