UPDATE jobs SET job_desc = 'EDITORS' WHERE (job_desc = 'Editor')如果有很多要改,最好建一个新旧企业编号匹配表吧
根据字典表去遍历 select * from user_tables:当前用户所有表 select * from user_tab_columns :表对应的所有列
麻烦还能再说的详细点嘛 我对Oracle存储过程不了解 先谢谢了
要复制所有的表?企业编号是要如何调整?可以用expdb impdb 做一下全库备份,再去调整企业编号。 乱搞个存储过程玩玩哈。把user1 的表全复制到 user2中,没管表空间什么的。 declare command varchar2(100); begin for cc in (select uo.object_name tname from user1.user_objects uo where uo.object_name='TABLE') loop command := 'create table user2.'||cc.object_name||'_bak as select * from '||cc.object_name||';'; execute immediate command; end loop end /
在更新之前最好对整个数据库备份一下 假设表的企业编号字段都为ID create or replace Pro_test is v_name user_tables%type; begin cursor my_cursor is select table_name from user_tables; open my_cursor; savepoint aa; loop fetch my_cursor into v_name; update v_name set ID='企业编号'; commit; exception when others then rollback to aa; Raise_Application_error(-20001,'更新数据失败!'); exit when my_cursor%NOTFOUND; end loop; close my_cursor; end;
CREATE OR REPLACE PROCEDURE pro_insertDoubleDate as v_sql varchar2(10000); v_col_list varchar2(10000); v_col_listcolumn varchar2(10000); v_enterprise_code varchar2(30) := '20000'; cursor c_1(no varchar2) is SELECT COLUMN_NAME from user_tab_columns where table_name = no; begin for t in (SELECT table_name FROM user_tables tab where exists (select 1 from SYS.USER_TAB_COLUMNS col where tab.TABLE_NAME = col.TABLE_NAME and col.COLUMN_NAME = 'ENTERPRISE_CODE') and rownum < 20) loop for i in c_1(t.table_name) loop if i.column_name = 'SID' then v_col_list := v_col_list || t.table_name || '_S.NEXTVAL as SID,'; v_col_listcolumn := v_col_listcolumn || 'SID,'; elsif i.column_name = 'ENTERPRISE_CODE' then v_col_list := v_col_list || '''' || v_enterprise_code || '''' || ' as ENTERPRISE_CODE, '; v_col_listcolumn := v_col_listcolumn || 'ENTERPRISE_CODE,'; else v_col_list := v_col_list || i.column_name || ','; v_col_listcolumn := v_col_listcolumn || i.column_name || ','; end if; end loop;
SET job_desc = 'EDITORS'
WHERE (job_desc = 'Editor')如果有很多要改,最好建一个新旧企业编号匹配表吧
select * from user_tables:当前用户所有表
select * from user_tab_columns :表对应的所有列
乱搞个存储过程玩玩哈。把user1 的表全复制到 user2中,没管表空间什么的。
declare
command varchar2(100);
begin
for cc in (select uo.object_name tname from user1.user_objects uo where uo.object_name='TABLE')
loop
command := 'create table user2.'||cc.object_name||'_bak as select * from '||cc.object_name||';';
execute immediate command;
end loop
end
/
假设表的企业编号字段都为ID
create or replace Pro_test
is
v_name user_tables%type;
begin
cursor my_cursor is
select table_name from user_tables;
open my_cursor;
savepoint aa;
loop
fetch my_cursor into v_name;
update v_name set ID='企业编号';
commit;
exception
when others then
rollback to aa;
Raise_Application_error(-20001,'更新数据失败!');
exit when my_cursor%NOTFOUND; end loop;
close my_cursor;
end;
CREATE OR REPLACE PROCEDURE pro_insertDoubleDate as
v_sql varchar2(10000);
v_col_list varchar2(10000);
v_col_listcolumn varchar2(10000);
v_enterprise_code varchar2(30) := '20000';
cursor c_1(no varchar2) is
SELECT COLUMN_NAME from user_tab_columns where table_name = no;
begin
for t in (SELECT table_name
FROM user_tables tab
where exists (select 1
from SYS.USER_TAB_COLUMNS col
where tab.TABLE_NAME = col.TABLE_NAME
and col.COLUMN_NAME = 'ENTERPRISE_CODE')
and rownum < 20) loop
for i in c_1(t.table_name) loop
if i.column_name = 'SID' then
v_col_list := v_col_list || t.table_name ||
'_S.NEXTVAL as SID,';
v_col_listcolumn := v_col_listcolumn || 'SID,';
elsif i.column_name = 'ENTERPRISE_CODE' then
v_col_list := v_col_list || '''' || v_enterprise_code || '''' ||
' as ENTERPRISE_CODE, ';
v_col_listcolumn := v_col_listcolumn || 'ENTERPRISE_CODE,';
else
v_col_list := v_col_list || i.column_name || ',';
v_col_listcolumn := v_col_listcolumn || i.column_name || ',';
end if;
end loop;
v_sql := 'insert into ' || t.table_name || '(' ||
rtrim(v_col_listcolumn, ',') || ') select ' ||
rtrim(v_col_list, ',') || ' from ' || t.table_name || '';
dbms_output.put_line(v_sql);
execute immediate v_sql;
v_col_list := '';
v_sql := '';
v_col_listcolumn := '';
end loop;
end;执行有异常,要么是序列不存在,或者标识符过长, 但当设置rownum <10的时候能却正常执行,那位能帮我看看哪里写错了??