Oracle:物化视图创建及刷新的脚本.

SET DEFINE OFF;

DROP MATERIALIZED VIEW TMS.TMS_LOCATION;

CREATE MATERIALIZED VIEW TMS.TMS_LOCATION

TABLESPACE TMSDATA

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

REFRESH FORCE ON DEMAND

WITH PRIMARY KEY

AS

select tl.*,decode(tl.location_type,'VENDOR',null,wpbu.business_unit_4) branch4,decode(tl.location_type,'VENDOR',null,wpbu.business_unit) branch5, decode(tvx.new_vendor_no,null,null,wl.location_id) vendor_location,tvx.new_vendor_no,tvx.old_vendor

from wms.wms_intf_location_tms_vw tl, wms.wms_location wl, tms_ven_xref tvx ,wms_ps_business_unit wpbu

where

tl.location_id=wpbu.campus_id(+) and

tl.location_id=wl.location_id and

SUBSTR ('000000' || tvx.new_vendor_no(+),

                                             -10

                                            )

                                  || SUBSTR ('0000000000' || tvx.new_location(+),

                                             -10

                                            ) = wl.location_name;

COMMENT ON MATERIALIZED VIEW TMS.TMS_LOCATION IS 'snapshot table for snapshot

TMS.TMS_LOCATION';

CREATE UNIQUE INDEX TMS.TMS_LOCATION_PK ON TMS.TMS_LOCATION

(LOCATION_ID)

LOGGING

TABLESPACE TMSDATA

NOPARALLEL;

GRANT SELECT ON  TMS.TMS_LOCATION TO TMS_GUEST;

dbms_mview.REFRESH ('TMS_LOCATION');