ORACLE存储过程之DDL（不动手永远也不知道细节）

CREATE OR REPLACE PROCEDURE DW.OUTBOUND_STATISTICS IS

      num number;
BEGIN
      select count(1) into num from all_tables where TABLE_NAME = 'T_OUTBOUND_STATISTICS' and OWNER='DW';
     IF  num=1   then
         execute immediate 'drop table T_OUTBOUND_STATISTICS';
      END IF;
  execute immediate
 'CREATE  TABLE T_OUTBOUND_STATISTICS AS
   SELECT QTY,
       TON,
       MONTH,
       TYPE,
       (SELECT COMPANY
          FROM WMWHSE8.STORER
         WHERE STORERKEY = T.STORERKEY
           AND STORER.TYPE = ''1'') AS STORERNAME
  FROM (
        SELECT SUM(ODL.SHIPPEDQTY) AS QTY,
               SUM(ODL.PRODUCT_WEIGHT / 1000) TON,
               TO_CHAR(ODL.EFFECTIVEDATE, ''YYYY-MM'') MONTH,
               ''REL'' TYPE,
               STORERKEY
          FROM WMWHSE8.ORDERDETAIL ODL
         WHERE ODL.EFFECTIVEDATE IS NOT NULL
         GROUP BY TO_CHAR(EFFECTIVEDATE, ''YYYY-MM''), STORERKEY
        ) T
 ORDER BY STORERNAME,MONTH, TYPE';
END;
/

[img]http://dl.iteye.com/upload/attachment/0082/5600/9239ee02-1474-3e74-9873-847f6a3619e4.jpg[/img]

grant create any table to public;