Oracle-CURSOR及EXECUTE IMMEDIATE使用
时间:2022-03-14 02:23
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;