写了一个你参考一下吧declare cursor tb is select table_name from user_all_tables ; table_name varchar2(100); updatestr varchar2(1000); colname varchar2(100); colvalue varchar2(100); begin colname:='col1'; open tb; loop fetch tb into table_name; exit when tb%notfound; updatestr:= 'update '||table_name||' set col1='''||colvalue||''''; --execute immediate updatestr; --执行update语句 dbms_output.put_line(updatestr); end loop; close tb; end;
供参考:declare cursor tb is select table_name from user_all_tables ; type cursor_t is ref cursor; tableinfo cursor_t; --cursor tableinfo(tabname) is select column_name,data_type from user_tab_columns where table_name=tabname; table_name user_tab_columns.TABLE_NAME%type; updatestr varchar2(4000); column_name user_tab_columns.COLUMN_NAME%type; data_type user_tab_columns.data_type%type; oldstr varchar2(10):='源字符串'; --根据实际情况确定内容和变量长度 newstr varchar2(10):='目标字符串'; --根据实际情况确定内容和变量长度begin open tb; loop fetch tb into table_name; exit when tb%notfound; open tableinfo for 'select column_name,data_type from user_tab_columns where table_name=:tabname' using table_name; loop fetch tableinfo into column_name,data_type; exit when tableinfo%notfound;
if data_type='VARCHAR2' or data_type='VARCAHR' or data_type='CHAR' then ---可根据需要确定哪些数据类型 updatestr:= 'update '||table_name||' set '||column_name||'= replace('||column_name||','''||oldstr||''','''||newstr||''')'; -- execute immediate updatestr; --确定拼装的SQL无误后可以打开执行update语句 dbms_output.put_line(updatestr); ---可能会报缓冲区满,没有关系,只要看一些前面的update语句是否正确。正式执行时注释掉 end if; end loop; close tableinfo;
end loop; close tb; end;
晕倒,编辑有问题,重发:declare cursor tb is select table_name from user_all_tables ; type cursor_t is ref cursor; tableinfo cursor_t; table_name user_tab_columns.TABLE_NAME%type; updatestr varchar2(4000); column_name user_tab_columns.COLUMN_NAME%type; data_type user_tab_columns.data_type%type; oldstr varchar2(10):='源字符串'; --根据实际情况确定内容和变量长度 newstr varchar2(10):='目标字符串'; --根据实际情况确定内容和变量长度begin open tb; loop fetch tb into table_name; exit when tb%notfound; open tableinfo for 'select column_name,data_type from user_tab_columns where table_name=:tabname' using table_name; loop fetch tableinfo into column_name,data_type; exit when tableinfo%notfound; if data_type='VARCHAR2' or data_type='VARCAHR' or data_type='CHAR' then ---可根据需要确定哪些数据类型 updatestr:= 'update '||table_name||' set '||column_name||'= replace('||column_name||','''||oldstr||''','''||newstr||''')'; -- execute immediate updatestr; --确定拼装的SQL无误后可以打开执行update语句 dbms_output.put_line(updatestr); ---可能会报缓冲区满,没有关系,只要看一些前面的update语句是否正确。正式执行时注释掉 end if; end loop; close tableinfo; end loop; close tb; end;
写了一个你参考一下吧declare
cursor tb is select table_name from user_all_tables ;
table_name varchar2(100);
updatestr varchar2(1000);
colname varchar2(100);
colvalue varchar2(100);
begin
colname:='col1';
open tb;
loop
fetch tb into table_name;
exit when tb%notfound;
updatestr:= 'update '||table_name||' set col1='''||colvalue||'''';
--execute immediate updatestr; --执行update语句
dbms_output.put_line(updatestr);
end loop;
close tb;
end;
有这方法??????
cursor tb is select table_name from user_all_tables ;
type cursor_t is ref cursor;
tableinfo cursor_t;
--cursor tableinfo(tabname) is select column_name,data_type from user_tab_columns where table_name=tabname;
table_name user_tab_columns.TABLE_NAME%type;
updatestr varchar2(4000);
column_name user_tab_columns.COLUMN_NAME%type;
data_type user_tab_columns.data_type%type;
oldstr varchar2(10):='源字符串'; --根据实际情况确定内容和变量长度
newstr varchar2(10):='目标字符串'; --根据实际情况确定内容和变量长度begin open tb;
loop
fetch tb into table_name;
exit when tb%notfound;
open tableinfo for 'select column_name,data_type from user_tab_columns where table_name=:tabname' using table_name;
loop
fetch tableinfo into column_name,data_type;
exit when tableinfo%notfound;
if data_type='VARCHAR2' or data_type='VARCAHR' or data_type='CHAR' then ---可根据需要确定哪些数据类型 updatestr:= 'update '||table_name||' set '||column_name||'= replace('||column_name||','''||oldstr||''','''||newstr||''')';
-- execute immediate updatestr; --确定拼装的SQL无误后可以打开执行update语句
dbms_output.put_line(updatestr); ---可能会报缓冲区满,没有关系,只要看一些前面的update语句是否正确。正式执行时注释掉 end if;
end loop;
close tableinfo;
end loop;
close tb;
end;
cursor tb is select table_name from user_all_tables ;
type cursor_t is ref cursor;
tableinfo cursor_t; table_name user_tab_columns.TABLE_NAME%type;
updatestr varchar2(4000);
column_name user_tab_columns.COLUMN_NAME%type;
data_type user_tab_columns.data_type%type;
oldstr varchar2(10):='源字符串'; --根据实际情况确定内容和变量长度
newstr varchar2(10):='目标字符串'; --根据实际情况确定内容和变量长度begin open tb;
loop
fetch tb into table_name;
exit when tb%notfound;
open tableinfo for 'select column_name,data_type from user_tab_columns where table_name=:tabname' using table_name;
loop
fetch tableinfo into column_name,data_type;
exit when tableinfo%notfound; if data_type='VARCHAR2' or data_type='VARCAHR' or data_type='CHAR' then ---可根据需要确定哪些数据类型 updatestr:= 'update '||table_name||' set '||column_name||'= replace('||column_name||','''||oldstr||''','''||newstr||''')';
-- execute immediate updatestr; --确定拼装的SQL无误后可以打开执行update语句
dbms_output.put_line(updatestr); ---可能会报缓冲区满,没有关系,只要看一些前面的update语句是否正确。正式执行时注释掉
end if;
end loop;
close tableinfo; end loop;
close tb;
end;