procedure acnt_add_nocmt_base(
money IN VARCHAR2,
userId IN VARCHAR2,
inId IN VARCHAR2,
inType IN VARCHAR2,
inRe IN VARCHAR2,
operId IN VARCHAR2,
tableAccount IN VARCHAR2,
tableAccount_his IN VARCHAR2,
outCode OUT INT,
errMsg OUT VARCHAR2
)
-- 帐户余额增加,非自动提交
-- create by lyf 08.09.16
AS
errCode number ;
newId VARCHAR2(20);
imoney_all number ;
before_m number;
after_m number;
ssql VARCHAR2(1000);
ssql1 VARCHAR2(1000);
ssql2 VARCHAR2(1000);
ssql3 VARCHAR2(1000);
begin
errCode := 1;
begin
ssql := 'select nvl(money_all,0) into imoney_all from'|| tableAccount ||
'where user_id=userId and status=0 and rownum<2';
execute immediate ssql;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;
before_m := imoney_all;
--修改余额
imoney_all := imoney_all + Money;
ssql1:='update'|| tableAccount ||'set money_all=imoney_all where user_id=userId;
execute immediate ssql1;
after_m := imoney_all ;
--日志
ssql2 :=' insert into log_account select * from'|| tableAccount ||'where user_id=userId and status=0';
execute immediate ssql2;
--记录
select seq_normal.NEXTVAL into newId from dual;
ssql2 :='insert into '|| tableAccount_his||' (id,user_id,create_time,money,types,ext_id,ext_type,Re,oper_id,pre_money,before_money,after_money) values(newId,userId,sysdate,money,''余额增加'',inId,inType,inRe,operId,imoney_all,before_m,after_m)';
execute immediate ssql3;
errCode := 0;
outCode := errCode;
exception when others then
errCode := 13;
outCode := errCode;
errMsg := sqlerrm;
end;
大家看看我这代码的动态存储过程错在哪里,插不进数据!谢谢了!
money IN VARCHAR2,
userId IN VARCHAR2,
inId IN VARCHAR2,
inType IN VARCHAR2,
inRe IN VARCHAR2,
operId IN VARCHAR2,
tableAccount IN VARCHAR2,
tableAccount_his IN VARCHAR2,
outCode OUT INT,
errMsg OUT VARCHAR2
)
-- 帐户余额增加,非自动提交
-- create by lyf 08.09.16
AS
errCode number ;
newId VARCHAR2(20);
imoney_all number ;
before_m number;
after_m number;
ssql VARCHAR2(1000);
ssql1 VARCHAR2(1000);
ssql2 VARCHAR2(1000);
ssql3 VARCHAR2(1000);
begin
errCode := 1;
begin
ssql := 'select nvl(money_all,0) into imoney_all from'|| tableAccount ||
'where user_id=userId and status=0 and rownum<2';
execute immediate ssql;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;
before_m := imoney_all;
--修改余额
imoney_all := imoney_all + Money;
ssql1:='update'|| tableAccount ||'set money_all=imoney_all where user_id=userId;
execute immediate ssql1;
after_m := imoney_all ;
--日志
ssql2 :=' insert into log_account select * from'|| tableAccount ||'where user_id=userId and status=0';
execute immediate ssql2;
--记录
select seq_normal.NEXTVAL into newId from dual;
ssql2 :='insert into '|| tableAccount_his||' (id,user_id,create_time,money,types,ext_id,ext_type,Re,oper_id,pre_money,before_money,after_money) values(newId,userId,sysdate,money,''余额增加'',inId,inType,inRe,operId,imoney_all,before_m,after_m)';
execute immediate ssql3;
errCode := 0;
outCode := errCode;
exception when others then
errCode := 13;
outCode := errCode;
errMsg := sqlerrm;
end;
大家看看我这代码的动态存储过程错在哪里,插不进数据!谢谢了!
ssql1:='update'|| tableAccount ||'set money_all=imoney_all where user_id=userId'; -- 少了个引号
ERROR 2>
ssql2 :=' insert into log_account select * from'|| tableAccount ||'where user_id=userId and status=0'; -- from后保持空格 e.g: SELECT * FROM DUAL;
ERROR 3>
COMMIT;
你试试看,我这边没有你的表,不好调试
create or replace procedure acnt_add_nocmt_base(
money IN VARCHAR2,
userId IN VARCHAR2,
inId IN VARCHAR2,
inType IN VARCHAR2,
inRe IN VARCHAR2,
operId IN VARCHAR2,
tableAccount IN VARCHAR2,
tableAccount_his IN VARCHAR2,
outCode OUT INT,
errMsg OUT VARCHAR2
)
-- 帐户余额增加,非自动提交
-- create by lyf 08.09.16
AS
errCode number ;
newId VARCHAR2(20);
imoney_all number ;
before_m number;
after_m number;
ssql VARCHAR2(1000);
ssql1 VARCHAR2(1000);
ssql2 VARCHAR2(1000);
ssql3 VARCHAR2(1000);
begin
errCode := 1;
begin
ssql := 'select nvl(money_all,0) from'|| tableAccount ||
'where user_id=userId and status=0 and rownum<2';
execute immediate ssql into imoney_all;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;
before_m := imoney_all;
--修改余额
imoney_all := imoney_all + to_number(Money);
ssql1:='update'|| tableAccount ||' set money_all='||imoney_all||' where user_id='''||userId||'''';
execute immediate ssql1;
after_m := imoney_all ;
--日志
ssql2 :=' insert into log_account select * from '|| tableAccount ||' where user_id='''||userId||''' and status=0';
execute immediate ssql2;
--记录
select seq_normal.NEXTVAL into newId from dual;
ssql2 :='insert into '|| tableAccount_his||' (id,user_id,create_time,money,types,ext_id,ext_type,Re,oper_id,pre_money,before_money,after_money)';
ssql2 := ssql2||' values('||newId||','''||userId|''','''||to_char(sysdate,'yyyy-mm-dd')|''','||money||','''||'余额增加'|''',';
ssql2 := ssql2||''''||inId||''','''||inType||''','''||inRe||''','''||operId||''','||imoney_all||','||before_m||','||after_m||')';
execute immediate ssql3;
errCode := 0;
outCode := errCode;
exception when others then
errCode := 13;
outCode := errCode;
errMsg := sqlerrm;
end;
PROCEDURE set_rid(p_id Number,
x_rid Number,
x_table_name varchar2,
sqlWhere varchar2) as
x_str varchar(200);
begin
x_str := 'update ' || x_table_name || ' set request_id =' ||
to_char(x_request_id) || ' where batch_id=' || to_char(p_id) || ' ' ||
sqlWhere;
execute immediate x_str;
commit;
end;
errCode := 1;
begin
ssql := 'select nvl(money_all,0) from'|| tableAccount ||
'where user_id='''||userId||''' and status=0 and rownum<2';
execute immediate ssql into imoney_all;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;这个也得换成上面的
create or replace procedure acnt_add_nocmt_base(
money IN VARCHAR2,
userId IN VARCHAR2,
inId IN VARCHAR2,
inType IN VARCHAR2,
inRe IN VARCHAR2,
operId IN VARCHAR2,
tableAccount IN VARCHAR2,
tableAccount_his IN VARCHAR2,
outCode OUT INT,
errMsg OUT VARCHAR2
)
-- 帐户余额增加,非自动提交
-- create by lyf 08.09.16
AS
errCode number ;
newId VARCHAR2(20);
imoney_all number ;
before_m number;
after_m number;
ssql VARCHAR2(1000);
ssql1 VARCHAR2(1000);
ssql2 VARCHAR2(1000);
ssql3 VARCHAR2(1000);
begin
errCode := 1;
begin
ssql := 'select nvl(money_all,0) from'|| tableAccount ||
'where user_id='''||userId||''' and status=0 and rownum<2';
execute immediate ssql into imoney_all;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;
before_m := imoney_all;
--修改余额
imoney_all := imoney_all + to_number(Money);
ssql1:='update'|| tableAccount ||' set money_all='||imoney_all||' where user_id='''||userId||'''';
execute immediate ssql1;
after_m := imoney_all ;
--日志
ssql2 :=' insert into log_account select * from '|| tableAccount ||' where user_id='''||userId||''' and status=0';
execute immediate ssql2;
--记录
select seq_normal.NEXTVAL into newId from dual;
ssql2 :='insert into '|| tableAccount_his||' (id,user_id,create_time,money,types,ext_id,ext_type,Re,oper_id,pre_money,before_money,after_money)';
ssql2 := ssql2||' values('||newId||','''||userId|''','''||to_char(sysdate,'yyyy-mm-dd')|''','||money||','''||'余额增加'|''',';
ssql2 := ssql2||''''||inId||''','''||inType||''','''||inRe||''','''||operId||''','||imoney_all||','||before_m||','||after_m||')';
execute immediate ssql3;
errCode := 0;
outCode := errCode;
exception when others then
errCode := 13;
outCode := errCode;
errMsg := sqlerrm;
end;
money IN VARCHAR2,
userId IN VARCHAR2,
inId IN VARCHAR2,
inType IN VARCHAR2,
inRe IN VARCHAR2,
operId IN VARCHAR2,
tableAccount IN VARCHAR2,
tableAccount_his IN VARCHAR2,
outCode OUT INT,
errMsg OUT VARCHAR2
)
-- 帐户余额增加,非自动提交
-- create by lyf 08.09.16
AS
errCode number ;
newId VARCHAR2(20);
imoney_all number ;
before_m number;
after_m number;
ssql VARCHAR2(1000);
ssql1 VARCHAR2(1000);
ssql2 VARCHAR2(1000);
ssql3 VARCHAR2(1000);
begin
errCode := 1;
begin
ssql := 'select nvl(money_all,0) from'|| tableAccount ||
'where user_id='''||userId||''' and status=0 and rownum<2';
execute immediate ssql into imoney_all;
exception when no_data_found then
outCode := 2;
errMsg := '未找到对应帐户信息';
return ;
end;
before_m := imoney_all;
--修改余额
imoney_all := imoney_all + to_number(Money);
ssql1:='update'|| tableAccount ||' set money_all='||imoney_all||' where user_id='''||userId||'''';
execute immediate ssql1;
after_m := imoney_all ;
--日志
ssql2 :=' insert into log_account select * from '|| tableAccount ||' where user_id='''||userId||''' and status=0';
execute immediate ssql2;
--记录
select seq_normal.NEXTVAL into newId from dual;
ssql3 :='insert into '|| tableAccount_his||' (id,user_id,create_time,money,types,ext_id,ext_type,Re,oper_id,pre_money,before_money,after_money)';
ssql3 := ssql3||' values('||newId||','''||userId|''','''||to_char(sysdate,'yyyy-mm-dd')|''','||money||','''||'余额增加'|''',';
ssql3 := ssql3||''''||inId||''','''||inType||''','''||inRe||''','''||operId||''','||imoney_all||','||before_m||','||after_m||')';
execute immediate ssql3;
errCode := 0;
outCode := errCode;
exception when others then
errCode := 13;
outCode := errCode;
errMsg := sqlerrm;
end;