Oracle-CURSOR及EXECUTE IMMEDIATE使用

CREATE OR REPLACE 
PROCEDURE P_REPLACE IS
v_sql VARCHAR2(2000);
CURSOR cur IS SELECT * FROM user_tab_cols;
tableinfo user_tab_cols%rowtype;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO tableinfo;
EXIT WHEN cur%notfound;
v_sql:='UPDATE '||tableinfo.table_name||' SET '||tableinfo.column_name||'='||'replace('||tableinfo.column_name||','||''''||'-'||''''||','||''''||''''||')';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
COMMIT;
END LOOP;
EXCEPTION WHEN others THEN
dbms_output.put_line(SQLERRM);
CLOSE cur;
END P_REPLACE;

原文：http://blog.csdn.net/jxzkin/article/details/41039921