1.1.1 Oracle物化视图简介
1. 物化视图说明
物化视图 (Materialized View)，在以前的Oracle版本中称为快照(Snapshot)。Oracle 的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速地 得到结果：
物化视图有很多方面和索引很相似：
使用物化视图的目的是为了提高查询性能
物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性
物化视图需要占用存储空间
当基表发生变化时，物化视图也应当刷新
物化视图可以分为以下三种类型：
包含聚集的物化视图
只包含连接的物化视图
嵌套物化视图
物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图
2. 物化视图详细说明
1. refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化， 建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on （主表名）。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。
2.MV数据刷新的时间：
on demand:在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
on commit:当主表中有数据提交的时候，立即刷新MV中的数据；
start ……：从指定的时间开始，每隔一段时间（由next指定）就刷新一次；
物化视图可以分为以下三种类型：包含聚集的物化视图；只包含连接的物化视图；嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：
创建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。
查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图 来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。
刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。 ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVE*。**ST刷 新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时 会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要 求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进 行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。
3. 物化视图详细EDOME
创建物化视图时应先创建存储的日志空间
CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
然后创建物化视图
--创建物化视图
create materialized view MV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空间
BUILD DEFERRED --延迟刷新不立即刷新
refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新
on demand --按照指定方式刷新
start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间
next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔
as
SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend,
......
ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) -
ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) -
ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) -
ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) -
ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal,
a.levydataid, a.budgetitemcode, taxtypecode,
......
FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b
WHERE a.levydataid = c.levydataid
AND a.budgetdistrscalecode = b.budgetdistrscalecode
AND a.budgetitemcode = b.budgetitemcode
AND c.incomeresidecode = b.rcvfisccode
AND C.TAXSTATUSCODE='08'
AND C.NEGATIVEFLAG!='9'
删除物化视图日志
--删除物化视图：
--删除日志： DROP materialized view log on mv_lvy_levytaxbgtdiv;
DROP materialized view log on tb_lvy_levydetaildata;
DROP materialized view log on tb_lvy_levydata;
--删除物化视图 drop materialized view MV_LVY_LEVYDETAILDATA
4. 物化视图授权
SQL> GRANT CREATE MATERIALIZED VIEW TO scott; --授予创建物化视图的权限(必须)
SQL> GRANT QUERY REWRITE TO scott; --实现查询重写权限，可无
SQL> GRANT ALTER ANY MATERIALIZED VIEW TO scott; --修改物化视图的权限，可无
SQL> GRANT LOCK ANY TABLE TO SCOTT; --可无
SQL> GRANT CREATE SESSION TO SCOTT; --可无, SCOTT用户刷新物化视图
5. 物化视图语法
CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE
AS (<SQL statement>);
CREATE MATERIALIZED VIEW [schema.]mview
Mview_Options
[USING INDEX storage_options]
[{REFRESH [refresh_options] |NEVER REFRESH]
[FORUPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS subbquery;
CREATE MATERIALIZED VIEW [schema.]mview
ON PREBUILT TABLE [{WITH |WITHOUT} REDUCED PRECISION]
[USING INDEX storage_options]
[{REFRESH [refresh_options]| NEVER REFRESH]
[FORUPDATE] [{ENABLE|DISABLE} QUERY REWRITE]
AS subbquery;
--说明：上面的选项可安装自己的需要进行选择
6. 简单的物化视图demo
CREATE MATERIALIZED VIEW emp_dept_view
REFRESH COMPLETE
START WITH
SYSDATE NEXT TRUNC(SYSDATE+29)+5.5/24 –-说明：执行任务从现在开始到
29天后的5点半在开始
AS
SELECT emp.empno,emp.ename,emp.job,emp.hiredate,emp.comm,dept.loc
FROM emp,dept
WHERE emp.deptno=dept.deptno
7. 物化视图管理
SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'EMP_DEPT_VIEW';
--查看该物化视图记录信息
SQL> SELECT name, table_name, updatable, refresh_method,refresh_mode FROM user_snapshots;
SQL> SELECT master, log_table, rowids, primary_key FROM user_snapshot_logs;
SQL> DROP MATERIALIZED VIEW LOG ON <table_name>; --删除创建的日志记录
8. 物化视图详解
http://www.psoug.org/reference/materialized_views.html
http://wz.csdn.net/item/2217076/
http://blog.csdn.net/suncrafted/archive/2009/06/26/4300358.aspx
http://www.psoug.org/reference/materialized_views.html
9. 物化视图实战
物化视图日志
CREATE MATERIALIZED VIEW LOG ON odrim163_itemcost WITH ROWID;
物化视图创建
CREATE MATERIALIZED VIEW T_VORIM163_ITEMCOST
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
--REFRESH FORCE ON DEMAND
--REFRESH FAST ON COMMIT
WITH ROWID
AS
SELECT odrim163_itemid vodrim163_itemid, odrim163_dlvrid vodrim163_dlvrid, odrim163_spec vodrim163_spec, odrim163_sdate vodrim163_sdate,ODRIM163_EDATE vodrim163_edate,
MAX (odrim163_unit) AS vodrim163_unit
FROM odrim163_itemcost
GROUP BY odrim163_itemid, odrim163_dlvrid, odrim163_spec, odrim163_sdate,ODRIM163_EDATE;
修改物化视图操作
ALTER MATERIALIZED VIEW T_VORIM163_ITEMCOST REFRESH FORCE ON DEMAND;
删除物化视图操作
DROP MATERIALIZED VIEW odrim163_itemcost;
--基本无主、外键，且使用ROWID方式，下面必须收到刷新才起作用
CREATE MATERIALIZED VIEW ODRD121_RETAPPLYD_REPORTS_VIEW
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH ON DEMAND
WITH ROWID
AS
select * from ODRD121_RETAPPLYD_REPORT;
10. 简单的案例
/*
使用说明：直接查询ODRD121_RETAPPLYD_REPORTS_VIEW该视图，可以得到需求数据
设置存储过程定时说明：
对odrd121_retapplyd_reports存储过程制定晚上2点自动执行脚本，可以根据项目的需求进行相应的调整，参考如下：
begin
sys.dbms_job.submit(job => :job,
what => 'odrd121_retapplyd_reports;',
next_date => to_date('04-09-2009 02:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate)+1+2/24');
commit;
end;
注意：创建后请不要再对物化视图进行刷新操作,由于odrd121_retapplyd_reports存储过程里已经对物化视图进行相关刷新操作，
如有需要可做相应的调整。
*/
--临时表ODRD121_RETAPPLYD_REPORT创建
create table ODRD121_RETAPPLYD_REPORT
(
ODRD121_ODRWAY_1 CHAR(2) not null,
ODRD121_ORGID_2 VARCHAR2(13) not null,
ODRD121_ODRDATE_3 CHAR(8) not null,
ODRD121_STFID_4 VARCHAR2(13) not null,
ODRD121_ORDERNO_5 VARCHAR2(18) not null,
ODRD121_LINENO_6 NUMBER(6) not null,
ODRD121_ITEMID_7 VARCHAR2(13) not null,
ODRD121_PLUCD_8 VARCHAR2(13) not null,
ODRD121_SPEC_9 NUMBER(10) not null,
ODRD121_TRIP_10 CHAR(1) not null,
ODRD121_PACKQTY_11 NUMBER(14,4) not null,
ODRD121_UNITQTY_12 NUMBER(14,4) not null,
ODRD121_ENTDATE_13 CHAR(14) not null,
ODRD121_RETREASONID_14 CHAR(2) not null,
ODRD121_GENDATE_15 CHAR(14) not null,
ODRD121_HQPACKQTY_16 NUMBER(14,4) not null,
ODRD121_HQUNITQTY_17 NUMBER(14,4) not null,
ODRD121_CHKFLG_18 CHAR(1) not null,
ODRD121_CHKSTFID_19 VARCHAR2(13) not null,
ODRD121_CHKDATE_20 CHAR(14) not null,
ODRD121_SNDFLG_21 CHAR(1) not null,
ODRD121_SNDDATE_22 CHAR(14) not null,
ODRD121_HEADFLG_23 CHAR(1) not null,
ODRIM150_ITEMID_24 VARCHAR2(13) not null,
ODRIM150_PLUCD_25 VARCHAR2(13) not null,
ODRIM150_ITEMCNM_26 VARCHAR2(60) not null,
VODRIM163_ITEMID_27 VARCHAR2(13) not null,
VODRIM163_DLVRID_28 VARCHAR2(13) not null,
VODRIM163_SPEC_29 NUMBER(10) not null,
VODRIM163_SDATE_30 VARCHAR2(8) not null,
VODRIM163_UNIT_31 VARCHAR2(4),
ODRIM910_URTLGRS_32 NUMBER(14,4) not null,
ODRIM910_URTLNET_33 NUMBER(14,4) not null,
ODRIP011_ITEMID_34 VARCHAR2(13) not null,
ODRIP011_ORGID_35 VARCHAR2(13) not null,
ODRIP011_QTY_36 NUMBER(14,4) not null,
ODRIM210_DLVRID_37 VARCHAR2(13) not null,
ODRIM210_DLVRCD_38 VARCHAR2(8) not null,
ODRIM210_DLVRCNM_39 VARCHAR2(50) not null,
ODRIM080_STRID_40 VARCHAR2(13) not null,
ODRIM080_STRCD_41 VARCHAR2(8) not null,
ODRIM080_SVID_42 VARCHAR2(13) not null,
ODRIM080_ORGID_43 VARCHAR2(13) not null,
ODRIM080_STRCNM_44 VARCHAR2(40) not null,
ODRIM013_SVID_45 VARCHAR2(13) not null,
ODRIM013_SVCD_46 VARCHAR2(7) not null,
ODRIM013_DOID_47 VARCHAR2(13) not null,
ODRIM012_DOID_48 VARCHAR2(13) not null,
ODRIM012_DOCD_49 VARCHAR2(4) not null,
ODRIM161_ITEMID_50 VARCHAR2(13) not null,
ODRIM161_DLVRID_51 VARCHAR2(13) not null,
ODRIS020_PARACD_52 VARCHAR2(13) not null,
ODRIS020_PARAVALUE_53 VARCHAR2(60) not null
);
--ODRD121_RETAPPLYD_REPORTS存储过程创建
CREATE OR REPLACE PROCEDURE ODRD121_RETAPPLYD_REPORTS IS
itmid ODRD121_RETAPPLYD.ODRD121_ITEMID%TYPE;
timidcount number;
CURSOR GetStrView IS
SELECT DISTINCT ODRD121_ITEMID FROM ODRD121_RETAPPLYD;
BEGIN
timidcount := 0;
OPEN GetStrView;
LOOP
FETCH GetStrView
INTO itmid;
EXIT WHEN GetStrView%NOTFOUND;
IF TRIM(itmid) IS NOT NULL THEN
--通过动态查询，把查询结果存放到ODRD121_RETAPPLYD_REPORT临时表里
EXECUTE IMMEDIATE ' INSERT /*+ APPEND*/ INTO ODRD121_RETAPPLYD_REPORT
SELECT /*+ STAR */ ODRD121_RETAPPLYD.ODRD121_ODRWAY AS ODRD121_ODRWAY_1,
ODRD121_RETAPPLYD.ODRD121_ORGID AS ODRD121_ORGID_2,
ODRD121_RETAPPLYD.ODRD121_ODRDATE AS ODRD121_ODRDATE_3,
ODRD121_RETAPPLYD.ODRD121_STFID AS ODRD121_STFID_4,
ODRD121_RETAPPLYD.ODRD121_ORDERNO AS ODRD121_ORDERNO_5,
ODRD121_RETAPPLYD.ODRD121_LINENO AS ODRD121_LINENO_6,
ODRD121_RETAPPLYD.ODRD121_ITEMID AS ODRD121_ITEMID_7,
ODRD121_RETAPPLYD.ODRD121_PLUCD AS ODRD121_PLUCD_8,
ODRD121_RETAPPLYD.ODRD121_SPEC AS ODRD121_SPEC_9,
ODRD121_RETAPPLYD.ODRD121_TRIP AS ODRD121_TRIP_10,
ODRD121_RETAPPLYD.ODRD121_PACKQTY AS ODRD121_PACKQTY_11,
ODRD121_RETAPPLYD.ODRD121_UNITQTY AS ODRD121_UNITQTY_12,
ODRD121_RETAPPLYD.ODRD121_ENTDATE AS ODRD121_ENTDATE_13,
ODRD121_RETAPPLYD.ODRD121_RETREASONID AS ODRD121_RETREASONID_14,
ODRD121_RETAPPLYD.ODRD121_GENDATE AS ODRD121_GENDATE_15,
ODRD121_RETAPPLYD.ODRD121_HQPACKQTY AS ODRD121_HQPACKQTY_16,
ODRD121_RETAPPLYD.ODRD121_HQUNITQTY AS ODRD121_HQUNITQTY_17,
ODRD121_RETAPPLYD.ODRD121_CHKFLG AS ODRD121_CHKFLG_18,
ODRD121_RETAPPLYD.ODRD121_CHKSTFID AS ODRD121_CHKSTFID_19,
ODRD121_RETAPPLYD.ODRD121_CHKDATE AS ODRD121_CHKDATE_20,
ODRD121_RETAPPLYD.ODRD121_SNDFLG AS ODRD121_SNDFLG_21,
ODRD121_RETAPPLYD.ODRD121_SNDDATE AS ODRD121_SNDDATE_22,
ODRD121_RETAPPLYD.ODRD121_HEADFLG AS ODRD121_HEADFLG_23,
ODRIM150_ITEM.ODRIM150_ITEMID AS ODRIM150_ITEMID_24,
ODRIM150_ITEM.ODRIM150_PLUCD AS ODRIM150_PLUCD_25,
ODRIM150_ITEM.ODRIM150_ITEMCNM AS ODRIM150_ITEMCNM_26,
VODRIM163_ITEMCOST.VODRIM163_ITEMID AS VODRIM163_ITEMID_27,
VODRIM163_ITEMCOST.VODRIM163_DLVRID AS VODRIM163_DLVRID_28,
VODRIM163_ITEMCOST.VODRIM163_SPEC AS VODRIM163_SPEC_29,
VODRIM163_ITEMCOST.VODRIM163_SDATE AS VODRIM163_SDATE_30,
VODRIM163_ITEMCOST.VODRIM163_UNIT AS VODRIM163_UNIT_31,
ODRIM910_STOREURTL.ODRIM910_URTLGRS AS ODRIM910_URTLGRS_32,
ODRIM910_STOREURTL.ODRIM910_URTLNET AS ODRIM910_URTLNET_33,
ODRIP011_STOCK.ODRIP011_ITEMID AS ODRIP011_ITEMID_34,
ODRIP011_STOCK.ODRIP011_ORGID AS ODRIP011_ORGID_35,
ODRIP011_STOCK.ODRIP011_QTY AS ODRIP011_QTY_36,
ODRIM210_DELIVERER.ODRIM210_DLVRID AS ODRIM210_DLVRID_37,
ODRIM210_DELIVERER.ODRIM210_DLVRCD AS ODRIM210_DLVRCD_38,
ODRIM210_DELIVERER.ODRIM210_DLVRCNM AS ODRIM210_DLVRCNM_39,
ODRIM080_STORE.ODRIM080_STRID AS ODRIM080_STRID_40,
ODRIM080_STORE.ODRIM080_STRCD AS ODRIM080_STRCD_41,
ODRIM080_STORE.ODRIM080_SVID AS ODRIM080_SVID_42,
ODRIM080_STORE.ODRIM080_ORGID AS ODRIM080_ORGID_43,
ODRIM080_STORE.ODRIM080_STRCNM AS ODRIM080_STRCNM_44,
ODRIM013_SV.ODRIM013_SVID AS ODRIM013_SVID_45,
ODRIM013_SV.ODRIM013_SVCD AS ODRIM013_SVCD_46,
ODRIM013_SV.ODRIM013_DOID AS ODRIM013_DOID_47,
ODRIM012_DO.ODRIM012_DOID AS ODRIM012_DOID_48,
ODRIM012_DO.ODRIM012_DOCD AS ODRIM012_DOCD_49,
ODRIM161_ITEMDLV.ODRIM161_ITEMID AS ODRIM161_ITEMID_50,
ODRIM161_ITEMDLV.ODRIM161_DLVRID AS ODRIM161_DLVRID_51,
ODRIS020_PARA.ODRIS020_PARACD AS ODRIS020_PARACD_52,
ODRIS020_PARA.ODRIS020_PARAVALUE AS ODRIS020_PARAVALUE_53
FROM (SELECT odrim163_itemid VODRIM163_ITEMID,
odrim163_dlvrid VODRIM163_DLVRID,
odrim163_spec VODRIM163_SPEC,
odrim163_sdate VODRIM163_SDATE,
ODRIM163_EDATE VODRIM163_EDATE,
MAX(odrim163_unit) VODRIM163_UNIT
FROM odrim163_itemcost
GROUP BY odrim163_itemid,
odrim163_dlvrid,
odrim163_spec,
odrim163_sdate,
ODRIM163_EDATE) VODRIM163_ITEMCOST,
(SELECT ODRIV910_ITEMID ODRIM910_ITEMID,
ODRIV910_SPEC ODRIM910_SPEC,
T2.STRID ODRIM910_STRID,
ODRIV910_SDATE ODRIM910_SDATE,
ODRIV910_EDATE ODRIM910_EDATE,
ODRIV910_URTLGRS ODRIM910_URTLGRS,
ODRIV910_URTLNET ODRIM910_URTLNET
FROM ODRIV910_ORGURTL T1,
(SELECT ODRIM080_strid ORGID,
ODRIM080_ORGID STRID,
1 LEVE
FROM ODRIM080_STORE ODRIM080_STORE11
UNION ALL
SELECT ODRIM080_PRREGIONID,
ODRIM080_ORGID,
2
FROM ODRIM080_STORE ODRIM080_STORE21
UNION ALL
SELECT ODRIM010_COMID,
ODRIM080_ORGID,
3
FROM ODRIM080_STORE ODRIM080_STORE31,
ODRIM010_COMPANY ODRIM010_COMPANY1,
ODRIM031_ACCREGION ODRIM031_ACCREGION1
WHERE ODRIM010_COMID = ODRIM031_COMID
AND ODRIM080_ACCREGIONID = ODRIM031_ACCREGIONID) T2
WHERE ODRIV910_ORGID = T2.ORGID
AND NOT EXISTS (SELECT 1
FROM ODRIV910_ORGURTL TT1,
(SELECT ODRIM080_strid ORGID,
ODRIM080_ORGID STRID,
1 LEVE
FROM ODRIM080_STORE ODRIM080_STORE12
UNION ALL
SELECT ODRIM080_PRREGIONID,
ODRIM080_ORGID,
2
FROM ODRIM080_STORE ODRIM080_STORE22
UNION ALL
SELECT ODRIM010_COMID,
ODRIM080_ORGID,
3
FROM ODRIM080_STORE ODRIM080_STORE32,
ODRIM010_COMPANY ODRIM010_COMPANY2,
ODRIM031_ACCREGION ODRIM031_ACCREGION2
WHERE ODRIM010_COMID = ODRIM031_COMID || '||''''''||'
AND ODRIM080_ACCREGIONID = ODRIM031_ACCREGIONID || '||''''''||') TT2
WHERE ODRIV910_ORGID = TT2.ORGID
AND T1.ODRIV910_ITEMID = TT1.ODRIV910_ITEMID
AND T1.ODRIV910_SPEC = TT1.ODRIV910_SPEC
AND T2.STRID = TT2.STRID
AND T2.LEVE > TT2.LEVE)) ODRIM910_STOREURTL,
ODRIM210_DELIVERER,
ODRIP011_STOCK,
ODRIM161_ITEMDLV,
ODRD121_RETAPPLYD,
ODRIS020_PARA,
ODRIM080_STORE,
ODRIM150_ITEM,
ODRIM013_SV,
ODRIM012_DO
WHERE ODRD121_RETAPPLYD.ODRD121_ITEMID = ODRIM150_ITEM.ODRIM150_ITEMID
AND ODRD121_RETAPPLYD.ODRD121_ORGID = ODRIM080_STORE.ODRIM080_ORGID
AND ODRIM910_STOREURTL.ODRIM910_STRID = ODRIM080_STORE.ODRIM080_ORGID
AND ODRD121_RETAPPLYD.ODRD121_ITEMID = ODRIM910_STOREURTL.ODRIM910_ITEMID
AND ODRD121_RETAPPLYD.ODRD121_SPEC = ODRIM910_STOREURTL.ODRIM910_SPEC
AND ODRD121_RETAPPLYD.ODRD121_ITEMID = ODRIM161_ITEMDLV.ODRIM161_ITEMID
AND ODRIM210_DELIVERER.ODRIM210_ORGID = ODRIM161_ITEMDLV.ODRIM161_DLVRID
AND ODRD121_RETAPPLYD.ODRD121_ITEMID = VODRIM163_ITEMCOST.VODRIM163_ITEMID
AND ODRIM210_DELIVERER.ODRIM210_ORGID = VODRIM163_ITEMCOST.VODRIM163_DLVRID
AND ODRD121_RETAPPLYD.ODRD121_SPEC = VODRIM163_ITEMCOST.VODRIM163_SPEC
AND ODRD121_RETAPPLYD.ODRD121_ITEMID = ODRIP011_STOCK.ODRIP011_ITEMID
AND ODRIM210_DELIVERER.ODRIM210_ORGID = ODRIP011_STOCK.ODRIP011_ORGID
AND ODRIM080_STORE.ODRIM080_SVID = ODRIM013_SV.ODRIM013_SVID
AND ODRIM013_SV.ODRIM013_DOID = ODRIM012_DO.ODRIM012_DOID
AND ODRIS020_PARA.ODRIS020_PARACD = ODRD121_RETAPPLYD.ODRD121_RETREASONID
AND ODRD121_RETAPPLYD.ODRD121_CHKFLG = 0
AND '||itmid||' = ODRIM150_ITEM.ODRIM150_ITEMID
AND ODRIM150_ITEM.ODRIM150_ITEMID = ODRIM910_STOREURTL.ODRIM910_ITEMID
AND ODRIM150_ITEM.ODRIM150_ITEMID = ODRIM161_ITEMDLV.ODRIM161_ITEMID
AND ODRIM150_ITEM.ODRIM150_ITEMID = VODRIM163_ITEMCOST.VODRIM163_ITEMID
AND ODRIM150_ITEM.ODRIM150_ITEMID = ODRIP011_STOCK.ODRIP011_ITEMID
AND '||itmid||' = ODRD121_RETAPPLYD.ODRD121_ITEMID
AND ODRIM910_STOREURTL.ODRIM910_STRID = ODRD121_RETAPPLYD.ODRD121_ORGID
AND ODRIM910_STOREURTL.ODRIM910_ITEMID = ODRIM161_ITEMDLV.ODRIM161_ITEMID
AND ODRIM910_STOREURTL.ODRIM910_ITEMID = VODRIM163_ITEMCOST.VODRIM163_ITEMID
AND ODRIM910_STOREURTL.ODRIM910_ITEMID = ODRIP011_STOCK.ODRIP011_ITEMID';
--记录当前记录
timidcount:=timidcount+1;
END IF;
COMMIT;
END LOOP;
CLOSE GetStrView;
--如果当前记录大于0则采用刷新物化视图及情况临时表数据，否不进行该操作
IF timidcount>0 THEN
--使视图得到最新刷新的数据
DBMS_MVIEW.REFRESH('ODRD121_RETAPPLYD_REPORTS_VIEW','C');
--清楚临时表数据
EXECUTE IMMEDIATE 'TRUNCATE TABLE ODRD121_RETAPPLYD_REPORT';
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('查询、插入数据时产生异常!');
END ODRD121_RETAPPLYD_REPORTS;
/
--物化视图创建ODRD121_RETAPPLYD_REPORTS_VIEW创建
CREATE MATERIALIZED VIEW ODRD121_RETAPPLYD_REPORTS_VIEW
REFRESH FORCE ON DEMAND
WITH ROWID
AS
SELECT "ODRD121_RETAPPLYD_REPORT"."ODRD121_ODRWAY_1" "ODRD121_ODRWAY_1","ODRD121_RETAPPLYD_REPORT"."ODRD121_ORGID_2" "ODRD121_ORGID_2","ODRD121_RETAPPLYD_REPORT"."ODRD121_ODRDATE_3" "ODRD121_ODRDATE_3","ODRD121_RETAPPLYD_REPORT"."ODRD121_STFID_4" "ODRD121_STFID_4","ODRD121_RETAPPLYD_REPORT"."ODRD121_ORDERNO_5" "ODRD121_ORDERNO_5","ODRD121_RETAPPLYD_REPORT"."ODRD121_LINENO_6" "ODRD121_LINENO_6","ODRD121_RETAPPLYD_REPORT"."ODRD121_ITEMID_7" "ODRD121_ITEMID_7","ODRD121_RETAPPLYD_REPORT"."ODRD121_PLUCD_8" "ODRD121_PLUCD_8","ODRD121_RETAPPLYD_REPORT"."ODRD121_SPEC_9" "ODRD121_SPEC_9","ODRD121_RETAPPLYD_REPORT"."ODRD121_TRIP_10" "ODRD121_TRIP_10","ODRD121_RETAPPLYD_REPORT"."ODRD121_PACKQTY_11" "ODRD121_PACKQTY_11","ODRD121_RETAPPLYD_REPORT"."ODRD121_UNITQTY_12" "ODRD121_UNITQTY_12","ODRD121_RETAPPLYD_REPORT"."ODRD121_ENTDATE_13" "ODRD121_ENTDATE_13","ODRD121_RETAPPLYD_REPORT"."ODRD121_RETREASONID_14" "ODRD121_RETREASONID_14","ODRD121_RETAPPLYD_REPORT"."ODRD121_GENDATE_15" "ODRD121_GENDATE_15","ODRD121_RETAPPLYD_REPORT"."ODRD121_HQPACKQTY_16" "ODRD121_HQPACKQTY_16","ODRD121_RETAPPLYD_REPORT"."ODRD121_HQUNITQTY_17" "ODRD121_HQUNITQTY_17","ODRD121_RETAPPLYD_REPORT"."ODRD121_CHKFLG_18" "ODRD121_CHKFLG_18","ODRD121_RETAPPLYD_REPORT"."ODRD121_CHKSTFID_19" "ODRD121_CHKSTFID_19","ODRD121_RETAPPLYD_REPORT"."ODRD121_CHKDATE_20" "ODRD121_CHKDATE_20","ODRD121_RETAPPLYD_REPORT"."ODRD121_SNDFLG_21" "ODRD121_SNDFLG_21","ODRD121_RETAPPLYD_REPORT"."ODRD121_SNDDATE_22" "ODRD121_SNDDATE_22","ODRD121_RETAPPLYD_REPORT"."ODRD121_HEADFLG_23" "ODRD121_HEADFLG_23","ODRD121_RETAPPLYD_REPORT"."ODRIM150_ITEMID_24" "ODRIM150_ITEMID_24","ODRD121_RETAPPLYD_REPORT"."ODRIM150_PLUCD_25" "ODRIM150_PLUCD_25","ODRD121_RETAPPLYD_REPORT"."ODRIM150_ITEMCNM_26" "ODRIM150_ITEMCNM_26","ODRD121_RETAPPLYD_REPORT"."VODRIM163_ITEMID_27" "VODRIM163_ITEMID_27","ODRD121_RETAPPLYD_REPORT"."VODRIM163_DLVRID_28" "VODRIM163_DLVRID_28","ODRD121_RETAPPLYD_REPORT"."VODRIM163_SPEC_29" "VODRIM163_SPEC_29","ODRD121_RETAPPLYD_REPORT"."VODRIM163_SDATE_30" "VODRIM163_SDATE_30","ODRD121_RETAPPLYD_REPORT"."VODRIM163_UNIT_31" "VODRIM163_UNIT_31","ODRD121_RETAPPLYD_REPORT"."ODRIM910_URTLGRS_32" "ODRIM910_URTLGRS_32","ODRD121_RETAPPLYD_REPORT"."ODRIM910_URTLNET_33" "ODRIM910_URTLNET_33","ODRD121_RETAPPLYD_REPORT"."ODRIP011_ITEMID_34" "ODRIP011_ITEMID_34","ODRD121_RETAPPLYD_REPORT"."ODRIP011_ORGID_35" "ODRIP011_ORGID_35","ODRD121_RETAPPLYD_REPORT"."ODRIP011_QTY_36" "ODRIP011_QTY_36","ODRD121_RETAPPLYD_REPORT"."ODRIM210_DLVRID_37" "ODRIM210_DLVRID_37","ODRD121_RETAPPLYD_REPORT"."ODRIM210_DLVRCD_38" "ODRIM210_DLVRCD_38","ODRD121_RETAPPLYD_REPORT"."ODRIM210_DLVRCNM_39" "ODRIM210_DLVRCNM_39","ODRD121_RETAPPLYD_REPORT"."ODRIM080_STRID_40" "ODRIM080_STRID_40","ODRD121_RETAPPLYD_REPORT"."ODRIM080_STRCD_41" "ODRIM080_STRCD_41","ODRD121_RETAPPLYD_REPORT"."ODRIM080_SVID_42" "ODRIM080_SVID_42","ODRD121_RETAPPLYD_REPORT"."ODRIM080_ORGID_43" "ODRIM080_ORGID_43","ODRD121_RETAPPLYD_REPORT"."ODRIM080_STRCNM_44" "ODRIM080_STRCNM_44","ODRD121_RETAPPLYD_REPORT"."ODRIM013_SVID_45" "ODRIM013_SVID_45","ODRD121_RETAPPLYD_REPORT"."ODRIM013_SVCD_46" "ODRIM013_SVCD_46","ODRD121_RETAPPLYD_REPORT"."ODRIM013_DOID_47" "ODRIM013_DOID_47","ODRD121_RETAPPLYD_REPORT"."ODRIM012_DOID_48" "ODRIM012_DOID_48","ODRD121_RETAPPLYD_REPORT"."ODRIM012_DOCD_49" "ODRIM012_DOCD_49","ODRD121_RETAPPLYD_REPORT"."ODRIM161_ITEMID_50" "ODRIM161_ITEMID_50","ODRD121_RETAPPLYD_REPORT"."ODRIM161_DLVRID_51" "ODRIM161_DLVRID_51","ODRD121_RETAPPLYD_REPORT"."ODRIS020_PARACD_52" "ODRIS020_PARACD_52","ODRD121_RETAPPLYD_REPORT"."ODRIS020_PARAVALUE_53" "ODRIS020_PARAVALUE_53" FROM "ODRD121_RETAPPLYD_REPORT" "ODRD121_RETAPPLYD_REPORT";
转载地址：http://edu.codepub.com/2009/1029/17066.php
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
