Oracle的VIEW UPDATE怎么使用

Question

我有下面一段SQL文，想要修改成用VIEW UPDATE做 请高手帮忙 指点一下MERGE INTO dlvr_date_mng_hdr ddh
        USING(
              --SQL[5-1-1]
              SELECT
                    iv.dlvr_date_mng_num dlvr_date_mng_num
                  , iv.dlvr_dely_day     dlvr_dely_day
                  , iv.dlvr_dely_qty     dlvr_dely_qty
                  , gdm.gnrl_cd          gnrl_cd
              FROM 
                  (
                   --SQL[5-1-2]
                   SELECT 
                         dpw.dlvr_date_mng_num     dlvr_date_mng_num
                       , dpw.dlvr_dely_day         dlvr_dely_day
                       , dpw.dlvr_dely_qty         dlvr_dely_qty
                       , dpw.dd_mng_mlstn_actl_flg dd_mng_mlstn_actl_flg
                   FROM 
                       dlvr_date_mng_prd_wk dpw
                   WHERE
                           dpw.dd_mng_mlstn_prcs_trgt_flg = CN_flag_y
                       AND dpw.delete_flag                = CN_flag_n
                   UNION ALL
                   SELECT 
                         dlvr_date_mng_num     dlvr_date_mng_num
                       , dlvr_dely_day         dlvr_dely_day
                       , dlvr_dely_qty         dlvr_dely_qty
                       , dd_mng_mlstn_actl_flg dd_mng_mlstn_actl_flg
                   FROM 
                       (
                        --SQL[5-1-3]
                        SELECT 
                              dtw.dlvr_date_mng_num                                                                             dlvr_date_mng_num
                            , MAX(dtw.dlvr_dely_day)                                                                            dlvr_dely_day
                            , SUM(dtw.dlvr_dely_qty)                                                                            dlvr_dely_qty
                            , DECODE(COUNT(DISTINCT dtw.dd_mng_mlstn_actl_flg ), 2, CN_flag_n, MIN(dtw.dd_mng_mlstn_actl_flg) ) dd_mng_mlstn_actl_flg
                        FROM 
                            dlvr_date_mng_trnpt_wk dtw
                        WHERE 
                                dtw.dd_mng_mlstn_prcs_trgt_flg = CN_flag_y
                            AND dtw.delete_flag                = CN_flag_n
                        GROUP BY
                            dtw.dlvr_date_mng_num
                        )
                   ) iv
                  , gnrl_cd_dtl_m gdm
                  , dlvr_date_mng_hdr dmh
                  , gnrl_cd_dtl_m gdmw
              WHERE
                      iv.dlvr_date_mng_num = dmh.dlvr_date_mng_num
                  AND gdm.delete_flag      = CN_flag_n
                  AND gdm.gnrl_cd_type_cd  = CN_gnrl_type_cd2
                  AND gdm.gnrl_cd_atrbt1   = CN_flag_y
                  AND gdm.gnrl_cd_atrbt2   = iv.dd_mng_mlstn_actl_flg
                  AND (
                          gdm.gnrl_cd_atrbt3 IS NULL 
                       OR gdm.gnrl_cd_atrbt3 = NVL(gdmw.gnrl_cd_atrbt3, CN_flag_n)
                       )
                  AND gdmw.delete_flag     = CN_flag_n
                  AND gdmw.gnrl_cd_type_cd = CN_gnrl_type_cd2
                  AND gdmw.gnrl_cd         = dmh.dlvr_dely_alrt_type_cd
             ) ddmh 
        ON(
                ddh.dlvr_date_mng_num      = ddmh.dlvr_date_mng_num(+)
            AND ddh.glbl_bu_cd             = I_glbl_bu_cd
            AND ddh.delete_flag            = CN_flag_n
            AND ddh.dlvr_date_mng_updt_flg = CN_flag_y
           )
        WHEN MATCHED THEN
        UPDATE
        SET 
              ddh.dlvr_dely_day          = NVL(ddmh.dlvr_dely_day, CN_zero)
            , ddh.dlvr_dely_qty          = NVL(ddmh.dlvr_dely_qty, CN_zero)
            , ddh.dlvr_dely_alrt_type_cd = NVL(ddmh.gnrl_cd, CN_alert_onshce)
            , ddh.update_user_id         = CN_user_id
            , ddh.update_date            = SYSDATE;

Answer 1

活到老学到老， view update 是神马？

Answer 2

我也是才听说，据说 是先把数据库查出来的结果放到VIEW里，之后更新一下这个VIEW，再更新到数据库表里，我也不是很明白，不知道该如何写，希望高手指点一下。

Answer 3

视图更新方式不可以更新你这种的如果用视图方式更新，假设你要使用表B更新表A，则你必须在B上关联字段建唯一索引或主键索引，而你此处却是一个联合查询结果，行不通的

Answer 4

视图如果包含 union，union all, intersect，minus ,distinct,group by,order by,connect by,start with...都是不能更新的，因为视图都是依赖基表存在的，所以存在很多约束都是不能用更新视图来更新基表的，譬如外键约束等

Answer 5

晕，LZ是要按视图方式更新他dlvr_date_mng_hdr表中的数据，不是更新视图，两个概念

Answer 6

恩 我是要按视图方式更新表中的数据，那我上面的SQL文应该如何修改呢，请高手指点

Answer 7

你的用view更新是不可以的举个用view更新的例子SQL> create table t1(
  2  id int,
  3  name varchar2(20),
  4  qty number(3)
  5  );表已创建。SQL> create table t2(
  2  id int,
  3  name varchar2(20),
  4  qty number(3)
  5  );表已创建。SQL> insert into t1 values(1, '',0);已创建 1 行。SQL> insert into t1 values(2, '',0);已创建 1 行。SQL> insert into t2 values(1, 'name1',20);已创建 1 行。SQL> insert into t2 values(2, 'name2',20);已创建 1 行。/*此处须建立唯一索引或主键，不然会有错，可自行试下*/SQL> alter table t2 add constraint uk_t2_id unique (id);表已更改。SQL> update (select t1.name name1,t1.qty qty1,t2.name name2,t2.qty qty2 from t1,t2 where t1.id=t2.id)
  2  set name1=name2,qty1=qty2;已更新2行。SQL> select * from t1;        ID NAME                                            QTY                  
---------- ---------------------------------------- ----------                  
         1 name1                                            20                  
         2 name2                                            20