利用动态SQL创建视图

DECLARE 
V_SQL VARCHAR2(4000);
BEGIN
  V_SQL:='CREATE OR REPLACE VIEW V_DWD_PASSWORD AS ';
  FOR I IN(
SELECT ' SELECT T.PLAT,
                T.DIRECT,
                T.DEVIC,
                T.PASSTIME,
                T.SPEED,
                T.SITENAME 
 FROM '||TABLE_NAME ||' T UNION ALL ' AS TEXT FROM USER_TABLES T
WHERE　T.TABLE_NAME LIKE '%DWD_PASSDATA%' ORDER BY TABLE_NAME) LOOP
V_SQL:=V_SQL||CHR(10)||I.TEXT;
END LOOP;
--最后一个循环的 UNION ALL 需要切割剔除
V_SQL:=SUBSTR(V_SQL,0,LENGTH(V_SQL)-10);
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;

         本站文章除注明转载/出处外，均为本站原创，转载前请务必署名,转载请标明出处