测试传的参数
v_unitstr 'G00009','G00037'$'G00044'
v_workno 001W0002create or replace procedure P_ZDHZ(v_unitstr in varchar2,
v_workno in varchar2,
v_retunum out number,
v_retext out varchar2) is
i int;
str varchar2(5000);
trueunit varchar2(5000);
falseunit varchar2(5000);
v_zdno varchar2(100);
dhrenum number;
dhreerr varchar2(100);
v_buyunit varchar2(100);
v_unit varchar2(100);
v_sql varchar2(5000);
ex_dan EXCEPTION;
begin
trueunit := '';
falseunit := '';
str := v_unitstr;
i := instr(str, '$');
trueunit := trueunit || substr(str, 1, i - 1);
falseunit := falseunit || substr(str, i + 1, LENGTH(str));
if trueunit is not null or trueunit <> '' then
p_mkdh('ZT', v_zdno, dhrenum, dhreerr);
if dhreerr = 'ok' then
select buyunit into v_buyunit from g_workers where workno = v_workno;
if sqlcode != 0 then
v_retunum := -1;
v_retext := '取操作员的单位出错' || sqlerrm;
raise ex_dan;
end if;
v_sql:='insert into y_zd (primaryno,zdno,buyunit,zdmlno,numbers,branchno,picino,bookid,jsunit,fsnumbers,discount,';
v_sql:=v_sql||'buylxr,gxmode,zdtype,grade,countno,tyno,fstype,fhyj,inputman,inputdate,ywman,ywdate,tag) ';
v_sql:=v_sql||'select F_GET_PRIMARYNO(''ZT''),'''||v_zdno||''','''||v_buyunit||''',zdmlno,sum(numbers),''001W'',picino,max(bookid),'''||v_buyunit||''',';
v_sql:=v_sql||'0,1,max(buylxr),max(gxmode),''0'',max(grade),max(countno),max(tyno),max(fstype),max(fhyj),'''||v_workno||''',sysdate,max(ywman),max(ywdate),''4''';
v_sql:=v_sql||' from y_zd_temp where picino = (select picino from y_pici where defaults = ''1'') and buyunit in ('||trueunit||') group by picino, zdmlno';
EXECUTE IMMEDIATE v_sql;
v_retunum := sqlcode; ----想看sqlcode值,所以赋给了v_retunum,此时变量跟踪 v_retunum 为0
if sqlcode != 0 then ----执行这里,然后竟然跳到raise ex_dan;
v_retunum := -1;
v_retext := '插入汇总后单位记录出错' || sqlerrm;
raise ex_dan; ---就算是有异常抛出,下一步也应该执行exception when ex_dan then,但是并没有,直接执行v_retext := '订单汇总成功'; end if;
else
v_retunum := -1;
v_retext := '生成订单号存储过程出错。'|| sqlerrm;
end if;
end if;
v_retext := '订单汇总成功'; ---执行这里,之后就到最后一句了。
exception
when ex_dan then
v_retunum := -1;
v_retext := sqlerrm || v_retext;
ROLLBACK;
when others then
v_retunum := -1;
v_retext := sqlerrm || v_retext;
ROLLBACK;
end P_ZDHZ; ----执行这里了。。我用测试这个过程
v_unitstr 'G00009','G00037'$'G00044'
v_workno 001W0002create or replace procedure P_ZDHZ(v_unitstr in varchar2,
v_workno in varchar2,
v_retunum out number,
v_retext out varchar2) is
i int;
str varchar2(5000);
trueunit varchar2(5000);
falseunit varchar2(5000);
v_zdno varchar2(100);
dhrenum number;
dhreerr varchar2(100);
v_buyunit varchar2(100);
v_unit varchar2(100);
v_sql varchar2(5000);
ex_dan EXCEPTION;
begin
trueunit := '';
falseunit := '';
str := v_unitstr;
i := instr(str, '$');
trueunit := trueunit || substr(str, 1, i - 1);
falseunit := falseunit || substr(str, i + 1, LENGTH(str));
if trueunit is not null or trueunit <> '' then
p_mkdh('ZT', v_zdno, dhrenum, dhreerr);
if dhreerr = 'ok' then
select buyunit into v_buyunit from g_workers where workno = v_workno;
if sqlcode != 0 then
v_retunum := -1;
v_retext := '取操作员的单位出错' || sqlerrm;
raise ex_dan;
end if;
v_sql:='insert into y_zd (primaryno,zdno,buyunit,zdmlno,numbers,branchno,picino,bookid,jsunit,fsnumbers,discount,';
v_sql:=v_sql||'buylxr,gxmode,zdtype,grade,countno,tyno,fstype,fhyj,inputman,inputdate,ywman,ywdate,tag) ';
v_sql:=v_sql||'select F_GET_PRIMARYNO(''ZT''),'''||v_zdno||''','''||v_buyunit||''',zdmlno,sum(numbers),''001W'',picino,max(bookid),'''||v_buyunit||''',';
v_sql:=v_sql||'0,1,max(buylxr),max(gxmode),''0'',max(grade),max(countno),max(tyno),max(fstype),max(fhyj),'''||v_workno||''',sysdate,max(ywman),max(ywdate),''4''';
v_sql:=v_sql||' from y_zd_temp where picino = (select picino from y_pici where defaults = ''1'') and buyunit in ('||trueunit||') group by picino, zdmlno';
EXECUTE IMMEDIATE v_sql;
v_retunum := sqlcode; ----想看sqlcode值,所以赋给了v_retunum,此时变量跟踪 v_retunum 为0
if sqlcode != 0 then ----执行这里,然后竟然跳到raise ex_dan;
v_retunum := -1;
v_retext := '插入汇总后单位记录出错' || sqlerrm;
raise ex_dan; ---就算是有异常抛出,下一步也应该执行exception when ex_dan then,但是并没有,直接执行v_retext := '订单汇总成功'; end if;
else
v_retunum := -1;
v_retext := '生成订单号存储过程出错。'|| sqlerrm;
end if;
end if;
v_retext := '订单汇总成功'; ---执行这里,之后就到最后一句了。
exception
when ex_dan then
v_retunum := -1;
v_retext := sqlerrm || v_retext;
ROLLBACK;
when others then
v_retunum := -1;
v_retext := sqlerrm || v_retext;
ROLLBACK;
end P_ZDHZ; ----执行这里了。。我用测试这个过程
解决方案 »
- EXP导出问题
- 【怪问题】oracle job已经停止,但total_time依然增加
- oracle按某列循环取值
- 求oracle数据库存储过程的增删改查语句
- oracle xpath 定位 操作xml节点
- TNS:no listener 如何解决
- ORA-12570:TNS:包阅读程序失败出错,出错。我该咋办??谁能 help me?
- echo怎么用啊?
- sql文中的简单问题!!!
- 一个比较菜的问题,请帮帮忙!
- 为何同义词不可以用在存储过程中?
- to_date('2006-01-02','yyyy-mm-dd')在oracle中执行后的值为:2006-1-2,怎样使其保持2006-01-02不变?
v_retunum := 0;
if 0 != 0 then
v_retunum := -1;
v_retext := '插入汇总后单位记录出错' || sqlerrm;
raise ex_dan;
end if;
写成这样,都能执行到raise ex_dan;
这到底是什么原因咯
SQL> ed
已写入 file afiedt.buf 1 declare
2 v_retunum int := 0 ;
3 ex_dan EXCEPTION;
4 v_retext varchar2(200);
5 begin
6 if 0 != 0 then
7 v_retunum := -1;
8 v_retext := sqlerrm;
9 raise ex_dan;
10 end if;
11 dbms_output.put_line( 'sccess');
12 exception
13 when ex_dan then
14 dbms_output.put_line( 'err' || sqlcode || v_retext );
15 when others then
16 dbms_output.put_line( 'others' || sqlcode );
17* end;
SQL> /
sccessPL/SQL 过程已成功完成。没有像楼主说的那样阿
EXECUTE IMMEDIATE v_sql;
的V_SQL的内容正确么
当V_SQL的SQL不能正确执行的时候,SQLCODE会被赋值的
DBMS_OUTPUT出来看看