-
create or replace procedure up8000(table_num in number) is
i number(4, 0) := 0;
j number(3, 0) := 1;
smstable varchar2(32);
update_sql varchar2(1000);
--TYPE temp_array IS ARRAY(20) OF VARCHAR2(500); --假设最多有20个元素
type temp_array is table of varchar2(1000);
temparray temp_array;
begin
temparray := temp_array();
temparray.Extend(4); --下面有几条这里就为几
while i < table_num loop
if (i >= 10) then
smstable := 'SMS_USER_' || to_char(i);
else
smstable := 'SMS_USER_0' || to_char(i);
end if;
--Extend()中添的数量
temparray(1) := 'update ' || smstable ||
' t set t.orderservid=''KX'' where t.orderservid=''XH'' or t.orderservid=''TYWJ'' or t.orderservid=''XYYM'' or t.orderservid=''XHY'' or t.orderservid=''TNFB'' or t.orderservid=''ZRBD'' or t.orderservid=''SKL'' or t.orderservid=''CM'' or t.orderservid=''CS'' or t.orderservid=''MGCD''';
temparray(2) := 'update ' || smstable ||
' t set t.orderservid=''JS'' where t.orderservid=''SHBK'' or t.orderservid=''XCWH'' or t.orderservid=''JCCP'' or t.orderservid=''BDCX''';
temparray(3) := 'update ' || smstable ||
' a set a.orderservid=''BF''
where exists (select '' x'' from ' ||
smstable ||
' b where a.mobileno=b.mobileno and a.orderservid=b.orderservid and a.rowid<b.rowid)';
temparray(4) := 'update ' || smstable ||
' a set a.orderservid=''BF''
where exists (select '' x'' from ' ||
smstable ||
' b where a.mobileno=b.mobileno and a.orderservid=b.orderservid and a.rowid<b.rowid)';
for j in 1 .. temparray.count loop
update_sql := temparray(j);
--DBMS_OUTPUT.put_line(update_sql);
execute immediate update_sql;
end loop;
commit;
i := i + 1;
end loop;
end up8000;
![](https://img2024.cnblogs.com/blog/35695/202408/35695-20240814092323678-2007548550.jpg)