在PL/SQL中,我想把两个Update 连起来一起处理。
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11'
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12'我是这样做的:
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11';Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12';它老提示:ORA-00911:无效字符请问应该怎样做呢?
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11'
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12'我是这样做的:
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11';Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12';它老提示:ORA-00911:无效字符请问应该怎样做呢?
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11';
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12;或者这样
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11' or MateNo = '12;
var_sql varchar2(8000);
begin
var_sql := 'begin
';
var_sql := var_sql || 'Update T_GD_WAREBASE Set WareQty = WareQty - 10 where
MateNo = ''11'';
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo
= ''12'';';
var_sql := var_sql || '
end;
';
execute immediate var_sql;
commit;
end;
用command window,这样执行:
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11';
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '12;或者这样
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = '11' or MateNo = '12;
**********************************我只是举个例子,并不都是减10 的。
呵呵,刚才没看到第二条,要执行动态sql的话就这样执行:declare
var_sql varchar2(8000);
begin
var_sql := 'begin
';
var_sql := var_sql || 'Update T_GD_WAREBASE Set WareQty = WareQty - 10 where
MateNo = ''11'';
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo
= ''12'';';
var_sql := var_sql || '
end;
';
execute immediate var_sql;
commit;
end;
*********************************************************************
有错误啊,大哥。
我在PL/SQL 中执行报错:
ORA-00900:无效的SQL语句
ORA-06512:在line7
begin
var_sql := 'begin';
var_sql := var_sql || 'Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = ''11'';
Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo = ''12'';';
var_sql := var_sql || 'end;';
execute immediate var_sql;
commit;
end;
create table T_GD_WAREBASE(WareQty number,
2 MateNo number);Table createdSQL> insert into T_GD_WAREBASE select 100,11 from dual;1 row insertedSQL> insert into T_GD_WAREBASE select 120,12 from dual;1 row insertedSQL> commit;Commit completeSQL> select * from T_GD_WAREBASE; WAREQTY MATENO
---------- ----------
100 11
120 12SQL> create or replace procedure pro_test1
2 is
3 var_sql varchar2(8000);
4 begin
5 var_sql := 'begin
6 ';
7 var_sql := var_sql || 'Update T_GD_WAREBASE Set WareQty = WareQty - 10 where
8 MateNo = ''11'';
9 Update T_GD_WAREBASE Set WareQty = WareQty - 10 where MateNo
10 = ''12'';';
11 var_sql := var_sql || '
12 end;
13 ';
14 execute immediate var_sql;
15 commit;
16 end;
17 /Procedure createdSQL> execute pro_test1;PL/SQL procedure successfully completedSQL> select * from T_GD_WAREBASE; WAREQTY MATENO
---------- ----------
90 11
110 12