请教一个oracle的存储过程，急。。

Question

create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
    cursor c_table is 
    select distinct table_name from user_tables a
    where column_name='USERID';
begin
    dbms_output.enable(1000000);
    v_count:=0;
    open c_table;
    
    loop
        fetch c_table into v_table;
        
        exit when c_table%NOTFOUND;
        v_sql := 'update '||v_table||' set userid=dddd||substr(userid,5)';
           begin
           execute immediate v_sql;
           commit;
           exception
              when others then 
              begin
                  v_count:=v_count+1;
                  dbms_output.put_line ('error:'||v_sql);
              end;
           end;
        
    end loop;
    close c_table;
    dbms_output.put_line ('error count:'||v_count);
end update_userid;

Answer 1

不好意思，有个地方错了
create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
    cursor c_table is 
    select distinct table_name from user_tables a
    where column_name='USERID';
begin
    dbms_output.enable(1000000);
    v_count:=0;
    open c_table;
    
    loop
        fetch c_table into v_table;
        
        exit when c_table%NOTFOUND;
        v_sql := 'update '||v_table||' set userid=''dddd''||substr(userid,5)';
           begin
           execute immediate v_sql;
           commit;
           exception
              when others then 
              begin
                  v_count:=v_count+1;
                  dbms_output.put_line ('error:'||v_sql);
              end;
           end;
        
    end loop;
    close c_table;
    dbms_output.put_line ('error count:'||v_count);
end update_userid;
/

Answer 2

多谢回复，我对标准sql了解，但是对写procedure没什么经验，我要明天才能去调试。不过对回复还有个问题。
user_tables 这个表不对吧，应该是all_tab_columns还有能否解释一下这句
v_sql := 'update '||v_table||' set userid=''dddd''||substr(userid,5)';
里面的||是什么意思，我一下子找不到书去查，谢谢了。

Answer 3

SELECT 'UPDATE '||OWNER||'.'||TABLE_NAME||' SET '||COLUMN_NAME||'='||'''XXX''||'||OWNER||'.'||
'substr('||COLUMN_NAME||',5)'||';'
FROM ALL_TAB_COLUMNS WHERE OWNER='xxx' AND COLUMN_NAME='xxx'生成一个脚本后,放到sql文件里面执行,记得commit;
xxx是你要代替的值

Answer 4

all_:是所有用户的
user_：是本用户的’||‘是字符串连接符
如：
select '123'||'abcd' from dual;--则得到123abcd

Answer 5

如果我还要在update的表里加个条件是不是该这样写：
 v_sql := 'update '||v_table||' set userid=''dddd''||substr(userid,5) where substr(userid,1,4)=''1234'' ';

Answer 6

最后个请求，最后要给个report看看结果，怎么输出到临时表里看看，或者文本文件什么的也凑合。就是想看看结果。。

Answer 7

spool c:\aaa.txt
select * from aaa;
spool off

Answer 8

这个输出写在上面的proc里该怎么写？直接加在里面好像无法执行；；
create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
    cursor c_table is 
    select distinct table_name from user_tables a
    where column_name='USERID';
……………………
spool c:\aaa.txt;
…………………………………………
spool off;
end update_userid;

Answer 9

如果一行行输出，可以用dbms_output.put_line(str);
然后在SQL窗口先set serveroutput on
再exec procname;