Question

问题：写一个存储过程实现更新数据库中所有表的某一特定字段，并且只更新这个字段中的值是满足'60__A'？？

我现在写了一个存储过程，执行是提示编译通过(Compiled successfully)，但是我查询数据库时，发现数据还是没有update啊？望哪位能帮忙看看我写的存储过程有什么问题吗？或者也可以帮我重新写一个。具体的存储过程如下：
create or replace procedure pro_test as cursor c is
select table_name from user_all_tables where table_name in (select table_name from user_tab_columns where column_name='TRAINNUM');
tablename varchar2(100);
strinsert varchar2(1000);
begin
open c;
loop
fetch c into tablename;
strinsert:= 'update '||tablename||' set trainnum=replace(trainnum,''C'',''A'') where trainnum like ''60__C'' ';
execute immediate strinsert;
end loop;
commit;
close c;
end;
我补充一下，我是说错了，问题应该是这样的：应该是字段的值满足'60__C'，而不是原来的字段满足'60__A'？
此外，我要补充一下，还有的我用的是PL/SQL Developer的工具。
这个问题太困扰了，谁能帮忙解决一下啊，解决出来以后，必有重赏啊。

Answer 1

create procedure pro_test
as

declare @tablename varchar(100),
@strinsert varchar(1000)

DECLARE @_CURSOR_SQL CURSOR
SET @_CURSOR_SQL = CURSOR FOR
select table_name from user_all_tables where table_name in (select table_name from user_tab_columns where column_name='TRAINNUM')

open @_CURSOR_SQL
fetch next from @_CURSOR_SQL into @tablename
while @@FETCH_STATUS = 0
begin

set @strinsert = 'update ' + '||' + @tablename + '||' + ' set trainnum=replace(trainnum,''C'',''A'') where trainnum like '+ '60__C' + ''
execute @strinsert

--重新循环
FETCH NEXT FROM @_CURSOR_SQL into @tablename
end
CLOSE @_CURSOR_SQL
DEALLOCATE @_CURSOR_SQL

追问

十分感谢您的回答，但是您给我修改后的代码是有问题编译通不过啊。我给您截个图吧。还有，我原来的怎么能通过编译，但是运行完以后数据库中所有表中trainnum的字段中包含'60__C'还是没有改变成'60__A'啊？

追答

你是用什么工具调试的，我是在sql server里面写存储过程的；
还有'update '||tablename||' set trainnum=replace(trainnum,''C'',''A'') where trainnum like ''60__C'' ';
中可能使用游标的时候tablename传值不正确，你可以先用print函数把tablename打印出来看看

追问

我的数据库系统是oracle的，使用的是PL/SQL Developer工具啊。忘了说了，呵呵。

追答

你可以看看游标访问的tablename是多少，可能是tablename没有访问到或值不对

追问

忽然之间我知道我错那了。我先试试改改。先谢谢你了，有什么问题再问你吧。

追答

unique constraint (CSR.PK_TRAINNUM_ID) violated 说明你违反了主键约束；
建议你看下CRH2_CHECK_INFO这张表的trainnum是否可以用c替换a；
另外你替换的目的是将'60__C'替换成'60__A'还是含有'C'的都换成'A'，你现在实现的是后者，如果要实现前者，replace(trainnum,'60__C','60__A')

追问

1，replace(trainnum,'60__C','60__A'),他不会把60__C中C前面的内容保留着，比如我现在要实现6002C换成6002A，如果按照replace(trainnum,'60__C','60__A')，他能替换过来吗？
2，老出现这个问题：buffer overflow, limit of 100000 bytes。怎么样让dbms_output.put_line(tablename);中输出以后就腾空了啊。不然的话，估计用set serveroutput on size 10000，设置多大都有可能出现上面那个问题？

追答

1，replace(trainnum,'60__C','60__A'),数据库会认为是将字符串'60__C'替换成'60__A'，而不是将符合'60**C'替换成'60**A'，'60__C'只能用在where 中的like里面，放在select,update,delete后面不认为'_'是*。
要实现你的功能，建议对@tablename 用游标遍历，得到trainnum，用substring函数截取字符串后，得到你想要的数据，再用update更新。
2.解决方法:
这个问题我baidu后是这样解决的
buffer_size 默认为10000,加大就行

set serveroutput on size 100000

参考资料： http://space.itpub.net/7199859/viewspace-604939

追问

好的，我试试您给我建议1这个方法，但是针对2，我有一些想问您的问题：
1）只有把buffer_size加大的方法了吗？我总觉得这种方法不好呢。如果一直超过buffer_size就一直加大下去吗？有没有别的方法？
2）另外，我用dbms_output()输出东西，只有当全部执行完以后他才输出东西，而不是执行到哪就输出到哪？这样的话岂不是都不知道他目前执行到哪？

追答

1，目前只有这种方法
2，可以考虑用pipeline管道 参考http://server.zol.com.cn/127/1273809.html

Answer 2

like ''%60__C%''加上百分号再试试