ORACLE通过对照表快速建view

DROP TABLE CONTRAST_TABLE PURGE;
CREATE TABLE CONTRAST_TABLE AS 
SELECT table_name,
       'N_' || table_name AS new_tbl_name,
       column_name,
       'new_' || column_name AS new_col_name
  FROM all_tab_cols
  WHERE owner = 'TERENCE';

/*建view*/
DECLARE
  v_sql CLOB;
BEGIN
  FOR cur IN (SELECT 'CREATE OR REPLACE VIEW ' || new_tbl_name || ' as ' ||
                     chr(10) || 'select ' || chr(10) ||
                     wmsys.wm_concat(column_name || ' as ' || new_col_name ||
                                     chr(10)) || 'from TERENCE.' ||
                     table_name AS create_view
                FROM CONTRAST_TABLE
               GROUP BY table_name, new_tbl_name) LOOP
    v_sql := cur.create_view;
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;