还不行的话,就把你生成的存储过程的代码导出来,看看具体哪出问题了 select text from user_source where type='PROCEDURE' and name='你的存储过程' order by line;
上图,单独一句就是可以生成的,再次复制一遍,就有问题了 问题如下: PROCEDURE NK_FXFX.USP_ZB_2321000ZB000048_CS 编译错误错误:PLS-00103: 出现符号 ""在需要下列之一时: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 行:17 文本:jg.temp1 || '],差额为[' || jg.temp4 || ']元';
存储过程代码如下 -----存储sql语句,产生存储过程(测试用) procedure usp_zb_sqlyj_cs(pro_name in varchar2, sqlyj in varchar2, code out number, errmsg out varchar2) is dyn_pro varchar2(4000); begin code := 0; errmsg := 'SUCCESS'; dyn_pro := 'create or replace procedure nk_fxfx.' || pro_name || '_CS ( as_fxpc in varchar2, as_znfw in varchar2, as_begin in varchar2, as_end in varchar2, err_code out number, err_msg out varchar2) is begin err_code:=0; err_msg :=''ok''; '|| sqlyj ||' end ' || pro_name || '_CS;';-- create procedure execute immediate dyn_pro;--execute immediate 'recompile nk_fxfz.'||pro_name; --更新指标表sql语句名称 update t_fx_fxmxqd t set t.mxjk = pro_name where t.fxmxid = substr(pro_name,8,20);-- invoke this procedure: do not forget begin/end --execute immediate 'begin ' || pro_name || '(:first, :2); end;' ; commit; exception when others then dbms_output.put_line(-1); code := 400; errmsg := 'FAILED:' || sqlerrm; rollback; return; end usp_zb_sqlyj_cs;
对照六楼的图片, 两句一样的update,程序调用的时候如果只跑一句是可以的,两句就不行了。 如果直接在存储过程里面测试,是好的create or replace procedure USP_ZB_2321000ZB000048_CS ( as_fxpc in varchar2, as_znfw in varchar2, as_begin in varchar2, as_end in varchar2, err_code out number, err_msg out varchar2) is begin err_code:=0; err_msg :='ok'; update t_nkls_jgfxjg jg set jg.fxfz = (case when jg.temp3 <= 0.5 then 90 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70 when jg.temp3 > 0.9 then 50 end), jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') || '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' || jg.temp1 || '],差额为[' || jg.temp4 || ']元'; update t_nkls_jgfxjg jg set jg.fxfz = (case when jg.temp3 <= 0.5 then 90 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70 when jg.temp3 > 0.9 then 50 end), jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') || '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' || jg.temp1 || '],差额为[' || jg.temp4 || ']元'; end USP_ZB_2321000ZB000048_CS;
SQL> create table t_nkls_jgfxjg(fxfz number,temp1 number,temp2 number,temp3 number,temp4 number,fxms varchar2(4000));表已创建。SQL> create or replace procedure USP_ZB_2321000ZB000048_CS ( as_fxpc in varchar2, 2 as_znfw in varchar2, 3 as_begin in varchar2, 4 as_end in varchar2, 5 err_code out number, 6 err_msg out varchar2) 7 is 8 begin 9 err_code:=0; 10 err_msg :='ok'; 11 update t_nkls_jgfxjg jg 12 set jg.fxfz = (case when jg.temp3 <= 0.5 then 90 13 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70 14 when jg.temp3 > 0.9 then 50 end), 15 jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') || 16 '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' || 17 jg.temp1 || '],差额为[' || jg.temp4 || ']元'; 18 19 update t_nkls_jgfxjg jg 20 set jg.fxfz = (case when jg.temp3 <= 0.5 then 90 21 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70 22 when jg.temp3 > 0.9 then 50 end), 23 jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') || 24 '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' || 25 jg.temp1 || '],差额为[' || jg.temp4 || ']元'; 26 end USP_ZB_2321000ZB000048_CS; 27 /过程已创建。SQL> 实际测试了下,没啥问题啊
select text from user_source
where type='PROCEDURE' and name='你的存储过程'
order by line;
上图,单独一句就是可以生成的,再次复制一遍,就有问题了
问题如下:
PROCEDURE NK_FXFX.USP_ZB_2321000ZB000048_CS 编译错误错误:PLS-00103: 出现符号 ""在需要下列之一时:
begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
行:17
文本:jg.temp1 || '],差额为[' || jg.temp4 || ']元';
-----存储sql语句,产生存储过程(测试用)
procedure usp_zb_sqlyj_cs(pro_name in varchar2, sqlyj in varchar2, code out number, errmsg out varchar2) is
dyn_pro varchar2(4000);
begin
code := 0;
errmsg := 'SUCCESS';
dyn_pro := 'create or replace procedure nk_fxfx.' || pro_name || '_CS ( as_fxpc in varchar2,
as_znfw in varchar2,
as_begin in varchar2,
as_end in varchar2,
err_code out number,
err_msg out varchar2)
is
begin
err_code:=0;
err_msg :=''ok'';
'|| sqlyj ||'
end ' || pro_name || '_CS;';-- create procedure
execute immediate dyn_pro;--execute immediate 'recompile nk_fxfz.'||pro_name;
--更新指标表sql语句名称
update t_fx_fxmxqd t set t.mxjk = pro_name where t.fxmxid = substr(pro_name,8,20);-- invoke this procedure: do not forget begin/end
--execute immediate 'begin ' || pro_name || '(:first, :2); end;' ;
commit;
exception
when others then
dbms_output.put_line(-1);
code := 400;
errmsg := 'FAILED:' || sqlerrm;
rollback;
return;
end usp_zb_sqlyj_cs;
两句一样的update,程序调用的时候如果只跑一句是可以的,两句就不行了。
如果直接在存储过程里面测试,是好的create or replace procedure USP_ZB_2321000ZB000048_CS ( as_fxpc in varchar2,
as_znfw in varchar2,
as_begin in varchar2,
as_end in varchar2,
err_code out number,
err_msg out varchar2)
is
begin
err_code:=0;
err_msg :='ok';
update t_nkls_jgfxjg jg
set jg.fxfz = (case when jg.temp3 <= 0.5 then 90
when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70
when jg.temp3 > 0.9 then 50 end),
jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') ||
'] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' ||
jg.temp1 || '],差额为[' || jg.temp4 || ']元'; update t_nkls_jgfxjg jg
set jg.fxfz = (case when jg.temp3 <= 0.5 then 90
when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70
when jg.temp3 > 0.9 then 50 end),
jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') ||
'] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' ||
jg.temp1 || '],差额为[' || jg.temp4 || ']元';
end USP_ZB_2321000ZB000048_CS;
2 as_znfw in varchar2,
3 as_begin in varchar2,
4 as_end in varchar2,
5 err_code out number,
6 err_msg out varchar2)
7 is
8 begin
9 err_code:=0;
10 err_msg :='ok';
11 update t_nkls_jgfxjg jg
12 set jg.fxfz = (case when jg.temp3 <= 0.5 then 90
13 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70
14 when jg.temp3 > 0.9 then 50 end),
15 jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') ||
16 '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' ||
17 jg.temp1 || '],差额为[' || jg.temp4 || ']元';
18
19 update t_nkls_jgfxjg jg
20 set jg.fxfz = (case when jg.temp3 <= 0.5 then 90
21 when (jg.temp3 > 0.5 and jg.temp3 <=0.9 ) then 70
22 when jg.temp3 > 0.9 then 50 end),
23 jg.fxms = '该纳税人[' || to_char(to_date('2012-01-01', 'yyyy-mm-dd'), 'yyyy') ||
24 '] 工资之处税收金额为[' || jg.temp2 || ']元,全员申报申报表中工资支出合计为[' ||
25 jg.temp1 || '],差额为[' || jg.temp4 || ']元';
26 end USP_ZB_2321000ZB000048_CS;
27 /过程已创建。SQL>
实际测试了下,没啥问题啊
1、sql语句首先是好的,plsql里面可以执行的
2、sql在存储过程里面测试也是好的
3、程序调用的时候,语句超过两个就有问题了。报上面的错误,但是再次点击一下执行就好了